4k ought to be enough for anyone (schema.Oracle Su pport in RT)

BEHAL_PAVEL_HP-Czech · March 18, 2003, 11:13am

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@mailbox.gu.edu.au]
Sent: Tuesday, March 18, 2003 6:51 AM
To: rt-devel@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@griffith.edu.au =
= // /// _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= /// / / /// /// _/ _/ Griffith University QLD 4111 =

BEHAL_PAVEL_HP-Czech · March 18, 2003, 12:18pm

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 in another thread.)

Pavel-----Original Message-----
From: Brook Schofield [mailto:B.Schofield@mailbox.gu.edu.au]
Sent: Tuesday, March 18, 2003 6:51 AM
To: rt-devel@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@griffith.edu.au =
= // /// _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= /// / / /// /// _/ _/ Griffith University QLD 4111 =

BEHAL_PAVEL_HP-Czech · March 18, 2003, 2:04pm

Hello once more,

I caught myself. Maybe we should remove maximum of CLOB fields as possible,
because I was not able to install the rt2.

I have modified the DBIx:SearchBuilder, so it does not use distinct all
some queries, together with Oracle limit
I have tested it and it works fine. But on all of my tables were using
the VARCHAR2 instead of CLOB
Now I dropped the rt2 schema, and I tried to install it back using CLOBs.
I have created the tables and indexes, but I totally failed on inserting
data. I have recognized, that it is because, there are some parts of RT2,
like RT::User, which do not use DBIx, but speak to the database directly
through DBD. And because the tables contain CLOBs, it returns a lot of
errors.

This seems to me as a really hard problem. How do you see it, Brook? Are you
getting the same results? I am afraid of some other internal SQL queries,
which will fail with Oracle’s CLOB.

Pavel-----Original Message-----
From: BEHAL,PAVEL (HP-Czechia,ex1) [mailto:pavel.behal@hp.com]
Sent: Tuesday, March 18, 2003 12:14 PM
To: ‘Brook Schofield’; rt-devel@lists.fsck.com
Subject: RE: [rt-devel] 4k ought to be enough for anyone (schema.Oracle Su
pport in RT)

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@mailbox.gu.edu.au]
Sent: Tuesday, March 18, 2003 6:51 AM
To: rt-devel@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@griffith.edu.au =
= // /// _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= /// / / /// /// _/ _/ Griffith University QLD 4111 =
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel