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 | |
bc6ae32e |
314 | |
315 | ### test hierarchical queries |
c0024355 |
316 | if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { |
317 | my $source = $schema->source('Artist'); |
318 | |
319 | $source->add_column( 'parentid' ); |
320 | |
321 | $source->add_relationship('children', 'DBICTest::Schema::Artist', |
322 | { 'foreign.parentid' => 'self.artistid' }, |
323 | { |
324 | accessor => 'multi', |
325 | join_type => 'LEFT', |
326 | cascade_delete => 1, |
327 | cascade_copy => 1, |
328 | } ); |
329 | $source->add_relationship('parent', 'DBICTest::Schema::Artist', |
330 | { 'foreign.artistid' => 'self.parentid' }, |
331 | { accessor => 'single' } ); |
332 | DBICTest::Schema::Artist->add_column( 'parentid' ); |
333 | DBICTest::Schema::Artist->has_many( |
334 | children => 'DBICTest::Schema::Artist', |
335 | { 'foreign.parentid' => 'self.artistid' } |
336 | ); |
337 | DBICTest::Schema::Artist->belongs_to( |
338 | parent => 'DBICTest::Schema::Artist', |
339 | { 'foreign.artistid' => 'self.parentid' } |
340 | ); |
341 | |
342 | $schema->resultset('Artist')->create ({ |
343 | name => 'root', |
344 | cds => [], |
345 | children => [ |
346 | { |
347 | name => 'child1', |
348 | children => [ |
349 | { |
350 | name => 'grandchild', |
351 | cds => [ |
352 | { |
353 | title => "grandchilds's cd" , |
354 | year => '2008', |
355 | tracks => [ |
356 | { |
357 | position => 1, |
358 | title => 'Track 1 grandchild', |
359 | } |
360 | ], |
361 | } |
362 | ], |
363 | children => [ |
364 | { |
365 | name => 'greatgrandchild', |
366 | } |
367 | ], |
368 | } |
369 | ], |
370 | }, |
371 | { |
372 | name => 'child2', |
373 | }, |
374 | ], |
375 | }); |
376 | |
bc6ae32e |
377 | # select the whole tree |
c0024355 |
378 | { |
bc6ae32e |
379 | my $rs = $schema->resultset('Artist')->search({}, { |
380 | start_with => { name => 'root' }, |
381 | connect_by => { parentid => { -prior => \ 'artistid' } }, |
382 | }); |
383 | |
384 | is_same_sql_bind ( |
385 | $rs->as_query, |
386 | '( |
387 | SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid |
388 | FROM artist me |
389 | START WITH name = ? |
390 | CONNECT BY parentid = prior artistid |
391 | )', |
392 | [ [ name => 'root'] ], |
393 | ); |
394 | is_deeply ( |
395 | [ $rs->get_column ('name')->all ], |
396 | [ qw/root child1 grandchild greatgrandchild child2/ ], |
397 | 'got artist tree', |
398 | ); |
399 | |
400 | |
401 | is_same_sql_bind ( |
402 | $rs->count_rs->as_query, |
403 | '( |
404 | SELECT COUNT( * ) |
405 | FROM artist me |
406 | START WITH name = ? |
407 | CONNECT BY parentid = prior artistid |
408 | )', |
409 | [ [ name => 'root'] ], |
410 | ); |
411 | |
c0024355 |
412 | is( $rs->count, 5, 'Connect By count ok' ); |
c0024355 |
413 | } |
414 | |
bc6ae32e |
415 | # use order siblings by statement |
c0024355 |
416 | { |
bc6ae32e |
417 | my $rs = $schema->resultset('Artist')->search({}, { |
418 | start_with => { name => 'root' }, |
419 | connect_by => { parentid => { -prior => \ 'artistid' } }, |
420 | order_siblings_by => { -desc => 'name' }, |
421 | }); |
422 | |
423 | is_same_sql_bind ( |
424 | $rs->as_query, |
425 | '( |
426 | SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid |
427 | FROM artist me |
428 | START WITH name = ? |
429 | CONNECT BY parentid = PRIOR( artistid ) |
430 | ORDER SIBLINGS BY name DESC |
431 | )', |
432 | [ [ name => 'root'] ], |
433 | ); |
434 | |
435 | is_deeply ( |
436 | [ $rs->get_column ('name')->all ], |
437 | [ qw/root child2 child1 grandchild greatgrandchild/ ], |
438 | 'Order Siblings By ok', |
439 | ); |
c0024355 |
440 | } |
441 | |
bc6ae32e |
442 | # get the root node |
c0024355 |
443 | { |
bc6ae32e |
444 | my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { |
445 | start_with => { name => 'greatgrandchild' }, |
446 | connect_by => { artistid => { -prior => \ 'parentid' } }, |
447 | }); |
448 | |
449 | is_same_sql_bind ( |
450 | $rs->as_query, |
451 | '( |
452 | SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid |
453 | FROM artist me |
454 | WHERE ( parentid IS NULL ) |
455 | START WITH name = ? |
456 | CONNECT BY artistid = PRIOR( parentid ) |
457 | )', |
458 | [ [ name => 'greatgrandchild'] ], |
459 | ); |
460 | |
461 | is_deeply( |
462 | [ $rs->get_column('name')->all ], |
463 | [ 'root' ], |
464 | 'found root node', |
465 | ); |
c0024355 |
466 | } |
467 | |
bc6ae32e |
468 | # combine a connect by with a join |
c0024355 |
469 | { |
bc6ae32e |
470 | my $rs = $schema->resultset('Artist')->search( |
471 | {'cds.title' => { -like => '%cd'} }, |
472 | { |
473 | join => 'cds', |
474 | start_with => { 'me.name' => 'root' }, |
475 | connect_by => { parentid => { -prior => \ 'artistid' } }, |
476 | } |
477 | ); |
478 | |
479 | is_same_sql_bind ( |
480 | $rs->as_query, |
481 | '( |
482 | SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid |
483 | FROM artist me |
484 | LEFT JOIN cd cds ON cds.artist = me.artistid |
485 | WHERE ( cds.title LIKE ? ) |
486 | START WITH me.name = ? |
487 | CONNECT BY parentid = PRIOR( artistid ) |
488 | )', |
489 | [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], |
490 | ); |
491 | |
492 | is_deeply( |
493 | [ $rs->get_column('name')->all ], |
494 | [ 'grandchild' ], |
495 | 'Connect By with a join result name ok' |
496 | ); |
497 | |
498 | |
499 | is_same_sql_bind ( |
500 | $rs->count_rs->as_query, |
501 | '( |
502 | SELECT COUNT( * ) |
503 | FROM artist me |
504 | LEFT JOIN cd cds ON cds.artist = me.artistid |
505 | WHERE ( cds.title LIKE ? ) |
506 | START WITH me.name = ? |
507 | CONNECT BY parentid = prior artistid |
508 | )', |
509 | [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ], |
510 | ); |
511 | |
c0024355 |
512 | is( $rs->count, 1, 'Connect By with a join; count ok' ); |
c0024355 |
513 | } |
514 | |
bc6ae32e |
515 | # combine a connect by with order_by |
c0024355 |
516 | { |
bc6ae32e |
517 | my $rs = $schema->resultset('Artist')->search({}, { |
518 | start_with => { name => 'greatgrandchild' }, |
519 | connect_by => { artistid => { -prior => \ 'parentid' } }, |
520 | order_by => { -asc => 'name' }, |
521 | }); |
522 | |
523 | is_same_sql_bind ( |
524 | $rs->as_query, |
525 | '( |
526 | SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid |
527 | FROM artist me |
528 | START WITH name = ? |
529 | CONNECT BY artistid = PRIOR( parentid ) |
530 | ORDER BY name ASC |
531 | )', |
532 | [ [ name => 'greatgrandchild' ] ], |
533 | ); |
534 | |
535 | is_deeply ( |
536 | [ $rs->get_column ('name')->all ], |
537 | [ qw/child1 grandchild greatgrandchild root/ ], |
538 | 'Connect By with a order_by - result name ok' |
539 | ); |
c0024355 |
540 | } |
541 | |
bc6ae32e |
542 | |
543 | # limit a connect by |
c0024355 |
544 | { |
bc6ae32e |
545 | my $rs = $schema->resultset('Artist')->search({}, { |
546 | start_with => { name => 'greatgrandchild' }, |
547 | connect_by => { artistid => { -prior => \ 'parentid' } }, |
548 | order_by => { -desc => 'name' }, |
549 | rows => 2, |
550 | }); |
551 | |
552 | is_same_sql_bind ( |
553 | $rs->as_query, |
554 | '( |
555 | need to fill in correct sql |
556 | )', |
557 | [], |
558 | ); |
559 | |
560 | is_deeply ( |
561 | [ $rs->get_column ('name')->all ], |
562 | [ qw/grandchild child1/ ], |
563 | 'LIMIT a Connect By query - correct names' |
564 | ); |
565 | |
566 | |
567 | is_same_sql_bind ( |
568 | $rs->count_rs->as_query, |
569 | '( |
570 | need to fill in correct sql |
571 | )', |
572 | [], |
573 | ); |
574 | |
575 | is( $rs->count, 2, 'Connect By; LIMIT count ok' ); |
c0024355 |
576 | } |
577 | } |
578 | |
86cc4156 |
579 | done_testing; |
580 | |
0567538f |
581 | # clean up our mess |
3ff5b740 |
582 | END { |
fe0d48d3 |
583 | if($schema && ($dbh = $schema->storage->dbh)) { |
3ff5b740 |
584 | $dbh->do("DROP SEQUENCE artist_seq"); |
e6dd7b42 |
585 | $dbh->do("DROP SEQUENCE cd_seq"); |
c0024355 |
586 | $dbh->do("DROP SEQUENCE track_seq"); |
39b8d119 |
587 | $dbh->do("DROP SEQUENCE pkid1_seq"); |
588 | $dbh->do("DROP SEQUENCE pkid2_seq"); |
589 | $dbh->do("DROP SEQUENCE nonpkid_seq"); |
3ff5b740 |
590 | $dbh->do("DROP TABLE artist"); |
39b8d119 |
591 | $dbh->do("DROP TABLE sequence_test"); |
3ff5b740 |
592 | $dbh->do("DROP TABLE track"); |
b7b18f32 |
593 | $dbh->do("DROP TABLE cd"); |
5db2758d |
594 | $dbh->do("DROP TABLE bindtype_test"); |
3ff5b740 |
595 | } |
596 | } |
0567538f |
597 | |