Many improvements of bigint handling across various DBD::SQLite versions
[dbsrgits/DBIx-Class.git] / t / 73oracle_hq.t
CommitLineData
12e05c15 1use strict;
2use warnings;
3
4use Test::Exception;
5use Test::More;
199fbc45 6use DBIx::Class::Optional::Dependencies ();
12e05c15 7use lib qw(t/lib);
8d6b1478 8use DBICTest::RunMode;
12e05c15 9use DBIC::SqlMakerTest;
10
fcb7fcbb 11use DBIx::Class::SQLMaker::LimitDialects;
12my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
13my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
14
994dc91b 15$ENV{NLS_SORT} = "BINARY";
16$ENV{NLS_COMP} = "BINARY";
17$ENV{NLS_LANG} = "AMERICAN";
18
12e05c15 19my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
20
21plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test.'
22 unless ($dsn && $user && $pass);
23
e6983054 24plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('rdbms_oracle')
25 unless DBIx::Class::Optional::Dependencies->req_ok_for ('rdbms_oracle');
26
12e05c15 27use DBICTest::Schema::Artist;
28BEGIN {
29 DBICTest::Schema::Artist->add_column('parentid');
30
31 DBICTest::Schema::Artist->has_many(
32 children => 'DBICTest::Schema::Artist',
33 { 'foreign.parentid' => 'self.artistid' }
34 );
35
36 DBICTest::Schema::Artist->belongs_to(
37 parent => 'DBICTest::Schema::Artist',
38 { 'foreign.artistid' => 'self.parentid' }
39 );
40}
41
8d6b1478 42use DBICTest;
12e05c15 43use DBICTest::Schema;
44
45my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
46
47note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
48
49my $dbh = $schema->storage->dbh;
50do_creates($dbh);
51
52### test hierarchical queries
53{
54 $schema->resultset('Artist')->create ({
55 name => 'root',
56 rank => 1,
57 cds => [],
58 children => [
59 {
60 name => 'child1',
61 rank => 2,
62 children => [
63 {
64 name => 'grandchild',
65 rank => 3,
66 cds => [
67 {
68 title => "grandchilds's cd" ,
69 year => '2008',
70 tracks => [
71 {
72 position => 1,
73 title => 'Track 1 grandchild',
74 }
75 ],
76 }
77 ],
78 children => [
79 {
80 name => 'greatgrandchild',
81 rank => 3,
82 }
83 ],
84 }
85 ],
86 },
87 {
88 name => 'child2',
89 rank => 3,
90 },
91 ],
92 });
93
94 $schema->resultset('Artist')->create({
95 name => 'cycle-root',
96 children => [
97 {
98 name => 'cycle-child1',
99 children => [ { name => 'cycle-grandchild' } ],
100 },
101 {
102 name => 'cycle-child2'
103 },
104 ],
105 });
106
107 $schema->resultset('Artist')->find({ name => 'cycle-root' })
108 ->update({ parentid => { -ident => 'artistid' } });
109
110 # select the whole tree
111 {
112 my $rs = $schema->resultset('Artist')->search({}, {
113 start_with => { name => 'root' },
114 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
115 });
116
117 is_same_sql_bind (
118 $rs->as_query,
119 '(
120 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
121 FROM artist me
122 START WITH name = ?
fcb7fcbb 123 CONNECT BY parentid = PRIOR artistid
12e05c15 124 )',
0e773352 125 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
126 => 'root'] ],
12e05c15 127 );
128 is_deeply (
129 [ $rs->get_column ('name')->all ],
130 [ qw/root child1 grandchild greatgrandchild child2/ ],
131 'got artist tree',
132 );
133
134 is_same_sql_bind (
135 $rs->count_rs->as_query,
136 '(
137 SELECT COUNT( * )
138 FROM artist me
139 START WITH name = ?
fcb7fcbb 140 CONNECT BY parentid = PRIOR artistid
12e05c15 141 )',
0e773352 142 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
143 => 'root'] ],
12e05c15 144 );
145
146 is( $rs->count, 5, 'Connect By count ok' );
147 }
148
149 # use order siblings by statement
150 SKIP: {
151 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
152 skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
153 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
154
155 my $rs = $schema->resultset('Artist')->search({}, {
156 start_with => { name => 'root' },
157 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
158 order_siblings_by => { -desc => 'name' },
159 });
160
161 is_same_sql_bind (
162 $rs->as_query,
163 '(
164 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
165 FROM artist me
166 START WITH name = ?
fcb7fcbb 167 CONNECT BY parentid = PRIOR artistid
12e05c15 168 ORDER SIBLINGS BY name DESC
169 )',
0e773352 170 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
171 => 'root'] ],
12e05c15 172 );
173
174 is_deeply (
175 [ $rs->get_column ('name')->all ],
176 [ qw/root child2 child1 grandchild greatgrandchild/ ],
177 'Order Siblings By ok',
178 );
179 }
180
181 # get the root node
182 {
183 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
184 start_with => { name => 'root' },
185 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
186 });
187
188 is_same_sql_bind (
189 $rs->as_query,
190 '(
191 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
192 FROM artist me
193 WHERE ( parentid IS NULL )
194 START WITH name = ?
fcb7fcbb 195 CONNECT BY parentid = PRIOR artistid
12e05c15 196 )',
0e773352 197 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
198 => 'root'] ],
12e05c15 199 );
200
201 is_deeply(
202 [ $rs->get_column('name')->all ],
203 [ 'root' ],
204 'found root node',
205 );
206 }
207
208 # combine a connect by with a join
209 SKIP: {
210 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
211 skip q{Oracle8i doesn't support connect by with join}, 1
212 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
213
214 my $rs = $schema->resultset('Artist')->search(
215 {'cds.title' => { -like => '%cd'} },
216 {
217 join => 'cds',
218 start_with => { 'me.name' => 'root' },
219 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
220 }
221 );
222
223 is_same_sql_bind (
224 $rs->as_query,
225 '(
226 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
227 FROM artist me
228 LEFT JOIN cd cds ON cds.artist = me.artistid
229 WHERE ( cds.title LIKE ? )
230 START WITH me.name = ?
fcb7fcbb 231 CONNECT BY parentid = PRIOR artistid
12e05c15 232 )',
0e773352 233 [
234 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
235 => '%cd'],
236 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
237 => 'root'],
238 ],
12e05c15 239 );
240
241 is_deeply(
242 [ $rs->get_column('name')->all ],
243 [ 'grandchild' ],
244 'Connect By with a join result name ok'
245 );
246
247 is_same_sql_bind (
248 $rs->count_rs->as_query,
249 '(
250 SELECT COUNT( * )
251 FROM artist me
252 LEFT JOIN cd cds ON cds.artist = me.artistid
253 WHERE ( cds.title LIKE ? )
254 START WITH me.name = ?
fcb7fcbb 255 CONNECT BY parentid = PRIOR artistid
12e05c15 256 )',
0e773352 257 [
258 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
259 => '%cd'],
260 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
261 => 'root'],
262 ],
12e05c15 263 );
264
265 is( $rs->count, 1, 'Connect By with a join; count ok' );
266 }
267
268 # combine a connect by with order_by
269 {
270 my $rs = $schema->resultset('Artist')->search({}, {
271 start_with => { name => 'root' },
272 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
273 order_by => { -asc => [ 'LEVEL', 'name' ] },
274 });
275
276 is_same_sql_bind (
277 $rs->as_query,
278 '(
279 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
280 FROM artist me
281 START WITH name = ?
fcb7fcbb 282 CONNECT BY parentid = PRIOR artistid
12e05c15 283 ORDER BY LEVEL ASC, name ASC
284 )',
0e773352 285 [
286 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
287 => 'root'],
288 ],
12e05c15 289 );
290
291
292 # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
293 # If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
294 # TODO: write extra test and fix order by handling on Oracle 8i
295 is_deeply (
296 [ map { $_->[1] } $rs->cursor->all ],
297 [ qw/root child1 child2 grandchild greatgrandchild/ ],
298 'Connect By with a order_by - result name ok (without get_column)'
299 );
300
301 SKIP: {
302 skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
303 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
304 is_deeply (
305 [ $rs->get_column ('name')->all ],
306 [ qw/root child1 child2 grandchild greatgrandchild/ ],
307 'Connect By with a order_by - result name ok (with get_column)'
308 );
309 }
310 }
311
312
313 # limit a connect by
314 SKIP: {
315 skip q{Oracle8i doesn't support order by in a subquery}, 1
316 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
317
318 my $rs = $schema->resultset('Artist')->search({}, {
319 start_with => { name => 'root' },
320 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
6a6394f1 321 order_by => [ { -asc => 'name' }, { -desc => 'artistid' } ],
12e05c15 322 rows => 2,
323 });
324
325 is_same_sql_bind (
326 $rs->as_query,
327 '(
90ed89cb 328 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
12e05c15 329 FROM (
330 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
331 FROM artist me
332 START WITH name = ?
333 CONNECT BY parentid = PRIOR artistid
6a6394f1 334 ORDER BY name ASC, artistid DESC
12e05c15 335 ) me
fcb7fcbb 336 WHERE ROWNUM <= ?
12e05c15 337 )',
0e773352 338 [
339 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
fcb7fcbb 340 => 'root'], [ $ROWS => 2 ],
0e773352 341 ],
12e05c15 342 );
343
344 is_deeply (
345 [ $rs->get_column ('name')->all ],
346 [qw/child1 child2/],
347 'LIMIT a Connect By query - correct names'
348 );
349
350 is_same_sql_bind (
351 $rs->count_rs->as_query,
352 '(
353 SELECT COUNT( * )
354 FROM (
90ed89cb 355 SELECT me.artistid
12e05c15 356 FROM (
cccd1876 357 SELECT me.artistid
358 FROM artist me
359 START WITH name = ?
360 CONNECT BY parentid = PRIOR artistid
12e05c15 361 ) me
cccd1876 362 WHERE ROWNUM <= ?
12e05c15 363 ) me
364 )',
0e773352 365 [
366 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
6a6394f1 367 => 'root'],
cccd1876 368 [ $ROWS => 2 ],
0e773352 369 ],
12e05c15 370 );
371
372 is( $rs->count, 2, 'Connect By; LIMIT count ok' );
373 }
374
375 # combine a connect_by with group_by and having
55d02972 376 # add some bindvals to make sure things still work
12e05c15 377 {
378 my $rs = $schema->resultset('Artist')->search({}, {
55d02972 379 select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
380 as => 'cnt',
12e05c15 381 start_with => { name => 'root' },
382 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
55d02972 383 group_by => \[ 'rank + ? ', [ __gbind => 1] ],
12e05c15 384 having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
385 });
386
387 is_same_sql_bind (
388 $rs->as_query,
389 '(
55d02972 390 SELECT COUNT(rank) + ?
12e05c15 391 FROM artist me
392 START WITH name = ?
393 CONNECT BY parentid = PRIOR artistid
55d02972 394 GROUP BY( rank + ? ) HAVING count(rank) < ?
12e05c15 395 )',
55d02972 396 [
0e773352 397 [ { dbic_colname => '__cbind' }
398 => 3 ],
399 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
400 => 'root'],
401 [ { dbic_colname => '__gbind' }
402 => 1 ],
403 [ { dbic_colname => 'cnt' }
404 => 2 ],
55d02972 405 ],
12e05c15 406 );
407
408 is_deeply (
409 [ $rs->get_column ('cnt')->all ],
55d02972 410 [4, 4],
12e05c15 411 'Group By a Connect By query - correct values'
412 );
413 }
414
415 # select the whole cycle tree without nocylce
416 {
417 my $rs = $schema->resultset('Artist')->search({}, {
418 start_with => { name => 'cycle-root' },
419 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
420 });
421
422 # ORA-01436: CONNECT BY loop in user data
423 throws_ok { $rs->get_column ('name')->all } qr/ORA-01436/,
424 "connect by initify loop detection without nocycle";
425 }
426
427 # select the whole cycle tree with nocylce
428 SKIP: {
429 # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
430 skip q{Oracle8i doesn't support connect by nocycle}, 1
431 if $schema->storage->_server_info->{normalized_dbms_version} < 9;
432
433 my $rs = $schema->resultset('Artist')->search({}, {
434 start_with => { name => 'cycle-root' },
435 '+select' => \ 'CONNECT_BY_ISCYCLE',
436 '+as' => [ 'connector' ],
437 connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
438 });
439
440 is_same_sql_bind (
441 $rs->as_query,
442 '(
443 SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
444 FROM artist me
445 START WITH name = ?
fcb7fcbb 446 CONNECT BY NOCYCLE parentid = PRIOR artistid
12e05c15 447 )',
0e773352 448 [
449 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
450 => 'cycle-root'],
451 ],
12e05c15 452 );
453 is_deeply (
454 [ $rs->get_column ('name')->all ],
455 [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
456 'got artist tree with nocycle (name)',
457 );
458 is_deeply (
459 [ $rs->get_column ('connector')->all ],
460 [ qw/1 0 0 0/ ],
461 'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
462 );
463
464 is_same_sql_bind (
465 $rs->count_rs->as_query,
466 '(
467 SELECT COUNT( * )
468 FROM artist me
469 START WITH name = ?
fcb7fcbb 470 CONNECT BY NOCYCLE parentid = PRIOR artistid
12e05c15 471 )',
0e773352 472 [
473 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
474 => 'cycle-root'],
475 ],
12e05c15 476 );
477
478 is( $rs->count, 4, 'Connect By Nocycle count ok' );
479 }
480}
481
482done_testing;
483
484sub do_creates {
485 my $dbh = shift;
486
487 eval {
488 $dbh->do("DROP SEQUENCE artist_autoinc_seq");
489 $dbh->do("DROP SEQUENCE artist_pk_seq");
490 $dbh->do("DROP SEQUENCE cd_seq");
491 $dbh->do("DROP SEQUENCE track_seq");
492 $dbh->do("DROP TABLE artist");
493 $dbh->do("DROP TABLE track");
494 $dbh->do("DROP TABLE cd");
495 };
496
497 $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
498 $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
499 $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
500
501 $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
502 $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
503
504 $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
505 $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
506
507 $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)");
508 $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
509
510 $dbh->do(qq{
511 CREATE OR REPLACE TRIGGER artist_insert_trg_pk
512 BEFORE INSERT ON artist
513 FOR EACH ROW
514 BEGIN
515 IF :new.artistid IS NULL THEN
516 SELECT artist_pk_seq.nextval
517 INTO :new.artistid
518 FROM DUAL;
519 END IF;
520 END;
521 });
522 $dbh->do(qq{
523 CREATE OR REPLACE TRIGGER cd_insert_trg
524 BEFORE INSERT OR UPDATE ON cd
525 FOR EACH ROW
526
527 DECLARE
528 tmpVar NUMBER;
529
530 BEGIN
531 tmpVar := 0;
532
533 IF :new.cdid IS NULL THEN
534 SELECT cd_seq.nextval
535 INTO tmpVar
536 FROM dual;
537
538 :new.cdid := tmpVar;
539 END IF;
540 END;
541 });
542 $dbh->do(qq{
543 CREATE OR REPLACE TRIGGER track_insert_trg
544 BEFORE INSERT ON track
545 FOR EACH ROW
546 BEGIN
547 IF :new.trackid IS NULL THEN
548 SELECT track_seq.nextval
549 INTO :new.trackid
550 FROM DUAL;
551 END IF;
552 END;
553 });
554}
555
556# clean up our mess
557END {
65d35121 558 if ($schema and my $dbh = $schema->storage->dbh) {
559 eval { $dbh->do($_) } for (
560 'DROP SEQUENCE artist_pk_seq',
561 'DROP SEQUENCE cd_seq',
562 'DROP SEQUENCE track_seq',
563 'DROP TABLE artist',
564 'DROP TABLE track',
565 'DROP TABLE cd',
566 );
12e05c15 567 };
65d35121 568 undef $schema;
12e05c15 569}