Rt notes

RT Developers
1

Ok, after a few hours of fighting with RT on postgres, here are my notes:

  1. oracle schema does translate almost well to pg. The only major problem
    (as I’ve pointed out 5 months ago, and I requested that change back then)
    is that “Right” (in ACL table) is a reserved word in sql92 (as in right
    join), and postgres enforces that. I renamed that field to aclright, and I
    think the only references to it were in lib/RT/User.pm

Jesse, could you please confirm that? And can you please modify it in
mysql and oracle schemas?

  1. The whole idea of different files containing different schemas/acls for
    different dbs pisses me off. There oughta be a better way. Unfortunately,
    ER/win doesn’t generate pgsql code, or I could be generating all 3 schemas
    from a single source.

Alternatively, it should be converted to a perl (or awk/m4) script which
generates schemas from a single source.

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)
b) date formats
c) types of autoincrement column and associated triggers/seqs.
d) inserting hardcoded values into tables with autoincrementing column.

It shouldn’t be too hard to have a generic schema and translate it into
your favorite db’s dialect of SQL. But I didn’t do that yet ;P)

  1. DBIx::SearchBuilder: on Create, it will use ‘null’ when the value is
    not given. Actually, it should not list the value or column at all, since
    specifying ‘null’ will NOT use the default (according to SQL standard).

This will help both Oracle and Pg, since you won’t need an extra trigger
to fix things up.

  1. rtmux must clear its environment completely, unsetting every
    environment variable present, and setting only known ones, otherwise you
    are asking for problems.

  2. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile:

I’ll send an email tomorrow with actual diffs.

2

Ok, after a few hours of fighting with RT on postgres, here are my notes:

  1. oracle schema does translate almost well to pg. The only major problem
    (as I’ve pointed out 5 months ago, and I requested that change back then)
    is that “Right” (in ACL table) is a reserved word in sql92 (as in right
    join), and postgres enforces that. I renamed that field to aclright, and I
    think the only references to it were in lib/RT/User.pm

Jesse, could you please confirm that? And can you please modify it in
mysql and oracle schemas?

  1. The whole idea of different files containing different schemas/acls for
    different dbs pisses me off. There oughta be a better way. Unfortunately,
    ER/win doesn’t generate pgsql code, or I could be generating all 3 schemas
    from a single source.

Alternatively, it should be converted to a perl (or awk/m4) script which
generates schemas from a single source.

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)
b) date formats
c) types of autoincrement column and associated triggers/seqs.
d) inserting hardcoded values into tables with autoincrementing column.

It shouldn’t be too hard to have a generic schema and translate it into
your favorite db’s dialect of SQL. But I didn’t do that yet ;P)

I have module code that generates MySQL and Pg schemas from a common Perl
data structure. I’ll give it a try.

  1. DBIx::SearchBuilder: on Create, it will use ‘null’ when the value is
    not given. Actually, it should not list the value or column at all, since
    specifying ‘null’ will NOT use the default (according to SQL standard).

This will help both Oracle and Pg, since you won’t need an extra trigger
to fix things up.

  1. rtmux must clear its environment completely, unsetting every
    environment variable present, and setting only known ones, otherwise you
    are asking for problems.

  2. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile:

I’ll send an email tomorrow with actual diffs.

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

meow
_ivan

3

Ok, after a few hours of fighting with RT on postgres, here are my notes:

  1. oracle schema does translate almost well to pg. The only major problem
    (as I’ve pointed out 5 months ago, and I requested that change back then)
    is that “Right” (in ACL table) is a reserved word in sql92 (as in right
    join), and postgres enforces that. I renamed that field to aclright, and I
    think the only references to it were in lib/RT/User.pm

Jesse, could you please confirm that? And can you please modify it in
mysql and oracle schemas?

Ack. I’m sorry. I thought that got done a while ago. What I suspect happened isthat I fixed it, then junked all the ACL work and reimplemented…and forgot. I’ll make this change after Alpha 1.

  1. The whole idea of different files containing different schemas/acls for
    different dbs pisses me off. There oughta be a better way. Unfortunately,
    ER/win doesn’t generate pgsql code, or I could be generating all 3 schemas
    from a single source.

Alternatively, it should be converted to a perl (or awk/m4) script which
generates schemas from a single source.

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)
b) date formats
c) types of autoincrement column and associated triggers/seqs.
d) inserting hardcoded values into tables with autoincrementing column.

It shouldn’t be too hard to have a generic schema and translate it into
your favorite db’s dialect of SQL. But I didn’t do that yet ;P)

This is one of the things I’ve been thinking about for a future version of SearchBuilder. It’s definitely waiting until after 2.0, but having the schema generated based on a perl data structure would be really convenient.

  1. DBIx::SearchBuilder: on Create, it will use ‘null’ when the value is
    not given. Actually, it should not list the value or column at all, since
    specifying ‘null’ will NOT use the default (according to SQL standard).

This will help both Oracle and Pg, since you won’t need an extra trigger
to fix things up.

Ah. good catch. thanks.

  1. rtmux must clear its environment completely, unsetting every
    environment variable present, and setting only known ones, otherwise you
    are asking for problems.

nod That’s one of several security related things that’s known to be needed. A security runthrough and redo is scheduled for sometime around alpha-3.
I certainly have no objection to it happening earlier as a contributed change, though.

  1. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile:

nod One thing I’ve been vaguely pondering is the idea of storing “large” attachments on disk, rather than in the database. This isn’t something I’m thrilled with, but it may be necessary to deal with many databases’ broken large-object handling.

I’ll send an email tomorrow with actual diffs.

Woo! That’s great. thanks.

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

jesse reed vincent — root@eruditorum.org — jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
<Dr_Memory> the point is that words were exchanged. neurolinguistic
programming will do the rest. they should be showing up at my house
any day now.

4

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)

  1. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile: >

I’m working on the Pg port now. Pg doesn’t seem to have a `TEXT’
datatype. What do you suggest using instead of MySQL BLOBs? for Pg 7.0?

This particular problem is going to be a doozy. Yuck.

meow
_ivan

5

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)

  1. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile: >

I’m working on the Pg port now. Pg doesn’t seem to have a `TEXT’
datatype. What do you suggest using instead of MySQL BLOBs? for Pg 7.0?

Nevermind, I’ve been up way too late. I found it.

This particular problem is going to be a doozy. Yuck.

(8k limit etc.) At least I seem to have mysql and Pg database both
working again.

meow
_ivan

6

The things that are different in each database:
a) datatypes (DATE vs TIMESTAMP, VARCHAR vs VARCHAR2, CLOB vs TEXT, etc)

  1. Because current large-object interface in postgres is such a pain to
    work with, I converted all columns to TEXT. However, postgres up to 7.1
    won’t allow you to put >8k of data into one row, which means things won’t
    quite work right. 7.1 is to be released sometime this year. :slight_smile: >

I’m working on the Pg port now. Pg doesn’t seem to have a `TEXT’
datatype. What do you suggest using instead of MySQL BLOBs? for Pg 7.0?

This particular problem is going to be a doozy. Yuck.

Go for lztext in 7.0, (compressed text) which has 8k limitation after row
has been compressed. Or get CVS postgres, which has TOAST, completely
removing limitation on the length of text datatype.

I don’t recommend you deal with Pg large-object-interface. Its a real
pain, and it has too many caveats. With 7.1 on horizon, its better to wait
for it.

By the time RT2 is released, I’m willing to bet that Postgres 7.1 with
TOAST will be released also ;P)

-alex