X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle.t;h=358ac5fed6d110d09f0525619065ef21507fa8d2;hb=a6646e1b0a25acfd21cc3e32b8c479dd0f3526ef;hp=4231127ac90a4b1a9b8cdf426286b0be57e6668e;hpb=07cda1c5a7df6656772dfd65c488c19c15126168;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/73oracle.t b/t/73oracle.t
index 4231127..358ac5f 100644
--- a/t/73oracle.t
+++ b/t/73oracle.t
@@ -5,9 +5,9 @@
   use base 'DBIx::Class::Core';
 
   __PACKAGE__->table(
-    defined $ENV{DBICTEST_ORA_USER}
+    $ENV{DBICTEST_ORA_USER}
       ? (uc $ENV{DBICTEST_ORA_USER}) . '.artist'
-      : 'artist'
+      : '??_no_user_??'
   );
   __PACKAGE__->add_columns(
     'artistid' => {
@@ -24,7 +24,7 @@
       is_auto_increment => 1,
     },
   );
-  __PACKAGE__->set_primary_key('artistid');
+  __PACKAGE__->set_primary_key(qw/ artistid autoinc_col /);
 
   1;
 }
@@ -34,11 +34,16 @@ use warnings;
 
 use Test::Exception;
 use Test::More;
+use Sub::Name;
 
 use lib qw(t/lib);
 use DBICTest;
 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/};
 
 # optional:
@@ -67,36 +72,84 @@ my @tryopt = (
 );
 
 # keep a database handle open for cleanup
-my $dbh;
+my ($dbh, $dbh2);
+
+# test insert returning
+
+# check if we indeed do support stuff
+my $test_server_supports_insert_returning = do {
+  my $v = DBICTest::Schema->connect($dsn, $user, $pass)
+                   ->storage
+                    ->_get_dbh
+                     ->get_info(18);
+  $v =~ /^(\d+)\.(\d+)/
+    or die "Unparseable Oracle server version: $v\n";
+
+# TODO find out which version supports the RETURNING syntax
+# 8i has it and earlier docs are a 404 on oracle.com
+  ( $1 > 8 || ($1 == 8 && $2 >= 1) ) ? 1 : 0;
+};
+is (
+  DBICTest::Schema->connect($dsn, $user, $pass)->storage->_use_insert_returning,
+  $test_server_supports_insert_returning,
+  'insert returning capability guessed correctly'
+);
 
