[rt-devel] rt notes

[Jesse]

On Sun, Sep 17, 2000 at 02:52:34AM -0400, Alex Pilosov wrote:
> 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.

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

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.

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

Ah. good catch. thanks.
> 
> 4. 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.

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

*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 at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel
> 

-- 
jesse reed vincent --- root at eruditorum.org --- jesse at 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.