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

[Brook Schofield]

Jesse,
        attached is a patch against DBIx::SearchBuilder 0.80 which performs the following:

a)      removes ApplyLimits from the _DoCount query because I can't understand why:

                SELECT count(DISTINCT main.id) 
                  FROM Tickets main   
                 WHERE ((main.EffectiveId = main.id)) 
                        AND ((main.Type = 'ticket')) 
                        AND ( (  ( (main.Status = 'new')OR(main.Status = 'open') ) 
                                AND ( (main.Queue = '1') 
                            ) )
                 LIMIT 50

        the LIMIT 50 is useful in a count(*) query as its only purpose is to limit the number of rows returned, which will always be 1. It doesn't affect the result set that count() operates on. Or does it?

b)      changes an oracle query into a sub select so that ApplyLimits can be easily applied

I haven't had an opportunity to test in my Oracle environment as I haven't got Oracle/Perl talking yet... but thought that it may be more useful out in the open. What else needs to be done to have Oracle "supported" as an RT database.

-Brook

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


diff -rc 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 -rc 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      Wed Mar 12 16:39:48 2003
***************
*** 122,126 ****
--- 122,175 ----
      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 oraquery.* FROM (
+     #         SELECT DISTINCT main.*,rownum rn 
+     #           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 oraquery.rn BETWEEN 1 AND 50
+     #
+ 
+     if ($per_page) {
+         # Oracle orders from 1 not zero
+         $first++; 
+         $$statementref =~ s/main\.\*/main.*,rownum rn/;
+         # Make current query a sub select
+         $$statementref = "SELECT oraquery.* FROM ($$statementref) oraquery WHERE oraquery.rn BETWEEN $first AND ";
+         $$statementref .= $per_page + $first - 1;
+     }
+ }
+ 
+ # }}}