Subject: Making OTRS play nice with Oracle
From: Kristoffer Gleditsch <[email-masked]>
Date: Mon, 15 Mar 2004 12:31:30 +0100
Newsgroups: gmane.comp.otrs.devel

Hi!

I'm setting up OTRS 1.2.2, and I need it to talk to an Oracle
database.  It seems to work OK now, so I thought I'd share the
modifications I needed to make.  These patches are against CVS as of
right now, and it seems to work fine so far:

- The XML database schema seems to have some name collisions and
  typos.  Also, 'session' is a reserved word in Oracle, and
  tables/indexes can't have names of more than 32 characters.  The
  following patch fixes some duplicate names, shortens some index
  names and renames 'session' to 'sessions':

Index: scripts/database/otrs-schema.xml
===================================================================
RCS file: /home/cvs/otrs/scripts/database/otrs-schema.xml,v
retrieving revision 1.20
diff -u -r1.20 otrs-schema.xml
--- scripts/database/otrs-schema.xml	2 Feb 2004 23:02:00 -0000	1.20
+++ scripts/database/otrs-schema.xml	12 Mar 2004 13:52:34 -0000
@@ -594,7 +594,7 @@
   <column name="create_by" required="true" type="INTEGER"/>
   <column name="change_time" required="true" type="DATE"/>
   <column name="change_by" required="true" type="INTEGER"/>
-  <index name="article_id">
+  <index name="article_plain_article_id">
     <index-column name="article_id"/>
   </index>
 </table>
@@ -727,20 +727,20 @@
   <column name="create_by" required="true" type="INTEGER"/>
   <column name="change_time" required="true" type="DATE"/>
   <column name="change_by" required="true" type="INTEGER"/>
-  <index name="index_ticket_id">
+  <index name="index_t_acc_ticket_id">
     <index-column name="ticket_id"/>
   </index>
 </table>
 
-<!-- session -->
-<table name="session">
-  <column name="session_id" required="true" size="120" type="VARCHAR"/>
+<!-- sessions -->
+<table name="sessions">
+  <column name="sessions_id" required="true" size="120" type="VARCHAR"/>
   <column name="value" required="true" type="LONGVARCHAR"/>
   <unique>
-    <unique-column name="session_id"/>
+    <unique-column name="sessions_id"/>
   </unique>
-  <index name="index_session_id">
-    <index-column name="session_id"/>
+  <index name="index_sessions_id">
+    <index-column name="sessions_id"/>
   </index>
 </table>
 
@@ -761,7 +761,7 @@
 <!-- ticket_lock_index -->
 <table name="ticket_lock_index">
   <column name="ticket_id" required="true" type="BIGINT"/>
-  <index name="index_ticket_id">
+  <index name="ticket_lock_id_index">
     <index-column name="ticket_id"/>
   </index>
 </table>
@@ -792,7 +792,7 @@
   <column name="user_id" required="true" size="250" type="VARCHAR"/>
   <column name="preferences_key" required="true" size="150" type="VARCHAR"/>
   <column name="preferences_value" required="false" size="250" type="VARCHAR"/>
-  <index name="index_customer_preferences_user_id">
+  <index name="index_customer_prefs_user_id">
     <index-column name="user_id"/>
   </index>
 </table>
@@ -801,10 +801,10 @@
 <table name="ticket_loop_protection">
   <column name="sent_to" required="true" size="250" type="VARCHAR"/>
   <column name="sent_date" required="true" size="150" type="VARCHAR"/>
-  <index name="index_ticket_loop_protection_sent_to">
+  <index name="index_ticket_l_prot_sent_to">
     <index-column name="sent_to"/>
   </index>
-  <index name="index_ticket_loop_protection_sent_date">
+  <index name="index_ticket_l_prot_sent_date">
     <index-column name="sent_date"/>
   </index>
 </table>

- This patch updates the values in Kernel/Config/Defaults.pm to
  reflect the renaming of the session table:

