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