﻿id,summary,reporter,owner,description,type,status,priority,component,severity,resolution,keywords,cc,release 5487,Work on Oracle backend using cx_Oracle,avsd,anybody,"= 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 [http://trac.edgewall.org/wiki/TracDownload 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: {{{ #!sql CREATE SEQUENCE seq_<> START WITH 1 INCREMENT BY 1 NOMAXVALUE; CREATE TRIGGER trig_<>_autoinc BEFORE INSERT ON report FOR EACH ROW BEGIN SELECT seq_<>.nextval INTO :new.<> 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: {{{ #!python # Replace %s placeholders with :1, :2, :3... c = 0 s = stmt for i in re.finditer(""(?> AND (<> + <>) }}} === 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: {{{ #!sql INSERT INTO wiki(version,name,time,author,ipnr,text) SELECT 1+COALESCE(max(version),0),:1,:2, 'trac','',:3 -- :3 contains long text! FROM wiki WHERE name=:4 }}} {{{ #!html 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. }}} ''[mailto:avsd05@gmail.com David Avsajanishvili]''",task,new,normal,Request-a-Hack,normal,,database db backend oracle,avsd,0.11