Index: Kernel/Config/Defaults.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/Config/Defaults.pm,v
retrieving revision 1.115
diff -u -r1.115 Defaults.pm
--- Kernel/Config/Defaults.pm	5 Mar 2004 08:37:54 -0000	1.115
+++ Kernel/Config/Defaults.pm	12 Mar 2004 13:56:05 -0000
@@ -950,9 +950,9 @@
     # SessionTable*
     # (just needed if $Self->{SessionModule}='Kernel::System::AuthSession::DB)  
     # SessionTable 
-    $Self->{SessionTable} = 'session';
+    $Self->{SessionTable} = 'sessions';
     # SessionTable id column
-    $Self->{SessionTableID} = 'session_id';
+    $Self->{SessionTableID} = 'sessions_id';
     # SessionTable value column
     $Self->{SessionTableValue} = 'value';

- This patch to Kernel/System/AuthSession/DB.pm does the same:

Index: Kernel/System/AuthSession/DB.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/System/AuthSession/DB.pm,v
retrieving revision 1.14
diff -u -r1.14 DB.pm
--- Kernel/System/AuthSession/DB.pm	13 Feb 2004 00:50:36 -0000	1.14
+++ Kernel/System/AuthSession/DB.pm	12 Mar 2004 14:03:49 -0000
@@ -39,7 +39,7 @@
     $Self->{Debug} = 0;    
     # session table data
     $Self->{SQLSessionTable} = $Self->{ConfigObject}->Get('SessionTable') 
-     || 'session';
+     || 'sessions';
     # id row
     $Self->{SQLSessionTableID} = $Self->{ConfigObject}->Get('SessionTableID') 
      || 'session_id';

- The following patch to Kernel/System/DB.pm fixes a typo
  ('QuoteSignle' -> 'QuoteSingle').

