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

[Brook Schofield]

At 01:48 PM 12/03/2003 -0500, Jesse Vincent wrote:
>Brook, Pavel,
>
>In the past 24 hours, you've both contributed patches to
>DBIx::SearchBuilder to help better support Oracle. I'm including both
>patches here. I'd love a bit of discussion on the relative merits of the
>two patches.

a)      the substitution that I perform in my ApplyLimits is potentially 
bad as this could effect the query. I have seen many queries from DBIx 
search builder and they all appear to be in the format of SELECT DISTINCT 
main.* so that addition of 'rownum rn' to this shouldn't cause a problem. 
But if the query is any different then that could induce problems. I have 
since taken Pavel's two sub selects approach to insulate the original query 
from damage/change etc.

b)      if the _DoCount function uses 'group by' then that could be a 
potential reason for applying limits. _DoCount doesn't do this *yet* - but 
the new "dual" subselect patch will allow for _DoCount to have a limit 
applied to it if there are any 'group by' counts performed.

c)      I think that my 'BETWEEN' syntax is easier to read for longer term 
maintenance ;-)

d)      there could be an efficiency issue with the dual subselects. I'll 
get a DBAs to do an explain plan to see what issue there is, if any. But 
for more robust code it is the better option.

-Brook


 > diff -cr DBIx-SearchBuilder-0.80.orig DBIx-SearchBuilder-0.80 | more
Common subdirectories: DBIx-SearchBuilder-0.80.orig/SearchBuilder and 
DBIx-SearchBuilder-0.80/SearchBuilder
diff -cr DBIx-SearchBuilder-0.80.orig/SearchBuilder.pm 
DBIx-SearchBuilder-0.80/SearchBuilder.pm
*** DBIx-SearchBuilder-0.80.orig/SearchBuilder.pm       Sat Mar  8 18:42:25 
2003
--- DBIx-SearchBuilder-0.80/SearchBuilder.pm    Wed Mar 12 16:23:10 2003
***************
*** 204,212 ****
         if ( $self->_isLimited > 0 );


-     $self->_ApplyLimits(\$QueryString) unless ($all);
-
-
       print STDERR "DBIx::SearchBuilder->DoSearch Query:  $QueryString\n"
         if ( $self->DEBUG );

--- 204,209 ----
Common subdirectories: DBIx-SearchBuilder-0.80.orig/SearchBuilder/Handle 
and DBIx-SearchBuilder-0.80/SearchBuilder/Handle
Common subdirectories: DBIx-SearchBuilder-0.80.orig/SearchBuilder/Record 
and DBIx-SearchBuilder-0.80/SearchBuilder/Record
diff -cr DBIx-SearchBuilder-0.80.orig/SearchBuilder/Handle/Oracle.pm 
DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm
*** DBIx-SearchBuilder-0.80.orig/SearchBuilder/Handle/Oracle.pm Sat Mar  8 
18:42:25 2003
--- DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm      Thu Mar 13 
11:17:15 2003
***************
*** 122,126 ****
--- 122,177 ----
       return( $self->{'id'}); #Add Succeded. return the id
     }

+ # }}}

+ # {{{ sub ApplyLimits
+
+ =head2 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
+
+ takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE 
starting with FIRST_ROW;
+
+
+ =cut
+
+ sub ApplyLimits {
+     my $self = shift;
+     my $statementref = shift;
+     my $per_page = shift;
+     my $first = shift;
+
+     # Transform an SQL query from:
+     #
+     # SELECT DISTINCT main.*
+     #   FROM Tickets main
+     #  WHERE ((main.EffectiveId = main.id))
+     #    AND ((main.Type = 'ticket'))
+     #    AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
+     #    AND ( (main.Queue = '1') ) )
+     #  ORDER BY main.id ASC
+     #
+     # to:
+     #
+     # SELECT * FROM (
+     #     SELECT oraquery.*,rownum rn FROM (
+     #             SELECT DISTINCT main.*
+     #               FROM Tickets main
+     #              WHERE ((main.EffectiveId = main.id))
+     #                AND ((main.Type = 'ticket'))
+     #                AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
+     #                AND ( (main.Queue = '1') ) )
+     #              ORDER BY main.id ASC
+     #     ) oraquery
+     # ) WHERE rn BETWEEN 1 AND 50
+     #
+
+     if ($per_page) {
+         # Oracle orders from 1 not zero
+         $first++;
+         # Make current query a sub select
+         $$statementref = "SELECT * FROM ( SELECT oraquery.*,rownum rn 
FROM ( $$statementref ) oraquery ) WHERE rn BETWEEN $first AND ";
+         $$statementref .= $per_page + $first - 1;
+     }
+ }
+
+ # }}}



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