Rt and Oracle

RT Developers
1

Hi!

Some time ago I asked if it was possible to use Oracle instead of MySQL with
RT. Is there any progression on this area? How is the MySQL database
integrated with RT, is it SQL commands from within the specific Perl script
with the Perl DBI?
The reason why I ask is that we need to create a database to track what we
call System Anomaly Reports, System Trouble Reports, System Change Request
and so on. We already have an Oracle environment and want to develop an
Intranet system based on Oracle. If it was possible to use RT together with
Oracle there could be a tremendous advantage in creating tickets based on
email and so on. The ticket could be initiated through mail and detail
information added at a later point with web interface.

Per Sverre

2

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesseOn Tue, Apr 11, 2000 at 01:49:03PM +0200, Stiansen, Per Sverre wrote:

Hi!

Some time ago I asked if it was possible to use Oracle instead of MySQL with
RT. Is there any progression on this area? How is the MySQL database
integrated with RT, is it SQL commands from within the specific Perl script
with the Perl DBI?
The reason why I ask is that we need to create a database to track what we
call System Anomaly Reports, System Trouble Reports, System Change Request
and so on. We already have an Oracle environment and want to develop an
Intranet system based on Oracle. If it was possible to use RT together with
Oracle there could be a tremendous advantage in creating tickets based on
email and so on. The ticket could be initiated through mail and detail
information added at a later point with web interface.

Per Sverre

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
Transporters are so ungodly. if god had wanted us to travel great distances
instantaneously, he would have given us an internal
materialisation/dematerialisation control.
– Shoshe Cole

3

Hello Jesse et al,
I have tossed this idea around for a while, but hadn’t had time to
implement it in rt-1.0, so I might as well work on it for 2.0 at this point.
If there is any interest at all besides me, I’d like to write a DBI
interface for IBM’s DB2 as well as the other 3 mentioned. I’m not sure how
many people out there are using it, but if it might be useful, I’ll happily
make the code part of the RT-2.0 release tree.
Question for Jesse, if it’s something you’re interested in, how do I
get access for cvs commits, and what is the current module to check out for
the latest rt-devel tree? Is it still rt-1-1?

Thanks,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: Tuesday, April 11, 2000 6:28 AM
To: Stiansen, Per Sverre
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Rt and Oracle

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesse

4

Dylan, that would definately be a great contribution. For now, the way to
submit new code for inclusion into RT is to send a pointer to unified diffs to
rt-devel for me and other rt hackers to take a look at. If it looks good
and fits with RT’s design philosophy, we’ll check the changes in. Most,
if not all, changes related to databases should be restricted to schema.
and the DBIx module in CVS (and not in RT itself) But yeah, rt-1-1 is the current tag name for the head of the development branch.

Thanks a bunch,

JesseOn Tue, Apr 11, 2000 at 09:53:36AM -0700, Dylan Vanderhoof wrote:

Hello Jesse et al,
I have tossed this idea around for a while, but hadn’t had time to
implement it in rt-1.0, so I might as well work on it for 2.0 at this point.
If there is any interest at all besides me, I’d like to write a DBI
interface for IBM’s DB2 as well as the other 3 mentioned. I’m not sure how
many people out there are using it, but if it might be useful, I’ll happily
make the code part of the RT-2.0 release tree.
Question for Jesse, if it’s something you’re interested in, how do I
get access for cvs commits, and what is the current module to check out for
the latest rt-devel tree? Is it still rt-1-1?

Thanks,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: Tuesday, April 11, 2000 6:28 AM
To: Stiansen, Per Sverre
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Rt and Oracle

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesse

jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
I admit that X is the second worst windowing system in the world, but all the
others I’ve used are tied for first.

5

I too am interested in having Oracle available for Rt. I think a db
interface that could handle several DBD:: data sources would be great. I
would be very interested in contributing to this portion of rt also.

Dylan, I would be interested too in collaborating with you on this. I
havn’t seen a cvs checkout. Jesse, is there a rt-devel faq? Does there
need to be one?

$::codyOn Tue, 11 Apr 2000, Dylan Vanderhoof wrote:

