X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F73oracle.t;h=7ab00a4e0593c3448cc91be1ccdbe845fb8c9fb6;hb=bc6ae32eb05f2db67d61f3e0acd98c0a867eea3b;hp=90698eaecbfccb13bc5036a030c515c76b480130;hpb=c00243555c65c5df61de5578f3a19afb939dd212;p=dbsrgits%2FDBIx-Class.git diff --git a/t/73oracle.t b/t/73oracle.t index 90698ea..7ab00a4 100644 --- a/t/73oracle.t +++ b/t/73oracle.t @@ -45,14 +45,6 @@ my $schema = DBICTest::Schema->connect($dsn, $user, $pass); my $dbh = $schema->storage->dbh; -if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { - plan tests => 46; -} -else { - plan tests => 36; -} - - eval { $dbh->do("DROP SEQUENCE artist_seq"); $dbh->do("DROP SEQUENCE cd_seq"); @@ -62,8 +54,8 @@ eval { $dbh->do("DROP SEQUENCE nonpkid_seq"); $dbh->do("DROP TABLE artist"); $dbh->do("DROP TABLE sequence_test"); - $dbh->do("DROP TABLE cd"); $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"); @@ -71,15 +63,19 @@ $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("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), single_track NUMBER(12), genreid NUMBER(12))"); -$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)"); +$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))"); -$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); +$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("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))"); + $dbh->do(qq{ CREATE OR REPLACE TRIGGER artist_insert_trg BEFORE INSERT ON artist @@ -157,12 +153,39 @@ 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($new->artistid, 1, "Oracle Auto-PK worked - using scalar ref as table name"); +is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name"); # test again with fully-qualified table name $new = $schema->resultset('ArtistFQN')->create( { name => 'bar' } ); is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" ); +# 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' + }, { + 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 my $track = $schema->resultset('Track')->create({ cd => $cd->cdid, @@ -226,6 +249,27 @@ is( $it->next, undef, "next past end of resultset ok" ); is( scalar @results, 1, "Group by with limit OK" ); } +# test with_deferred_fk_checks +lives_ok { + $schema->storage->with_deferred_fk_checks(sub { + $schema->resultset('Track')->create({ + trackid => 999, cd => 999, position => 1, title => 'deferred FK track' + }); + $schema->resultset('CD')->create({ + artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd' + }); + }); +} 'with_deferred_fk_checks code survived'; + +is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track', + 'code in with_deferred_fk_checks worked'; + +throws_ok { + $schema->resultset('Track')->create({ + trackid => 1, cd => 9999, position => 1, title => 'Track1' + }); +} qr/constraint/i, 'with_deferred_fk_checks is off'; + # test auto increment using sequences WITHOUT triggers for (1..5) { my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' }); @@ -237,31 +281,38 @@ my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually"); SKIP: { - skip 'buggy BLOB support in DBD::Oracle 1.23', 8 - if $DBD::Oracle::VERSION == 1.23; + my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) ); + $binstr{'large'} = $binstr{'small'} x 1024; + + my $maxloblen = length $binstr{'large'}; + note "Localizing LongReadLen to $maxloblen to avoid truncation of test data"; + local $dbh->{'LongReadLen'} = $maxloblen; - my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) ); - $binstr{'large'} = $binstr{'small'} x 1024; + my $rs = $schema->resultset('BindType'); + my $id = 0; - my $maxloblen = length $binstr{'large'}; - note "Localizing LongReadLen to $maxloblen to avoid truncation of test data"; - local $dbh->{'LongReadLen'} = $maxloblen; + if ($DBD::Oracle::VERSION eq '1.23') { + throws_ok { $rs->create({ id => 1, blob => $binstr{large} }) } + qr/broken/, + 'throws on blob insert with DBD::Oracle == 1.23'; - my $rs = $schema->resultset('BindType'); - my $id = 0; + skip 'buggy BLOB support in DBD::Oracle 1.23', 7; + } - foreach my $type (qw( blob clob )) { - foreach my $size (qw( small large )) { - $id++; + foreach my $type (qw( blob clob )) { + foreach my $size (qw( small large )) { + $id++; - lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) } - "inserted $size $type without dying"; - ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" ); - } - } + lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) } + "inserted $size $type without dying"; + + ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" ); + } + } } -# test hierarchical querys + +### test hierarchical queries if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { my $source = $schema->source('Artist'); @@ -323,244 +374,210 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { ], }); + # select the whole tree { - # select the whole tree - my $rs = $schema->resultset('Artist')->search({}, - { - 'start_with' => { 'name' => 'root' }, - 'connect_by' => { 'parentid' => 'prior artistid'}, - }); -=pod - SELECT - COUNT( * ) - FROM - artist me - START WITH - name = ? - CONNECT BY - parentid = prior artistid - - Parameters: 'root' -=cut + my $rs = $schema->resultset('Artist')->search({}, { + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid + FROM artist me + START WITH name = ? + CONNECT BY parentid = prior artistid + )', + [ [ name => 'root'] ], + ); + is_deeply ( + [ $rs->get_column ('name')->all ], + [ qw/root child1 grandchild greatgrandchild child2/ ], + 'got artist tree', + ); + + + is_same_sql_bind ( + $rs->count_rs->as_query, + '( + SELECT COUNT( * ) + FROM artist me + START WITH name = ? + CONNECT BY parentid = prior artistid + )', + [ [ name => 'root'] ], + ); + is( $rs->count, 5, 'Connect By count ok' ); - my $ok = 1; -=pod - SELECT - me.artistid, me.name, me.rank, me.charfield, me.parentid - FROM - artist me - START WITH - name = ? - CONNECT BY - parentid = prior artistid - - Parameters: 'root' -=cut - foreach my $node_name (qw(root child1 grandchild greatgrandchild child2)) { - $ok = 0 if $rs->next->name ne $node_name; - } - ok( $ok, 'got artist tree'); } + # use order siblings by statement { - # use order siblings by statement - my $rs = $schema->resultset('Artist')->search({}, - { - 'start_with' => { 'name' => 'root' }, - 'connect_by' => { 'parentid' => 'prior artistid'}, - 'order_siblings_by' => 'name DESC', - }); - my $ok = 1; -=pod - SELECT - me.artistid, me.name, me.rank, me.charfield, me.parentid - FROM - artist me - START WITH - name = ? - CONNECT BY - parentid = prior artistid - ORDER SIBLINGS BY - name DESC - - Parameters: 'root' -=cut - foreach my $node_name (qw(root child2 child1 grandchild greatgrandchild)) { - $ok = 0 if $rs->next->name ne $node_name; - } - ok( $ok, 'Order Siblings By ok'); + my $rs = $schema->resultset('Artist')->search({}, { + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + order_siblings_by => { -desc => 'name' }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid + FROM artist me + START WITH name = ? + CONNECT BY parentid = PRIOR( artistid ) + ORDER SIBLINGS BY name DESC + )', + [ [ name => 'root'] ], + ); + + is_deeply ( + [ $rs->get_column ('name')->all ], + [ qw/root child2 child1 grandchild greatgrandchild/ ], + 'Order Siblings By ok', + ); } + # get the root node { - # get the root node - my $rs = $schema->resultset('Artist')->search({ parentid => undef }, - { - 'start_with' => { 'name' => 'greatgrandchild' }, - 'connect_by' => { 'prior parentid' => 'artistid'}, - }); -=pod - SELECT - COUNT( * ) - FROM - artist me - WHERE - ( parentid IS NULL ) - START WITH - name = ? - CONNECT BY - prior parentid = artistid - - Parameters: 'greatgrandchild' -=cut - is( $rs->count, 1, 'root node count ok' ); -=pod - SELECT - me.artistid, me.name, me.rank, me.charfield, me.parentid - FROM - artist me - WHERE - ( parentid IS NULL ) - START WITH - name = ? - CONNECT BY - prior parentid = artistid - - Parameters: 'greatgrandchild' -=cut - ok( $rs->next->name eq 'root', 'found root node'); + my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { + start_with => { name => 'greatgrandchild' }, + connect_by => { artistid => { -prior => \ 'parentid' } }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid + FROM artist me + WHERE ( parentid IS NULL ) + START WITH name = ? + CONNECT BY artistid = PRIOR( parentid ) + )', + [ [ name => 'greatgrandchild'] ], + ); + + is_deeply( + [ $rs->get_column('name')->all ], + [ 'root' ], + 'found root node', + ); } + # combine a connect by with a join { - # combine a connect by with a join - my $rs = $schema->resultset('Artist')->search({'cds.title' => { 'like' => '%cd'}}, - { - 'join' => 'cds', - 'start_with' => { 'name' => 'root' }, - 'connect_by' => { 'parentid' => 'prior artistid'}, - }); -=pod - SELECT - COUNT( * ) - FROM - artist me - LEFT JOIN - cd cds ON cds.artist = me.artistid - WHERE - ( cds.title LIKE ? ) - START WITH - name = ? - CONNECT BY - parentid = prior artistid - - Parameters: '%cd', 'root' -=cut + my $rs = $schema->resultset('Artist')->search( + {'cds.title' => { -like => '%cd'} }, + { + join => 'cds', + start_with => { 'me.name' => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + } + ); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid + FROM artist me + LEFT JOIN cd cds ON cds.artist = me.artistid + WHERE ( cds.title LIKE ? ) + START WITH me.name = ? + CONNECT BY parentid = PRIOR( artistid ) + )', + [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], + ); + + is_deeply( + [ $rs->get_column('name')->all ], + [ 'grandchild' ], + 'Connect By with a join result name ok' + ); + + + is_same_sql_bind ( + $rs->count_rs->as_query, + '( + SELECT COUNT( * ) + FROM artist me + LEFT JOIN cd cds ON cds.artist = me.artistid + WHERE ( cds.title LIKE ? ) + START WITH me.name = ? + CONNECT BY parentid = prior artistid + )', + [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], + ); + is( $rs->count, 1, 'Connect By with a join; count ok' ); -=pod - SELECT - me.artistid, me.name, me.rank, me.charfield, me.parentid - FROM - artist me - LEFT JOIN - cd cds ON cds.artist = me.artistid - WHERE - ( cds.title LIKE ? ) - START WITH - name = ? - CONNECT BY - parentid = prior artistid - - Parameters: '%cd', 'root' -=cut - ok( $rs->next->name eq 'grandchild', 'Connect By with a join; result name ok') } + # combine a connect by with order_by { - # combine a connect by with order_by - my $rs = $schema->resultset('Artist')->search({}, - { - 'start_with' => { 'name' => 'greatgrandchild' }, - 'connect_by' => { 'prior parentid' => 'artistid'}, - 'order_by' => 'name ASC', - }); - my $ok = 1; -=pod - SELECT - me.artistid, me.name, me.rank, me.charfield, me.parentid - FROM - artist me - START WITH - name = ? - CONNECT BY - prior parentid = artistid - ORDER BY - name ASC - - Parameters: 'greatgrandchild' -=cut - foreach my $node_name (qw(child1 grandchild greatgrandchild root)) { - $ok = 0 if $rs->next->name ne $node_name; - } - ok( $ok, 'Connect By with a order_by; result name ok'); + my $rs = $schema->resultset('Artist')->search({}, { + start_with => { name => 'greatgrandchild' }, + connect_by => { artistid => { -prior => \ 'parentid' } }, + order_by => { -asc => 'name' }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + 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 + )', + [ [ name => 'greatgrandchild' ] ], + ); + + is_deeply ( + [ $rs->get_column ('name')->all ], + [ qw/child1 grandchild greatgrandchild root/ ], + 'Connect By with a order_by - result name ok' + ); } + + # limit a connect by { - # limit a connect by - my $rs = $schema->resultset('Artist')->search({}, - { - 'start_with' => { 'name' => 'greatgrandchild' }, - 'connect_by' => { 'prior parentid' => 'artistid'}, - 'order_by' => 'name ASC', - 'rows' => 2, - 'page' => 1, - }); -=pod - SELECT - COUNT( * ) - FROM - artist me - START WITH - name = ? - CONNECT BY - prior parentid = artistid - - Parameters: 'greatgrandchild' -=cut - is( $rs->count(), 2, 'Connect By; LIMIT count ok' ); - my $ok = 1; -=pod - 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 - prior parentid = artistid - ORDER BY - name ASC - ) A - WHERE - ROWNUM < 3 - ) B - WHERE - r >= 1 - Parameters: 'greatgrandchild' -=cut - foreach my $node_name (qw(child1 grandchild)) { - $ok = 0 if $rs->next->name ne $node_name; - } - ok( $ok, 'LIMIT a Connect By query ok'); + my $rs = $schema->resultset('Artist')->search({}, { + start_with => { name => 'greatgrandchild' }, + connect_by => { artistid => { -prior => \ 'parentid' } }, + order_by => { -desc => 'name' }, + rows => 2, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + need to fill in correct sql + )', + [], + ); + + is_deeply ( + [ $rs->get_column ('name')->all ], + [ qw/grandchild child1/ ], + 'LIMIT a Connect By query - correct names' + ); + + + is_same_sql_bind ( + $rs->count_rs->as_query, + '( + need to fill in correct sql + )', + [], + ); + + is( $rs->count, 2, 'Connect By; LIMIT count ok' ); } } +done_testing; + # clean up our mess END { if($schema && ($dbh = $schema->storage->dbh)) { @@ -572,8 +589,8 @@ END { $dbh->do("DROP SEQUENCE nonpkid_seq"); $dbh->do("DROP TABLE artist"); $dbh->do("DROP TABLE sequence_test"); - $dbh->do("DROP TABLE cd"); $dbh->do("DROP TABLE track"); + $dbh->do("DROP TABLE cd"); $dbh->do("DROP TABLE bindtype_test"); } }