[rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle

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

Hello Brook,

I have not installed the rt3 on Oracle, so my experience has come from rt2.
But because the queries are generated from DBIx:SearchBuilder,  the problems
are version independent. Let's look:
- The rt2 schema for Oracle contains CLOB fields, and you can not make a
SELECT DISTINCT * query on these tables. This leads to an Oracle error and
it is an issue with all versions of Oracle DB. One ugly hack was to replace
the CLOB with VARCHAR2(4000), but this has limited functionality very much.
Or you should enumerate the fields and ignore the LOB's, when you are
constructing the query. My solution, I am thinking about, is to dynamically
enumerate the fields in table and replace the * with the correct ones,
before the query executes.
Small hint, how to enumerate the fileds. It is from DBD:Oracle included
examples, $dbh is handle from DBI->connect :
--- cut ----
$sth = $dbh->prepare( "SELECT * FROM affectedtable WHERE 1 = 2");
@name   = @{$sth->{NAME}};
@length = @{$sth->{PRECISION}};
@type   = @{$sth->{TYPE}}; 

foreach $i ( 0 .. $#name )
{
  print $name[$i] . "  " . $length[$i]. "   " . $type[$i] . "\n";
} 
--- cut ---
- The second problem is with ANSI syntax of LEFT JOIN. It is supported on
MySQL and PostgreSQL, but not on older versions of Oracle. I hope, that
Oracle 9.x.x will be fine. But I have no tested it yet.
- There may be other problems, like requireig SID in connection string.
Which should be configurable.

Points 5 and 6: Thank you for clarification Brook. Now I understand, you ar
right.

Pavel

-----Original Message-----
From: Brook Schofield [mailto:B.Schofield at mailbox.gu.edu.au]
Sent: Friday, March 14, 2003 1:25 AM
To: BEHAL,PAVEL (HP-Czechia,ex1); Jesse Vincent
Cc: rt-devel at fsck.com
Subject: RE: [rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle


At 03:13 PM 13/03/2003 +0100, BEHAL,PAVEL (HP-Czechia,ex1) wrote:
>There are some more issues to solve with Oracle.

What are the remaining issues with Oracle?

I'm going to finish running up an RT dev environment on my Mac tonight. 
Hopefully I'll have a good weekend of RT/Oracle hacking ahead of me. My 
institution runs on Oracle so RT isn't an option for a wider audience 
without full Oracle support.

What other Oracle tips/problems can people suggest?

>5) I am unsure about one thing in Brook's patch. During paging, the latest
>row from previous page is the first rown of next page. I think, this
>intended and correct. Am I right?

This is what RT currently does under MySQL so I am guessing that it should 
be the same behaviour under Oracle. So for a search result of 10 the pages 
displayed will be: 1 - 10, 10 - 19, 19 - 28. Which is also the numeric 
count at the bottom of the Search/Listing.html page.

>6) Brook, you have suggested to remove the
>"$self->_ApplyLimits(\$QueryString) unless ($all);" from _DoCount. Jesee,
>is this safe also in MySQL and PostgreSQL? I have not tested it.

count(*) will only return ONLY ONE ROW - ever. Unless that count(*) query 
also has a group by clause. Since SearchBuilder doesn't use group by in its 
_DoCount then the removal of ApplyLimits for every database is a good thing.

-Brook

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