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 ],
32 ordered_limit_offset => [
34 SELECT me.id, ? * ?, ?
36 WHERE source != ? AND me.title = ? AND source = ?
37 GROUP BY avg(me.id / ?)
49 [ { sqlt_datatype => 'integer' } => 4 ],
50 [ { sqlt_datatype => 'integer' } => 3 ],
56 ordered_limit_offset => [
58 SELECT me.id, ? * ?, ?
60 WHERE source != ? AND me.title = ? AND source = ?
61 GROUP BY avg(me.id / ?)
72 [ { sqlt_datatype => 'integer' } => 3 ],
73 [ { sqlt_datatype => 'integer' } => 4 ],
79 ordered_limit_offset => [
81 SELECT SKIP ? FIRST ? me.id, ? * ?, ?
83 WHERE source != ? AND me.title = ? AND source = ?
84 GROUP BY avg(me.id / ?)
89 [ { sqlt_datatype => 'integer' } => 3 ],
90 [ { sqlt_datatype => 'integer' } => 4 ],
101 ordered_limit_offset => [
103 SELECT FIRST ? SKIP ? me.id, ? * ?, ?
105 WHERE source != ? AND me.title = ? AND source = ?
106 GROUP BY avg(me.id / ?)
111 [ { sqlt_datatype => 'integer' } => 4 ],
112 [ { sqlt_datatype => 'integer' } => 3 ],
122 RowNumberOver => do {
123 my $unordered_sql = '(
124 SELECT me.id, bar, baz
126 SELECT me.id, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
128 SELECT me.id, ? * ? AS bar, ? AS baz
130 WHERE source != ? AND me.title = ? AND source = ?
131 GROUP BY avg(me.id / ?)
135 WHERE rno__row__index >= ? AND rno__row__index <= ?
139 SELECT me.id, bar, baz
141 SELECT me.id, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index
143 SELECT me.id, ? * ? AS bar, ? AS baz,
144 ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
146 WHERE source != ? AND me.title = ? AND source = ?
147 GROUP BY avg(me.id / ?)
151 WHERE rno__row__index >= ? AND rno__row__index <= ?
155 limit => [$unordered_sql,
161 [ { sqlt_datatype => 'integer' } => 1 ],
162 [ { sqlt_datatype => 'integer' } => 4 ],
165 limit_offset => [$unordered_sql,
171 [ { sqlt_datatype => 'integer' } => 4 ],
172 [ { sqlt_datatype => 'integer' } => 7 ],
175 ordered_limit => [$ordered_sql,
182 [ { sqlt_datatype => 'integer' } => 1 ],
183 [ { sqlt_datatype => 'integer' } => 4 ],
186 ordered_limit_offset => [$ordered_sql,
193 [ { sqlt_datatype => 'integer' } => 4 ],
194 [ { sqlt_datatype => 'integer' } => 7 ],
201 my $limit_sql = sub {
203 SELECT me.id, bar, baz
205 SELECT me.id, ? * ? AS bar, ? AS baz
207 WHERE source != ? AND me.title = ? AND source = ?
208 GROUP BY avg(me.id / ?)
217 limit => [ $limit_sql->(),
223 [ { sqlt_datatype => 'integer' } => 4 ],
228 SELECT me.id, bar, baz
230 SELECT me.id, bar, baz, ROWNUM rownum__index
232 SELECT me.id, ? * ? AS bar, ? AS baz
234 WHERE source != ? AND me.title = ? AND source = ?
235 GROUP BY avg(me.id / ?)
239 WHERE rownum__index BETWEEN ? AND ?
246 [ { sqlt_datatype => 'integer' } => 4 ],
247 [ { sqlt_datatype => 'integer' } => 7 ],
250 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
257 [ { sqlt_datatype => 'integer' } => 4 ],
260 ordered_limit_offset => [
262 SELECT me.id, bar, baz
264 SELECT me.id, bar, baz, ROWNUM rownum__index
266 SELECT me.id, ? * ? AS bar, ? AS baz
268 WHERE source != ? AND me.title = ? AND source = ?
269 GROUP BY avg(me.id / ?)
275 WHERE rownum__index >= ?
283 [ { sqlt_datatype => 'integer' } => 7 ],
284 [ { sqlt_datatype => 'integer' } => 4 ],
294 SELECT me.id, ? * ?, ?
296 WHERE source != ? AND me.title = ? AND source = ?
297 GROUP BY avg(me.id / ?)
299 FETCH FIRST 4 ROWS ONLY
310 SELECT me.id, bar, baz
312 SELECT me.id, ? * ? AS bar, ? AS baz
314 WHERE source != ? AND me.title = ? AND source = ?
315 GROUP BY avg(me.id / ?)
318 FETCH FIRST 7 ROWS ONLY
321 FETCH FIRST 4 ROWS ONLY
332 SELECT me.id, ? * ?, ?
334 WHERE source != ? AND me.title = ? AND source = ?
335 GROUP BY avg(me.id / ?)
338 FETCH FIRST 4 ROWS ONLY
348 ordered_limit_offset => [
350 SELECT me.id, bar, baz
352 SELECT me.id, bar, baz, ORDER__BY__1, ORDER__BY__2
354 SELECT me.id, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
356 WHERE source != ? AND me.title = ? AND source = ?
357 GROUP BY avg(me.id / ?)
360 FETCH FIRST 7 ROWS ONLY
362 ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
363 FETCH FIRST 4 ROWS ONLY
365 ORDER BY ORDER__BY__1, ORDER__BY__2
373 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
381 SELECT TOP 4 me.id, ? * ?, ?
383 WHERE source != ? AND me.title = ? AND source = ?
384 GROUP BY avg(me.id / ?)
396 SELECT TOP 4 me.id, bar, baz
398 SELECT TOP 7 me.id, ? * ? AS bar, ? AS baz
400 WHERE source != ? AND me.title = ? AND source = ?
401 GROUP BY avg(me.id / ?)
416 SELECT TOP 4 me.id, ? * ?, ?
418 WHERE source != ? AND me.title = ? AND source = ?
419 GROUP BY avg(me.id / ?)
431 ordered_limit_offset => [
433 SELECT me.id, bar, baz
435 SELECT TOP 4 me.id, bar, baz, ORDER__BY__1, ORDER__BY__2
437 SELECT TOP 7 me.id, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
439 WHERE source != ? AND me.title = ? AND source = ?
440 GROUP BY avg(me.id / ?)
444 ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
446 ORDER BY ORDER__BY__1, ORDER__BY__2
454 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
459 RowCountOrGenericSubQ => {
463 SELECT me.id, ? * ?, ?
465 WHERE source != ? AND me.title = ? AND source = ?
466 GROUP BY avg(me.id / ?)
480 SELECT me.id, bar, baz
482 SELECT me.id, ? * ? AS bar, ? AS baz
484 WHERE source != ? AND me.title = ? AND source = ?
485 GROUP BY avg( me.id / ? )
490 FROM books rownum__emulation
491 WHERE rownum__emulation.id < me.id
500 [ { sqlt_datatype => 'integer' } => 3 ],
501 [ { sqlt_datatype => 'integer' } => 6 ],
509 SELECT me.id, bar, baz
511 SELECT me.id, ? * ? AS bar, ? AS baz
513 WHERE source != ? AND me.title = ? AND source = ?
514 GROUP BY avg( me.id / ? )
519 FROM books rownum__emulation
520 WHERE rownum__emulation.id < me.id
529 [ { sqlt_datatype => 'integer' } => 4 ],
534 SELECT me.id, bar, baz
536 SELECT me.id, ? * ? AS bar, ? AS baz
538 WHERE source != ? AND me.title = ? AND source = ?
539 GROUP BY avg( me.id / ? )
544 FROM books rownum__emulation
545 WHERE rownum__emulation.id < me.id
554 [ { sqlt_datatype => 'integer' } => 3 ],
555 [ { sqlt_datatype => 'integer' } => 6 ],
561 for my $limtype (sort keys %$tests) {
563 delete $schema->storage->_sql_maker->{_cached_syntax};
564 $schema->storage->_sql_maker->limit_dialect ($limtype);
566 # chained search is necessary to exercise the recursive {where} parser
567 my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
568 columns => { identifier => 'me.id' }, # people actually do that. BLEH!!! :)
570 '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
571 group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
572 having => \[ '?', [ $attr => 31 ] ],
573 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
577 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
580 # only limit, no offset, no order
583 @{$tests->{$limtype}{limit}},
584 "$limtype: Unordered limit with select/group/having",
585 ) if $tests->{$limtype}{limit};
587 # limit + offset, no order
589 $rs->search({}, { offset => 3 })->as_query,
590 @{$tests->{$limtype}{limit_offset}},
591 "$limtype: Unordered limit+offset with select/group/having",
592 ) if $tests->{$limtype}{limit_offset};
594 # order + limit, no offset
595 $rs = $rs->search(undef, {
596 order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
601 @{$tests->{$limtype}{ordered_limit}},
602 "$limtype: Ordered limit with select/group/having",
603 ) if $tests->{$limtype}{ordered_limit};
605 # order + limit + offset
607 $rs->search({}, { offset => 3 })->as_query,
608 @{$tests->{$limtype}{ordered_limit_offset}},
609 "$limtype: Ordered limit+offset with select/group/having",
610 ) if $tests->{$limtype}{ordered_limit_offset};