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