Index: Kernel/System/DB.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/System/DB.pm,v
retrieving revision 1.38
diff -u -b -B -r1.38 DB.pm
--- Kernel/System/DB.pm	29 Feb 2004 19:09:13 -0000	1.38
+++ Kernel/System/DB.pm	15 Mar 2004 10:50:10 -0000
@@ -120,7 +120,7 @@
     if ($Self->{'DB::Type'} eq 'mysql') {
         $Self->{'DB::Limit'} = 'limit';
         $Self->{'DB::DirectBlob'} = 1;
-        $Self->{'DB::QuoteSignle'} = '\\';
+        $Self->{'DB::QuoteSingle'} = '\\';
         $Self->{'DB::QuoteBack'} = '\\';
         $Self->{'DB::QuoteSemicolon'} = '\\';
         $Self->{'DB::Attribute'} = {};
@@ -128,7 +128,7 @@
     elsif ($Self->{'DB::Type'} eq 'postgresql') {
         $Self->{'DB::Limit'} = 'limit';
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\\';
+        $Self->{'DB::QuoteSingle'} = '\\';
         $Self->{'DB::QuoteBack'} = '\\';
         $Self->{'DB::QuoteSemicolon'} = '\\';
         $Self->{'DB::Attribute'} = {};
@@ -136,7 +136,7 @@
     elsif ($Self->{'DB::Type'} eq 'oracle') {
         $Self->{'DB::Limit'} = 0;
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\'';
+        $Self->{'DB::QuoteSingle'} = '\'';
         $Self->{'DB::QuoteBack'} = 0;
         $Self->{'DB::QuoteSemicolon'} = '';
         $Self->{'DB::Attribute'} = {
@@ -147,7 +147,7 @@
     elsif ($Self->{'DB::Type'} eq 'db2') {
         $Self->{'DB::Limit'} = 'fetch';
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\\';
+        $Self->{'DB::QuoteSingle'} = '\\';
         $Self->{'DB::QuoteBack'} = '\\';
         $Self->{'DB::QuoteSemicolon'} = '\\';
         $Self->{'DB::Attribute'} = {};
@@ -155,7 +155,7 @@
     elsif ($Self->{'DB::Type'} eq 'sapdb') {
         $Self->{'DB::Limit'} = 0;
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\'';
+        $Self->{'DB::QuoteSingle'} = '\'';
         $Self->{'DB::QuoteBack'} = 0;
         $Self->{'DB::QuoteSemicolon'} = '\'';
         $Self->{'DB::Attribute'} = {
@@ -167,7 +167,7 @@
     elsif ($Self->{'DB::Type'} eq 'mssql') {
         $Self->{'DB::Limit'} = 0;
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\'';
+        $Self->{'DB::QuoteSingle'} = '\'';
         $Self->{'DB::QuoteBack'} = 0;
         $Self->{'DB::QuoteSemicolon'} = '\'';
         $Self->{'DB::Attribute'} = {
@@ -178,7 +178,7 @@
     elsif ($Self->{'DB::Type'} eq 'generic') {
         $Self->{'DB::Limit'} = 0;
         $Self->{'DB::DirectBlob'} = 0;
-        $Self->{'DB::QuoteSignle'} = '\\';
+        $Self->{'DB::QuoteSingle'} = '\\';
         $Self->{'DB::QuoteBack'} = '\\';
         $Self->{'DB::QuoteSemicolon'} = '\\';
         $Self->{'DB::Attribute'} = {
@@ -285,8 +286,8 @@
     if ($Self->{'DB::QuoteBack'}) {
         $Text =~ s/\\/$Self->{'DB::QuoteBack'}\\/g;
     }
-    if ($Self->{'DB::QuoteSignle'}) {
-        $Text =~ s/'/$Self->{'DB::QuoteSignle'}'/g;
+    if ($Self->{'DB::QuoteSingle'}) {
+        $Text =~ s/'/$Self->{'DB::QuoteSingle'}'/g; #' dont mess up my perl mode
     }
     if ($Self->{'DB::QuoteSemicolon'}) {
         $Text =~ s/;/$Self->{'DB::QuoteSemicolon'};/g;

  (I had some other patches to that file as well, but someone fixed
  them after the 1.2.2 release.  Drat!  :)

- This patch to scripts/database/initial_insert.sql adds a (commented
  out) Oracle statement to disable substition variable scanning, which
  created problems for me when I piped it through sqlplus.  It also
  changes the "empty answer" in standard_response from '' to ' ',
  since '' violates the NOT NULL constraint in Oracle:

Index: scripts/database/initial_insert.sql
===================================================================
RCS file: /home/cvs/otrs/scripts/database/initial_insert.sql,v
retrieving revision 1.22
diff -u -b -B -r1.22 initial_insert.sql
--- scripts/database/initial_insert.sql	11 Mar 2004 14:39:44 -0000	1.22
+++ scripts/database/initial_insert.sql	15 Mar 2004 10:53:46 -0000
@@ -176,6 +176,14 @@
 -- did not receive this file, see http://www.gnu.org/licenses/gpl.txt.
 -- --
 
+-- --
+-- If you use Oracle and shove this file through sql*plus, you might
+-- want to set this, to disable the substition mechanism which is
+-- otherwise triggered by some of the notification strings:
+-- --
+-- set define off;
+-- --
+
 -- table valid
 INSERT INTO valid
     (name, create_by, create_time, change_by, change_time)
@@ -612,7 +620,7 @@
 INSERT INTO standard_response
         (name, text, valid_id, create_by, create_time, change_by, change_time)
         VALUES
-        ('empty answer', '', 1, 1, current_timestamp, 1, current_timestamp);
+        ('empty answer', ' ', 1, 1, current_timestamp, 1, current_timestamp);
 INSERT INTO standard_response
         (name, text, valid_id, create_by, create_time, change_by, change_time)
         VALUES

- Oracle's lack of something resembling PostgreSQL's SERIAL datatype
  was a bit of a challenge.  Someone pointed me at
  http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=rule-210694185806%4017.1.7.156
  , which shows how to emulate it with triggers.  It should be
  possible to have the torque schemas generate such triggers, but
  since it doesn't, I adapted the SQL file by hand to include such
  triggers for all the ID fields using autoincrement in the XML file:

I just upgraded my test installation to CVS with these patches
applied, and things seem to work OK.

Regards,
-- 
Kristoffer.