X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FSQLMaker%2FLimitDialects.pm;h=6bb7b9282f7964b74e300f5892a6f4b3a6e7192f;hb=7cec43565df63cdbf6320721c7d7c33cb6ce6e96;hp=e3da121881522ea7409080440d4d7c0226c768ed;hpb=69d3c2708f5564ce38d5878fa694b04f6740cde0;p=dbsrgits%2FDBIx-Class.git

diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
index e3da121..6bb7b92 100644
--- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm
+++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
@@ -169,13 +169,13 @@ sub _SkipFirst {
   return sprintf ('SELECT %s%s%s%s',
     $offset
       ? do {
-         push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset];
+         push @{$self->{pre_select_bind}}, [ $self->__offset_bindtype => $offset];
          'SKIP ? '
       }
       : ''
     ,
     do {
-       push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+       push @{$self->{pre_select_bind}}, [ $self->__rows_bindtype => $rows ];
        'FIRST ? '
     },
     $sql,
@@ -199,12 +199,12 @@ sub _FirstSkip {
 
   return sprintf ('SELECT %s%s%s%s',
     do {
-       push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+       push @{$self->{pre_select_bind}}, [ $self->__rows_bindtype => $rows ];
        'FIRST ? '
     },
     $offset
       ? do {
-         push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset];
+         push @{$self->{pre_select_bind}}, [ $self->__offset_bindtype => $offset];
          'SKIP ? '
       }
       : ''
@@ -214,14 +214,31 @@ sub _FirstSkip {
   );
 }
 
+
 =head2 RowNum
 
+Depending on the resultset attributes one of:
+
  SELECT * FROM (
   SELECT *, ROWNUM rownum__index FROM (
    SELECT ...
   ) WHERE ROWNUM <= ($limit+$offset)
  ) WHERE rownum__index >= ($offset+1)
 
+or
+
+ SELECT * FROM (
+  SELECT *, ROWNUM rownum__index FROM (
+    SELECT ...
+  )
+ ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
+
+or
+
+ SELECT * FROM (
+    SELECT ...
+  ) WHERE ROWNUM <= ($limit+1)
+
 Supported by B<Oracle>.
 
 =cut
@@ -234,33 +251,60 @@ sub _RowNum {
   my $idx_name = $self->_quote ('rownum__index');
   my $order_group_having = $self->_parse_rs_attrs($rs_attrs);
 
+  #
+  # There are two ways to limit in Oracle, one vastly faster than the other
+  # on large resultsets: https://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/
+  # However Oracle is retarded and does not preserve stable ROWNUM() values
+  # when called twice in the same scope. Therefore unless the resultset is
+  # ordered by a unique set of columns, it is not safe to use the faster
+  # method, and the slower BETWEEN query is used instead
+  #
+  # FIXME - this is quite expensive, and does not perform caching of any sort
+  # as soon as some of the DQ work becomes viable consider switching this
+  # over
+  if (
+    $rs_attrs->{order_by}
+      and
+    $rs_attrs->{_rsroot_rsrc}->storage->_order_by_is_stable(
+      $rs_attrs->{from}, $rs_attrs->{order_by}
+    )
+  ) {
+    # if offset is 0 (first page) the we can skip a subquery
+    if (! $offset) {
+      push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
 
-  if ($offset) {
-
-    push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
+      return <<EOS;
+SELECT $outsel FROM (
+  SELECT $insel ${stripped_sql}${order_group_having}
+) $qalias WHERE ROWNUM <= ?
+EOS
+    }
+    else {
+      push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
 
-    return <<EOS;
+      return <<EOS;
 SELECT $outsel FROM (
   SELECT $outsel, ROWNUM $idx_name FROM (
     SELECT $insel ${stripped_sql}${order_group_having}
   ) $qalias WHERE ROWNUM <= ?
 ) $qalias WHERE $idx_name >= ?
 EOS
-
+    }
   }
   else {
-    push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+    push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset + 1 ], [ $self->__total_bindtype => $offset + $rows ];
 
     return <<EOS;
-  SELECT $outsel FROM (
+SELECT $outsel FROM (
+  SELECT $outsel, ROWNUM $idx_name FROM (
     SELECT $insel ${stripped_sql}${order_group_having}
-  ) $qalias WHERE ROWNUM <= ?
+  ) $qalias
+) $qalias WHERE $idx_name BETWEEN ? AND ?
 EOS
-
   }
 }
 
-# used by _Top and _FetchFirst
+# used by _Top and _FetchFirst below
 sub _prep_for_skimming_limit {
   my ( $self, $sql, $rs_attrs ) = @_;
 
@@ -314,11 +358,9 @@ sub _prep_for_skimming_limit {
       $r{mid_sel} .= ', ' . $extra_order_sel->{$extra_col};
     }
 
-    # since whatever order bindvals there are, they will be realiased
-    # and need to show up in front of the entire initial inner subquery
-    # *unshift* the selector bind stack to make this happen (horrible,
-    # horrible, but we don't have another mechanism yet)
-    unshift @{$self->{select_bind}}, @{$self->{order_bind}};
+    # Whatever order bindvals there are, they will be realiased and
+    # need to show up in front of the entire initial inner subquery
+    push @{$self->{pre_select_bind}}, @{$self->{order_bind}};
   }
 
   # and this is order re-alias magic
@@ -608,7 +650,7 @@ $order_sql
 #
 # Returns mangled proto-sql, inner/outer strings of SQL QUOTED selectors
 # with aliases (to be used in whatever select statement), and an alias
-# index hashref of QUOTED SEL => QUOTED ALIAS pairs (to maybe be used 
+# index hashref of QUOTED SEL => QUOTED ALIAS pairs (to maybe be used
 # for string-subst higher up).
 # If an order_by is supplied, the inner select needs to bring out columns
 # used in implicit (non-selected) orders, and the order condition itself
@@ -623,9 +665,14 @@ sub _subqueried_limit_attrs {
     'Limit dialect implementation usable only in the context of DBIC (missing $rs_attrs)'
   ) unless ref ($rs_attrs) eq 'HASH';
 
-  # mangle the input sql as we will be replacing the selector
-  $proto_sql =~ s/^ \s* SELECT \s+ .+ \s+ (?= \b FROM \b )//ix
-    or $self->throw_exception("Unrecognizable SELECT: $proto_sql");
+  # mangle the input sql as we will be replacing the selector entirely
+  unless (
+    $rs_attrs->{_selector_sql}
+      and
+    $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{_selector_sql}//ix
+  ) {
+    $self->throw_exception("Unrecognizable SELECT: $proto_sql");
+  }
 
   my ($re_sep, $re_alias) = map { quotemeta $_ } ( $self->{name_sep}, $rs_attrs->{alias} );