Move find_co_root into DBICTest::Util

[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / rownum.t

diff --git a/t/sqlmaker/limit_dialects/rownum.t b/t/sqlmaker/limit_dialects/rownum.t
index 522b4d4..806bba4 100644 (file)
--- a/t/sqlmaker/limit_dialects/rownum.t
+++ b/t/sqlmaker/limit_dialects/rownum.t
@@ -4,13 +4,13 @@ use warnings;
 use Test::More;
 
 use lib qw(t/lib);
-use DBICTest;
-use DBIC::SqlMakerTest;
+use DBICTest ':DiffSQL';
 use DBIx::Class::SQLMaker::LimitDialects;
 
-my ($TOTAL, $OFFSET) = (
+my ($TOTAL, $OFFSET, $ROWS) = (
    DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
    DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+   DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
 );
 
 my $s = DBICTest->init_schema (no_deploy => 1, );
@@ -18,6 +18,12 @@ $s->storage->sql_maker->limit_dialect ('RowNum');
 
 my $rs = $s->resultset ('CD')->search({ id => 1 });
 
+# important for a test below, never traversed
+$rs->result_source->add_relationship(
+  ends_with_me => 'DBICTest::Schema::Artist', sub {}
+);
+
+
 my $where_bind = [ { dbic_colname => 'id' }, 1 ];
 
 for my $test_set (
@@ -28,16 +34,16 @@ for my $test_set (
       offset => 3,
       columns => [
         { id => 'foo.id' },
-        { 'bar.id' => 'bar.id' },
+        { 'artist.id' => 'bar.id' },
         { bleh => \'TO_CHAR (foo.womble, "blah")' },
       ]
     }),
     sql => '(
-      SELECT id, bar__id, bleh
+      SELECT id, artist__id, bleh
       FROM (
-        SELECT id, bar__id, bleh, ROWNUM rownum__index
+        SELECT id, artist__id, bleh, ROWNUM AS rownum__index
         FROM (
-          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR (foo.womble, "blah") AS bleh
+          SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR (foo.womble, "blah") AS bleh
             FROM cd me
           WHERE id = ?
         ) me
@@ -55,17 +61,17 @@ for my $test_set (
       offset => 3,
       columns => [
         { id => 'foo.id' },
-        { 'bar.id' => 'bar.id' },
+        { 'artist.id' => 'bar.id' },
         { bleh => \'TO_CHAR (foo.womble, "blah")' },
       ],
       order_by => [qw( artist title )],
     }),
     sql => '(
-      SELECT id, bar__id, bleh
+      SELECT id, artist__id, bleh
       FROM (
-        SELECT id, bar__id, bleh, ROWNUM rownum__index
+        SELECT id, artist__id, bleh, ROWNUM AS rownum__index
         FROM (
-          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
+          SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR(foo.womble, "blah") AS bleh
             FROM cd me
           WHERE id = ?
           ORDER BY artist, title
@@ -87,17 +93,17 @@ for my $test_set (
       offset => 3,
       columns => [
         { id => 'foo.id' },
-        { 'bar.id' => 'bar.id' },
+        { 'artist.id' => 'bar.id' },
         { bleh => \'TO_CHAR (foo.womble, "blah")' },
       ],
       order_by => 'artist',
     }),
     sql => '(
-      SELECT id, bar__id, bleh
+      SELECT id, artist__id, bleh
       FROM (
-        SELECT id, bar__id, bleh, ROWNUM rownum__index
+        SELECT id, artist__id, bleh, ROWNUM AS rownum__index
         FROM (
-          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
+          SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR(foo.womble, "blah") AS bleh
             FROM cd me
           WHERE id = ?
           ORDER BY artist
@@ -123,7 +129,7 @@ for my $test_set (
     sql => '(
       SELECT id, ends_with_me__id
       FROM (
-        SELECT id, ends_with_me__id, ROWNUM rownum__index
+        SELECT id, ends_with_me__id, ROWNUM AS rownum__index
         FROM (
           SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
             FROM cd me
@@ -145,17 +151,17 @@ for my $test_set (
         { id => 'foo.id' },
         { 'ends_with_me.id' => 'ends_with_me.id' },
       ],
-      order_by => [qw( artist title )],
+      order_by => [qw( year artist title )],
     }),
     sql => '(
       SELECT id, ends_with_me__id
       FROM (
-        SELECT id, ends_with_me__id, ROWNUM rownum__index
+        SELECT id, ends_with_me__id, ROWNUM AS rownum__index
         FROM (
           SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
             FROM cd me
           WHERE id = ?
-          ORDER BY artist, title
+          ORDER BY year, artist, title
         ) me
         WHERE ROWNUM <= ?
       ) me
@@ -195,7 +201,7 @@ is_same_sql_bind(
   '(
     SELECT owner_name, owner_books
       FROM (
-        SELECT owner_name, owner_books, ROWNUM rownum__index
+        SELECT owner_name, owner_books, ROWNUM AS rownum__index
           FROM (
             SELECT  owner.name AS owner_name,
               ( SELECT COUNT( * ) FROM owners owner WHERE (count.id = owner.id)) AS owner_books
@@ -243,21 +249,18 @@ my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search( { -exists => $sub
 is_same_sql_bind(
   $rs_selectas_rel->as_query,
   '(
-    SELECT id, owner FROM (
-      SELECT id, owner, ROWNUM rownum__index FROM (
-        SELECT me.id, me.owner  FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) )
-      ) me
-    ) me WHERE rownum__index BETWEEN ? AND ?
+    SELECT me.id, me.owner FROM (
+      SELECT me.id, me.owner  FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) )
+    ) me
+    WHERE ROWNUM <= ?
   )',
   [
     [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
-    [ $OFFSET => 1 ],
-    [ $TOTAL => 1 ],
+    [ $ROWS => 1 ],
   ],
   'Pagination with sub-query in WHERE works'
 );
 
 }
 
-
 done_testing;