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