fixed sql mistakes from hierarchical query tests
[dbsrgits/DBIx-Class.git] / t / 73oracle.t
CommitLineData
cb464582 1{
2 package # hide from PAUSE
3 DBICTest::Schema::ArtistFQN;
4
5 use base 'DBIx::Class::Core';
6
7 __PACKAGE__->table(
8 defined $ENV{DBICTEST_ORA_USER}
9 ? $ENV{DBICTEST_ORA_USER} . '.artist'
10 : 'artist'
11 );
12 __PACKAGE__->add_columns(
13 'artistid' => {
14 data_type => 'integer',
15 is_auto_increment => 1,
16 },
17 'name' => {
18 data_type => 'varchar',
19 size => 100,
20 is_nullable => 1,
21 },
22 );
23 __PACKAGE__->set_primary_key('artistid');
24
25 1;
26}
27
70350518 28use strict;
e6dd7b42 29use warnings;
70350518 30
5db2758d 31use Test::Exception;
70350518 32use Test::More;
ab6e0924 33
70350518 34use lib qw(t/lib);
35use DBICTest;
8ce8340f 36use DBIC::SqlMakerTest;
0567538f 37
38my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
39
70350518 40plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
39b8d119 41 'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
42 ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
0567538f 43 unless ($dsn && $user && $pass);
44
cb464582 45DBICTest::Schema->load_classes('ArtistFQN');
9900b569 46my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
0567538f 47
3ff5b740 48my $dbh = $schema->storage->dbh;
0567538f 49
50eval {
51 $dbh->do("DROP SEQUENCE artist_seq");
e6dd7b42 52 $dbh->do("DROP SEQUENCE cd_seq");
c0024355 53 $dbh->do("DROP SEQUENCE track_seq");
39b8d119 54 $dbh->do("DROP SEQUENCE pkid1_seq");
55 $dbh->do("DROP SEQUENCE pkid2_seq");
56 $dbh->do("DROP SEQUENCE nonpkid_seq");
0567538f 57 $dbh->do("DROP TABLE artist");
39b8d119 58 $dbh->do("DROP TABLE sequence_test");
2660b14e 59 $dbh->do("DROP TABLE track");
b7b18f32 60 $dbh->do("DROP TABLE cd");
0567538f 61};
62$dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
e6dd7b42 63$dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
c0024355 64$dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
39b8d119 65$dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
66$dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
67$dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
b7b18f32 68
c0024355 69$dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), rank NUMBER(38), charfield VARCHAR2(10))");
b7b18f32 70$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
71
39b8d119 72$dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))");
b7b18f32 73$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
2660b14e 74
b7b18f32 75$dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
e6dd7b42 76$dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
b7b18f32 77
78$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)");
c0024355 79$dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
b7b18f32 80
0567538f 81$dbh->do(qq{
82 CREATE OR REPLACE TRIGGER artist_insert_trg
83 BEFORE INSERT ON artist
84 FOR EACH ROW
85 BEGIN
86 IF :new.artistid IS NULL THEN
87 SELECT artist_seq.nextval
88 INTO :new.artistid
89 FROM DUAL;
90 END IF;
91 END;
92});
e6dd7b42 93$dbh->do(qq{
94 CREATE OR REPLACE TRIGGER cd_insert_trg
95 BEFORE INSERT ON cd
96 FOR EACH ROW
97 BEGIN
98 IF :new.cdid IS NULL THEN
99 SELECT cd_seq.nextval
100 INTO :new.cdid
101 FROM DUAL;
102 END IF;
103 END;
104});
c0024355 105$dbh->do(qq{
106 CREATE OR REPLACE TRIGGER cd_insert_trg
107 BEFORE INSERT ON cd
108 FOR EACH ROW
109 BEGIN
110 IF :new.cdid IS NULL THEN
111 SELECT cd_seq.nextval
112 INTO :new.cdid
113 FROM DUAL;
114 END IF;
115 END;
116});
117$dbh->do(qq{
118 CREATE OR REPLACE TRIGGER track_insert_trg
119 BEFORE INSERT ON track
120 FOR EACH ROW
121 BEGIN
122 IF :new.trackid IS NULL THEN
123 SELECT track_seq.nextval
124 INTO :new.trackid
125 FROM DUAL;
126 END IF;
127 END;
128});
0567538f 129
5db2758d 130{
131 # Swiped from t/bindtype_columns.t to avoid creating my own Resultset.
132
133 local $SIG{__WARN__} = sub {};
134 eval { $dbh->do('DROP TABLE bindtype_test') };
135
136 $dbh->do(qq[
e6dd7b42 137 CREATE TABLE bindtype_test
5db2758d 138 (
139 id integer NOT NULL PRIMARY KEY,
140 bytea integer NULL,
141 blob blob NULL,
142 clob clob NULL
143 )
144 ],{ RaiseError => 1, PrintError => 1 });
145}
146
3ff5b740 147# This is in Core now, but it's here just to test that it doesn't break
148$schema->class('Artist')->load_components('PK::Auto');
149# These are compat shims for PK::Auto...
150$schema->class('CD')->load_components('PK::Auto::Oracle');
151$schema->class('Track')->load_components('PK::Auto::Oracle');
0567538f 152
153# test primary key handling
3ff5b740 154my $new = $schema->resultset('Artist')->create({ name => 'foo' });
c8f4b52b 155is($new->artistid, 1, "Oracle Auto-PK worked");
0567538f 156
e6dd7b42 157my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
62d4dbae 158is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name");
e6dd7b42 159
cb464582 160# test again with fully-qualified table name
161$new = $schema->resultset('ArtistFQN')->create( { name => 'bar' } );
162is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" );
163
62d4dbae 164# test rel names over the 30 char limit
165my $query = $schema->resultset('Artist')->search({
6c0230de 166 artistid => 1
62d4dbae 167}, {
168 prefetch => 'cds_very_very_very_long_relationship_name'
169});
170
171lives_and {
6c0230de 172 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
62d4dbae 173} 'query with rel name over 30 chars survived and worked';
174
6c0230de 175# rel name over 30 char limit with user condition
176# This requires walking the SQLA data structure.
177{
178 local $TODO = 'user condition on rel longer than 30 chars';
179
180 $query = $schema->resultset('Artist')->search({
181 'cds_very_very_very_long_relationship_name.title' => 'EP C'
182 }, {
183 prefetch => 'cds_very_very_very_long_relationship_name'
184 });
185
186 lives_and {
187 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
188 } 'query with rel name over 30 chars and user condition survived and worked';
189}
190
9900b569 191# test join with row count ambiguity
d2a3958e 192
c0024355 193my $track = $schema->resultset('Track')->create({ cd => $cd->cdid,
9900b569 194 position => 1, title => 'Track1' });
3ff5b740 195my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
2660b14e 196 { join => 'cd',
197 rows => 2 }
198);
199
d2a3958e 200ok(my $row = $tjoin->next);
201
202is($row->title, 'Track1', "ambiguous column ok");
2660b14e 203
286f32b3 204# check count distinct with multiple columns
c0024355 205my $other_track = $schema->resultset('Track')->create({ cd => $cd->cdid, position => 1, title => 'Track2' });
11d68671 206
3ff5b740 207my $tcount = $schema->resultset('Track')->search(
11d68671 208 {},
209 {
210 select => [ qw/position title/ ],
211 distinct => 1,
212 }
213);
f12f0d97 214is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
11d68671 215
216$tcount = $schema->resultset('Track')->search(
217 {},
218 {
219 columns => [ qw/position title/ ],
220 distinct => 1,
221 }
222);
f12f0d97 223is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
286f32b3 224
11d68671 225$tcount = $schema->resultset('Track')->search(
226 {},
e6dd7b42 227 {
11d68671 228 group_by => [ qw/position title/ ]
229 }
230);
f12f0d97 231is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok');
2660b14e 232
0567538f 233# test LIMIT support
234for (1..6) {
3ff5b740 235 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
0567538f 236}
3ff5b740 237my $it = $schema->resultset('Artist')->search( {},
0567538f 238 { rows => 3,
cb464582 239 offset => 3,
0567538f 240 order_by => 'artistid' }
241);
242is( $it->count, 3, "LIMIT count ok" );
243is( $it->next->name, "Artist 2", "iterator->next ok" );
244$it->next;
245$it->next;
246is( $it->next, undef, "next past end of resultset ok" );
247
e8e971f2 248{
249 my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 });
250 my @results = $rs->all;
251 is( scalar @results, 1, "Group by with limit OK" );
252}
253
b7b18f32 254# test with_deferred_fk_checks
255lives_ok {
256 $schema->storage->with_deferred_fk_checks(sub {
257 $schema->resultset('Track')->create({
258 trackid => 999, cd => 999, position => 1, title => 'deferred FK track'
259 });
260 $schema->resultset('CD')->create({
261 artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd'
262 });
263 });
264} 'with_deferred_fk_checks code survived';
265
266is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track',
267 'code in with_deferred_fk_checks worked';
268
269throws_ok {
270 $schema->resultset('Track')->create({
271 trackid => 1, cd => 9999, position => 1, title => 'Track1'
272 });
273} qr/constraint/i, 'with_deferred_fk_checks is off';
274
ccd6f984 275# test auto increment using sequences WITHOUT triggers
39b8d119 276for (1..5) {
277 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
278 is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
279 is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
280 is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
281}
282my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
283is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
ccd6f984 284
8068691e 285SKIP: {
d7f20fdf 286 my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
287 $binstr{'large'} = $binstr{'small'} x 1024;
8068691e 288
d7f20fdf 289 my $maxloblen = length $binstr{'large'};
290 note "Localizing LongReadLen to $maxloblen to avoid truncation of test data";
291 local $dbh->{'LongReadLen'} = $maxloblen;
5db2758d 292
d7f20fdf 293 my $rs = $schema->resultset('BindType');
294 my $id = 0;
5db2758d 295
931e5d43 296 if ($DBD::Oracle::VERSION eq '1.23') {
297 throws_ok { $rs->create({ id => 1, blob => $binstr{large} }) }
298 qr/broken/,
299 'throws on blob insert with DBD::Oracle == 1.23';
5db2758d 300
931e5d43 301 skip 'buggy BLOB support in DBD::Oracle 1.23', 7;
302 }
5db2758d 303
d7f20fdf 304 foreach my $type (qw( blob clob )) {
305 foreach my $size (qw( small large )) {
306 $id++;
5db2758d 307
d7f20fdf 308 lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
309 "inserted $size $type without dying";
310
311 ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
312 }
313 }
5db2758d 314}
315
bc6ae32e 316
317### test hierarchical queries
c0024355 318if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
319 my $source = $schema->source('Artist');
320
321 $source->add_column( 'parentid' );
322
323 $source->add_relationship('children', 'DBICTest::Schema::Artist',
324 { 'foreign.parentid' => 'self.artistid' },
325 {
326 accessor => 'multi',
327 join_type => 'LEFT',
328 cascade_delete => 1,
329 cascade_copy => 1,
330 } );
331 $source->add_relationship('parent', 'DBICTest::Schema::Artist',
332 { 'foreign.artistid' => 'self.parentid' },
333 { accessor => 'single' } );
334 DBICTest::Schema::Artist->add_column( 'parentid' );
335 DBICTest::Schema::Artist->has_many(
336 children => 'DBICTest::Schema::Artist',
337 { 'foreign.parentid' => 'self.artistid' }
338 );
339 DBICTest::Schema::Artist->belongs_to(
340 parent => 'DBICTest::Schema::Artist',
341 { 'foreign.artistid' => 'self.parentid' }
342 );
343
344 $schema->resultset('Artist')->create ({
345 name => 'root',
346 cds => [],
347 children => [
348 {
349 name => 'child1',
350 children => [
351 {
352 name => 'grandchild',
353 cds => [
354 {
355 title => "grandchilds's cd" ,
356 year => '2008',
357 tracks => [
358 {
359 position => 1,
360 title => 'Track 1 grandchild',
361 }
362 ],
363 }
364 ],
365 children => [
366 {
367 name => 'greatgrandchild',
368 }
369 ],
370 }
371 ],
372 },
373 {
374 name => 'child2',
375 },
376 ],
377 });
378
bc6ae32e 379 # select the whole tree
c0024355 380 {
bc6ae32e 381 my $rs = $schema->resultset('Artist')->search({}, {
382 start_with => { name => 'root' },
383 connect_by => { parentid => { -prior => \ 'artistid' } },
384 });
385
386 is_same_sql_bind (
387 $rs->as_query,
388 '(
389 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
390 FROM artist me
391 START WITH name = ?
11b2b148 392 CONNECT BY parentid = PRIOR( artistid )
bc6ae32e 393 )',
394 [ [ name => 'root'] ],
395 );
396 is_deeply (
397 [ $rs->get_column ('name')->all ],
398 [ qw/root child1 grandchild greatgrandchild child2/ ],
399 'got artist tree',
400 );
401
402
403 is_same_sql_bind (
404 $rs->count_rs->as_query,
405 '(
406 SELECT COUNT( * )
407 FROM artist me
408 START WITH name = ?
11b2b148 409 CONNECT BY parentid = PRIOR( artistid )
bc6ae32e 410 )',
411 [ [ name => 'root'] ],
412 );
413
c0024355 414 is( $rs->count, 5, 'Connect By count ok' );
c0024355 415 }
416
bc6ae32e 417 # use order siblings by statement
c0024355 418 {
bc6ae32e 419 my $rs = $schema->resultset('Artist')->search({}, {
420 start_with => { name => 'root' },
421 connect_by => { parentid => { -prior => \ 'artistid' } },
422 order_siblings_by => { -desc => 'name' },
423 });
424
425 is_same_sql_bind (
426 $rs->as_query,
427 '(
428 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
429 FROM artist me
430 START WITH name = ?
431 CONNECT BY parentid = PRIOR( artistid )
432 ORDER SIBLINGS BY name DESC
433 )',
434 [ [ name => 'root'] ],
435 );
436
437 is_deeply (
438 [ $rs->get_column ('name')->all ],
439 [ qw/root child2 child1 grandchild greatgrandchild/ ],
440 'Order Siblings By ok',
441 );
c0024355 442 }
443
bc6ae32e 444 # get the root node
c0024355 445 {
bc6ae32e 446 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
447 start_with => { name => 'greatgrandchild' },
448 connect_by => { artistid => { -prior => \ 'parentid' } },
449 });
450
451 is_same_sql_bind (
452 $rs->as_query,
453 '(
454 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
455 FROM artist me
456 WHERE ( parentid IS NULL )
457 START WITH name = ?
458 CONNECT BY artistid = PRIOR( parentid )
459 )',
460 [ [ name => 'greatgrandchild'] ],
461 );
462
463 is_deeply(
464 [ $rs->get_column('name')->all ],
465 [ 'root' ],
466 'found root node',
467 );
c0024355 468 }
469
bc6ae32e 470 # combine a connect by with a join
c0024355 471 {
bc6ae32e 472 my $rs = $schema->resultset('Artist')->search(
473 {'cds.title' => { -like => '%cd'} },
474 {
475 join => 'cds',
476 start_with => { 'me.name' => 'root' },
477 connect_by => { parentid => { -prior => \ 'artistid' } },
478 }
479 );
480
481 is_same_sql_bind (
482 $rs->as_query,
483 '(
484 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
485 FROM artist me
486 LEFT JOIN cd cds ON cds.artist = me.artistid
487 WHERE ( cds.title LIKE ? )
488 START WITH me.name = ?
489 CONNECT BY parentid = PRIOR( artistid )
490 )',
491 [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
492 );
493
494 is_deeply(
495 [ $rs->get_column('name')->all ],
496 [ 'grandchild' ],
497 'Connect By with a join result name ok'
498 );
499
500
501 is_same_sql_bind (
502 $rs->count_rs->as_query,
503 '(
504 SELECT COUNT( * )
505 FROM artist me
506 LEFT JOIN cd cds ON cds.artist = me.artistid
507 WHERE ( cds.title LIKE ? )
508 START WITH me.name = ?
509 CONNECT BY parentid = prior artistid
510 )',
511 [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
512 );
513
c0024355 514 is( $rs->count, 1, 'Connect By with a join; count ok' );
c0024355 515 }
516
bc6ae32e 517 # combine a connect by with order_by
c0024355 518 {
bc6ae32e 519 my $rs = $schema->resultset('Artist')->search({}, {
520 start_with => { name => 'greatgrandchild' },
521 connect_by => { artistid => { -prior => \ 'parentid' } },
522 order_by => { -asc => 'name' },
523 });
524
525 is_same_sql_bind (
526 $rs->as_query,
527 '(
528 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
529 FROM artist me
530 START WITH name = ?
531 CONNECT BY artistid = PRIOR( parentid )
532 ORDER BY name ASC
533 )',
534 [ [ name => 'greatgrandchild' ] ],
535 );
536
537 is_deeply (
538 [ $rs->get_column ('name')->all ],
539 [ qw/child1 grandchild greatgrandchild root/ ],
540 'Connect By with a order_by - result name ok'
541 );
c0024355 542 }
543
bc6ae32e 544
545 # limit a connect by
c0024355 546 {
bc6ae32e 547 my $rs = $schema->resultset('Artist')->search({}, {
548 start_with => { name => 'greatgrandchild' },
549 connect_by => { artistid => { -prior => \ 'parentid' } },
550 order_by => { -desc => 'name' },
551 rows => 2,
552 });
553
554 is_same_sql_bind (
555 $rs->as_query,
556 '(
557 need to fill in correct sql
558 )',
559 [],
560 );
561
562 is_deeply (
563 [ $rs->get_column ('name')->all ],
564 [ qw/grandchild child1/ ],
565 'LIMIT a Connect By query - correct names'
566 );
567
568
569 is_same_sql_bind (
570 $rs->count_rs->as_query,
571 '(
572 need to fill in correct sql
573 )',
574 [],
575 );
576
577 is( $rs->count, 2, 'Connect By; LIMIT count ok' );
c0024355 578 }
579}
580
86cc4156 581done_testing;
582
0567538f 583# clean up our mess
3ff5b740 584END {
fe0d48d3 585 if($schema && ($dbh = $schema->storage->dbh)) {
3ff5b740 586 $dbh->do("DROP SEQUENCE artist_seq");
e6dd7b42 587 $dbh->do("DROP SEQUENCE cd_seq");
c0024355 588 $dbh->do("DROP SEQUENCE track_seq");
39b8d119 589 $dbh->do("DROP SEQUENCE pkid1_seq");
590 $dbh->do("DROP SEQUENCE pkid2_seq");
591 $dbh->do("DROP SEQUENCE nonpkid_seq");
3ff5b740 592 $dbh->do("DROP TABLE artist");
39b8d119 593 $dbh->do("DROP TABLE sequence_test");
3ff5b740 594 $dbh->do("DROP TABLE track");
b7b18f32 595 $dbh->do("DROP TABLE cd");
5db2758d 596 $dbh->do("DROP TABLE bindtype_test");
3ff5b740 597 }
598}
0567538f 599