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/;
-#
-# MSSQL is retarded wrt TOP (crappy limit) and ordering.
-# One needs to add a TOP to *all* ordered subqueries, if
-# TOP has been used in the statement at least once.
-# Do it here.
-#
-sub select {
- my $self = shift;
-
- my ($sql, @bind) = $self->SUPER::select (@_);
-
- # ordering was requested and there are at least 2 SELECT/FROM pairs
- # (thus subquery), and there is no TOP specified
- if (
- $sql =~ /\bSELECT\b .+? \bFROM\b .+? \bSELECT\b .+? \bFROM\b/isx
- &&
- $sql !~ /^ \s* SELECT \s+ TOP \s+ \d+ /xi
- &&
- scalar $self->_order_by_chunks ($_[3]->{order_by})
- ) {
- $sql =~ s/^ \s* SELECT \s/SELECT TOP 100 PERCENT /xi;
- }
-
- return wantarray ? ($sql, @bind) : $sql;
+# 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;