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

[Jesse Vincent]

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.

	Thanks,
	Jesse

Brook's patch:
---------------

> 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;
> +     }
> + }
> + 
> + # }}}
> 

Pavel's patch
-------------

diff -ru DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm DBIx-SearchBuilder-0.80-pb/SearchBuilder/Handle/Oracle.pm
--- DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm	2003-03-08 09:42:26.000000000 +0100
+++ DBIx-SearchBuilder-0.80-pb/SearchBuilder/Handle/Oracle.pm	2003-03-11 15:55:22.000000000 +0100
@@ -122,5 +122,36 @@
     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;
+
+    my $limit_clause_pre = '';
+    my $limit_clause_pos = '';
+
+    if ( $per_page) {
+        if ( $first >  0 ) {
+                $first++;
+        }       
+        $limit_clause_pre = " SELECT * FROM ( SELECT A.*, ROWNUM LIMRNUM FROM ( ";
+        $limit_clause_pos = " ) A WHERE ROWNUM <= " . ($first + $per_page) . " ) WHERE LIMRNUM > " . $first ;
+    }
+
+   $$statementref = $limit_clause_pre . $$statementref . $limit_clause_pos;
+
+}
+
+# }}}
 

-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.