Modify

Opened 15 years ago

Last modified 14 years ago

#5487 new task

Work on Oracle backend using cx_Oracle

Reported by: David Avsajanishvili Owned by: anybody
Priority: normal Component: Request-a-Hack
Severity: normal Keywords: database db backend oracle
Cc: David Avsajanishvili Trac Release: 0.11

Description

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

Attachments (1)

Trac-0.11.4-OracleBackend.patch (31.0 KB) - added by David Avsajanishvili 15 years ago.
Patch against Trac distribution, version 0.11.4 for supporting Oracle

Download all attachments as: .zip

Change History (3)

Changed 15 years ago by David Avsajanishvili

Patch against Trac distribution, version 0.11.4 for supporting Oracle

comment:1 Changed 15 years ago by Ryan J Ollos

Component: SELECT A HACKRequest-a-Hack
Owner: changed from anonymous to anybody

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

comment:2 Changed 14 years ago by 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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The owner will remain anybody.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.