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@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

schema.Oracle (9.29 KB)

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

That all makes sense, though I can’t see when any of these would ever be
searched with a LIKE :wink:

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@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

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

if this is storing a signed pgp key, 4000 characters seems a little on
the small side.

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

why would you ever search on pgp keys?

seph

At 11:36 PM 17/03/2003 -0800, seph wrote:

if this is storing a signed pgp key, 4000 characters seems a little on
the small side.

Really… I guess it isn’t soo much of an issue - since you don’t need to
search for values on it. Maybe CLOB is a good idea for this!

why would you ever search on pgp keys?

Point taken on the searching of PGPKeys or other attributes.

-Brook

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