From: Peter Rabbitson <ribasushi@cpan.org>
Date: Fri, 4 Dec 2009 14:06:11 +0000 (+0000)
Subject: Unify the MSSQL and DB2 RNO implementations - they are the same
X-Git-Tag: v0.08116~103^2~10
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=6553ac3837081c481ecdf269c7aff407c348a807

Unify the MSSQL and DB2 RNO implementations - they are the same
---

diff --git a/Makefile.PL b/Makefile.PL
index c3ebdcc..5b8ae47 100644
--- a/Makefile.PL
+++ b/Makefile.PL
@@ -143,7 +143,7 @@ resources 'repository'  => 'http://dev.catalyst.perl.org/repos/bast/DBIx-Class/'
 resources 'MailingList' => 'http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class';
 
 no_index 'DBIx::Class::SQLAHacks';
-no_index 'DBIx::Class::SQLAHacks::MSSQL';
+no_index 'DBIx::Class::SQLAHacks::MySQL';
 no_index 'DBIx::Class::SQLAHacks::OracleJoins';
 no_index 'DBIx::Class::Storage::DBI::AmbiguousGlob';
 no_index 'DBIx::Class::Storage::DBIHacks';
diff --git a/lib/DBIx/Class/SQLAHacks.pm b/lib/DBIx/Class/SQLAHacks.pm
index 81661fb..3208a76 100644
--- a/lib/DBIx/Class/SQLAHacks.pm
+++ b/lib/DBIx/Class/SQLAHacks.pm
@@ -47,31 +47,31 @@ sub new {
 }
 
 
-# Slow but ANSI standard Limit/Offset support. DB2 uses this
+# ANSI standard Limit/Offset implementation. DB2 and MSSQL use this
 sub _RowNumberOver {
   my ($self, $sql, $order, $rows, $offset ) = @_;
 
-  $offset += 1;
-  my $last = $rows + $offset - 1;
-  my ( $order_by ) = $self->_order_by( $order );
+  # get the order_by only (or make up an order if none exists)
+  my $order_by = $self->_order_by(
+    (delete $order->{order_by}) || \ '(SELECT (1))'
+  );
 
-  $sql = <<"SQL";
-SELECT * FROM
-(
-   SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM (
-      $sql
-      $order_by
-   ) Q1
-) Q2
-WHERE ROW_NUM BETWEEN $offset AND $last
+  # whatever is left
+  my $group_having = $self->_order_by($order);
 
-SQL
+  $sql = sprintf (<<'EOS', $order_by, $sql, $group_having, $offset + 1, $offset + $rows, );
+
+SELECT * FROM (
+  SELECT orig_query.*, ROW_NUMBER() OVER(%s ) AS rno__row__index FROM (%s%s) orig_query
+) rno_subq WHERE rno__row__index BETWEEN %d AND %d
+
+EOS
 
+  $sql =~ s/\s*\n\s*/ /g;   # easier to read in the debugger
   return $sql;
 }
 
-# Crappy Top based Limit/Offset support. MSSQL uses this currently,
-# but may have to switch to RowNumberOver one day
+# Crappy Top based Limit/Offset support. Legacy from MSSQL.
 sub _Top {
   my ( $self, $sql, $order, $rows, $offset ) = @_;
 
diff --git a/lib/DBIx/Class/SQLAHacks/MSSQL.pm b/lib/DBIx/Class/SQLAHacks/MSSQL.pm
deleted file mode 100644
index ee2e4cf..0000000
--- a/lib/DBIx/Class/SQLAHacks/MSSQL.pm
+++ /dev/null
@@ -1,36 +0,0 @@
-package # Hide from PAUSE
-  DBIx::Class::SQLAHacks::MSSQL;
-
-use warnings;
-use strict;
-
-use base qw( DBIx::Class::SQLAHacks );
-use Carp::Clan qw/^DBIx::Class|^SQL::Abstract/;
-
-# an MSSQL-specific implementation of the Row-Number-Over limiting
-# technique
-
-sub _MSRowNumberOver {
-  my ($self, $sql, $order, $rows, $offset ) = @_;
-
-  # get the order_by only (or make up an order if none exists)
-  my $order_by = $self->_order_by(
-    (delete $order->{order_by}) || \ '(SELECT (1))'
-  );
-
-  # whatever is left
-  my $group_having = $self->_order_by($order);
-
-  $sql = sprintf (<<'EOS', $order_by, $sql, $group_having, $offset + 1, $offset + $rows, );
-
-SELECT * FROM (
-  SELECT orig_query.*, ROW_NUMBER() OVER(%s ) AS rno__row__index FROM (%s%s) orig_query
-) rno_subq WHERE rno__row__index BETWEEN %d AND %d
-
-EOS
-
-  $sql =~ s/\s*\n\s*/ /g;   # easier to read in the debugger
-  return $sql;
-}
-
-1;
diff --git a/lib/DBIx/Class/Storage/DBI/MSSQL.pm b/lib/DBIx/Class/Storage/DBI/MSSQL.pm
index 6dd3085..28b87f2 100644
--- a/lib/DBIx/Class/Storage/DBI/MSSQL.pm
+++ b/lib/DBIx/Class/Storage/DBI/MSSQL.pm
@@ -247,7 +247,7 @@ sub _sql_maker_opts {
     $self->{_sql_maker_opts} = { %$opts };
   }
 
-  return { limit_dialect => 'MSRowNumberOver', %{$self->{_sql_maker_opts}||{}} };
+  return { limit_dialect => 'RowNumberOver', %{$self->{_sql_maker_opts}||{}} };
 }
 
 1;