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