X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle.t;h=62a034912083abc09fd771f2261de8cbcbee0b2e;hb=9f775126bf5575a72f493da091383e8206b9d56b;hp=8c9459218ef4b1ab353fb74fe00a8080a028dcb4;hpb=1756ae89ebe434057d6ef9c242aa93d521a94eec;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/73oracle.t b/t/73oracle.t
index 8c94592..62a0349 100644
--- a/t/73oracle.t
+++ b/t/73oracle.t
@@ -92,7 +92,7 @@ $dbh->do(qq{
 });
 $dbh->do(qq{
   CREATE OR REPLACE TRIGGER cd_insert_trg
-  BEFORE INSERT ON cd
+  BEFORE INSERT OR UPDATE ON cd
   FOR EACH ROW
   BEGIN
     IF :new.cdid IS NULL THEN
@@ -251,6 +251,18 @@ is( $it->next, undef, "next past end of resultset ok" );
   is( scalar @results, 1, "Group by with limit OK" );
 }
 
+# test identifiers over the 30 char limit
+{
+  lives_ok {
+    my @results = $schema->resultset('CD')->search(undef, {
+      prefetch => 'very_long_artist_relationship',
+      rows => 3,
+      offset => 0,
+    })->all;
+    ok( scalar @results > 0, 'limit with long identifiers returned something');
+  } 'limit with long identifiers executed successfully';
+}
+
 # test with_deferred_fk_checks
 lives_ok {
   $schema->storage->with_deferred_fk_checks(sub {
@@ -343,13 +355,16 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
 
     $schema->resultset('Artist')->create ({
         name => 'root',
+        rank => 1,
         cds => [],
         children => [
             {
                 name => 'child1',
+                rank => 2,
                 children => [
                     {
                         name => 'grandchild',
+                        rank => 3,
                         cds => [
                             {
                                 title => "grandchilds's cd" ,
@@ -365,6 +380,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
                         children => [
                             {
                                 name => 'greatgrandchild',
+                                rank => 3,
                             }
                         ],
                     }
@@ -372,6 +388,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
             },
             {
                 name => 'child2',
+                rank => 3,
             },
         ],
     });
@@ -570,22 +587,21 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       is_same_sql_bind (
         $rs->as_query,
         '( 
-            SELECT * FROM (
-                  SELECT A.*, ROWNUM r FROM (
+            SELECT artistid, name, rank, charfield, parentid FROM (
+                  SELECT artistid, name, rank, charfield, parentid, ROWNUM rownum__index FROM (
                       SELECT 
-                          me.artistid AS col1,
-                          me.name AS col2,
-                          me.rank AS col3,
-                          me.charfield AS col4,
-                          me.parentid AS col5 
+                          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
-                  ) A
-                  WHERE ROWNUM < 3
-              ) B
-              WHERE r >= 1 
+                      ORDER BY name ASC 
+                  ) me 
+            ) me
+            WHERE rownum__index BETWEEN 1 AND 2
         )',
         [ [ name => 'root' ] ],
       );
@@ -602,29 +618,58 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       # after count_subq, 
       # I will fix this later...
       # 
-      # is_same_sql_bind (
-      #   $rs->count_rs->as_query,
-      #   '( 
-      #       SELECT COUNT( * ) FROM (
-      #           SELECT * FROM (
-      #               SELECT A.*, ROWNUM r FROM (
-      #                   SELECT 
-      #                       me.artistid AS col1 
-      #                   FROM artist me 
-      #                   START WITH name = ? 
-      #                   CONNECT BY artistid = PRIOR parentid 
-      #               ) A
-      #               WHERE ROWNUM < 3
-      #           ) B
-      #           WHERE r >= 1
-      #       ) count_subq 
-      #   )',
-      #   [ [ name => 'greatgrandchild' ] ],
-      # );
-      # 
-      # is( $rs->count, 2, 'Connect By; LIMIT count ok' );
+      is_same_sql_bind (
+        $rs->count_rs->as_query,
+        '( 
+            SELECT COUNT( * ) FROM (
+                SELECT artistid FROM (
+                    SELECT artistid, ROWNUM rownum__index FROM (
+                        SELECT 
+                            me.artistid
+                        FROM artist me 
+                        START WITH name = ? 
+                        CONNECT BY parentid = PRIOR artistid
+                    ) me
+                ) me 
+                WHERE rownum__index BETWEEN 1 AND 2
+            ) me
+        )',
+        [ [ name => 'root' ] ],
+      );
+
+      is( $rs->count, 2, 'Connect By; LIMIT count ok' );
     }
 
+    # combine a connect_by with group_by and having
+    {
+      my $rs = $schema->resultset('Artist')->search({}, {
+        select => ['count(rank)'],
+        start_with => { name => 'root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
+        group_by => ['rank'],
+        having => { 'count(rank)' => { '<', 2 } },
+      });
+
+      is_same_sql_bind (
+        $rs->as_query,
+        '(
+            SELECT count(rank)
+            FROM artist me
+            START WITH name = ?
+            CONNECT BY parentid = PRIOR artistid
+            GROUP BY rank HAVING count(rank) < ?
+        )',
+        [ [ name => 'root' ], [ 'count(rank)' => 2 ] ],
+      );
+
+      is_deeply (
+        [ $rs->get_column ('count(rank)')->all ],
+        [1, 1],
+        'Group By a Connect By query - correct values'
+      );
+    }
+
+
     # select the whole cycle tree without nocylce
     {
       my $rs = $schema->resultset('Artist')->search({}, {
@@ -642,10 +687,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
     # select the whole cycle tree with nocylce
     {
       my $rs = $schema->resultset('Artist')->search({}, {
-        nocycle    => 1,
         start_with => { name => 'cycle-root' },
         '+select'  => [ \ 'CONNECT_BY_ISCYCLE' ],
-        connect_by => { parentid => { -prior => \ 'artistid' } },
+        connect_by_nocycle => { parentid => { -prior => \ 'artistid' } },
       });
 
       is_same_sql_bind (