Hello Jesse et al,
I have tossed this idea around for a while, but hadn’t had time to
implement it in rt-1.0, so I might as well work on it for 2.0 at this point.
If there is any interest at all besides me, I’d like to write a DBI
interface for IBM’s DB2 as well as the other 3 mentioned. I’m not sure how
many people out there are using it, but if it might be useful, I’ll happily
make the code part of the RT-2.0 release tree.
Question for Jesse, if it’s something you’re interested in, how do I
get access for cvs commits, and what is the current module to check out for
the latest rt-devel tree? Is it still rt-1-1?

Thanks,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: Tuesday, April 11, 2000 6:28 AM
To: Stiansen, Per Sverre
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Rt and Oracle

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesse

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

6

I too am interested in having Oracle available for Rt. I think a db
interface that could handle several DBD:: data sources would be great.

You may want to look at DBIx::AnyDBI (i belive that was the name) which
does some abstraction of such things.) Other issues that need to be dealt
with is the fact that the Attachments table uses MySQL’s blob support which may or may not be easily portable.

I
would be very interested in contributing to this portion of rt also.

Dylan, I would be interested too in collaborating with you on this. I
havn’t seen a cvs checkout. Jesse, is there a rt-devel faq? Does there
need to be one?

If you’re volunteering to help coordinate it, that would be just great :slight_smile:
Bounce me content and I’ll put it on the web :wink:

$::cody

Hello Jesse et al,
I have tossed this idea around for a while, but hadn’t had time to
implement it in rt-1.0, so I might as well work on it for 2.0 at this point.
If there is any interest at all besides me, I’d like to write a DBI
interface for IBM’s DB2 as well as the other 3 mentioned. I’m not sure how
many people out there are using it, but if it might be useful, I’ll happily
make the code part of the RT-2.0 release tree.
Question for Jesse, if it’s something you’re interested in, how do I
get access for cvs commits, and what is the current module to check out for
the latest rt-devel tree? Is it still rt-1-1?

Thanks,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: Tuesday, April 11, 2000 6:28 AM
To: Stiansen, Per Sverre
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Rt and Oracle

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesse

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
They’ll take my private key when they pry it from my cold dead fingers!

7

But yeah, rt-1-1 is the current tag name for the head of the development
branch.

Oh, btw, the new schema.mysql contains quite some data that shouldn’t be
there - they should be inserted by the admin tools which nobody has made
yet :slight_smile:

If somebody desperately needs to use other DBs with rt-1-0, I know
somebody has made patches for this. The FunRT tag points more or less to
the version we’re using here now, I can’t guarantee that it will work any
other places than here, but at least we’re using DBI instead of the older
MySQL-modules.

Tobias Brox
aka TobiX
+47 22 925 871

8

Other issues that need to be dealt with is the fact that the Attachments
table uses MySQL’s blob support which may or may not be easily portable.

Another issue is the primary keys. MySQL has “autoincrement” which means
that a NULL value might be issued for the primary key, and MySQL will find
the number itself and return it. Different engines do have different ways
of handling this, as well as different ways of returning the number.

Tobias Brox
aka TobiX
+47 22 925 871

9

I can’t speak for Oracle, but I know that DB2 has support for the BLOB type,
althought it akes fairly excessive use of binds. I’ll have to de some
research for that.
Also, I have a trigger for DB2 that will likely work with Oracle as well
with some tweaking to emulate MySQL’s AUTO_INCREMENT column variable.

Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation-----Original Message-----
From: Tobias Brox [mailto:tobiasb@tobiasb.funcom.com]
Sent: Wednesday, April 12, 2000 1:04 AM
To: Jesse
Cc: Aryeh Cody Sherr; Dylan Vanderhoof; ‘rt-devel@lists.fsck.com’
Subject: Re: [rt-devel] Rt and Oracle

Other issues that need to be dealt with is the fact that the Attachments
table uses MySQL’s blob support which may or may not be easily portable.

Another issue is the primary keys. MySQL has “autoincrement” which means
that a NULL value might be issued for the primary key, and MySQL will find
the number itself and return it. Different engines do have different ways
of handling this, as well as different ways of returning the number.

Tobias Brox
aka TobiX
+47 22 925 871

10

I can’t speak for Oracle, but I know that DB2 has support for the BLOB type,
althought it akes fairly excessive use of binds. I’ll have to de some
research for that.

