X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle.t;h=b353e37cb3b0b9f77b20a9c1b6fc26f98fda0ae9;hb=ab4f4e4cdbe2eee3b6230d03f152942e96fb24f9;hp=f1c1ca0b8fcee70ac96c995c2db5997c954be1f5;hpb=2ba03b1627e18f422a84948277b72fd1c80da3a0;p=dbsrgits%2FDBIx-Class.git diff --git a/t/73oracle.t b/t/73oracle.t index f1c1ca0..b353e37 100644 --- a/t/73oracle.t +++ b/t/73oracle.t @@ -19,6 +19,10 @@ size => 100, is_nullable => 1, }, + 'autoinc_col' => { + data_type => 'integer', + is_auto_increment => 1, + }, ); __PACKAGE__->set_primary_key('artistid'); @@ -35,7 +39,10 @@ use lib qw(t/lib); use DBICTest; use DBIC::SqlMakerTest; -my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; +my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; + +# optional: +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\''. @@ -47,85 +54,7 @@ my $schema = DBICTest::Schema->connect($dsn, $user, $pass); my $dbh = $schema->storage->dbh; -eval { - $dbh->do("DROP SEQUENCE artist_seq"); - $dbh->do("DROP SEQUENCE cd_seq"); - $dbh->do("DROP SEQUENCE track_seq"); - $dbh->do("DROP SEQUENCE pkid1_seq"); - $dbh->do("DROP SEQUENCE pkid2_seq"); - $dbh->do("DROP SEQUENCE nonpkid_seq"); - $dbh->do("DROP TABLE artist"); - $dbh->do("DROP TABLE sequence_test"); - $dbh->do("DROP TABLE track"); - $dbh->do("DROP TABLE cd"); -}; -$dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0"); - -$dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), rank NUMBER(38), charfield VARCHAR2(10))"); -$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); - -$dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))"); -$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))"); - -$dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))"); -$dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))"); - -$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)"); -$dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))"); - -$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; -}); -$dbh->do(qq{ - CREATE OR REPLACE TRIGGER cd_insert_trg - BEFORE INSERT ON cd - FOR EACH ROW - BEGIN - IF :new.cdid IS NULL THEN - SELECT cd_seq.nextval - INTO :new.cdid - FROM DUAL; - END IF; - END; -}); -$dbh->do(qq{ - CREATE OR REPLACE TRIGGER cd_insert_trg - BEFORE INSERT ON cd - FOR EACH ROW - BEGIN - IF :new.cdid IS NULL THEN - SELECT cd_seq.nextval - INTO :new.cdid - FROM DUAL; - END IF; - END; -}); -$dbh->do(qq{ - CREATE OR REPLACE TRIGGER track_insert_trg - BEFORE INSERT ON track - FOR EACH ROW - BEGIN - IF :new.trackid IS NULL THEN - SELECT track_seq.nextval - INTO :new.trackid - FROM DUAL; - END IF; - END; -}); +do_creates($dbh); { # Swiped from t/bindtype_columns.t to avoid creating my own Resultset. @@ -150,42 +79,74 @@ $schema->class('Artist')->load_components('PK::Auto'); $schema->class('CD')->load_components('PK::Auto::Oracle'); $schema->class('Track')->load_components('PK::Auto::Oracle'); -# test primary key handling + +# test primary key handling with multiple triggers my $new = $schema->resultset('Artist')->create({ name => 'foo' }); is($new->artistid, 1, "Oracle Auto-PK worked"); -my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' }); -is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name"); +like ($new->result_source->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Correct PK sequence selected'); # test again with fully-qualified table name -$new = $schema->resultset('ArtistFQN')->create( { name => 'bar' } ); +my $artistfqn_rs = $schema->resultset('ArtistFQN'); +my $artist_rsrc = $artistfqn_rs->result_source; + +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" ); +delete $artist_rsrc->column_info('artistid')->{sequence}; + +$new = $artistfqn_rs->create( { name => 'bar', autoinc_col => 1000 } ); +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"); + +like ($artist_rsrc->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Still correct PK sequence'); + +# test LIMIT support +for (1..6) { + $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); +} +my $it = $schema->resultset('Artist')->search( { name => { -like => 'Artist %' }}, + { rows => 3, + offset => 4, + order_by => 'artistid' } +); +is( $it->count, 2, "LIMIT count past end of RS ok" ); +is( $it->next->name, "Artist 5", "iterator->next ok" ); +is( $it->next->name, "Artist 6", "iterator->next ok" ); +is( $it->next, undef, "next past end of resultset ok" ); + +my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' }); +is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name"); + # test rel names over the 30 char limit -my $query = $schema->resultset('Artist')->search({ - artistid => 1 -}, { - prefetch => 'cds_very_very_very_long_relationship_name' -}); - -lives_and { - is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1 -} 'query with rel name over 30 chars survived and worked'; - -# rel name over 30 char limit with user condition -# This requires walking the SQLA data structure. { - local $TODO = 'user condition on rel longer than 30 chars'; - - $query = $schema->resultset('Artist')->search({ - 'cds_very_very_very_long_relationship_name.title' => 'EP C' + my $query = $schema->resultset('Artist')->search({ + artistid => 1 }, { prefetch => 'cds_very_very_very_long_relationship_name' }); lives_and { is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1 - } 'query with rel name over 30 chars and user condition survived and worked'; + } 'query with rel name over 30 chars survived and worked'; + + # rel name over 30 char limit with user condition + # This requires walking the SQLA data structure. + { + local $TODO = 'user condition on rel longer than 30 chars'; + + $query = $schema->resultset('Artist')->search({ + 'cds_very_very_very_long_relationship_name.title' => 'EP C' + }, { + prefetch => 'cds_very_very_very_long_relationship_name' + }); + + lives_and { + is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1 + } 'query with rel name over 30 chars and user condition survived and worked'; + } } # test join with row count ambiguity @@ -230,27 +191,24 @@ $tcount = $schema->resultset('Track')->search( ); is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok'); -# test LIMIT support -for (1..6) { - $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); -} -my $it = $schema->resultset('Artist')->search( {}, - { rows => 3, - offset => 3, - order_by => 'artistid' } -); -is( $it->count, 3, "LIMIT count ok" ); -is( $it->next->name, "Artist 2", "iterator->next ok" ); -$it->next; -$it->next; -is( $it->next, undef, "next past end of resultset ok" ); - { my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 }); my @results = $rs->all; 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 { @@ -301,6 +259,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++; @@ -311,6 +273,8 @@ SKIP: { ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" ); } } + + $schema->storage->debug ($orig_debug); } @@ -343,13 +307,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 +332,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { children => [ { name => 'greatgrandchild', + rank => 3, } ], } @@ -372,10 +340,27 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { }, { name => 'child2', + rank => 3, }, ], }); + $schema->resultset('Artist')->create( + { + name => 'cycle-root', + children => [ + { + name => 'cycle-child1', + children => [ { name => 'cycle-grandchild' } ], + }, + { name => 'cycle-child2' }, + ], + } + ); + + $schema->resultset('Artist')->find({ name => 'cycle-root' }) + ->update({ parentid => \'artistid' }); + # select the whole tree { my $rs = $schema->resultset('Artist')->search({}, { @@ -389,7 +374,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid FROM artist me START WITH name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid )', [ [ name => 'root'] ], ); @@ -406,7 +391,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { SELECT COUNT( * ) FROM artist me START WITH name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid )', [ [ name => 'root'] ], ); @@ -428,7 +413,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid FROM artist me START WITH name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid ORDER SIBLINGS BY name DESC )', [ [ name => 'root'] ], @@ -444,8 +429,8 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { # get the root node { my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { - start_with => { name => 'greatgrandchild' }, - connect_by => { artistid => { -prior => \ 'parentid' } }, + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, }); is_same_sql_bind ( @@ -455,9 +440,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { FROM artist me WHERE ( parentid IS NULL ) START WITH name = ? - CONNECT BY artistid = PRIOR( parentid ) + CONNECT BY parentid = PRIOR artistid )', - [ [ name => 'greatgrandchild'] ], + [ [ name => 'root'] ], ); is_deeply( @@ -486,7 +471,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { LEFT JOIN cd cds ON cds.artist = me.artistid WHERE ( cds.title LIKE ? ) START WITH me.name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid )', [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], ); @@ -506,7 +491,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { LEFT JOIN cd cds ON cds.artist = me.artistid WHERE ( cds.title LIKE ? ) START WITH me.name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid )', [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], ); @@ -517,9 +502,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { # combine a connect by with order_by { my $rs = $schema->resultset('Artist')->search({}, { - start_with => { name => 'greatgrandchild' }, - connect_by => { artistid => { -prior => \ 'parentid' } }, - order_by => { -asc => 'name' }, + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + order_by => { -asc => [ 'LEVEL', 'name' ] }, }); is_same_sql_bind ( @@ -528,15 +513,15 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid FROM artist me START WITH name = ? - CONNECT BY artistid = PRIOR( parentid ) - ORDER BY name ASC + CONNECT BY parentid = PRIOR artistid + ORDER BY LEVEL ASC, name ASC )', - [ [ name => 'greatgrandchild' ] ], + [ [ name => 'root' ] ], ); is_deeply ( [ $rs->get_column ('name')->all ], - [ qw/child1 grandchild greatgrandchild root/ ], + [ qw/root child1 child2 grandchild greatgrandchild/ ], 'Connect By with a order_by - result name ok' ); } @@ -545,92 +530,136 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { # limit a connect by { my $rs = $schema->resultset('Artist')->search({}, { - start_with => { name => 'greatgrandchild' }, - connect_by => { artistid => { -prior => \ 'parentid' } }, - order_by => { -desc => 'name' }, + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + order_by => { -asc => 'name' }, rows => 2, }); is_same_sql_bind ( $rs->as_query, - '( - SELECT * FROM ( - SELECT A.*, ROWNUM r FROM ( - SELECT - me.artistid AS col1, - me.name AS col2, - me.rank AS col3, - me.charfield AS col4, - me.parentid AS col5 - FROM artist me - START WITH name = ? - CONNECT BY artistid = PRIOR( parentid ) - ORDER BY name DESC - ) A - WHERE ROWNUM < 3 - ) B - WHERE r >= 1 + '( + SELECT artistid, name, rank, charfield, parentid 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 + WHERE ROWNUM <= 2 )', - [ [ name => 'greatgrandchild' ] ], + [ [ name => 'root' ] ], ); is_deeply ( [ $rs->get_column ('name')->all ], - [qw/root greatgrandchild/], + [qw/child1 child2/], 'LIMIT a Connect By query - correct names' ); # TODO: # prints "START WITH name = ? - # CONNECT BY artistid = PRIOR( parentid )" + # CONNECT BY artistid = PRIOR parentid " # 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 + me.artistid + FROM artist me + START WITH name = ? + CONNECT BY parentid = PRIOR artistid + ) me + WHERE ROWNUM <= 2 + ) me + )', + [ [ name => 'root' ] ], + ); + + is( $rs->count, 2, 'Connect By; LIMIT count ok' ); } - # select the whole tree with nocylce + # combine a connect_by with group_by and having { my $rs = $schema->resultset('Artist')->search({}, { - nocycle => 1, + 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 me.artistid, me.name, me.rank, me.charfield, me.parentid + 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({}, { + start_with => { name => 'cycle-root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + }); + eval { $rs->get_column ('name')->all }; + if ( $@ =~ /ORA-01436/ ){ # ORA-01436: CONNECT BY loop in user data + pass "connect by initify loop detection without nocycle"; + }else{ + fail "connect by initify loop detection without nocycle, not detected by oracle"; + } + } + + # select the whole cycle tree with nocylce + { + my $rs = $schema->resultset('Artist')->search({}, { + start_with => { name => 'cycle-root' }, + '+select' => [ \ 'CONNECT_BY_ISCYCLE' ], + connect_by_nocycle => { parentid => { -prior => \ 'artistid' } }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE FROM artist me START WITH name = ? - CONNECT BY NOCYCLE parentid = PRIOR( artistid ) + CONNECT BY NOCYCLE parentid = PRIOR artistid )', - [ [ name => 'root'] ], + [ [ name => 'cycle-root'] ], ); is_deeply ( [ $rs->get_column ('name')->all ], - [ qw/root child1 grandchild greatgrandchild child2/ ], - 'got artist tree with nocycle', + [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ], + 'got artist tree with nocycle (name)', + ); + is_deeply ( + [ $rs->get_column ('CONNECT_BY_ISCYCLE')->all ], + [ qw/1 0 0 0/ ], + 'got artist tree with nocycle (CONNECT_BY_ISCYCLE)', ); @@ -640,31 +669,186 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { SELECT COUNT( * ) FROM artist me START WITH name = ? - CONNECT BY NOCYCLE parentid = PRIOR( artistid ) + CONNECT BY NOCYCLE parentid = PRIOR artistid )', - [ [ name => 'root'] ], + [ [ name => 'cycle-root'] ], ); - is( $rs->count, 5, 'Connect By Nocycle count ok' ); + is( $rs->count, 4, 'Connect By Nocycle count ok' ); } } +my $schema2; + +# test sequence detection from a different schema +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 $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'; + + # 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; +sub do_creates { + my $dbh = shift; + + eval { + $dbh->do("DROP SEQUENCE artist_autoinc_seq"); + $dbh->do("DROP SEQUENCE artist_pk_seq"); + $dbh->do("DROP SEQUENCE cd_seq"); + $dbh->do("DROP SEQUENCE track_seq"); + $dbh->do("DROP SEQUENCE pkid1_seq"); + $dbh->do("DROP SEQUENCE pkid2_seq"); + $dbh->do("DROP SEQUENCE nonpkid_seq"); + $dbh->do("DROP TABLE artist"); + $dbh->do("DROP TABLE sequence_test"); + $dbh->do("DROP TABLE track"); + $dbh->do("DROP TABLE cd"); + }; + $dbh->do("CREATE SEQUENCE artist_autoinc_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0"); + $dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0"); + + $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))"); + $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); + + $dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))"); + $dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))"); + + $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))"); + $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))"); + + $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)"); + $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))"); + + $dbh->do(qq{ + CREATE OR REPLACE TRIGGER artist_insert_trg_auto + BEFORE INSERT ON artist + FOR EACH ROW + BEGIN + IF :new.autoinc_col IS NULL THEN + SELECT artist_autoinc_seq.nextval + INTO :new.autoinc_col + FROM DUAL; + END IF; + END; + }); + $dbh->do(qq{ + CREATE OR REPLACE TRIGGER artist_insert_trg_pk + BEFORE INSERT ON artist + FOR EACH ROW + BEGIN + IF :new.artistid IS NULL THEN + SELECT artist_pk_seq.nextval + INTO :new.artistid + FROM DUAL; + END IF; + END; + }); + $dbh->do(qq{ + CREATE OR REPLACE TRIGGER cd_insert_trg + BEFORE INSERT OR UPDATE ON cd + FOR EACH ROW + BEGIN + IF :new.cdid IS NULL THEN + SELECT cd_seq.nextval + INTO :new.cdid + FROM DUAL; + END IF; + END; + }); + $dbh->do(qq{ + CREATE OR REPLACE TRIGGER cd_insert_trg + BEFORE INSERT ON cd + FOR EACH ROW + BEGIN + IF :new.cdid IS NULL THEN + SELECT cd_seq.nextval + INTO :new.cdid + FROM DUAL; + END IF; + END; + }); + $dbh->do(qq{ + CREATE OR REPLACE TRIGGER track_insert_trg + BEFORE INSERT ON track + FOR EACH ROW + BEGIN + IF :new.trackid IS NULL THEN + SELECT track_seq.nextval + INTO :new.trackid + FROM DUAL; + END IF; + END; + }); +} + # clean up our mess END { - if($schema && ($dbh = $schema->storage->dbh)) { - $dbh->do("DROP SEQUENCE artist_seq"); - $dbh->do("DROP SEQUENCE cd_seq"); - $dbh->do("DROP SEQUENCE track_seq"); - $dbh->do("DROP SEQUENCE pkid1_seq"); - $dbh->do("DROP SEQUENCE pkid2_seq"); - $dbh->do("DROP SEQUENCE nonpkid_seq"); - $dbh->do("DROP TABLE artist"); - $dbh->do("DROP TABLE sequence_test"); - $dbh->do("DROP TABLE track"); - $dbh->do("DROP TABLE cd"); - $dbh->do("DROP TABLE bindtype_test"); - } + for my $dbh (map $_->storage->dbh, grep $_, ($schema, $schema2)) { + eval { + $dbh->do("DROP SEQUENCE artist_autoinc_seq"); + $dbh->do("DROP SEQUENCE artist_pk_seq"); + $dbh->do("DROP SEQUENCE cd_seq"); + $dbh->do("DROP SEQUENCE track_seq"); + $dbh->do("DROP SEQUENCE pkid1_seq"); + $dbh->do("DROP SEQUENCE pkid2_seq"); + $dbh->do("DROP SEQUENCE nonpkid_seq"); + $dbh->do("DROP TABLE artist"); + $dbh->do("DROP TABLE sequence_test"); + $dbh->do("DROP TABLE track"); + $dbh->do("DROP TABLE cd"); + $dbh->do("DROP TABLE bindtype_test"); + }; + } } -