id,summary,reporter,owner,description,type,status,priority,component,severity,resolution,keywords,cc,release
5487,Work on Oracle backend using cx_Oracle,David Avsajanishvili,anybody,= Oracle Backend for Trac =\r\n\r\n== Summary ==\r\n\r\nOracle 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.\r\n\r\nSource of the {{{oracle_backend.py}}} is created according on {{{mysql_backend.py}}} by similarity.\r\n\r\nMore information about Trac DB backens could be found here:\r\n\r\n * trac.edgewall.org/wiki/DatabaseBackend\r\n\r\n== Solution ==\r\n\r\nThe 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/\r\n\r\nFile 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:\r\n\r\n * ftp.edgewall.com/pub/trac/Trac-0.11.4.tar.gz\r\n\r\n== Problems ==\r\n\r\nThere are several Oracle-specific issues_ which hindered backend development.\r\n\r\n=== Lack of Autoincrement type ===\r\n\r\nThere 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:\r\n\r\n{{{\r\n#!sql\r\nCREATE SEQUENCE seq_<<TABLENAME>> START WITH 1 INCREMENT BY 1 NOMAXVALUE;\r\n\r\nCREATE TRIGGER trig_<<TABLENAME>>_autoinc BEFORE INSERT ON report\r\nFOR EACH ROW\r\nBEGIN\r\n    SELECT seq_<<TABLENAME>>.nextval INTO :new.<<COLNAME>> FROM DUAL;\r\nEND;\r\n\r\n}}}\r\n\r\n\r\n=== CLOB columns in Indexes not supported ===\r\n\r\nSo_ 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.\r\n\r\n=== CLOB can't be in PK's and length of PK is lilmited ===\r\n\r\nPK 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.\r\n\r\n=== Different syntax of parametrized SQL ===\r\n\r\nSyntax of parameters in cx_Oracle is different -- ":1_ :2_ :3" instead of "%s_ %s_ %s". Therefore a special code is required to translate them:\r\n\r\n{{{\r\n#!python\r\n# Replace %s placeholders with :1_ :2_ :3...\r\nc = 0\r\ns = stmt\r\nfor i in re.finditer("(?<!%)%[sd]"_ s):\r\n    c+=1\r\n    s = s[:i.start()] + (":%d" % c) + s[i.end():]\r\n}}}\r\n\r\nAlso_ the parameters should be passed strictly as {{{list}}}_\r\nbut in source of Trac often {{{tuple}}} is used. So_\r\n{{{cx_Oracle.Cursor}}} was replaced with\r\n{{{OracleCursor}}} to convert parameters to {{{list}}} explicitly.\r\n\r\n=== Lack of LIMIT/OFFSET keywords in queries ===\r\n\r\nThere 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":\r\n\r\n{{{\r\n#!sql\r\nSELECT * FROM (SELECT .....)\r\nWHERE rownum BETWEEN <<OFFSET>> AND (<<OFFSET>> + <<LIMIT>>)\r\n}}}\r\n\r\n=== Oracle keywords as Table/Column names ===\r\n\r\nIn Oracle following names are keywords and could not be used for naming Tables and Columns:\r\n\r\n * size\r\n * session\r\n * comment\r\n\r\nAll places in Trac source code where these names were used have had to change with acceptable names!\r\n\r\n=== Parameter size limitation ===\r\n\r\nParameters_ 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:\r\n\r\n{{{\r\n#!sql\r\nINSERT INTO wiki(version_name_time_author_ipnr_text)\r\nSELECT 1+COALESCE(max(version)_0)_:1_:2_\r\n       'trac'_'127.0.0.1'_:3 -- :3 contains long text!\r\nFROM wiki  WHERE name=:4\r\n}}}\r\n\r\n{{{\r\n#!html\r\n<span style="font-size:larger; color: red; font-weight:bold">This deadlocked development!</span>\r\nI 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.\r\n}}}\r\n\r\n''[mailto:avsd05@gmail.com David Avsajanishvili]'',task,new,normal,Request-a-Hack,normal,,database db backend oracle,David Avsajanishvili <avsd05@gmail.com>,0.11