X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle.t;h=3192acbac419c02e52b8c9b2c6c937fb1fa93139;hb=df6e3f5c0c1a8250692b4f89e4c72c235d178bd2;hp=ca554873177f00dfd1b9fc4c137ee471e3fd27a6;hpb=2a7879e2eee29d75934667ab79f788741cd7bd79;p=dbsrgits%2FDBIx-Class.git diff --git a/t/73oracle.t b/t/73oracle.t index ca55487..3192acb 100644 --- a/t/73oracle.t +++ b/t/73oracle.t @@ -35,7 +35,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_ORA2_${_}" } 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 +50,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. @@ -251,6 +176,18 @@ is( $it->next, undef, "next past end of resultset ok" ); 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 { @@ -343,13 +280,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 +305,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { children => [ { name => 'greatgrandchild', + rank => 3, } ], } @@ -372,6 +313,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { }, { name => 'child2', + rank => 3, }, ], }); @@ -405,7 +347,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'] ], ); @@ -422,7 +364,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'] ], ); @@ -444,7 +386,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'] ], @@ -471,7 +413,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { FROM artist me WHERE ( parentid IS NULL ) START WITH name = ? - CONNECT BY parentid = PRIOR( artistid ) + CONNECT BY parentid = PRIOR artistid )', [ [ name => 'root'] ], ); @@ -502,7 +444,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' ] ], ); @@ -522,7 +464,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' ] ], ); @@ -544,7 +486,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 BY LEVEL ASC, name ASC )', [ [ name => 'root' ] ], @@ -570,22 +512,21 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { is_same_sql_bind ( $rs->as_query, '( - SELECT * FROM ( - SELECT A.*, ROWNUM r FROM ( + SELECT artistid, name, rank, charfield, parentid FROM ( + SELECT artistid, name, rank, charfield, parentid, ROWNUM rownum__index FROM ( SELECT - me.artistid AS col1, - me.name AS col2, - me.rank AS col3, - me.charfield AS col4, - me.parentid AS col5 + 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 - ) A - WHERE ROWNUM < 3 - ) B - WHERE r >= 1 + CONNECT BY parentid = PRIOR artistid + ORDER BY name ASC + ) me + ) me + WHERE rownum__index BETWEEN 1 AND 2 )', [ [ name => 'root' ] ], ); @@ -598,33 +539,62 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { # 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 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 + )', + [ [ name => 'root' ] ], + ); + + is( $rs->count, 2, 'Connect By; LIMIT count ok' ); } + # combine a connect_by with group_by and having + { + my $rs = $schema->resultset('Artist')->search({}, { + 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 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({}, { @@ -632,7 +602,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { connect_by => { parentid => { -prior => \ 'artistid' } }, }); eval { $rs->get_column ('name')->all }; - if ( $@ =~ /ORA-01436/ ){ # ORA-01436: CONNECT BY loop in user data + 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"; @@ -642,10 +612,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { # select the whole cycle tree with nocylce { my $rs = $schema->resultset('Artist')->search({}, { - nocycle => 1, start_with => { name => 'cycle-root' }, '+select' => [ \ 'CONNECT_BY_ISCYCLE' ], - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by_nocycle => { parentid => { -prior => \ 'artistid' } }, }); is_same_sql_bind ( @@ -654,7 +623,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { 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 => 'cycle-root'] ], ); @@ -676,7 +645,7 @@ 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 => 'cycle-root'] ], ); @@ -685,22 +654,132 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { } } +my $schema2; + +# test sequence detection from a different schema +if ($dsn2 && $user2) { + $schema2 = DBICTest::Schema->connect($dsn2, $user2, $pass2); + + my $dbh = $schema->storage->dbh; + my $dbh2 = $schema2->storage->dbh; + + $dbh->do("GRANT INSERT ON artist TO $user2"); + $dbh->do("GRANT SELECT ON artist_seq TO $user2"); + + my $rs = $schema2->resultset('Artist'); + + # qualify table with schema + local $rs->result_source->{name} = "${user}.artist"; + + lives_and { + my $row = $rs->create({ name => 'Different Schema' }); + ok $row->artistid; + } 'detected autoinc sequence across schemas'; +} + done_testing; +sub do_creates { + my $dbh = shift; + + 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 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_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"); + }; + + eval { $dbh->do('DROP SYNONYM artist') }; + } } -