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' => {
is_auto_increment => 1,
},
);
- __PACKAGE__->set_primary_key('artistid');
+ __PACKAGE__->set_primary_key(qw/ artistid autoinc_col /);
1;
}
use Test::Exception;
use Test::More;
+use Sub::Name;
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('test_rdbms_oracle')
+ unless DBIx::Class::Optional::Dependencies->req_ok_for ('test_rdbms_oracle');
+
+$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:
);
# 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');
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};
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
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)
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';
? '"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
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);
$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)");
}
END {
- if ($dbh) {
+ for ($dbh, $dbh2) {
+ next unless $_;
local $SIG{__WARN__} = sub {};
- do_clean($dbh);
- $dbh->disconnect;
+ do_clean($_);
+ $_->disconnect;
}
}
projects / dbsrgits/DBIx-Class.git / blobdiff