More moving around

[dbsrgits/DBIx-Class.git] / t / 746mssql.t

diff --git a/t/746mssql.t b/t/746mssql.t
index 88a6f7d..8a97776 100644 (file)
--- a/t/746mssql.t
+++ b/t/746mssql.t
@@ -178,10 +178,10 @@ is $rs->find($row->id)->amount, undef,'updated money value to NULL round-trip';
 
 $schema->storage->dbh_do (sub {
     my ($storage, $dbh) = @_;
-    eval { $dbh->do("DROP TABLE Owners") };
-    eval { $dbh->do("DROP TABLE Books") };
+    eval { $dbh->do("DROP TABLE owners") };
+    eval { $dbh->do("DROP TABLE books") };
     $dbh->do(<<'SQL');
-CREATE TABLE Books (
+CREATE TABLE books (
    id INT IDENTITY (1, 1) NOT NULL,
    source VARCHAR(100),
    owner INT,
@@ -189,7 +189,7 @@ CREATE TABLE Books (
    price INT NULL
 )
 
-CREATE TABLE Owners (
+CREATE TABLE owners (
    id INT IDENTITY (1, 1) NOT NULL,
    name VARCHAR(100),
 )
@@ -205,10 +205,10 @@ lives_ok ( sub {
     [qw/1   wiggle/],
     [qw/2   woggle/],
     [qw/3   boggle/],
-    [qw/4   fREW/],
-    [qw/5   fRIOUX/],
-    [qw/6   fROOH/],
-    [qw/7   fRUE/],
+    [qw/4   fRIOUX/],
+    [qw/5   fRUE/],
+    [qw/6   fREW/],
+    [qw/7   fROOH/],
     [qw/8   fISMBoC/],
     [qw/9   station/],
     [qw/10   mirror/],
@@ -267,6 +267,53 @@ lives_ok ( sub {
   is ($owners->count, 8, 'Correct amount of book owners');
   is ($owners->all, 8, 'Correct amount of book owner objects');
 }
+# make sure right-join-side single-prefetch ordering limit works
+{
+  my $rs = $schema->resultset ('BooksInLibrary')->search (
+    {
+      'owner.name' => { '!=', 'woggle' },
+    },
+    {
+      prefetch => 'owner',
+      order_by => 'owner.name',
+    }
+  );
+  # this is the order in which they should come from the above query
+  my @owner_names = qw/boggle fISMBoC fREW fRIOUX fROOH fRUE wiggle wiggle/;
+
+  is ($rs->all, 8, 'Correct amount of objects from right-sorted joined resultset');
+  is_deeply (
+    [map { $_->owner->name } ($rs->all) ],
+    \@owner_names,
+    'Rows were properly ordered'
+  );
+
+  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2});
+  is ($limited_rs->count, 6, 'Correct count of limited right-sorted joined resultset');
+  is ($limited_rs->count_rs->next, 6, 'Correct count_rs of limited right-sorted joined resultset');
+
+  my $queries;
+  $schema->storage->debugcb(sub { $queries++; });
+  $schema->storage->debug(1);
+
+  is_deeply (
+    [map { $_->owner->name } ($limited_rs->all) ],
+    [@owner_names[2 .. 7]],
+    'Limited rows were properly ordered'
+  );
+  is ($queries, 1, 'Only one query with prefetch');
+
+  $schema->storage->debugcb(undef);
+  $schema->storage->debug(0);
+
+
+  is_deeply (
+    [map { $_->name } ($limited_rs->search_related ('owner')->all) ],
+    [@owner_names[2 .. 7]],
+    'Rows are still properly ordered after search_related'
+  );
+}
+
 
 #
 # try a prefetch on tables with identically named columns
@@ -337,53 +384,23 @@ $schema->storage->_sql_maker->{name_sep} = '.';
   is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
 }
 
-# make sure right-join-side single-prefetch ordering limit works
+
+
+# Just to aid bug-hunting, delete block before merging
 {
-  my $rs = $schema->resultset ('BooksInLibrary')->search (
+
+  my $limited_rs = $schema->resultset ('BooksInLibrary')->search (
     {
       'owner.name' => { '!=', 'woggle' },
     },
     {
       prefetch => 'owner',
-      order_by => { -desc => 'owner.name' },
+      order_by => 'owner.name',
+      rows => 7,
+      offset => 2,
     }
   );
 
-  is ($rs->all, 8, 'Correct amount of objects from right-sorted joined resultset');
-  is_deeply (
-    [map { $_->owner->name } ($rs->all) ],
-    [qw/wiggle wiggle fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Rows were properly ordered'
-  );
-
-  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2});
-  is ($limited_rs->count, 6, 'Correct count of limited right-sorted joined resultset');
-  is ($limited_rs->count_rs->next, 6, 'Correct count_rs of limited right-sorted joined resultset');
-
-  my $queries;
-  $schema->storage->debugcb(sub { $queries++; });
-  $schema->storage->debug(1);
-
-  is_deeply (
-    [map { $_->owner->name } ($limited_rs->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Limited rows were properly ordered'
-  );
-  is ($queries, 1, 'Only one query with prefetch');
-
-  $schema->storage->debugcb(undef);
-  $schema->storage->debug(0);
-
-
-  is_deeply (
-    [map { $_->name } ($limited_rs->search_related ('owner')->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Rows are still properly ordered after search_related'
-  );
-
-
-# Just to aid bug-hunting, delete block before merging
-{
 
 =begin
 
@@ -417,7 +434,7 @@ Alan's SQL:
         FROM (
           SELECT *
             FROM (
-              SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index 
+              SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index
                 FROM (
                   SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                     FROM (
@@ -425,7 +442,7 @@ Alan's SQL:
                         FROM [books] [me]
                         JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
                       WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-                      ORDER BY [owner].[name] DESC
+                      ORDER BY [owner].[name]
                     ) [me]
                 ) [me]
             ) rno_subq
@@ -433,22 +450,20 @@ Alan's SQL:
         ) [me]
         JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
       WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-      ORDER BY [owner].[name] DESC
+      ORDER BY [owner].[name]
     )',
     [ ([ 'owner.name' => 'woggle' ], [ source => 'Library' ]) x 2 ],
     'Expected SQL executed',
   );
 }
 
-}
-
 done_testing;
 
 # clean up our mess
 END {
   if (my $dbh = eval { $schema->storage->_dbh }) {
     eval { $dbh->do("DROP TABLE $_") }
-      for qw/artist money_test Books Owners/;
+      for qw/artist money_test books owners/;
   }
 }
 # vim:sw=2 sts=2