On Monday, March 15, 2004 12:31 PM
Kristoffer Gleditsch
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:
I think you've doubled existing work here, although it was our fault: We already have a working Oracle solution iirc, but somehow the SQL code must have been left outside CVS and therefore isn't included in any package. Sorry for that.
- 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': - This patch updates the values in Kernel/Config/Defaults.pm to reflect the renaming of the session table: - This patch to Kernel/System/AuthSession/DB.pm does the same:
This wasn't an issue in our Oracle 9.2.1 release. Which Oracle version are you using?
- The following patch to Kernel/System/DB.pm fixes a typo ('QuoteSignle' -> 'QuoteSingle').
Thanks.
- 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:
Again, the latter hasn't been an issue so far. Thanks.
- 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-21 0694185806%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:
We will compare your solution with the one we have, thanks. It will take some time, though, but we'll come back if there are unsolvable diffs or things like that. Sorry again for the unconvenience we caused.
I just upgraded my test installation to CVS with these patches applied, and things seem to work OK.
Nice to hear.
On Monday, March 15, 2004 1:16 PM
Kristoffer Gleditsch
Oops, posting through Gmane didn't work too well. Let's see if it works better now. (It might be a good idea to mention on the mailing list web page that you can't post to the dev list without being subscribed to it.)
We couldn't do better if we wanted the list members to be spammed all over, yes... With kind regards, Robert Kehl -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Tel. +49 (0)6172 4832388