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