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