Work on Oracle backend using cx_Oracle

[David Avsajanishvili]

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:

• size
• session
• comment

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

This deadlocked development! 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

[David Avsajanishvili]

Patch against Trac distribution, version 0.11.4 for supporting Oracle

[Ryan J Ollos]

This might be more appropriate for Edgewall: ​http://trac.edgewall.org/

[Richard Lyders]

true, you can not insert a new CLOB directly into a table using INSERT in Oracle (I deal with Oracle 9iR2)... but you can UPDATE a CLOB directly ....

so just create the record with a NULL CLOB (use EMPTY_BLOB())

and then update the CLOB using UPDATE...

see an example here: arjudba.blog spot.com/2008/06/how-to-insert-blob-dataimage-video-into.html