Rt 2-0-15 and oracle

Hi all,

This is my first posting here, so excuse me and correct me
if I violate the netiquette or some unwritten rules of the community :slight_smile:

I have blatantly patched DBIx/SearchBuilder/Handle/Oracle.pm in a rush
to make rt run with oracle; the change is trivial, and near the beginning,
DBIx/SearchBuilder/Handle/Oracle.pm looks like this:

my %args = @_;

%args = ( Driver => $args{‘Driver’}, Database => $args{‘Database’}, User => $args{‘User’}, Password => $args{‘Password’}, SID => $args{‘Database’}, Host => $args{‘Host’});

So, $conn = DBI->connect(‘dbi:Oracle:host=my.oracle.host;SID=MYSID’,‘user’,‘password’)
works like a charm;

I have not tested the installation procedure, but created an oracle database
by hand and ported data from a live running 2-0-9 there;

RT generally runs with oracle, however when retrieving a ticket,
a query like this:
SELECT DISTINCT main.* FROM Attachments main WHERE ((main.Parent = ‘0’)) AND ((main.TransactionId = ‘17125’));
fails because there are CLOB-type fields, on which you can’t make distinct
selects (this is what our oracle guru says, please correct me if I’m wrong).

Did someone solve this problem? Any ideas will be greatly appretiated.

Regards,
Stoyan Genov

RT generally runs with oracle, however when retrieving a ticket,
a query like this:
SELECT DISTINCT main.* FROM Attachments main WHERE ((main.Parent = ‘0’)) AND ((main.TransactionId = ‘17125’));
fails because there are CLOB-type fields, on which you can’t make distinct
selects (this is what our oracle guru says, please correct me if I’m wrong).

Did someone solve this problem? Any ideas will be greatly appretiated.
Yes, just delete the DISTINCT because the select is unique even without
the distinct. The ID column is autogenerated and included because of the
main.*. Just ask your Oracle guru.

Next problem you’re going to run into is that CLOB’s don’t take binary
data very well. Either you convert to BLOB format or you undef
BinarySafeBLOBs like for PostgresSql. (Just did that myself and works OK.
Our site gets a binary once in a while so it is a minor performance hit
because they are now stored as Base64 data in the CLOB column)
Add this to: SearchBuilder/Handle/Oracle.pm

{{{ BinarySafeBLOBs

=head2 BinarySafeBLOBs

Return undef, as workaround for CLOB/BLOB conversion

=cut

sub BinarySafeBLOBs {
my $self = shift;
return(undef);
}

Next problem and a lot bigger than the previous one is how Left Joins
are constructed. Turn on debugging and have a look at your Apache error
log to see how they look or have a look at the mysql manual. Oracle uses
a different syntax altogether so what I have done is to let
SearchBuilder make the left join, test if there is one and if so break
it apart and reconstruct it in the Oracle way. Works like a charm as far
as I can tell (tickets show up on my Home link where previously they
didn’t)…

Another small problem is the LIMIT clause which Oracle doesn’t know
about. Simple, drop that. All that you loose is the ability to show n
items on a page, you’ll always get the full listing.
I do have a query which implements something like LIMIT but I think it
will kill performance (select from a select minus the same thing).
select * from (select tickets.*, row_number() over ( order by id asc ) x from tickets) where x <6 and x>3

I just thought of another SMALL problem, you can’t search on email body
content anymore as soon as you switch to CLOB (I used VARCHAR2(4000)
instead for a while and disallowed attachements so had no problems.)
Ofcourse you can seach CLOB columns using Intermedia but here you have
the same problem as with Left Joins you’ll need to rewrite the where
condition of the query to use Intermedia syntax for example:
This is what we need:
select content from attachments where contains(content,’%blablabla%’)>0
and this is what is constructed reformatted:
SELECT main.*
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((LOWER (attachments_2.content) LIKE ‘%blablabla%’))
AND ((main.effectiveid = main.id))
AND attachments_2.transactionid = transactions_1.id
AND main.id = transactions_1.ticket
ORDER BY main.id ASC

If time permitting I might do that today since that is a question asked
in the department.

I’m still using 2.0.13 because I don’t have problems with it. I can
donate my changes is there’s enough interest.

Joop

RT generally runs with oracle, however when retrieving a ticket,
a query like this:
SELECT DISTINCT main.* FROM Attachments main WHERE ((main.Parent = ‘0’)) AND ((main.TransactionId = ‘17125’));
fails because there are CLOB-type fields, on which you can’t make distinct
selects (this is what our oracle guru says, please correct me if I’m wrong).

Did someone solve this problem? Any ideas will be greatly appretiated.

I just thought of another SMALL problem, you can’t search on email body
content anymore as soon as you switch to CLOB (I used VARCHAR2(4000)
instead for a while and disallowed attachements so had no problems.)
Ofcourse you can seach CLOB columns using Intermedia but here you have
the same problem as with Left Joins you’ll need to rewrite the where
condition of the query to use Intermedia syntax for example:
This is what we need:
select content from attachments where contains(content,’%blablabla%’)>0
and this is what is constructed reformatted:
SELECT main.*
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((LOWER (attachments_2.content) LIKE ‘%blablabla%’))
AND ((main.effectiveid = main.id))
AND attachments_2.transactionid = transactions_1.id
AND main.id = transactions_1.ticket
ORDER BY main.id ASC

If time permitting I might do that today since that is a question asked
in the department.

I have just completed this and seems to work quite nice.
One thing that strikes me is that if I do a search on ticket content
that doesn’t match ‘test’ and belongs to owner ‘Dick’ that I get a
result set which contains duplicate entries. The reason for this is that
there are normally more than one transaction per ticket which don’t
contain the search string.
Is this normal behaviour?
The problem I have with this is that it leads to quite a big result set
if there are a lot of transactions per ticket which makes picking the
right ticket quite a problem.

Joop