Massively refactor and sanify condition collapsing

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

diff --git a/t/sqlmaker/limit_dialects/torture.t b/t/sqlmaker/limit_dialects/torture.t
index f273189..74e60a2 100644 (file)
--- a/t/sqlmaker/limit_dialects/torture.t
+++ b/t/sqlmaker/limit_dialects/torture.t
@@ -11,9 +11,11 @@ use DBIC::SqlMakerTest;
 my $schema = DBICTest->init_schema;
 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
 
+my $where_string = 'me.title = ? AND source != ? AND source = ?';
+
 my @where_bind = (
-  [ {} => 'Study' ],
   [ {} => 'kama sutra' ],
+  [ {} => 'Study' ],
   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
 );
 my @select_bind = (
@@ -37,16 +39,16 @@ my $tests = {
 
   LimitOffset => {
     limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         LIMIT ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -56,17 +58,17 @@ my $tests = {
       ],
     ],
     limit_offset => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         LIMIT ?
         OFFSET ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -77,17 +79,17 @@ my $tests = {
       ],
     ],
     ordered_limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -98,18 +100,18 @@ my $tests = {
       ]
     ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?
         OFFSET ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -121,7 +123,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -130,7 +132,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -140,17 +142,17 @@ my $tests = {
 
   LimitXY => {
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -162,7 +164,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -171,7 +173,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -181,16 +183,16 @@ my $tests = {
 
   SkipFirst => {
     ordered_limit_offset => [
-      '(
+      "(
         SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 4 ],
@@ -202,7 +204,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT SKIP ? FIRST ? me.name, me.id
@@ -210,7 +212,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -220,16 +222,16 @@ my $tests = {
 
   FirstSkip => {
     ordered_limit_offset => [
-      '(
+      "(
         SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 4 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -241,7 +243,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT FIRST ? SKIP ? me.name, me.id
@@ -249,7 +251,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -258,7 +260,7 @@ my $tests = {
   },
 
   RowNumberOver => do {
-    my $unordered_sql = '(
+    my $unordered_sql = "(
       SELECT me.id, owner__id, owner__name, bar, baz
         FROM (
           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
@@ -267,15 +269,15 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
+              WHERE $where_string
               GROUP BY (me.id / ?), owner.id
               HAVING ?
             ) me
       ) me
       WHERE rno__row__index >= ? AND rno__row__index <= ?
-    )';
+    )";
 
-    my $ordered_sql = '(
+    my $ordered_sql = "(
       SELECT me.id, owner__id, owner__name, bar, baz
         FROM (
           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
@@ -285,13 +287,13 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
+              WHERE $where_string
               GROUP BY (me.id / ?), owner.id
               HAVING ?
             ) me
       ) me
       WHERE rno__row__index >= ? AND rno__row__index <= ?
-    )';
+    )";
 
     {
       limit => [$unordered_sql,
@@ -337,7 +339,7 @@ my $tests = {
         ],
       ],
       limit_offset_prefetch => [
-        '(
+        "(
           SELECT me.name, books.id, books.source, books.owner, books.title, books.price
             FROM (
               SELECT me.name, me.id
@@ -351,7 +353,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -362,20 +364,20 @@ my $tests = {
 
   RowNum => do {
     my $limit_sql = sub {
-      sprintf '(
+      sprintf "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
               FROM books me
               JOIN owners owner
                 ON owner.id = me.owner
-            WHERE source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
             %s
           ) me
         WHERE ROWNUM <= ?
-      )', $_[0] || '';
+      )", $_[0] || '';
     };
 
     {
@@ -389,7 +391,7 @@ my $tests = {
         ],
       ],
       limit_offset => [
-        '(
+        "(
           SELECT me.id, owner__id, owner__name, bar, baz
             FROM (
               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
@@ -398,13 +400,13 @@ my $tests = {
                     FROM books me
                     JOIN owners owner
                       ON owner.id = me.owner
-                  WHERE source != ? AND me.title = ? AND source = ?
+                  WHERE $where_string
                   GROUP BY (me.id / ?), owner.id
                   HAVING ?
                 ) me
             ) me
           WHERE rownum__index BETWEEN ? AND ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -425,7 +427,7 @@ my $tests = {
         ],
       ],
       ordered_limit_offset => [
-        '(
+        "(
           SELECT me.id, owner__id, owner__name, bar, baz
             FROM (
               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
@@ -434,7 +436,7 @@ my $tests = {
                     FROM books me
                     JOIN owners owner
                       ON owner.id = me.owner
-                  WHERE source != ? AND me.title = ? AND source = ?
+                  WHERE $where_string
                   GROUP BY (me.id / ?), owner.id
                   HAVING ?
                   ORDER BY ? / ?, ?
@@ -442,7 +444,7 @@ my $tests = {
               WHERE ROWNUM <= ?
             ) me
           WHERE rownum__index >= ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -454,7 +456,7 @@ my $tests = {
         ],
       ],
       limit_offset_prefetch => [
-        '(
+        "(
           SELECT me.name, books.id, books.source, books.owner, books.title, books.price
             FROM (
               SELECT me.name, me.id
@@ -468,7 +470,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -479,16 +481,16 @@ my $tests = {
 
   FetchFirst => {
     limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -497,14 +499,14 @@ my $tests = {
       ],
     ],
     limit_offset => [
-      '(
+      "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
               FROM books me
               JOIN owners owner
                 ON owner.id = me.owner
-            WHERE source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
             ORDER BY me.id
@@ -512,7 +514,7 @@ my $tests = {
           ) me
         ORDER BY me.id DESC
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -521,17 +523,17 @@ my $tests = {
       ],
     ],
     ordered_limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -541,7 +543,7 @@ my $tests = {
       ],
     ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
@@ -550,7 +552,7 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
+                WHERE $where_string
                 GROUP BY (me.id / ?), owner.id
                 HAVING ?
                 ORDER BY ? / ?, ?
@@ -560,7 +562,7 @@ my $tests = {
             FETCH FIRST 4 ROWS ONLY
           ) me
         ORDER BY ORDER__BY__001, ORDER__BY__002
-      )',
+      )",
       [
         @select_bind,
         @order_bind,
@@ -571,7 +573,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -586,22 +588,22 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [],
     ],
   },
 
   Top => {
     limit => [
-      '(
+      "(
         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -610,20 +612,20 @@ my $tests = {
       ],
     ],
     limit_offset => [
-      '(
+      "(
         SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
               FROM books me
               JOIN owners owner
                 ON owner.id = me.owner
-            WHERE source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
             ORDER BY me.id
           ) me
         ORDER BY me.id DESC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -632,16 +634,16 @@ my $tests = {
       ],
     ],
     ordered_limit => [
-      '(
+      "(
         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -651,7 +653,7 @@ my $tests = {
       ],
     ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
@@ -660,7 +662,7 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
+                WHERE $where_string
                 GROUP BY (me.id / ?), owner.id
                 HAVING ?
                 ORDER BY ? / ?, ?
@@ -668,7 +670,7 @@ my $tests = {
             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
           ) me
         ORDER BY ORDER__BY__001, ORDER__BY__002
-      )',
+      )",
       [
         @select_bind,
         @order_bind,
@@ -679,7 +681,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT TOP 3 me.name, me.id
@@ -692,21 +694,21 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [],
     ],
   },
 
   GenericSubQ => {
     ordered_limit => [
-      '(
+      "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
               FROM books me
               JOIN owners owner
                 ON owner.id = me.owner
-            WHERE source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
@@ -735,7 +737,7 @@ my $tests = {
             )
           ) < ?
         ORDER BY me.price DESC, me.id ASC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -745,14 +747,14 @@ my $tests = {
       ],
     ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
               FROM books me
               JOIN owners owner
                 ON owner.id = me.owner
-            WHERE source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
@@ -781,7 +783,7 @@ my $tests = {
             )
           ) BETWEEN ? AND ?
         ORDER BY me.price DESC, me.id ASC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -792,7 +794,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -819,7 +821,7 @@ my $tests = {
           LEFT JOIN books books
             ON books.owner = me.id
         ORDER BY me.name ASC, me.id DESC
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],