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

diff --git a/t/73oracle_hq.t b/t/73oracle_hq.t
index d79fae3..0dde669 100644
--- a/t/73oracle_hq.t
+++ b/t/73oracle_hq.t
@@ -1,20 +1,15 @@
+BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
+use DBIx::Class::Optional::Dependencies -skip_all_without => 'test_rdbms_oracle';
+
 use strict;
 use warnings;
 
 use Test::Exception;
 use Test::More;
 
-use lib qw(t/lib);
-use DBIC::SqlMakerTest;
-
-$ENV{NLS_SORT} = "BINARY";
-$ENV{NLS_COMP} = "BINARY";
-$ENV{NLS_LANG} = "AMERICAN";
-
-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);
+# I *strongly* suspect Oracle has an implicit stable output order when
+# dealing with HQs. So just punt on the entire shuffle thing.
+BEGIN { $ENV{DBIC_SHUFFLE_UNORDERED_RESULTSETS} = 0 }
 
 use DBICTest::Schema::Artist;
 BEGIN {
@@ -31,8 +26,14 @@ BEGIN {
   );
 }
 
+use DBICTest;
 use DBICTest::Schema;
 
+$ENV{NLS_SORT} = "BINARY";
+$ENV{NLS_COMP} = "BINARY";
+$ENV{NLS_LANG} = "AMERICAN";
+
+my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};
 my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
 
 note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
@@ -105,33 +106,12 @@ do_creates($dbh);
       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
-      )',
-      [ [ name => 'root'] ],
-    );
     is_deeply (
       [ $rs->get_column ('name')->all ],
       [ qw/root child1 grandchild greatgrandchild child2/ ],
       'got artist tree',
     );
 
-    is_same_sql_bind (
-      $rs->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
-      )',
-      [ [ name => 'root'] ],
-    );
-
     is( $rs->count, 5, 'Connect By count ok' );
   }
 
@@ -147,18 +127,6 @@ do_creates($dbh);
       order_siblings_by => { -desc => 'name' },
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
-        ORDER SIBLINGS BY name DESC
-      )',
-      [ [ name => 'root'] ],
-    );
-
     is_deeply (
       [ $rs->get_column ('name')->all ],
       [ qw/root child2 child1 grandchild greatgrandchild/ ],
@@ -173,18 +141,6 @@ do_creates($dbh);
       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-          FROM artist me
-        WHERE ( parentid IS NULL )
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
-      )',
-      [ [ name => 'root'] ],
-    );
-
     is_deeply(
       [ $rs->get_column('name')->all ],
       [ 'root' ],
@@ -207,38 +163,12 @@ do_creates($dbh);
       }
     );
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-          FROM artist me
-          LEFT JOIN cd cds ON cds.artist = me.artistid
-        WHERE ( cds.title LIKE ? )
-        START WITH me.name = ?
-        CONNECT BY parentid = PRIOR artistid 
-      )',
-      [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
-    );
-
     is_deeply(
       [ $rs->get_column('name')->all ],
       [ 'grandchild' ],
       'Connect By with a join result name ok'
     );
 
-    is_same_sql_bind (
-      $rs->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM artist me
-          LEFT JOIN cd cds ON cds.artist = me.artistid
-        WHERE ( cds.title LIKE ? )
-        START WITH me.name = ?
-        CONNECT BY parentid = PRIOR artistid 
-      )',
-      [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
-    );
-
     is( $rs->count, 1, 'Connect By with a join; count ok' );
   }
 
@@ -250,19 +180,6 @@ do_creates($dbh);
       order_by => { -asc => [ 'LEVEL', 'name' ] },
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid 
-        ORDER BY LEVEL ASC, name ASC
-      )',
-      [ [ name => 'root' ] ],
-    );
-
-
     # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
     #   If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
     # TODO: write extra test and fix order by handling on Oracle 8i
@@ -292,78 +209,34 @@ 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,
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        SELECT artistid, name, rank, charfield, parentid
-          FROM (
-            SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
-              FROM artist me
-            START WITH name = ?
-            CONNECT BY parentid = PRIOR artistid
-            ORDER BY name ASC
-          ) me
-        WHERE ROWNUM <= 2
-      )',
-      [ [ name => 'root' ] ],
-    );
-
     is_deeply (
       [ $rs->get_column ('name')->all ],
       [qw/child1 child2/],
       'LIMIT a Connect By query - correct names'
     );
 
-    is_same_sql_bind (
-      $rs->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM (
-            SELECT artistid
-              FROM (
-                SELECT me.artistid
-                  FROM artist me
-                START WITH name = ?
-                CONNECT BY parentid = PRIOR artistid
-              ) me
-            WHERE ROWNUM <= 2
-          ) me
-      )',
-      [ [ name => 'root' ] ],
-    );
-
     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
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY parentid = PRIOR artistid
-        GROUP BY rank HAVING count(rank) < ?
-      )',
-      [ [ name => 'root' ], [ cnt => 2 ] ],
-    );
-
     is_deeply (
       [ $rs->get_column ('cnt')->all ],
-      [1, 1],
+      [4, 4],
       'Group By a Connect By query - correct values'
     );
   }
@@ -393,16 +266,6 @@ do_creates($dbh);
       connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
     });
 
-    is_same_sql_bind (
-      $rs->as_query,
-      '(
-        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 
-      )',
-      [ [ name => 'cycle-root'] ],
-    );
     is_deeply (
       [ $rs->get_column ('name')->all ],
       [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
@@ -414,17 +277,6 @@ do_creates($dbh);
       'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
     );
 
-    is_same_sql_bind (
-      $rs->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM artist me
-        START WITH name = ?
-        CONNECT BY NOCYCLE parentid = PRIOR artistid 
-      )',
-      [ [ name => 'cycle-root'] ],
-    );
-
     is( $rs->count, 4, 'Connect By Nocycle count ok' );
   }
 }
@@ -505,13 +357,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;
 }