I’ve had some experience with Solid, and it had a “long varchar” - but
when using this extensively, the DB server got extremely slow and sucked
all available hard disk space. Luckily, mysql is a bit better suited for
handling blobs :slight_smile:

There might be that somebody might want to have the data stored in files
and only have the meta data stored in the DB. Unfortunately, this will be
quite difficult to do with the new architecture.

Tobias Brox
aka TobiX
+47 22 925 871

11

Oracle supports auto increment with triggers. It also has support for
character and large objects both, at least in 8 and 8i. I don’t know about
7.

CodyOn Wed, 12 Apr 2000, Dylan Vanderhoof wrote:

I can’t speak for Oracle, but I know that DB2 has support for the BLOB type,
althought it akes fairly excessive use of binds. I’ll have to de some
research for that.
Also, I have a trigger for DB2 that will likely work with Oracle as well
with some tweaking to emulate MySQL’s AUTO_INCREMENT column variable.

Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Tobias Brox [mailto:tobiasb@tobiasb.funcom.com]
Sent: Wednesday, April 12, 2000 1:04 AM
To: Jesse
Cc: Aryeh Cody Sherr; Dylan Vanderhoof; ‘rt-devel@lists.fsck.com’
Subject: Re: [rt-devel] Rt and Oracle

Other issues that need to be dealt with is the fact that the Attachments
table uses MySQL’s blob support which may or may not be easily portable.

Another issue is the primary keys. MySQL has “autoincrement” which means
that a NULL value might be issued for the primary key, and MySQL will find
the number itself and return it. Different engines do have different ways
of handling this, as well as different ways of returning the number.

–
Tobias Brox
aka TobiX
+47 22 925 871

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

12

Cool. In my book, supporting oracle 8 and not oracle 7 would not be a great loss.On Wed, Apr 12, 2000 at 10:06:23AM -0600, Aryeh Cody Sherr wrote:

Oracle supports auto increment with triggers. It also has support for
character and large objects both, at least in 8 and 8i. I don’t know about
7.

Cody

On Wed, 12 Apr 2000, Dylan Vanderhoof wrote:

I can’t speak for Oracle, but I know that DB2 has support for the BLOB type,
althought it akes fairly excessive use of binds. I’ll have to de some
research for that.
Also, I have a trigger for DB2 that will likely work with Oracle as well
with some tweaking to emulate MySQL’s AUTO_INCREMENT column variable.

Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Tobias Brox [mailto:tobiasb@tobiasb.funcom.com]
Sent: Wednesday, April 12, 2000 1:04 AM
To: Jesse
Cc: Aryeh Cody Sherr; Dylan Vanderhoof; ‘rt-devel@lists.fsck.com’
Subject: Re: [rt-devel] Rt and Oracle

Other issues that need to be dealt with is the fact that the Attachments
table uses MySQL’s blob support which may or may not be easily portable.

Another issue is the primary keys. MySQL has “autoincrement” which means
that a NULL value might be issued for the primary key, and MySQL will find
the number itself and return it. Different engines do have different ways
of handling this, as well as different ways of returning the number.

–
Tobias Brox
aka TobiX
+47 22 925 871

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
“It’s buried in the desert, got sand in it, melts Nazis. You know,
the Ark of the Covenant” – siva

13

I’ve taken a look, and I think it might be useful to dump some info on the
project under “Hacking on RT” at fsck for interested developers. I
volunteer for doing coordination and maybe some contribution to the
content. If people want to bounce me content, in whatever form they think
should be out there, and then I gel it and present it back to everyone for
review.

some suggestestions on sections:

general state of the project
what people need to know to start contributing
what needs to be worked on - projects and refinements that are desired but
don’t have anyone currently working on them.

Not a lot of ceremony, just whatever is most useful.

CodyOn Tue, 11 Apr 2000, Jesse wrote:

On Tue, Apr 11, 2000 at 11:32:03AM -0600, Aryeh Cody Sherr wrote:

I too am interested in having Oracle available for Rt. I think a db
interface that could handle several DBD:: data sources would be great.

You may want to look at DBIx::AnyDBI (i belive that was the name) which
does some abstraction of such things.) Other issues that need to be dealt
with is the fact that the Attachments table uses MySQL’s blob support which may or may not be easily portable.