-for my $opt (@tryopt) {
-  # clean all cached sequences from previous run
-  for (map { values %{DBICTest::Schema->source($_)->columns_info} } (qw/Artist CD Track/) ) {
-    delete $_->{sequence};
-  }
+my $schema;
+for my $use_insert_returning ($test_server_supports_insert_returning
+  ? (1,0)
+  : (0)
+) {
+
+  no warnings qw/once/;
+  local *DBICTest::Schema::connection = subname 'DBICTest::Schema::connection' => sub {
+    my $s = shift->next::method (@_);
+    $s->storage->_use_insert_returning ($use_insert_returning);
+    $s;
+  };
+
+  for my $opt (@tryopt) {
+    # clean all cached sequences from previous run
+    for (map { values %{DBICTest::Schema->source($_)->columns_info} } (qw/Artist CD Track/) ) {
+      delete $_->{sequence};
+    }
 
-  my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opt);
-  my $q = $schema -> storage -> sql_maker -> quote_char || '';
+    my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opt);
 
-  $dbh = $schema->storage->dbh;
+    $dbh = $schema->storage->dbh;
+    my $q = $schema->storage->sql_maker->quote_char || '';
 
-  do_creates($dbh, $q);
+    do_creates($dbh, $q);
+
+    _run_tests($schema, $opt);
+  }
+}
+
+sub _run_tests {
+  my ($schema, $opt) = @_;
+
+  my $q = $schema->storage->sql_maker->quote_char || '';
 
 # test primary key handling with multiple triggers
   my ($new, $seq);
 
-  $new = $schema->resultset('Artist')->create({ name => 'foo' });
-  is($new->artistid, 1, "Oracle Auto-PK worked for standard sqlt-like trigger");
-  $seq = $new->result_source->column_info('artistid')->{sequence};
-  $seq = $$seq if ref $seq;
-  like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
-
-  $new = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
-  is($new->cdid, 1, 'Oracle Auto-PK worked - using scalar ref as table name/custom weird trigger');
-  $seq = $new->result_source->column_info('cdid')->{sequence};
-  $seq = $$seq if ref $seq;
-  like ($seq, qr/\.${q}cd_seq${q}$/, 'Correct PK sequence selected for custom trigger');
+  my $new_artist = $schema->resultset('Artist')->create({ name => 'foo' });
+  my $new_cd     = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
 
+  SKIP: {
+    skip 'not detecting sequences when using INSERT ... RETURNING', 4
+      if $schema->storage->_use_insert_returning;
+
+    is($new_artist->artistid, 1, "Oracle Auto-PK worked for standard sqlt-like trigger");
+    $seq = $new_artist->result_source->column_info('artistid')->{sequence};
+    $seq = $$seq if ref $seq;
+    like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
+
+    is($new_cd->cdid, 1, 'Oracle Auto-PK worked - using scalar ref as table name/custom weird trigger');
+    $seq = $new_cd->result_source->column_info('cdid')->{sequence};
+    $seq = $$seq if ref $seq;
+    like ($seq, qr/\.${q}cd_seq${q}$/, 'Correct PK sequence selected for custom trigger');
+  }
 
 # test PKs again with fully-qualified table name
   my $artistfqn_rs = $schema->resultset('ArtistFQN');
@@ -105,7 +158,9 @@ for my $opt (@tryopt) {
   delete $artist_rsrc->column_info('artistid')->{sequence};
   $new = $artistfqn_rs->create( { name => 'bar' } );
 
-  is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" );
+  is_deeply( {map { $_ => $new->$_ } $artist_rsrc->primary_columns},
+    { artistid => 2, autoinc_col => 2},
+    "Oracle Multi-Auto-PK worked with fully-qualified tablename" );
 
 
   delete $artist_rsrc->column_info('artistid')->{sequence};
@@ -113,9 +168,15 @@ for my $opt (@tryopt) {
 
   is( $new->artistid, 3, "Oracle Auto-PK worked with fully-qualified tablename" );
   is( $new->autoinc_col, 1000, "Oracle Auto-Inc overruled with fully-qualified tablename");
-  $seq = $new->result_source->column_info('artistid')->{sequence};
-  $seq = $$seq if ref $seq;
-  like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
+
+  SKIP: {
+    skip 'not detecting sequences when using INSERT ... RETURNING', 1
+      if $schema->storage->_use_insert_returning;
+
+    $seq = $new->result_source->column_info('artistid')->{sequence};
+    $seq = $$seq if ref $seq;
+    like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
+  }
 
 
 # test LIMIT support
@@ -301,9 +362,12 @@ for my $opt (@tryopt) {
   TODO: {
     skip ((join '',
       'Set DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS to a *DIFFERENT* Oracle user',
-      ' to run the cross-schema autoincrement test.'),
+      ' to run the cross-schema sequence detection test.'),
     1) unless $dsn2 && $user2 && $user2 ne $user;
 
+    skip 'not detecting cross-schema sequence name when using INSERT ... RETURNING', 1
+      if $schema->storage->_use_insert_returning;
+
     # Oracle8i Reference Release 2 (8.1.6) 
     #   http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch294.htm#993
     # Oracle Database Reference 10g Release 2 (10.2)
@@ -311,20 +375,23 @@ for my $opt (@tryopt) {
     local $TODO = "On Oracle8i all_triggers view is empty, i don't yet know why..."
       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 
-    my $schema2 = DBICTest::Schema->connect($dsn2, $user2, $pass2, $opt);
-
+    my $schema2 = $schema->connect($dsn2, $user2, $pass2, $opt);
+    my $dbh2 = $schema2->storage->dbh;
 
-    my $schema1_dbh  = $schema->storage->dbh;
-    $schema1_dbh->do("GRANT INSERT ON ${q}artist${q} TO " . uc $user2);
-    $schema1_dbh->do("GRANT SELECT ON ${q}artist_pk_seq${q} TO " . uc $user2);
+    # create identically named tables/sequences in the other schema
+    do_creates($dbh2, $q);
 
+    # grand select privileges to the 2nd user
+    $dbh->do("GRANT INSERT ON ${q}artist${q} TO " . uc $user2);
+    $dbh->do("GRANT SELECT ON ${q}artist_pk_seq${q} TO " . uc $user2);
+    $dbh->do("GRANT SELECT ON ${q}artist_autoinc_seq${q} TO " . uc $user2);
 
-    my $rs = $schema2->resultset('ArtistFQN');
-    delete $rs->result_source->column_info('artistid')->{sequence};
+    # test with a fully qualified table (user1/schema prepended)
+    my $rs2 = $schema2->resultset('ArtistFQN');
+    delete $rs2->result_source->column_info('artistid')->{sequence};
 
-    # first test with unquoted (default) sequence name in trigger body
     lives_and {
-      my $row = $rs->create({ name => 'From Different Schema' });
+      my $row = $rs2->create({ name => 'From Different Schema' });
       ok $row->artistid;
     } 'used autoinc sequence across schemas';
 
@@ -333,8 +400,8 @@ for my $opt (@tryopt) {
       ? '"artist_pk_seq"'
       : '"ARTIST_PK_SEQ"'
     ;
-    delete $rs->result_source->column_info('artistid')->{sequence};
-    $schema1_dbh->do(qq{
+    delete $rs2->result_source->column_info('artistid')->{sequence};
+    $dbh->do(qq{
       CREATE OR REPLACE TRIGGER ${q}artist_insert_trg_pk${q}
       BEFORE INSERT ON ${q}artist${q}
       FOR EACH ROW
@@ -349,15 +416,31 @@ for my $opt (@tryopt) {
 
 
     lives_and {
-      my $row = $rs->create({ name => 'From Different Schema With Quoted Sequence' });
+      my $row = $rs2->create({ name => 'From Different Schema With Quoted Sequence' });
       ok $row->artistid;
     } 'used quoted autoinc sequence across schemas';
 
-    my $schema_name = uc $user;
-
-    is_deeply $rs->result_source->column_info('artistid')->{sequence},
-      \qq|${schema_name}.$q_seq|,
+    is_deeply $rs2->result_source->column_info('artistid')->{sequence},
+      \( (uc $user) . ".$q_seq"),
       'quoted sequence name correctly extracted';
+
+    # try an insert operation on the default user2 artist
+    my $art1 = $schema->resultset('Artist');
+    my $art2 = $schema2->resultset('Artist');
+    my $art1_count = $art1->count || 0;
+    my $art2_count = $art2->count;
+
+    is( $art2_count, 0, 'No artists created yet in second schema' );
+
+    delete $art2->result_source->column_info('artistid')->{sequence};
+    my $new_art = $art2->create({ name => '2nd best' });
+
+    is ($art1->count, $art1_count, 'No new rows in main schema');
+    is ($art2->count, 1, 'One artist create in 2nd schema');
+
+    is( $new_art->artistid, 1, 'Expected first PK' );
+
+    do_clean ($dbh2);
   }}
 
   do_clean ($dbh);
@@ -391,7 +474,7 @@ sub do_creates {
   $dbh->do("CREATE TABLE cd (${q}cdid${q} NUMBER(12), ${q}artist${q} NUMBER(12), ${q}title${q} VARCHAR(255), ${q}year${q} VARCHAR(4), ${q}genreid${q} NUMBER(12), ${q}single_track${q} NUMBER(12))");
   $dbh->do("ALTER TABLE cd ADD (CONSTRAINT ${q}cd_pk${q} PRIMARY KEY (${q}cdid${q}))");
 
-  $dbh->do("CREATE TABLE ${q}track${q} (${q}trackid${q} NUMBER(12), ${q}cd${q} NUMBER(12) REFERENCES CD(${q}cdid${q}) DEFERRABLE, ${q}position${q} NUMBER(12), ${q}title${q} VARCHAR(255), ${q}last_updated_on${q} DATE, ${q}last_updated_at${q} DATE, ${q}small_dt${q} DATE)");
+  $dbh->do("CREATE TABLE ${q}track${q} (${q}trackid${q} NUMBER(12), ${q}cd${q} NUMBER(12) REFERENCES CD(${q}cdid${q}) DEFERRABLE, ${q}position${q} NUMBER(12), ${q}title${q} VARCHAR(255), ${q}last_updated_on${q} DATE, ${q}last_updated_at${q} DATE)");
   $dbh->do("ALTER TABLE ${q}track${q} ADD (CONSTRAINT ${q}track_pk${q} PRIMARY KEY (${q}trackid${q}))");
 
   $dbh->do("CREATE TABLE ${q}bindtype_test${q} (${q}id${q} integer NOT NULL PRIMARY KEY, ${q}bytea${q} integer NULL, ${q}blob${q} blob NULL, ${q}clob${q} clob NULL)");
@@ -486,9 +569,10 @@ sub do_clean {
 }
 
 END {
-  if ($dbh) {
+  for ($dbh, $dbh2) {
+    next unless $_;
     local $SIG{__WARN__} = sub {};
-    do_clean($dbh);
-    $dbh->disconnect;
+    do_clean($_);
+    $_->disconnect;
   }
 }