I completed my first try to get OTRS with Oracle working.
Problem: Oracle text fields do have a 4000 char limit (VARCHAR2(4000)). With a CLOB (character Large OBject) you can avoid this limitation but the column then is NOT searchable NOR indexable.
To be able to put a LOB, you have to do a normal INSERT and then reSELECT the row FOR UPDATE. To achieve this you need a clear SELECT criterium. Because ticketid is triggered you cannot use this, so I also inserted 5 lines so that articles without message id do get a unique one.
My path was this one:
a) create a ORADoLob method as a substitute for the Do-Method in System/DB.pm.
This method gets called when an INSERT involving LOBs is to be done.
b) Change the tables so that there is a 4000 char-short-column for the lob
field (with an index to search) and the LOB itself (containing the full
content).
c) Change Ticket/Article.pm and Ticket/ArticleStorageDB.pm.
d) Create a modified sql schema (with triggers for the missing
auto_increment or serial feature of oracle)
e) Create a modified initial insert script.
To use it:
1) Create a tablespace for OTRS. 2) Create an Oracle User "otrs" and give him the tablespace. 3) Install Oracle Client on your OTRS machine. You need the Oracle Caller Interface 8 (C-Client) and the sqlplus program. Don't forget to set ORACLE_HOME! 4) Install DBD::Oracle 5) Gunzip and untar the attachment
It contains:
-rw-r--r-- pascal/staff 12789 2004-06-08 10:41:03 otrs-oracle.patch -rw-r--r-- pascal/staff 42343 2004-06-08 10:12:56 otrs-schema.oracle.sql -rw-r--r-- pascal/staff 47467 2004-06-08 10:12:12 initial_insert.oracle
6) Run sqlplus to apply initial scripts:
sqlplus otrs/[EMAIL PROTECTED] < otrs-schema.oracle.sql
Errors complaining about unexisting sequences are harmless. I included the DROPs to be able to call the script more than once.
Then type:
sqlplus otrs/[EMAIL PROTECTED] < initial_insert.oracle
7) Apply patches to otrs:
cd /your/path/to/otrs patch -p1 < /where/you/have/your/otrs-oracle.patch
Result should be: patching file Kernel/System/DB.pm patching file Kernel/System/Ticket/Article.pm patching file Kernel/System/Ticket/ArticleStorageDB.pm
8) Modify System/Config.pm to reflect your Oracle Setup (use the oracle example).
9) Start Apache and Perl module (if existent). Happy ride!
Known problems:
- In System/DB.pm, there is a line:
LongReadLen => 1000*1024,
When retrieving LOBs via SELECT, then the maximum number of bytes returned per column is defined by this Parameter. So at the moment, an attachment with 1 MB base64-coded is the maximum- longer ones get truncated (without error). You may make the value higher but keep in mind that - when using the perl module for Apache - Apache eats more RAM when you set the value higher. It is also possible to put binary attachments into BLOBs (Binary Large OBjects) but they are quite uncomfortable to handle and they are not retrieveable by sqlplus for test runs - so I stuck with base64-encodings and CLOBs.
- I used LOBs only for article storage and attachments. FAQs are still limited to 4000 chars per field.
TODO:
- Modules/AgentTicketPrint.pm DOES NOT WORK - You can not GROUP BY a LOB. I work on this. - You can't do a full body search at the moment - I am working on this too.
Patches are attached. They are provided without warranty.
Patches are done against Version 1.2.3 as I don't possess the CVS version nor CVS access...
I hope it is helpful for some people ;-)
Yours, Pascal Gienger
otrs-oracle.gz
Description: GNU Zip compressed data_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
