Commit | Line | Data |
65c2b042 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
65c2b042 |
5 | use lib qw(t/lib); |
e606d0ce |
6 | use DBICTest; |
65c2b042 |
7 | use DBIC::SqlMakerTest; |
ed0648ee |
8 | |
e606d0ce |
9 | my $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 |
20f44a33 |
14 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
e606d0ce |
15 | $schema->storage->_sql_maker->limit_dialect ('Top'); |
16 | |
20f44a33 |
17 | my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 2, offset => 3 }); |
e606d0ce |
18 | |
20f44a33 |
19 | for my $null_order ( |
20 | undef, |
21 | '', |
22 | {}, |
23 | [], |
24 | [{}], |
25 | ) { |
26 | my $rs = $books_45_and_owners->search ({}, {order_by => $null_order }); |
e606d0ce |
27 | is_same_sql_bind( |
20f44a33 |
28 | $rs->as_query, |
29 | '(SELECT TOP 2 |
30 | id, source, owner, title, price, owner__id, owner__name |
31 | FROM ( |
32 | SELECT TOP 5 |
33 | me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name |
34 | FROM books me |
35 | JOIN owners owner ON owner.id = me.owner |
36 | WHERE ( source = ? ) |
37 | ORDER BY me.id |
38 | ) me |
39 | ORDER BY me.id DESC |
40 | )', |
0e773352 |
41 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
42 | => 'Library' ] ], |
65c2b042 |
43 | ); |
44 | } |
45 | |
69d3c270 |
46 | { |
47 | my $subq = $schema->resultset('Owners')->search({ |
48 | 'count.id' => { -ident => 'owner.id' }, |
49 | }, { alias => 'owner' })->count_rs; |
50 | |
51 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
52 | columns => [ |
53 | { owner_name => 'owner.name' }, |
54 | { owner_books => $subq->as_query }, |
55 | ], |
56 | join => 'owner', |
57 | rows => 2, |
58 | offset => 3, |
59 | }); |
60 | |
61 | is_same_sql_bind( |
62 | $rs_selectas_rel->as_query, |
63 | '( |
64 | SELECT TOP 2 owner_name, owner_books |
65 | FROM ( |
66 | SELECT TOP 5 owner.name AS owner_name, |
67 | ( SELECT COUNT( * ) |
68 | FROM owners owner |
69 | WHERE ( count.id = owner.id ) |
70 | ) AS owner_books |
71 | FROM books me |
72 | JOIN owners owner ON owner.id = me.owner |
73 | WHERE ( source = ? ) |
74 | ORDER BY me.id |
75 | ) me |
76 | ORDER BY me.id DESC |
77 | )', |
78 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
79 | => 'Library' ] ], |
80 | 'pagination with subqueries works' |
81 | ); |
82 | |
83 | } |
20f44a33 |
84 | |
85 | for my $ord_set ( |
e606d0ce |
86 | { |
83dee2e9 |
87 | order_by => \'foo DESC', |
e606d0ce |
88 | order_inner => 'foo DESC', |
94fa8410 |
89 | order_outer => 'ORDER__BY__1 ASC', |
90 | order_req => 'ORDER__BY__1 DESC', |
91 | exselect_outer => 'ORDER__BY__1', |
92 | exselect_inner => 'foo AS ORDER__BY__1', |
e606d0ce |
93 | }, |
94 | { |
95 | order_by => { -asc => 'foo' }, |
e606d0ce |
96 | order_inner => 'foo ASC', |
94fa8410 |
97 | order_outer => 'ORDER__BY__1 DESC', |
98 | order_req => 'ORDER__BY__1 ASC', |
99 | exselect_outer => 'ORDER__BY__1', |
100 | exselect_inner => 'foo AS ORDER__BY__1', |
e606d0ce |
101 | }, |
102 | { |
20f44a33 |
103 | order_by => { -desc => 'foo' }, |
104 | order_inner => 'foo DESC', |
94fa8410 |
105 | order_outer => 'ORDER__BY__1 ASC', |
106 | order_req => 'ORDER__BY__1 DESC', |
107 | exselect_outer => 'ORDER__BY__1', |
108 | exselect_inner => 'foo AS ORDER__BY__1', |
e606d0ce |
109 | }, |
110 | { |
20f44a33 |
111 | order_by => 'foo', |
112 | order_inner => 'foo', |
94fa8410 |
113 | order_outer => 'ORDER__BY__1 DESC', |
114 | order_req => 'ORDER__BY__1', |
115 | exselect_outer => 'ORDER__BY__1', |
116 | exselect_inner => 'foo AS ORDER__BY__1', |
e606d0ce |
117 | }, |
118 | { |
20f44a33 |
119 | order_by => [ qw{ foo me.owner} ], |
120 | order_inner => 'foo, me.owner', |
94fa8410 |
121 | order_outer => 'ORDER__BY__1 DESC, me.owner DESC', |
122 | order_req => 'ORDER__BY__1, me.owner', |
123 | exselect_outer => 'ORDER__BY__1', |
124 | exselect_inner => 'foo AS ORDER__BY__1', |
e606d0ce |
125 | }, |
126 | { |
127 | order_by => ['foo', { -desc => 'bar' } ], |
20f44a33 |
128 | order_inner => 'foo, bar DESC', |
94fa8410 |
129 | order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC', |
130 | order_req => 'ORDER__BY__1, ORDER__BY__2 DESC', |
131 | exselect_outer => 'ORDER__BY__1, ORDER__BY__2', |
132 | exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2', |
e606d0ce |
133 | }, |
134 | { |
135 | order_by => { -asc => [qw{ foo bar }] }, |
e606d0ce |
136 | order_inner => 'foo ASC, bar ASC', |
94fa8410 |
137 | order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 DESC', |
138 | order_req => 'ORDER__BY__1 ASC, ORDER__BY__2 ASC', |
139 | exselect_outer => 'ORDER__BY__1, ORDER__BY__2', |
140 | exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2', |
e606d0ce |
141 | }, |
142 | { |
143 | order_by => [ |
20f44a33 |
144 | 'foo', |
e606d0ce |
145 | { -desc => [qw{bar}] }, |
20f44a33 |
146 | { -asc => [qw{me.owner sensors}]}, |
e606d0ce |
147 | ], |
20f44a33 |
148 | order_inner => 'foo, bar DESC, me.owner ASC, sensors ASC', |
94fa8410 |
149 | order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC, me.owner DESC, ORDER__BY__3 DESC', |
150 | order_req => 'ORDER__BY__1, ORDER__BY__2 DESC, me.owner ASC, ORDER__BY__3 ASC', |
151 | exselect_outer => 'ORDER__BY__1, ORDER__BY__2, ORDER__BY__3', |
152 | exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2, sensors AS ORDER__BY__3', |
e606d0ce |
153 | }, |
20f44a33 |
154 | ) { |
155 | my $o_sel = $ord_set->{exselect_outer} |
156 | ? ', ' . $ord_set->{exselect_outer} |
157 | : '' |
158 | ; |
159 | my $i_sel = $ord_set->{exselect_inner} |
160 | ? ', ' . $ord_set->{exselect_inner} |
161 | : '' |
162 | ; |
83dee2e9 |
163 | |
20f44a33 |
164 | is_same_sql_bind( |
165 | $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query, |
166 | "(SELECT TOP 2 |
167 | id, source, owner, title, price, owner__id, owner__name |
168 | FROM ( |
169 | SELECT TOP 2 |
170 | id, source, owner, title, price, owner__id, owner__name$o_sel |
171 | FROM ( |
172 | SELECT TOP 5 |
173 | me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel |
174 | FROM books me |
175 | JOIN owners owner ON owner.id = me.owner |
176 | WHERE ( source = ? ) |
177 | ORDER BY $ord_set->{order_inner} |
178 | ) me |
179 | ORDER BY $ord_set->{order_outer} |
180 | ) me |
181 | ORDER BY $ord_set->{order_req} |
182 | )", |
0e773352 |
183 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
184 | => 'Library' ] ], |
94fa8410 |
185 | ); |
20f44a33 |
186 | } |
8f6dbee9 |
187 | |
20f44a33 |
188 | # with groupby |
8f6dbee9 |
189 | is_same_sql_bind ( |
20f44a33 |
190 | $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query, |
191 | '(SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name |
192 | FROM ( |
193 | SELECT TOP 2 id, source, owner, title, price |
194 | FROM ( |
195 | SELECT TOP 2 |
75f025cf |
196 | id, source, owner, title, price |
20f44a33 |
197 | FROM ( |
198 | SELECT TOP 5 |
75f025cf |
199 | me.id, me.source, me.owner, me.title, me.price |
20f44a33 |
200 | FROM books me |
201 | JOIN owners owner ON owner.id = me.owner |
202 | WHERE ( source = ? ) |
203 | GROUP BY title |
204 | ORDER BY title |
205 | ) me |
75f025cf |
206 | ORDER BY title DESC |
20f44a33 |
207 | ) me |
75f025cf |
208 | ORDER BY title |
83dee2e9 |
209 | ) me |
20f44a33 |
210 | JOIN owners owner ON owner.id = me.owner |
211 | WHERE ( source = ? ) |
212 | ORDER BY title |
213 | )', |
0e773352 |
214 | [ map { [ |
215 | { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
216 | => 'Library' ] |
217 | } (1,2) ], |
8f6dbee9 |
218 | ); |
a5f843e3 |
219 | |
20f44a33 |
220 | # test deprecated column mixing over join boundaries |
41eac664 |
221 | my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, { |
222 | '+select' => ['owner.name'], |
223 | '+as' => ['owner_name'], |
224 | join => 'owner', |
69d3c270 |
225 | rows => 1 |
41eac664 |
226 | }); |
a5f843e3 |
227 | |
228 | is_same_sql_bind( $rs_selectas_top->search({})->as_query, |
41eac664 |
229 | '(SELECT |
230 | TOP 1 me.id, me.source, me.owner, me.title, me.price, |
20f44a33 |
231 | owner.name AS owner_name |
41eac664 |
232 | FROM books me |
233 | JOIN owners owner ON owner.id = me.owner |
234 | WHERE ( source = ? ) |
20f44a33 |
235 | ORDER BY me.id |
0e773352 |
236 | )', |
237 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
238 | => 'Library' ] ], |
a5f843e3 |
239 | ); |
240 | |
d7632687 |
241 | { |
242 | my $rs = $schema->resultset('Artist')->search({}, { |
243 | columns => 'name', |
244 | offset => 1, |
245 | order_by => 'name', |
246 | }); |
247 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
248 | |
249 | like ( |
250 | ${$rs->as_query}->[0], |
251 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
252 | 'Newlines/spaces preserved in final sql', |
253 | ); |
254 | } |
255 | |
a5f843e3 |
256 | done_testing; |