X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle_hq.t;h=538fdf8ba66ec64ca5fe42956d2429e0c6c816f1;hb=20beaf5fcd9bf0ee24805ee60013ad616e0f5363;hp=d79fae3e6dbdada00297f0153d49f5212aafa6c0;hpb=994dc91b510ccb6d25438bd2dd0e308a0a46d4c5;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/73oracle_hq.t b/t/73oracle_hq.t
index d79fae3..538fdf8 100644
--- a/t/73oracle_hq.t
+++ b/t/73oracle_hq.t
@@ -3,10 +3,14 @@ use warnings;
 
 use Test::Exception;
 use Test::More;
-
+use DBIx::Class::Optional::Dependencies ();
 use lib qw(t/lib);
 use DBIC::SqlMakerTest;
 
+use DBIx::Class::SQLMaker::LimitDialects;
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
+
 $ENV{NLS_SORT} = "BINARY";
 $ENV{NLS_COMP} = "BINARY";
 $ENV{NLS_LANG} = "AMERICAN";
@@ -16,6 +20,9 @@ my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/
 plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test.'
  unless ($dsn && $user && $pass);
 
+plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('rdbms_oracle')
+  unless DBIx::Class::Optional::Dependencies->req_ok_for ('rdbms_oracle');
+
 use DBICTest::Schema::Artist;
 BEGIN {
   DBICTest::Schema::Artist->add_column('parentid');
@@ -111,9 +118,10 @@ do_creates($dbh);
         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
           FROM artist me
         START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
       )',
-      [ [ name => 'root'] ],
+      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'] ],
     );
     is_deeply (
       [ $rs->get_column ('name')->all ],
@@ -127,9 +135,10 @@ do_creates($dbh);
         SELECT COUNT( * )
           FROM artist me
         START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
       )',
-      [ [ name => 'root'] ],
+      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'] ],
     );
 
     is( $rs->count, 5, 'Connect By count ok' );
@@ -153,10 +162,11 @@ do_creates($dbh);
         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
           FROM artist me
         START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
         ORDER SIBLINGS BY name DESC
       )',
-      [ [ name => 'root'] ],
+      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'] ],
     );
 
     is_deeply (
@@ -180,9 +190,10 @@ do_creates($dbh);
           FROM artist me
         WHERE ( parentid IS NULL )
         START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
       )',
-      [ [ name => 'root'] ],
+      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'] ],
     );
 
     is_deeply(
@@ -215,9 +226,14 @@ do_creates($dbh);
           LEFT JOIN cd cds ON cds.artist = me.artistid
         WHERE ( cds.title LIKE ? )
         START WITH me.name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
       )',
-      [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
+            => '%cd'],
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
+            => 'root'],
+      ],
     );
 
     is_deeply(
@@ -234,9 +250,14 @@ do_creates($dbh);
           LEFT JOIN cd cds ON cds.artist = me.artistid
         WHERE ( cds.title LIKE ? )
         START WITH me.name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
       )',
-      [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
+            => '%cd'],
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
+            => 'root'],
+      ],
     );
 
     is( $rs->count, 1, 'Connect By with a join; count ok' );
@@ -256,10 +277,13 @@ do_creates($dbh);
         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
           FROM artist me
         START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
+        CONNECT BY parentid = PRIOR artistid
         ORDER BY LEVEL ASC, name ASC
       )',
-      [ [ name => 'root' ] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'],
+      ],
     );
 
 
@@ -292,7 +316,7 @@ do_creates($dbh);
     my $rs = $schema->resultset('Artist')->search({}, {
       start_with => { name => 'root' },
       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
-      order_by => { -asc => 'name' },
+      order_by => [ { -asc => 'name' }, {  -desc => 'artistid' } ],
       rows => 2,
     });
 
@@ -305,11 +329,14 @@ do_creates($dbh);
               FROM artist me
             START WITH name = ?
             CONNECT BY parentid = PRIOR artistid
