This test in fact requires DT::F::SQLite
[dbsrgits/DBIx-Class-Historic.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 },
ab4f4e4c 22 'autoinc_col' => {
23 data_type => 'integer',
24 is_auto_increment => 1,
25 },
cb464582 26 );
27 __PACKAGE__->set_primary_key('artistid');
28
29 1;
30}
31
70350518 32use strict;
e6dd7b42 33use warnings;
70350518 34
5db2758d 35use Test::Exception;
70350518 36use Test::More;
ab6e0924 37
70350518 38use lib qw(t/lib);
39use DBICTest;
8ce8340f 40use DBIC::SqlMakerTest;
0567538f 41
df6e3f5c 42my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
43
44# optional:
9d7d2f00 45my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_ORA_EXTRAUSER_${_}" } qw/DSN USER PASS/};
0567538f 46
70350518 47plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
39b8d119 48 'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
49 ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
0567538f 50 unless ($dsn && $user && $pass);
51
cb464582 52DBICTest::Schema->load_classes('ArtistFQN');
9900b569 53my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
0567538f 54
ba12b23f 55note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
56
3ff5b740 57my $dbh = $schema->storage->dbh;
0567538f 58
df6e3f5c 59do_creates($dbh);
0567538f 60
5db2758d 61{
62 # Swiped from t/bindtype_columns.t to avoid creating my own Resultset.
63
64 local $SIG{__WARN__} = sub {};
65 eval { $dbh->do('DROP TABLE bindtype_test') };
66
67 $dbh->do(qq[
e6dd7b42 68 CREATE TABLE bindtype_test
5db2758d 69 (
70 id integer NOT NULL PRIMARY KEY,
71 bytea integer NULL,
72 blob blob NULL,
73 clob clob NULL
74 )
75 ],{ RaiseError => 1, PrintError => 1 });
76}
77
3ff5b740 78# This is in Core now, but it's here just to test that it doesn't break
79$schema->class('Artist')->load_components('PK::Auto');
80# These are compat shims for PK::Auto...
81$schema->class('CD')->load_components('PK::Auto::Oracle');
82$schema->class('Track')->load_components('PK::Auto::Oracle');
0567538f 83
ab4f4e4c 84
85# test primary key handling with multiple triggers
3ff5b740 86my $new = $schema->resultset('Artist')->create({ name => 'foo' });
c8f4b52b 87is($new->artistid, 1, "Oracle Auto-PK worked");
0567538f 88
ab4f4e4c 89like ($new->result_source->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Correct PK sequence selected');
e6dd7b42 90
cb464582 91# test again with fully-qualified table name
ab4f4e4c 92my $artistfqn_rs = $schema->resultset('ArtistFQN');
93my $artist_rsrc = $artistfqn_rs->result_source;
94
95delete $artist_rsrc->column_info('artistid')->{sequence};
96
97$new = $artistfqn_rs->create( { name => 'bar' } );
cb464582 98is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" );
99
ab4f4e4c 100delete $artist_rsrc->column_info('artistid')->{sequence};
101
102$new = $artistfqn_rs->create( { name => 'bar', autoinc_col => 1000 } );
103is( $new->artistid, 3, "Oracle Auto-PK worked with fully-qualified tablename" );
104is( $new->autoinc_col, 1000, "Oracle Auto-Inc overruled with fully-qualified tablename");
105
106like ($artist_rsrc->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Still correct PK sequence');
107
108# test LIMIT support
109for (1..6) {
110 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
111}
112my $it = $schema->resultset('Artist')->search( { name => { -like => 'Artist %' }},
113 { rows => 3,
114 offset => 4,
115 order_by => 'artistid' }
116);
117is( $it->count, 2, "LIMIT count past end of RS ok" );
118is( $it->next->name, "Artist 5", "iterator->next ok" );
119is( $it->next->name, "Artist 6", "iterator->next ok" );
120is( $it->next, undef, "next past end of resultset ok" );
121
122my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
123is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name");
124
62d4dbae 125# test rel names over the 30 char limit
6c0230de 126{
ab4f4e4c 127 my $query = $schema->resultset('Artist')->search({
128 artistid => 1
6c0230de 129 }, {
130 prefetch => 'cds_very_very_very_long_relationship_name'
131 });
132
133 lives_and {
134 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
ab4f4e4c 135 } 'query with rel name over 30 chars survived and worked';
136
137 # rel name over 30 char limit with user condition
138 # This requires walking the SQLA data structure.
139 {
140 local $TODO = 'user condition on rel longer than 30 chars';
141
142 $query = $schema->resultset('Artist')->search({
143 'cds_very_very_very_long_relationship_name.title' => 'EP C'
144 }, {
145 prefetch => 'cds_very_very_very_long_relationship_name'
146 });
147
148 lives_and {
149 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
150 } 'query with rel name over 30 chars and user condition survived and worked';
151 }
6c0230de 152}
153
9900b569 154# test join with row count ambiguity
d2a3958e 155
c0024355 156my $track = $schema->resultset('Track')->create({ cd => $cd->cdid,
9900b569 157 position => 1, title => 'Track1' });
3ff5b740 158my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
2660b14e 159 { join => 'cd',
160 rows => 2 }
161);
162
d2a3958e 163ok(my $row = $tjoin->next);
164
165is($row->title, 'Track1', "ambiguous column ok");
2660b14e 166
286f32b3 167# check count distinct with multiple columns
c0024355 168my $other_track = $schema->resultset('Track')->create({ cd => $cd->cdid, position => 1, title => 'Track2' });
11d68671 169
3ff5b740 170my $tcount = $schema->resultset('Track')->search(
11d68671 171 {},
172 {
173 select => [ qw/position title/ ],
174 distinct => 1,
175 }
176);
f12f0d97 177is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
11d68671 178
179$tcount = $schema->resultset('Track')->search(
180 {},
181 {
182 columns => [ qw/position title/ ],
183 distinct => 1,
184 }
185);
f12f0d97 186is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
286f32b3 187
11d68671 188$tcount = $schema->resultset('Track')->search(
189 {},
e6dd7b42 190 {
11d68671 191 group_by => [ qw/position title/ ]
192 }
193);
f12f0d97 194is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok');
2660b14e 195
e8e971f2 196{
197 my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 });
198 my @results = $rs->all;
199 is( scalar @results, 1, "Group by with limit OK" );
200}
201
bd691933 202# test identifiers over the 30 char limit
203{
204 lives_ok {
205 my @results = $schema->resultset('CD')->search(undef, {
206 prefetch => 'very_long_artist_relationship',
207 rows => 3,
208 offset => 0,
209 })->all;
210 ok( scalar @results > 0, 'limit with long identifiers returned something');
211 } 'limit with long identifiers executed successfully';
212}
213
b7b18f32 214# test with_deferred_fk_checks
215lives_ok {
216 $schema->storage->with_deferred_fk_checks(sub {
217 $schema->resultset('Track')->create({
218 trackid => 999, cd => 999, position => 1, title => 'deferred FK track'
219 });
220 $schema->resultset('CD')->create({
221 artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd'
222 });
223 });
224} 'with_deferred_fk_checks code survived';
225
226is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track',
227 'code in with_deferred_fk_checks worked';
228
229throws_ok {
230 $schema->resultset('Track')->create({
231 trackid => 1, cd => 9999, position => 1, title => 'Track1'
232 });
233} qr/constraint/i, 'with_deferred_fk_checks is off';
234
ccd6f984 235# test auto increment using sequences WITHOUT triggers
39b8d119 236for (1..5) {
237 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
238 is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
239 is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
240 is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
241}
242my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
243is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
ccd6f984 244
8068691e 245SKIP: {
d7f20fdf 246 my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
247 $binstr{'large'} = $binstr{'small'} x 1024;
8068691e 248
d7f20fdf 249 my $maxloblen = length $binstr{'large'};
250 note "Localizing LongReadLen to $maxloblen to avoid truncation of test data";
251 local $dbh->{'LongReadLen'} = $maxloblen;
5db2758d 252
d7f20fdf 253 my $rs = $schema->resultset('BindType');
254 my $id = 0;
5db2758d 255
931e5d43 256 if ($DBD::Oracle::VERSION eq '1.23') {
257 throws_ok { $rs->create({ id => 1, blob => $binstr{large} }) }
258 qr/broken/,
259 'throws on blob insert with DBD::Oracle == 1.23';
5db2758d 260
931e5d43 261 skip 'buggy BLOB support in DBD::Oracle 1.23', 7;
262 }
5db2758d 263
f3f6c13a 264 # disable BLOB mega-output
265 my $orig_debug = $schema->storage->debug;
266 $schema->storage->debug (0);
267
d7f20fdf 268 foreach my $type (qw( blob clob )) {
269 foreach my $size (qw( small large )) {
270 $id++;
5db2758d 271
d7f20fdf 272 lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
273 "inserted $size $type without dying";
274
275 ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
276 }
277 }
f3f6c13a 278
279 $schema->storage->debug ($orig_debug);
5db2758d 280}
281
bc6ae32e 282
283### test hierarchical queries
c0024355 284if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
285 my $source = $schema->source('Artist');
286
287 $source->add_column( 'parentid' );
288
289 $source->add_relationship('children', 'DBICTest::Schema::Artist',
290 { 'foreign.parentid' => 'self.artistid' },
291 {
292 accessor => 'multi',
293 join_type => 'LEFT',
294 cascade_delete => 1,
295 cascade_copy => 1,
296 } );
297 $source->add_relationship('parent', 'DBICTest::Schema::Artist',
298 { 'foreign.artistid' => 'self.parentid' },
299 { accessor => 'single' } );
300 DBICTest::Schema::Artist->add_column( 'parentid' );
301 DBICTest::Schema::Artist->has_many(
302 children => 'DBICTest::Schema::Artist',
303 { 'foreign.parentid' => 'self.artistid' }
304 );
305 DBICTest::Schema::Artist->belongs_to(
306 parent => 'DBICTest::Schema::Artist',
307 { 'foreign.artistid' => 'self.parentid' }
308 );
309
310 $schema->resultset('Artist')->create ({
311 name => 'root',
5c810af7 312 rank => 1,
c0024355 313 cds => [],
314 children => [
315 {
316 name => 'child1',
5c810af7 317 rank => 2,
c0024355 318 children => [
319 {
320 name => 'grandchild',
5c810af7 321 rank => 3,
c0024355 322 cds => [
323 {
324 title => "grandchilds's cd" ,
325 year => '2008',
326 tracks => [
327 {
328 position => 1,
329 title => 'Track 1 grandchild',
330 }
331 ],
332 }
333 ],
334 children => [
335 {
336 name => 'greatgrandchild',
5c810af7 337 rank => 3,
c0024355 338 }
339 ],
340 }
341 ],
342 },
343 {
344 name => 'child2',
5c810af7 345 rank => 3,
c0024355 346 },
347 ],
348 });
349
2a7879e2 350 $schema->resultset('Artist')->create(
351 {
352 name => 'cycle-root',
353 children => [
354 {
355 name => 'cycle-child1',
356 children => [ { name => 'cycle-grandchild' } ],
357 },
358 { name => 'cycle-child2' },
359 ],
360 }
361 );
362
363 $schema->resultset('Artist')->find({ name => 'cycle-root' })
e6600283 364 ->update({ parentid => { -ident => 'artistid' } });
2a7879e2 365
bc6ae32e 366 # select the whole tree
c0024355 367 {
bc6ae32e 368 my $rs = $schema->resultset('Artist')->search({}, {
369 start_with => { name => 'root' },
e6600283 370 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
bc6ae32e 371 });
372
373 is_same_sql_bind (
374 $rs->as_query,
375 '(
376 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
377 FROM artist me
378 START WITH name = ?
1756ae89 379 CONNECT BY parentid = PRIOR artistid
bc6ae32e 380 )',
381 [ [ name => 'root'] ],
382 );
383 is_deeply (
384 [ $rs->get_column ('name')->all ],
385 [ qw/root child1 grandchild greatgrandchild child2/ ],
386 'got artist tree',
387 );
388
389
390 is_same_sql_bind (
391 $rs->count_rs->as_query,
392 '(
393 SELECT COUNT( * )
394 FROM artist me
395 START WITH name = ?
1756ae89 396 CONNECT BY parentid = PRIOR artistid
bc6ae32e 397 )',
398 [ [ name => 'root'] ],
399 );
400
c0024355 401 is( $rs->count, 5, 'Connect By count ok' );
c0024355 402 }
403
bc6ae32e 404 # use order siblings by statement
ba12b23f 405 SKIP: {
406 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
407 skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
408 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
409
bc6ae32e 410 my $rs = $schema->resultset('Artist')->search({}, {
411 start_with => { name => 'root' },
e6600283 412 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
bc6ae32e 413 order_siblings_by => { -desc => 'name' },
414 });
415
416 is_same_sql_bind (
417 $rs->as_query,
418 '(
419 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
420 FROM artist me
421 START WITH name = ?
1756ae89 422 CONNECT BY parentid = PRIOR artistid
bc6ae32e 423 ORDER SIBLINGS BY name DESC
424 )',
425 [ [ name => 'root'] ],
426 );
427
428 is_deeply (
429 [ $rs->get_column ('name')->all ],
430 [ qw/root child2 child1 grandchild greatgrandchild/ ],
431 'Order Siblings By ok',
432 );
c0024355 433 }
434
bc6ae32e 435 # get the root node
c0024355 436 {
bc6ae32e 437 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
b34a62e5 438 start_with => { name => 'root' },
e6600283 439 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
bc6ae32e 440 });
441
442 is_same_sql_bind (
443 $rs->as_query,
444 '(
445 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
446 FROM artist me
447 WHERE ( parentid IS NULL )
448 START WITH name = ?
1756ae89 449 CONNECT BY parentid = PRIOR artistid
bc6ae32e 450 )',
b34a62e5 451 [ [ name => 'root'] ],
bc6ae32e 452 );
453
454 is_deeply(
455 [ $rs->get_column('name')->all ],
456 [ 'root' ],
457 'found root node',
458 );
c0024355 459 }
460
bc6ae32e 461 # combine a connect by with a join
ba12b23f 462 SKIP: {
463 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
464 skip q{Oracle8i doesn't support connect by with join}, 1
465 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
466
bc6ae32e 467 my $rs = $schema->resultset('Artist')->search(
468 {'cds.title' => { -like => '%cd'} },
469 {
470 join => 'cds',
471 start_with => { 'me.name' => 'root' },
e6600283 472 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
bc6ae32e 473 }
474 );
475
476 is_same_sql_bind (
477 $rs->as_query,
478 '(
479 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
480 FROM artist me
481 LEFT JOIN cd cds ON cds.artist = me.artistid
482 WHERE ( cds.title LIKE ? )
483 START WITH me.name = ?
1756ae89 484 CONNECT BY parentid = PRIOR artistid
bc6ae32e 485 )',
486 [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
487 );
488
489 is_deeply(
490 [ $rs->get_column('name')->all ],
491 [ 'grandchild' ],
492 'Connect By with a join result name ok'
493 );
494
495
496 is_same_sql_bind (
497 $rs->count_rs->as_query,
498 '(
499 SELECT COUNT( * )
500 FROM artist me
501 LEFT JOIN cd cds ON cds.artist = me.artistid
502 WHERE ( cds.title LIKE ? )
503 START WITH me.name = ?
1756ae89 504 CONNECT BY parentid = PRIOR artistid
bc6ae32e 505 )',
506 [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
507 );
508
c0024355 509 is( $rs->count, 1, 'Connect By with a join; count ok' );
c0024355 510 }
511
bc6ae32e 512 # combine a connect by with order_by
c0024355 513 {
bc6ae32e 514 my $rs = $schema->resultset('Artist')->search({}, {
b34a62e5 515 start_with => { name => 'root' },
e6600283 516 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
b34a62e5 517 order_by => { -asc => [ 'LEVEL', 'name' ] },
bc6ae32e 518 });
519
520 is_same_sql_bind (
521 $rs->as_query,
522 '(
523 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
ba12b23f 524 FROM artist me
bc6ae32e 525 START WITH name = ?
1756ae89 526 CONNECT BY parentid = PRIOR artistid
b34a62e5 527 ORDER BY LEVEL ASC, name ASC
bc6ae32e 528 )',
b34a62e5 529 [ [ name => 'root' ] ],
bc6ae32e 530 );
531
ba12b23f 532
533 # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
534 # If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
535 # TODO: write extra test and fix order by handling on Oracle 8i
bc6ae32e 536 is_deeply (
ba12b23f 537 [ map { $_->[1] } $rs->cursor->all ],
b34a62e5 538 [ qw/root child1 child2 grandchild greatgrandchild/ ],
ba12b23f 539 'Connect By with a order_by - result name ok (without get_column)'
bc6ae32e 540 );
ba12b23f 541
542 SKIP: {
543 skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
544 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
545 is_deeply (
546 [ $rs->get_column ('name')->all ],
547 [ qw/root child1 child2 grandchild greatgrandchild/ ],
548 'Connect By with a order_by - result name ok (with get_column)'
549 );
550 }
c0024355 551 }
552
bc6ae32e 553
554 # limit a connect by
ba12b23f 555 SKIP: {
556 skip q{Oracle8i doesn't support order by in a subquery}, 1
557 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
558
bc6ae32e 559 my $rs = $schema->resultset('Artist')->search({}, {
b34a62e5 560 start_with => { name => 'root' },
e6600283 561 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
b34a62e5 562 order_by => { -asc => 'name' },
bc6ae32e 563 rows => 2,
564 });
565
566 is_same_sql_bind (
567 $rs->as_query,
d9672fb9 568 '(
9a5a7d7e 569 SELECT artistid, name, rank, charfield, parentid FROM (
d9672fb9 570 SELECT
571 me.artistid,
572 me.name,
573 me.rank,
574 me.charfield,
575 me.parentid
576 FROM artist me
577 START WITH name = ?
578 CONNECT BY parentid = PRIOR artistid
579 ORDER BY name ASC
9a5a7d7e 580 ) me
d9672fb9 581 WHERE ROWNUM <= 2
bc6ae32e 582 )',
b34a62e5 583 [ [ name => 'root' ] ],
bc6ae32e 584 );
585
586 is_deeply (
587 [ $rs->get_column ('name')->all ],
b34a62e5 588 [qw/child1 child2/],
bc6ae32e 589 'LIMIT a Connect By query - correct names'
590 );
591
d815b6a5 592 is_same_sql_bind (
593 $rs->count_rs->as_query,
d9672fb9 594 '(
595 SELECT COUNT( * ) FROM (
596 SELECT artistid
597 FROM (
598 SELECT
599 me.artistid
600 FROM artist me
601 START WITH name = ?
602 CONNECT BY parentid = PRIOR artistid
603 ) me
604 WHERE ROWNUM <= 2
605 ) me
d815b6a5 606 )',
607 [ [ name => 'root' ] ],
608 );
609
610 is( $rs->count, 2, 'Connect By; LIMIT count ok' );
2ba03b16 611 }
612
5c810af7 613 # combine a connect_by with group_by and having
614 {
615 my $rs = $schema->resultset('Artist')->search({}, {
616 select => ['count(rank)'],
617 start_with => { name => 'root' },
e6600283 618 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
5c810af7 619 group_by => ['rank'],
620 having => { 'count(rank)' => { '<', 2 } },
621 });
622
623 is_same_sql_bind (
624 $rs->as_query,
625 '(
626 SELECT count(rank)
627 FROM artist me
628 START WITH name = ?
629 CONNECT BY parentid = PRIOR artistid
630 GROUP BY rank HAVING count(rank) < ?
631 )',
632 [ [ name => 'root' ], [ 'count(rank)' => 2 ] ],
633 );
634
635 is_deeply (
636 [ $rs->get_column ('count(rank)')->all ],
637 [1, 1],
638 'Group By a Connect By query - correct values'
639 );
640 }
641
642
2a7879e2 643 # select the whole cycle tree without nocylce
644 {
645 my $rs = $schema->resultset('Artist')->search({}, {
646 start_with => { name => 'cycle-root' },
e6600283 647 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
2a7879e2 648 });
649 eval { $rs->get_column ('name')->all };
73d6cd33 650 if ( $@ =~ /ORA-01436/ ){ # ORA-01436: CONNECT BY loop in user data
2a7879e2 651 pass "connect by initify loop detection without nocycle";
652 }else{
653 fail "connect by initify loop detection without nocycle, not detected by oracle";
654 }
655 }
656
657 # select the whole cycle tree with nocylce
ba12b23f 658 SKIP: {
659 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
660 skip q{Oracle8i doesn't support connect by nocycle}, 1
661 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
662
2ba03b16 663 my $rs = $schema->resultset('Artist')->search({}, {
2a7879e2 664 start_with => { name => 'cycle-root' },
665 '+select' => [ \ 'CONNECT_BY_ISCYCLE' ],
e6600283 666 connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
2ba03b16 667 });
668
669 is_same_sql_bind (
670 $rs->as_query,
671 '(
2a7879e2 672 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
2ba03b16 673 FROM artist me
674 START WITH name = ?
1756ae89 675 CONNECT BY NOCYCLE parentid = PRIOR artistid
2ba03b16 676 )',
2a7879e2 677 [ [ name => 'cycle-root'] ],
2ba03b16 678 );
679 is_deeply (
680 [ $rs->get_column ('name')->all ],
2a7879e2 681 [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
682 'got artist tree with nocycle (name)',
683 );
684 is_deeply (
685 [ $rs->get_column ('CONNECT_BY_ISCYCLE')->all ],
686 [ qw/1 0 0 0/ ],
687 'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
2ba03b16 688 );
689
690
bc6ae32e 691 is_same_sql_bind (
692 $rs->count_rs->as_query,
2ba03b16 693 '(
694 SELECT COUNT( * )
695 FROM artist me
696 START WITH name = ?
1756ae89 697 CONNECT BY NOCYCLE parentid = PRIOR artistid
bc6ae32e 698 )',
2a7879e2 699 [ [ name => 'cycle-root'] ],
bc6ae32e 700 );
701
2a7879e2 702 is( $rs->count, 4, 'Connect By Nocycle count ok' );
c0024355 703 }
704}
705
df6e3f5c 706my $schema2;
707
708# test sequence detection from a different schema
9d7d2f00 709SKIP: {
606b30c3 710TODO: {
9d7d2f00 711 skip ((join '',
712'Set DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS to a *DIFFERENT* Oracle user',
713' to run the cross-schema autoincrement test.'),
714 1) unless $dsn2 && $user2 && $user2 ne $user;
715
606b30c3 716 # Oracle8i Reference Release 2 (8.1.6)
717 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch294.htm#993
718 # Oracle Database Reference 10g Release 2 (10.2)
719 # http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2107.htm#sthref1297
720 local $TODO = "On Oracle8i all_triggers view is empty, i don't yet know why..."
721 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
722
df6e3f5c 723 $schema2 = DBICTest::Schema->connect($dsn2, $user2, $pass2);
724
9d7d2f00 725 my $schema1_dbh = $schema->storage->dbh;
df6e3f5c 726
9d7d2f00 727 $schema1_dbh->do("GRANT INSERT ON artist TO $user2");
f121db2e 728 $schema1_dbh->do("GRANT SELECT ON artist_pk_seq TO $user2");
df6e3f5c 729
72044892 730 my $rs = $schema2->resultset('ArtistFQN');
df6e3f5c 731
72044892 732 # first test with unquoted (default) sequence name in trigger body
df6e3f5c 733
734 lives_and {
9d7d2f00 735 my $row = $rs->create({ name => 'From Different Schema' });
df6e3f5c 736 ok $row->artistid;
9d7d2f00 737 } 'used autoinc sequence across schemas';
72044892 738
739 # now quote the sequence name
72044892 740 $schema1_dbh->do(qq{
f121db2e 741 CREATE OR REPLACE TRIGGER artist_insert_trg_pk
72044892 742 BEFORE INSERT ON artist
743 FOR EACH ROW
744 BEGIN
745 IF :new.artistid IS NULL THEN
f121db2e 746 SELECT "ARTIST_PK_SEQ".nextval
72044892 747 INTO :new.artistid
748 FROM DUAL;
749 END IF;
750 END;
751 });
752
753 # sequence is cached in the rsrc
754 delete $rs->result_source->column_info('artistid')->{sequence};
755
756 lives_and {
757 my $row = $rs->create({ name => 'From Different Schema With Quoted Sequence' });
758 ok $row->artistid;
759 } 'used quoted autoinc sequence across schemas';
760
761 my $schema_name = uc $user;
762
763 is $rs->result_source->column_info('artistid')->{sequence},
f121db2e 764 qq[${schema_name}."ARTIST_PK_SEQ"],
72044892 765 'quoted sequence name correctly extracted';
606b30c3 766} }
df6e3f5c 767
86cc4156 768done_testing;
769
df6e3f5c 770sub do_creates {
771 my $dbh = shift;
772
773 eval {
ab4f4e4c 774 $dbh->do("DROP SEQUENCE artist_autoinc_seq");
775 $dbh->do("DROP SEQUENCE artist_pk_seq");
df6e3f5c 776 $dbh->do("DROP SEQUENCE cd_seq");
777 $dbh->do("DROP SEQUENCE track_seq");
778 $dbh->do("DROP SEQUENCE pkid1_seq");
779 $dbh->do("DROP SEQUENCE pkid2_seq");
780 $dbh->do("DROP SEQUENCE nonpkid_seq");
781 $dbh->do("DROP TABLE artist");
782 $dbh->do("DROP TABLE sequence_test");
783 $dbh->do("DROP TABLE track");
784 $dbh->do("DROP TABLE cd");
785 };
ab4f4e4c 786 $dbh->do("CREATE SEQUENCE artist_autoinc_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
787 $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
df6e3f5c 788 $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
789 $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
790 $dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
791 $dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
792 $dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
793
ab4f4e4c 794 $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
df6e3f5c 795 $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
796
797 $dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))");
798 $dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
799
800 $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
801 $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
802
803 $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)");
804 $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
805
806 $dbh->do(qq{
ab4f4e4c 807 CREATE OR REPLACE TRIGGER artist_insert_trg_auto
808 BEFORE INSERT ON artist
809 FOR EACH ROW
810 BEGIN
811 IF :new.autoinc_col IS NULL THEN
812 SELECT artist_autoinc_seq.nextval
813 INTO :new.autoinc_col
814 FROM DUAL;
815 END IF;
816 END;
817 });
818 $dbh->do(qq{
819 CREATE OR REPLACE TRIGGER artist_insert_trg_pk
df6e3f5c 820 BEFORE INSERT ON artist
821 FOR EACH ROW
822 BEGIN
823 IF :new.artistid IS NULL THEN
ab4f4e4c 824 SELECT artist_pk_seq.nextval
df6e3f5c 825 INTO :new.artistid
826 FROM DUAL;
827 END IF;
828 END;
829 });
830 $dbh->do(qq{
831 CREATE OR REPLACE TRIGGER cd_insert_trg
832 BEFORE INSERT OR UPDATE ON cd
833 FOR EACH ROW
834 BEGIN
835 IF :new.cdid IS NULL THEN
836 SELECT cd_seq.nextval
837 INTO :new.cdid
838 FROM DUAL;
839 END IF;
840 END;
841 });
842 $dbh->do(qq{
843 CREATE OR REPLACE TRIGGER cd_insert_trg
844 BEFORE INSERT ON cd
845 FOR EACH ROW
846 BEGIN
847 IF :new.cdid IS NULL THEN
848 SELECT cd_seq.nextval
849 INTO :new.cdid
850 FROM DUAL;
851 END IF;
852 END;
853 });
854 $dbh->do(qq{
855 CREATE OR REPLACE TRIGGER track_insert_trg
856 BEFORE INSERT ON track
857 FOR EACH ROW
858 BEGIN
859 IF :new.trackid IS NULL THEN
860 SELECT track_seq.nextval
861 INTO :new.trackid
862 FROM DUAL;
863 END IF;
864 END;
865 });
866}
867
0567538f 868# clean up our mess
3ff5b740 869END {
df6e3f5c 870 for my $dbh (map $_->storage->dbh, grep $_, ($schema, $schema2)) {
871 eval {
ab4f4e4c 872 $dbh->do("DROP SEQUENCE artist_autoinc_seq");
873 $dbh->do("DROP SEQUENCE artist_pk_seq");
df6e3f5c 874 $dbh->do("DROP SEQUENCE cd_seq");
875 $dbh->do("DROP SEQUENCE track_seq");
876 $dbh->do("DROP SEQUENCE pkid1_seq");
877 $dbh->do("DROP SEQUENCE pkid2_seq");
878 $dbh->do("DROP SEQUENCE nonpkid_seq");
879 $dbh->do("DROP TABLE artist");
880 $dbh->do("DROP TABLE sequence_test");
881 $dbh->do("DROP TABLE track");
882 $dbh->do("DROP TABLE cd");
883 $dbh->do("DROP TABLE bindtype_test");
884 };
df6e3f5c 885 }
3ff5b740 886}