7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema;
13 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
14 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
15 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
18 [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
27 [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
33 ordered_limit_offset => [
35 SELECT me.id, owner.id, owner.name, ? * ?, ?
38 ON owner.id = me.owner
39 WHERE source != ? AND me.title = ? AND source = ?
40 GROUP BY avg(me.id / ?)
52 [ { sqlt_datatype => 'integer' } => 4 ],
53 [ { sqlt_datatype => 'integer' } => 3 ],
59 ordered_limit_offset => [
61 SELECT me.id, owner.id, owner.name, ? * ?, ?
64 ON owner.id = me.owner
65 WHERE source != ? AND me.title = ? AND source = ?
66 GROUP BY avg(me.id / ?)
77 [ { sqlt_datatype => 'integer' } => 3 ],
78 [ { sqlt_datatype => 'integer' } => 4 ],
84 ordered_limit_offset => [
86 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
89 ON owner.id = me.owner
90 WHERE source != ? AND me.title = ? AND source = ?
91 GROUP BY avg(me.id / ?)
96 [ { sqlt_datatype => 'integer' } => 3 ],
97 [ { sqlt_datatype => 'integer' } => 4 ],
108 ordered_limit_offset => [
110 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
113 ON owner.id = me.owner
114 WHERE source != ? AND me.title = ? AND source = ?
115 GROUP BY avg(me.id / ?)
120 [ { sqlt_datatype => 'integer' } => 4 ],
121 [ { sqlt_datatype => 'integer' } => 3 ],
131 RowNumberOver => do {
132 my $unordered_sql = '(
133 SELECT me.id, owner__id, owner__name, bar, baz
135 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
137 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
140 ON owner.id = me.owner
141 WHERE source != ? AND me.title = ? AND source = ?
142 GROUP BY avg(me.id / ?)
146 WHERE rno__row__index >= ? AND rno__row__index <= ?
150 SELECT me.id, owner__id, owner__name, bar, baz
152 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index
154 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
155 ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
158 ON owner.id = me.owner
159 WHERE source != ? AND me.title = ? AND source = ?
160 GROUP BY avg(me.id / ?)
164 WHERE rno__row__index >= ? AND rno__row__index <= ?
168 limit => [$unordered_sql,
174 [ { sqlt_datatype => 'integer' } => 1 ],
175 [ { sqlt_datatype => 'integer' } => 4 ],
178 limit_offset => [$unordered_sql,
184 [ { sqlt_datatype => 'integer' } => 4 ],
185 [ { sqlt_datatype => 'integer' } => 7 ],
188 ordered_limit => [$ordered_sql,
195 [ { sqlt_datatype => 'integer' } => 1 ],
196 [ { sqlt_datatype => 'integer' } => 4 ],
199 ordered_limit_offset => [$ordered_sql,
206 [ { sqlt_datatype => 'integer' } => 4 ],
207 [ { sqlt_datatype => 'integer' } => 7 ],
214 my $limit_sql = sub {
216 SELECT me.id, owner__id, owner__name, bar, baz
218 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
221 ON owner.id = me.owner
222 WHERE source != ? AND me.title = ? AND source = ?
223 GROUP BY avg(me.id / ?)
232 limit => [ $limit_sql->(),
238 [ { sqlt_datatype => 'integer' } => 4 ],
243 SELECT me.id, owner__id, owner__name, bar, baz
245 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
247 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
250 ON owner.id = me.owner
251 WHERE source != ? AND me.title = ? AND source = ?
252 GROUP BY avg(me.id / ?)
256 WHERE rownum__index BETWEEN ? AND ?
263 [ { sqlt_datatype => 'integer' } => 4 ],
264 [ { sqlt_datatype => 'integer' } => 7 ],
267 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
274 [ { sqlt_datatype => 'integer' } => 4 ],
277 ordered_limit_offset => [
279 SELECT me.id, owner__id, owner__name, bar, baz
281 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
283 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
286 ON owner.id = me.owner
287 WHERE source != ? AND me.title = ? AND source = ?
288 GROUP BY avg(me.id / ?)
294 WHERE rownum__index >= ?
302 [ { sqlt_datatype => 'integer' } => 7 ],
303 [ { sqlt_datatype => 'integer' } => 4 ],
312 SELECT me.id, owner.id, owner.name, ? * ?, ?
315 ON owner.id = me.owner
316 WHERE source != ? AND me.title = ? AND source = ?
317 GROUP BY avg(me.id / ?)
319 FETCH FIRST 4 ROWS ONLY
330 SELECT me.id, owner__id, owner__name, bar, baz
332 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
335 ON owner.id = me.owner
336 WHERE source != ? AND me.title = ? AND source = ?
337 GROUP BY avg(me.id / ?)
340 FETCH FIRST 7 ROWS ONLY
343 FETCH FIRST 4 ROWS ONLY
354 SELECT me.id, owner.id, owner.name, ? * ?, ?
357 ON owner.id = me.owner
358 WHERE source != ? AND me.title = ? AND source = ?
359 GROUP BY avg(me.id / ?)
362 FETCH FIRST 4 ROWS ONLY
372 ordered_limit_offset => [
374 SELECT me.id, owner__id, owner__name, bar, baz
376 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
378 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
381 ON owner.id = me.owner
382 WHERE source != ? AND me.title = ? AND source = ?
383 GROUP BY avg(me.id / ?)
386 FETCH FIRST 7 ROWS ONLY
388 ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
389 FETCH FIRST 4 ROWS ONLY
391 ORDER BY ORDER__BY__1, ORDER__BY__2
399 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
407 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
410 ON owner.id = me.owner
411 WHERE source != ? AND me.title = ? AND source = ?
412 GROUP BY avg(me.id / ?)
424 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
426 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
429 ON owner.id = me.owner
430 WHERE source != ? AND me.title = ? AND source = ?
431 GROUP BY avg(me.id / ?)
446 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
449 ON owner.id = me.owner
450 WHERE source != ? AND me.title = ? AND source = ?
451 GROUP BY avg(me.id / ?)
463 ordered_limit_offset => [
465 SELECT me.id, owner__id, owner__name, bar, baz
467 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
469 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
472 ON owner.id = me.owner
473 WHERE source != ? AND me.title = ? AND source = ?
474 GROUP BY avg(me.id / ?)
478 ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
480 ORDER BY ORDER__BY__1, ORDER__BY__2
488 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
493 RowCountOrGenericSubQ => {
497 SELECT me.id, owner.id, owner.name, ? * ?, ?
500 ON owner.id = me.owner
501 WHERE source != ? AND me.title = ? AND source = ?
502 GROUP BY avg(me.id / ?)
516 SELECT me.id, owner__id, owner__name, bar, baz
518 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
521 ON owner.id = me.owner
522 WHERE source != ? AND me.title = ? AND source = ?
523 GROUP BY avg( me.id / ? )
528 FROM books rownum__emulation
529 WHERE rownum__emulation.id < me.id
538 [ { sqlt_datatype => 'integer' } => 3 ],
539 [ { sqlt_datatype => 'integer' } => 6 ],
547 SELECT me.id, owner__id, owner__name, bar, baz
549 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
552 ON owner.id = me.owner
553 WHERE source != ? AND me.title = ? AND source = ?
554 GROUP BY avg( me.id / ? )
559 FROM books rownum__emulation
560 WHERE rownum__emulation.id < me.id
569 [ { sqlt_datatype => 'integer' } => 4 ],
574 SELECT me.id, owner__id, owner__name, bar, baz
576 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
579 ON owner.id = me.owner
580 WHERE source != ? AND me.title = ? AND source = ?
581 GROUP BY avg( me.id / ? )
586 FROM books rownum__emulation
587 WHERE rownum__emulation.id < me.id
596 [ { sqlt_datatype => 'integer' } => 3 ],
597 [ { sqlt_datatype => 'integer' } => 6 ],
603 for my $limtype (sort keys %$tests) {
605 Test::Builder->new->is_passing or exit;
607 delete $schema->storage->_sql_maker->{_cached_syntax};
608 $schema->storage->_sql_maker->limit_dialect ($limtype);
610 # chained search is necessary to exercise the recursive {where} parser
611 my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
612 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
613 join => 'owner', # single-rel manual prefetch
615 '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
616 group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
617 having => \[ '?', [ $attr => 31 ] ],
618 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
622 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
625 # only limit, no offset, no order
628 @{$tests->{$limtype}{limit}},
629 "$limtype: Unordered limit with select/group/having",
630 ) if $tests->{$limtype}{limit};
632 # limit + offset, no order
634 $rs->search({}, { offset => 3 })->as_query,
635 @{$tests->{$limtype}{limit_offset}},
636 "$limtype: Unordered limit+offset with select/group/having",
637 ) if $tests->{$limtype}{limit_offset};
639 # order + limit, no offset
640 $rs = $rs->search(undef, {
641 order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
646 @{$tests->{$limtype}{ordered_limit}},
647 "$limtype: Ordered limit with select/group/having",
648 ) if $tests->{$limtype}{ordered_limit};
650 # order + limit + offset
652 $rs->search({}, { offset => 3 })->as_query,
653 @{$tests->{$limtype}{ordered_limit_offset}},
654 "$limtype: Ordered limit+offset with select/group/having",
655 ) if $tests->{$limtype}{ordered_limit_offset};