[rt-devel] 4k ought to be enough for anyone (schema.Oracle Su pport in RT)

[BEHAL,PAVEL (HP-Czechia,ex1)]

Hello,

I personally prefer the CLOB type over the 4000 varchar2. My reasons are:
- It is really possible, that in the real world, the people will need to
store large data inside. (i.e.: large document as an Attachment.Content,
complex Scrip code)
- I do not think, anybody will search through "LIKE" these fields. If
somebody wants it, he should go through some metadata.
- The Attachment.Content is generic, based on the stored MIME header. So you
will not be able to use LIKE for searching here, because you have to conform
the stored data. So if we would like to search attachments, we need some
table with ascii extracted from attachments for fulltext search.

I think that primarily we have to look into DBIx:SearchBuilder and recompose
the code there. (See my next email in another thread.)

Pavel


-----Original Message-----
From: Brook Schofield [mailto:B.Schofield at mailbox.gu.edu.au]
Sent: Tuesday, March 18, 2003 6:51 AM
To: rt-devel at lists.fsck.com
Subject: [rt-devel] 4k ought to be enough for anyone (schema.Oracle Support
in RT)


All,

Looking at RTs database schema (attached in full as the patch is actually 
larger) there are the following table/column combinations which are of type 
CLOB:

Attachments.Content
Attachments.Headers
Scrips.ConditionRules
Scrips.ActionRules
Scrips.CustomIsApplicableCode
Scrips.CustomPrepareCode
Scrips.CustomCommitCode
Users.Comments
Users.Signature
Users.FreeFormContactInfo
Users.PGPKey
Templates.Content
sessions.a_session

Currently all of these columns are of type CLOB. I suggest that moving the 
User table values to VARCHAR(4000) to store 4k of data within them *COULD* 
be a good thing. Can anyone see issues with this?

Users.Comments                  "According to wc this email is only 1743 
characters"
Users.Signature         "Even my awful .signature is only 524 Characters"
Users.FreeFormContactInfo
Users.PGPKey

This will allow common LIKE queries to work with the Users tables. 
Resulting in less work modifying DBIx::SearchBuilder to handle the CLOB 
data type (this actually won't result is less work - but means that some 
functionality will work without changes to SearchBuilder).

NB:- the attached schema.Oracle file still has the Users table using CLOB 
just in case people don't like the idea of changing.

-Brook

=========================================================================
=     _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/  _/ Brook Schofield                =
=    _/  _/ _/  _/ _/  _/ _/  _/ _/ _/   B.Schofield at griffith.edu.au    =
=   _/_/   _/_/_/ _/  _/ _/  _/ _/_/     Ph: +61 7 387 53779 - WCN 0.28 =
=  _/  _/ _/ _/  _/  _/ _/  _/ _/ _/     Directory Services Integration =
= _/_/_/ _/  _/ _/_/_/ _/_/_/ _/  _/     Griffith University QLD 4111   =
=========================================================================