Oracle Backend for Trac
Summary
Oracle database backend needed for Trac. Because there is no such one, I decided to create it. Finally, development of this backend deadlocked because there is a lot of unsolvable or intractable issues. Although, some work performed and, maybe, it will be helpful for other people who will attempt to port Trac to Oracle.
Source of the oracle_backend.py is created according on mysql_backend.py by similarity.
More information about Trac DB backens could be found here:
- trac.edgewall.org/wiki/DatabaseBackend
Solution
The backend is based on cx_Oracle Python extension module. It has different implementations for different versions of Oracle client software. The extension module could be obtained here: cx-oracle.sourceforge.net/
File attached to this ticked contains patch against Trac distribution, version, which could be obtained from Trac Download Page, or by this direct link:
- ftp.edgewall.com/pub/trac/Trac-0.11.4.tar.gz
Problems
There are several Oracle-specific issues, which hindered backend development.
Lack of Autoincrement type
There is no built-in "Autoincrement" type in Oracle. Thus additional workarounds became required to simulate it, based on approach described here: jen.fluxcapacitor.net/geek/autoincr.html. A Sequence and a Trigger is added for table having autoincrement field:
CREATE SEQUENCE seq_<<TABLENAME>> START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER trig_<<TABLENAME>>_autoinc BEFORE INSERT ON report
FOR EACH ROW
BEGIN
SELECT seq_<<TABLENAME>>.nextval INTO :new.<<COLNAME>> FROM DUAL;
END;
CLOB columns in Indexes not supported
So, all columns participated in index creation are created of type VARCHAR(4000). This adds limitation on length of such columns. Because indexed columns usually have little amount of data, this limitation is not significant in practicle.
CLOB can't be in PK's and length of PK is lilmited
PK columns not only must be created of type VARCHAR instead of CLOB, but their total length must be bounded. This limitation is not significant in practicle, as well.
Different syntax of parametrized SQL
Syntax of parameters in cx_Oracle is different -- ":1, :2, :3" instead of "%s, %s, %s". Therefore a special code is required to translate them:
# Replace %s placeholders with :1, :2, :3...
c = 0
s = stmt
for i in re.finditer("(?<!%)%[sd]", s):
c+=1
s = s[:i.start()] + (":%d" % c) + s[i.end():]
Also, the parameters should be passed strictly as list,
but in source of Trac often tuple is used. So,
cx_Oracle.Cursor was replaced with
OracleCursor to convert parameters to list explicitly.
Lack of LIMIT/OFFSET keywords in queries
There is no LIMIT and OFFSET keywords in Oracle -- "rownum" special column is used instead. So, all SQL queries, where these keywords present, must be wrapped in additional SELECT, filtered by "rownum":
SELECT * FROM (SELECT .....)
WHERE rownum BETWEEN <<OFFSET>> AND (<<OFFSET>> + <<LIMIT>>)
Oracle keywords as Table/Column names
In Oracle following names are keywords and could not be used for naming Tables and Columns:
All places in Trac source code where these names were used have had to change with acceptable names!
Parameter size limitation
Parameters, passed to cx_Oracle.execute() method are limited in length. This method could not be used to INSERT or UPDATE rows with long-data CLOB columns. Actually, following script raised error when initializing database:
INSERT INTO wiki(version,name,time,author,ipnr,text)
SELECT 1+COALESCE(max(version),0),:1,:2,
'trac','127.0.0.1',:3 -- :3 contains long text!
FROM wiki WHERE name=:4
I could not find a reasonable solution for this issue and gave up! It remained only to wait until ORM approach implemented in Trac... But if anybody decides to continue working on Oracle backend, please, feel free to use this stuff.
David Avsajanishvili
