This code belogs in Storage::DBI
[dbsrgits/DBIx-Class-Historic.git] / t / 42toplimit.t
CommitLineData
65c2b042 1use strict;
2use warnings;
3
4use Test::More;
65c2b042 5use lib qw(t/lib);
e606d0ce 6use DBICTest;
65c2b042 7use DBIC::SqlMakerTest;
ed0648ee 8
e606d0ce 9my $schema = DBICTest->init_schema;
65c2b042 10
e606d0ce 11# Trick the sqlite DB to use Top limit emulation
8f6dbee9 12# We could test all of this via $sq->$op directly,
ed0648ee 13# but some conditions need a $rsrc
e606d0ce 14delete $schema->storage->_sql_maker->{_cached_syntax};
15$schema->storage->_sql_maker->limit_dialect ('Top');
16
07dc2055 17my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 1, offset => 3 });
65c2b042 18
83dee2e9 19sub default_test_order {
20 my $order_by = shift;
21 is_same_sql_bind(
22 $rs->search ({}, {order_by => $order_by})->as_query,
23 "(SELECT
24 TOP 1 me__id, source, owner, title, price, owner__id, name FROM
25 (SELECT
26 TOP 4 me.id AS me__id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name
27 FROM books me
28 JOIN owners owner ON
29 owner.id = me.owner
30 WHERE ( source = ? )
31 ORDER BY me__id ASC
32 ) me ORDER BY me__id DESC
33 )",
34 [ [ source => 'Library' ] ],
35 );
36}
37
65c2b042 38sub test_order {
39 my $args = shift;
65c2b042 40
e606d0ce 41 my $req_order = $args->{order_req}
42 ? "ORDER BY $args->{order_req}"
43 : ''
44 ;
45
46 is_same_sql_bind(
47 $rs->search ({}, {order_by => $args->{order_by}})->as_query,
83dee2e9 48 "(SELECT
49 me__id, source, owner, title, price, owner__id, name FROM
50 (SELECT
51 TOP 1 me__id, source, owner, title, price, owner__id, name FROM
ed0648ee 52 (SELECT
53 TOP 4 me.id AS me__id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name FROM
54 books me
55 JOIN owners owner ON owner.id = me.owner
56 WHERE ( source = ? )
57 ORDER BY $args->{order_inner}
58 ) me ORDER BY $args->{order_outer}
83dee2e9 59 ) me $req_order
e606d0ce 60 )",
07dc2055 61 [ [ source => 'Library' ] ],
65c2b042 62 );
63}
64
e606d0ce 65my @tests = (
66 {
83dee2e9 67 order_by => \'foo DESC',
e606d0ce 68 order_req => 'foo DESC',
69 order_inner => 'foo DESC',
83dee2e9 70 order_outer => 'foo ASC'
e606d0ce 71 },
72 {
73 order_by => { -asc => 'foo' },
74 order_req => 'foo ASC',
75 order_inner => 'foo ASC',
76 order_outer => 'foo DESC',
77 },
78 {
79 order_by => 'foo',
80 order_req => 'foo',
81 order_inner => 'foo ASC',
82 order_outer => 'foo DESC',
83 },
84 {
85 order_by => [ qw{ foo bar} ],
86 order_req => 'foo, bar',
87 order_inner => 'foo ASC,bar ASC',
88 order_outer => 'foo DESC, bar DESC',
89 },
90 {
91 order_by => { -desc => 'foo' },
92 order_req => 'foo DESC',
93 order_inner => 'foo DESC',
94 order_outer => 'foo ASC',
95 },
96 {
97 order_by => ['foo', { -desc => 'bar' } ],
98 order_req => 'foo, bar DESC',
99 order_inner => 'foo ASC, bar DESC',
100 order_outer => 'foo DESC, bar ASC',
101 },
102 {
103 order_by => { -asc => [qw{ foo bar }] },
104 order_req => 'foo ASC, bar ASC',
105 order_inner => 'foo ASC, bar ASC',
106 order_outer => 'foo DESC, bar DESC',
107 },
108 {
109 order_by => [
110 { -asc => 'foo' },
111 { -desc => [qw{bar}] },
112 { -asc => [qw{hello sensors}]},
113 ],
114 order_req => 'foo ASC, bar DESC, hello ASC, sensors ASC',
115 order_inner => 'foo ASC, bar DESC, hello ASC, sensors ASC',
116 order_outer => 'foo DESC, bar ASC, hello DESC, sensors DESC',
117 },
e606d0ce 118);
6a67b557 119
83dee2e9 120my @default_tests = ( undef, '', {}, [] );
121
122plan (tests => scalar @tests + scalar @default_tests + 1);
8f6dbee9 123
e606d0ce 124test_order ($_) for @tests;
83dee2e9 125default_test_order ($_) for @default_tests;
126
8f6dbee9 127
128is_same_sql_bind (
07dc2055 129 $rs->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
83dee2e9 130'(SELECT
131me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name FROM
132 ( SELECT
133 id, source, owner, title, price FROM
134 ( SELECT
ed0648ee 135 TOP 1 id, source, owner, title, price FROM
136 ( SELECT
137 TOP 4 me.id, me.source, me.owner, me.title, me.price FROM
138 books me JOIN
139 owners owner ON owner.id = me.owner
140 WHERE ( source = ? )
141 GROUP BY title
142 ORDER BY title ASC
143 ) me
144 ORDER BY title DESC
83dee2e9 145 ) me
146 ORDER BY title
147 ) me JOIN
148 owners owner ON owner.id = me.owner WHERE
149 ( source = ? )
150 ORDER BY title)' ,
151 [ [ source => 'Library' ], [ source => 'Library' ] ],
8f6dbee9 152);