[rt-devel] rt notes

[Alex Pilosov]

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? 

2. 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)

3. 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.

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

5. 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. :)

I'll send an email tomorrow with actual diffs.