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\''.
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;
- $dbh->do("GRANT INSERT ON artist TO $user2");
- $dbh->do("GRANT SELECT ON artist_seq TO $user2");
+ $schema1_dbh->do("GRANT INSERT ON artist TO $user2");
+ $schema1_dbh->do("GRANT SELECT ON artist_seq TO $user2");
- my $rs = $schema2->resultset('Artist');
+ my $rs = $schema2->resultset('ArtistFQN');
- # qualify table with schema
- local $rs->result_source->{name} = "${user}.artist";
+ # first test with unquoted (default) sequence name in trigger body
lives_and {
- my $row = $rs->create({ name => 'Different Schema' });
+ my $row = $rs->create({ name => 'From Different Schema' });
ok $row->artistid;
- } 'detected autoinc sequence across schemas';
+ } 'used autoinc sequence across schemas';
+
+ # now quote the sequence name
+
+ $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;
+ });
+
+ # sequence is cached in the rsrc
+ delete $rs->result_source->column_info('artistid')->{sequence};
+
+ lives_and {
+ my $row = $rs->create({ name => 'From Different Schema With Quoted Sequence' });
+ ok $row->artistid;
+ } '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;
$dbh->do("DROP TABLE cd");
$dbh->do("DROP TABLE bindtype_test");
};
-
- eval { $dbh->do('DROP SYNONYM artist') };
}
}