I
would be very interested in contributing to this portion of rt also.

Dylan, I would be interested too in collaborating with you on this. I
havn’t seen a cvs checkout. Jesse, is there a rt-devel faq? Does there
need to be one?

If you’re volunteering to help coordinate it, that would be just great :slight_smile:
Bounce me content and I’ll put it on the web :wink:

$::cody

On Tue, 11 Apr 2000, Dylan Vanderhoof wrote:

Hello Jesse et al,
I have tossed this idea around for a while, but hadn’t had time to
implement it in rt-1.0, so I might as well work on it for 2.0 at this point.
If there is any interest at all besides me, I’d like to write a DBI
interface for IBM’s DB2 as well as the other 3 mentioned. I’m not sure how
many people out there are using it, but if it might be useful, I’ll happily
make the code part of the RT-2.0 release tree.
Question for Jesse, if it’s something you’re interested in, how do I
get access for cvs commits, and what is the current module to check out for
the latest rt-devel tree? Is it still rt-1-1?

Thanks,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation

-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: Tuesday, April 11, 2000 6:28 AM
To: Stiansen, Per Sverre
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Rt and Oracle

As it stands now, RT 2.0 should be portable to oracle. Heck, even 1.0 should
be portable, but it’s a nontrivial change right now. FWIW, RT already
allows you to create tickets by mail and update them by the web. If you
are interested in helping port to oracle, we could certainly use the help.
jesse

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

–
jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91

They’ll take my private key when they pry it from my cold dead fingers!

14

I’ve taken a look, and I think it might be useful to dump some info on the
project under “Hacking on RT” at fsck for interested developers.

RT 2.0 or RT 1.0?

There is clearly needed more docs for developers … docs for how things
are supposed to behave, design docs, etc. I’m not sure the right way to
do is to make it as a FAQ.

Check the HACKING document on the root of the RT 2.0 distribution (it is
at least present in the CVS…). There might be some useful stuff there.

some suggestestions on sections:

general state of the project

Maybe this fits more into the README or a TODO file or something?

what needs to be worked on - projects and refinements that are desired but
don’t have anyone currently working on them.

Hm … also sounds a bit like if it belongs to the TODO file.

Btw, the TODO file points to a RT queue. That might make sense.

Tobias Brox
aka TobiX
+47 22 925 871

15

I’ve taken a look, and I think it might be useful to dump some info on the
project under “Hacking on RT” at fsck for interested developers.

RT 2.0 or RT 1.0?

There is clearly needed more docs for developers … docs for how things
are supposed to behave, design docs, etc. I’m not sure the right way to
do is to make it as a FAQ.

Check the HACKING document on the root of the RT 2.0 distribution (it is
at least present in the CVS…). There might be some useful stuff there.

some suggestestions on sections:

general state of the project

Maybe this fits more into the README or a TODO file or something?

what needs to be worked on - projects and refinements that are desired but
don’t have anyone currently working on them.

Hm … also sounds a bit like if it belongs to the TODO file.

Btw, the TODO file points to a RT queue. That might make sense.

Actually, I have a whole bunch of things to get up into that queue.
People who want to suggest things to add should bounce them to me. (Not to
steal your thunder, cody. I really appreciate the collection work you’ve
volunteered to do)

jesse

–
Tobias Brox
aka TobiX
+47 22 925 871

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
“If IBM wanted to make clones, we could make them cheaper and faster than
anyone else!” - An IBM Rep. visiting Vassar College’s Comp Sci Department.

16

| Oracle supports auto increment with triggers. It also has support for
| character and large objects both, at least in 8 and 8i. I don’t know about
| 7.
±–>8

Oracle6 had large text and binary objects; but there was a significant
space penalty to their use, and IIRC they worked best if you only ever did
inserts and selects — no updates, no deletes (the space used would be
“lost” IIRC). I don’t know when, if ever, this was changed to something
sane.

brandon s. allbery [os/2][linux][solaris][japh] allbery@kf8nh.apk.net
system administrator [WAY too many hats] allbery@ece.cmu.edu
electrical and computer engineering KF8NH
carnegie mellon university [“better check the oblivious first” -ke6sls]