Subject: [rt-users] rt 2-0-15 and oracle

[Joop van de Wege]

> 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