-            ORDER BY name ASC
+            ORDER BY name ASC, artistid DESC
           ) me
-        WHERE ROWNUM <= 2
+        WHERE ROWNUM <= ?
       )',
-      [ [ name => 'root' ] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'], [ $ROWS => 2 ],
+      ],
     );
 
     is_deeply (
@@ -330,40 +357,55 @@ do_creates($dbh);
                 START WITH name = ?
                 CONNECT BY parentid = PRIOR artistid
               ) me
-            WHERE ROWNUM <= 2
+            WHERE ROWNUM <= ?
           ) me
       )',
-      [ [ name => 'root' ] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'],
+        [ $ROWS => 2 ],
+      ],
     );
 
     is( $rs->count, 2, 'Connect By; LIMIT count ok' );
   }
 
   # combine a connect_by with group_by and having
+  # add some bindvals to make sure things still work
   {
     my $rs = $schema->resultset('Artist')->search({}, {
-      select => { count => 'rank', -as => 'cnt' },
+      select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
+      as => 'cnt',
       start_with => { name => 'root' },
       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
-      group_by => ['rank'],
+      group_by => \[ 'rank + ? ', [ __gbind =>  1] ],
       having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
     });
 
     is_same_sql_bind (
       $rs->as_query,
       '(
-        SELECT COUNT(rank) AS cnt
+        SELECT COUNT(rank) + ?
           FROM artist me
         START WITH name = ?
         CONNECT BY parentid = PRIOR artistid
-        GROUP BY rank HAVING count(rank) < ?
+        GROUP BY( rank + ? ) HAVING count(rank) < ?
       )',
-      [ [ name => 'root' ], [ cnt => 2 ] ],
+      [
+        [ { dbic_colname => '__cbind' }
+            => 3 ],
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'root'],
+        [ { dbic_colname => '__gbind' }
+            => 1 ],
+        [ { dbic_colname => 'cnt' }
+            => 2 ],
+      ],
     );
 
     is_deeply (
       [ $rs->get_column ('cnt')->all ],
-      [1, 1],
+      [4, 4],
       'Group By a Connect By query - correct values'
     );
   }
@@ -399,9 +441,12 @@ do_creates($dbh);
         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
           FROM artist me
         START WITH name = ?
-        CONNECT BY NOCYCLE parentid = PRIOR artistid 
+        CONNECT BY NOCYCLE parentid = PRIOR artistid
       )',
-      [ [ name => 'cycle-root'] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'cycle-root'],
+      ],
     );
     is_deeply (
       [ $rs->get_column ('name')->all ],
@@ -420,9 +465,12 @@ do_creates($dbh);
         SELECT COUNT( * )
           FROM artist me
         START WITH name = ?
-        CONNECT BY NOCYCLE parentid = PRIOR artistid 
+        CONNECT BY NOCYCLE parentid = PRIOR artistid
       )',
-      [ [ name => 'cycle-root'] ],
+      [
+        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
+            => 'cycle-root'],
+      ],
     );
 
     is( $rs->count, 4, 'Connect By Nocycle count ok' );
@@ -505,13 +553,15 @@ sub do_creates {
 
 # clean up our mess
 END {
-  eval {
-    my $dbh = $schema->storage->dbh;
-    $dbh->do("DROP SEQUENCE artist_pk_seq");
-    $dbh->do("DROP SEQUENCE cd_seq");
-    $dbh->do("DROP SEQUENCE track_seq");
-    $dbh->do("DROP TABLE artist");
-    $dbh->do("DROP TABLE track");
-    $dbh->do("DROP TABLE cd");
+  if ($schema and my $dbh = $schema->storage->dbh) {
+    eval { $dbh->do($_) } for (
+      'DROP SEQUENCE artist_pk_seq',
+      'DROP SEQUENCE cd_seq',
+      'DROP SEQUENCE track_seq',
+      'DROP TABLE artist',
+      'DROP TABLE track',
+      'DROP TABLE cd',
+    );
   };
+  undef $schema;
 }