Changes/author for a1e1a51

[dbsrgits/DBIx-Class.git] / t / 73oracle.t

diff --git a/t/73oracle.t b/t/73oracle.t
index 3192acb..2203e26 100644 (file)
--- a/t/73oracle.t
+++ b/t/73oracle.t
@@ -38,7 +38,7 @@ use DBIC::SqlMakerTest;
 my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};
 
 # optional:
-my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_ORA2_${_}" } qw/DSN USER PASS/};
+my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_ORA_EXTRAUSER_${_}" } qw/DSN USER PASS/};
 
 plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
   'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
@@ -238,6 +238,10 @@ SKIP: {
     skip 'buggy BLOB support in DBD::Oracle 1.23', 7;
   }
 
+  # disable BLOB mega-output
+  my $orig_debug = $schema->storage->debug;
+  $schema->storage->debug (0);
+
   foreach my $type (qw( blob clob )) {
     foreach my $size (qw( small large )) {
       $id++;
@@ -248,6 +252,8 @@ SKIP: {
       ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
     }
   }
+
+  $schema->storage->debug ($orig_debug);
 }
 
 
@@ -511,22 +517,20 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
 
       is_same_sql_bind (
         $rs->as_query,
-        '( 
+        '(
             SELECT artistid, name, rank, charfield, parentid FROM (
-                  SELECT artistid, name, rank, charfield, parentid, ROWNUM rownum__index 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 
+              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__index BETWEEN 1 AND 2
+            WHERE ROWNUM <= 2
         )',
         [ [ name => 'root' ] ],
       );
@@ -545,19 +549,18 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       # 
       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
+        '(
+          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' ] ],
       );
@@ -657,24 +660,56 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
 my $schema2;
 
 # test sequence detection from a different schema
-if ($dsn2 && $user2) {
+SKIP: {
+  skip ((join '',
+'Set DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS to a *DIFFERENT* Oracle user',
+' to run the cross-schema autoincrement test.'),
+    1) unless $dsn2 && $user2 && $user2 ne $user;
+
   $schema2 = DBICTest::Schema->connect($dsn2, $user2, $pass2);
 
-  my $dbh  = $schema->storage->dbh;
-  my $dbh2 = $schema2->storage->dbh;
+  my $schema1_dbh  = $schema->storage->dbh;
+
+  $schema1_dbh->do("GRANT INSERT ON artist TO $user2");
+  $schema1_dbh->do("GRANT SELECT ON artist_seq TO $user2");
+
+  my $rs = $schema2->resultset('ArtistFQN');
+
+  # first test with unquoted (default) sequence name in trigger body
+
+  lives_and {
+    my $row = $rs->create({ name => 'From Different Schema' });
+    ok $row->artistid;
+  } 'used autoinc sequence across schemas';
 
-  $dbh->do("GRANT INSERT ON artist TO $user2");
-  $dbh->do("GRANT SELECT ON artist_seq TO $user2");
+  # now quote the sequence name
 
-  my $rs = $schema2->resultset('Artist');
+  $schema1_dbh->do(qq{
+    CREATE OR REPLACE TRIGGER artist_insert_trg
+    BEFORE INSERT ON artist
+    FOR EACH ROW
+    BEGIN
+      IF :new.artistid IS NULL THEN
+        SELECT "ARTIST_SEQ".nextval
+        INTO :new.artistid
+        FROM DUAL;
+      END IF;
+    END;
+  });
 
-  # qualify table with schema
-  local $rs->result_source->{name} = "${user}.artist";
+  # sequence is cached in the rsrc
+  delete $rs->result_source->column_info('artistid')->{sequence};
 
   lives_and {
-    my $row = $rs->create({ name => 'Different Schema' });
+    my $row = $rs->create({ name => 'From Different Schema With Quoted Sequence' });
     ok $row->artistid;
-  } 'detected autoinc sequence across schemas';
+  } 'used quoted autoinc sequence across schemas';
+
+  my $schema_name = uc $user;
+
+  is $rs->result_source->column_info('artistid')->{sequence},
+    qq[${schema_name}."ARTIST_SEQ"],
+    'quoted sequence name correctly extracted';
 }
 
 done_testing;
@@ -779,7 +814,5 @@ END {
       $dbh->do("DROP TABLE cd");
       $dbh->do("DROP TABLE bindtype_test");
     };
-
-    eval { $dbh->do('DROP SYNONYM artist') };
   }
 }