[gmane.comp.otrs.devel] Making OTRS play nice with Oracle
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.) -- Kristoffer.
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
Hi!
"Robert Kehl"
This wasn't an issue in our Oracle 9.2.1 release. Which Oracle version are you using?
We're running 9.2.0.1:
dbi:Oracle:otrs>create table session ( foo varchar(20) ) /
create table session ( foo varchar(20) ) : error:
- 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.
Strange. I'm guessing they've changed the behaviour between the
versions:
dbi:Oracle:otrs>create table test ( foo varchar(20) not null ) /
Ok - 0 rows affected
create table test ( foo varchar(20) not null ) : success
dbi:Oracle:otrs>insert into test (foo) values ('') /
insert into test (foo) values ('') : error:
(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...
Note that I'm not complaining about the list being closed as such; I just think it would be a good idea to let people know about it without making them have to play the bounce-game first. :) Regards, -- Kristoffer.
Hi, just about talking OTRS on Oracle. :) Ivan, can you give us feedback about your OTRS on Oracle installation. Thanks! Martin -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
Hi!
"Robert Kehl"
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.
I'm just wondering if this code is going to be committed any time soon. I've seen several different e-mails to this list containing information (and questions) about running OTRS on top of Oracle, but no patches seem to have made their way to the CVS tree yet. While we're perfectly capable of maintaining our own patched, Oracle-enabled installation, we would prefer to know it if that's what we will have to do, so that we can let our customer know too. Regards, -- Kristoffer Gleditsch Systemkonsulent/Systems Consultant tlf: +47 22 87 11 80 Linpro AS http://www.linpro.no/
Hi!
Kristoffer Gleditsch
I'm just wondering if this code is going to be committed any time soon.
...please? Am I the only one who thinks Oracle support would be a great feature for 1.3? I really would appreciate some kind of answer, even it it is "your patches are junk". If you tell me what you don't like about them, I might even do something about it, but when the only answer is silence, it's kind of hard to figure out what I'm doing wrong. Regards, -- Kristoffer.
On Monday, August 23, 2004 10:06 PM
Kristoffer Gleditsch
...please? Am I the only one who thinks Oracle support would be a great feature for 1.3? I really would appreciate some kind of answer, even it it is "your patches are junk". If you tell me what you don't like about them, I might even do something about it, but when the only answer is silence, it's kind of hard to figure out what I'm doing wrong.
Though I didn't test them in any way, I've incorparated your patches in the Wiki: http://wiki.robertkehl.de/index.pl?PatchMakingOtrsPlayNiceWithOracle Feel free to edit the entry! With kind regards, Robert Kehl -- Wir hatten alle die Hosen voll. Aber bei mir lief es ganz flüssig. Paul Breitner
Hi!
"Robert Kehl"
Though I didn't test them in any way, I've incorparated your patches in the Wiki:
http://wiki.robertkehl.de/index.pl?PatchMakingOtrsPlayNiceWithOracle
Feel free to edit the entry!
Thanks for the help with publishing the patches. However, I have to admit I'm a bit confused about what is expected of me now. - Does the publication of the patches on this Wiki mean that someone is assuming any kind of responsibility for keeping them in sync with current OTRS releases? (They are against 1.2.2 or something, so they are already a bit outdated.) - Is Oracle support going to be included in the upstream OTRS at any point? Right now I've got the feeling that the OTRS developers don't want Oracle support in their application. That's fair enough, although a bit strange, considering http://doc.otrs.org/1.3/en/html/faq.html#AEN1848 . I'm not going to force anyone to support databases they don't want to support. I would, however, appreciate a clarification on this issue. Again, if there are technical issues with the patches which are causing this reluctance to accept them, I can try to fix that. But I need to know about it before I can fix it... Regards, -- Kristoffer.
Hi Kristoffer, Kristoffer Gleditsch wrote:
Thanks for the help with publishing the patches. However, I have to admit I'm a bit confused about what is expected of me now.
- Does the publication of the patches on this Wiki mean that someone is assuming any kind of responsibility for keeping them in sync with current OTRS releases? (They are against 1.2.2 or something, so they are already a bit outdated.)
JFI. ((otrs.de)) is not responsible for this Wiki.
- Is Oracle support going to be included in the upstream OTRS at any point?
If it's good, then yes. Our main problem is. that we don't have a current oracle admin. So we do have problems to test OTRS on oracle like we want/need. So the state is still (experimental). Is there somebody on the list with oracle knowledge who can help the OTRS project with an working test instance for OTRS releases?
Right now I've got the feeling that the OTRS developers don't want Oracle support in their application. That's fair enough, although a bit strange, considering http://doc.otrs.org/1.3/en/html/faq.html#AEN1848 . I'm not going to force anyone to support databases they don't want to support. I would, however, appreciate a clarification on this issue.
The OTRS project want to support oracle. OTRS is already working on oracle. But at the moment we need some oracle knowledge to do a good job.
Again, if there are technical issues with the patches which are causing this reluctance to accept them, I can try to fix that. But I need to know about it before I can fix it...
Maybe you are a good oracle admin? :)
Regards,
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
Martin Edenhofer schrieb:
- Does the publication of the patches on this Wiki mean that someone is assuming any kind of responsibility for keeping them in sync with current OTRS releases? (They are against 1.2.2 or something, so they are already a bit outdated.)
That's what the naming of the release means. If you're adapting a patch to a later version yu're encouraged to publish it, too. It may appear on the same page, any previous should remain intact. The Wiki is free to use for everyone. Ie., anyone may edit the patches to their liking. Feel yourself invited to care about "your patch", ie. adapt it to later versions, socument it, and so on.
The OTRS project want to support oracle. OTRS is already working on oracle. But at the moment we need some oracle knowledge to do a good job.
What about releasing the code you have? Some time ago we worked out an Oracle port together, do you remember, Martin? What about the results, weren't they nearly finished? With kind regards, Robert Kehl
Robert Kehl wrote:
The OTRS project want to support oracle. OTRS is already working on oracle. But at the moment we need some oracle knowledge to do a good job.
What about releasing the code you have? Some time ago we worked out an Oracle port together, do you remember, Martin? What about the results, weren't they nearly finished?
The result of this work is in the cvs (also 1.3.1). It's all what I have. IMO, just the create scripts not in the cvs. Can you post the oracle create scripts for OTRS 1.3 on this list?
Robert Kehl
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
Martin Edenhofer
If it's good, then yes. Our main problem is. that we don't have a current oracle admin. So we do have problems to test OTRS on oracle like we want/need. So the state is still (experimental).
Well, that's fair enough. It's pretty hard to support a database without a test environment.
Is there somebody on the list with oracle knowledge who can help the OTRS project with an working test instance for OTRS releases?
I don't think Installing Oracle with a development license on a Redhat-ish machine is terribly hard. I have only done the client-side installation myself, though. http://www.puschitz.com/OracleOnLinux.shtml is a pretty good reference on Oracle installations on Linux.
Maybe you are a good oracle admin? :)
I'm not much of an Oracle DBA, sorry... :) Mvh, -- Kristoffer.
Hi! I've now upgraded our customer's OTRS/Oracle installation to 1.3.2. This reduced the number of patches needed quite a bit, although we're not done testing it yet. I've attached the patches I needed (apart from the obviously site-specific ones, like stylesheets and 'use lib'). The updated schema file is also attached. Note that I've made CLOBs of the fields having the datatype TEXT in PostgreSQL. Since the "=" operator doesn't like CLOBs, the patch below changes "=" to "LIKE" in some string comparison statements. I think that should work just as well as "=" on all databases. The patches I've currently applied to my installation: The only other gotcha I can think of is that the session database shouldn't be called "session". The schema calls it "sessions", but this must be reflected in Kernel/Config.pm. Also, is this the right place to ask about the "commercial support" list on http://otrs.org/support/? The company I work for (Linpro AS, www.linpro.no) has customized and supported OTRS for several customers, and would appreciate being mentioned in this list. :) Regards, -- Kristoffer.
Hi Kristoffer, thanks for this patches, info. Means this, that OTRS (application) works just with this one Kernel/System/Ticket/Article.pm change on Oracle? Thanks, -Martin Kristoffer Gleditsch wrote:
I've now upgraded our customer's OTRS/Oracle installation to 1.3.2. This reduced the number of patches needed quite a bit, although we're not done testing it yet. I've attached the patches I needed (apart from the obviously site-specific ones, like stylesheets and 'use lib'). The updated schema file is also attached.
Note that I've made CLOBs of the fields having the datatype TEXT in PostgreSQL. Since the "=" operator doesn't like CLOBs, the patch below changes "=" to "LIKE" in some string comparison statements. I think that should work just as well as "=" on all databases.
The patches I've currently applied to my installation:
------------------------------------------------------------------------
Index: Article.pm =================================================================== --- Article.pm (.../tags/otrs-1.3.2/otrs/Kernel/System/Ticket/Article.pm) (revision 37) +++ Article.pm (.../branches/installed-1.3.2/otrs/Kernel/System/Ticket/Article.pm) (revision 37) @@ -355,10 +355,10 @@ $SQL .= "a_message_id = '$Param{MessageID}' AND "; } if ($Param{From}) { - $SQL .= "a_from = '$Param{From}' AND "; + $SQL .= "a_from LIKE '$Param{From}' AND "; } if ($Param{Subject}) { - $SQL .= "a_subject = '$Param{Subject}' AND "; + $SQL .= "a_subject LIKE '$Param{Subject}' AND "; } $SQL .= " incoming_time = '$Param{IncomingTime}'"; # start query
------------------------------------------------------------------------
Index: initial_insert.sql =================================================================== --- initial_insert.sql (.../tags/otrs-1.3.2/otrs/scripts/database/initial_insert.sql) (revision 37) +++ initial_insert.sql (.../branches/installed-1.3.2/otrs/scripts/database/initial_insert.sql) (revision 37) @@ -206,6 +206,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) @@ -634,7 +642,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
------------------------------------------------------------------------
The only other gotcha I can think of is that the session database shouldn't be called "session". The schema calls it "sessions", but this must be reflected in Kernel/Config.pm.
Also, is this the right place to ask about the "commercial support" list on http://otrs.org/support/? The company I work for (Linpro AS, www.linpro.no) has customized and supported OTRS for several customers, and would appreciate being mentioned in this list. :)
Regards,
Martin Edenhofer
Means this, that OTRS (application) works just with this one Kernel/System/Ticket/Article.pm change on Oracle?
Well ... I think so. :-) The Oracle-enabled version of OTRS 1.3.2 is still in a test phase, though. Also, we've written our own Auth, CustomerAuth, CustomerUser and AuthSession backend modules (in order to authenticate users against an existing system), so the default ones have not been tested with Oracle. Regards, -- Kristoffer.
Hi Kristoffer, Kristoffer Gleditsch wrote:
The only other gotcha I can think of is that the session database shouldn't be called "session". The schema calls it "sessions", but this must be reflected in Kernel/Config.pm.
We also cam rename this table in future releases. I'll renames session to sessions.
Regards,
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
participants (4)
-
Kristoffer Gleditsch -
Martin Edenhofer -
Robert Kehl -
Robert Kehl