Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
75f025cf |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
c0329273 |
7 | |
318e3d94 |
8 | use List::Util 'min'; |
a5a7bb73 |
9 | use DBICTest ':DiffSQL'; |
fcb7fcbb |
10 | use DBIx::Class::SQLMaker::LimitDialects; |
11 | my ($ROWS, $TOTAL, $OFFSET) = ( |
12 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
13 | DBIx::Class::SQLMaker::LimitDialects->__total_bindtype, |
14 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
15 | ); |
16 | |
75f025cf |
17 | |
18 | my $schema = DBICTest->init_schema; |
19 | |
20 | $schema->storage->_sql_maker->limit_dialect ('GenericSubQ'); |
21 | |
22 | my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
23 | '+columns' => [{ owner_name => 'owner.name' }], |
24 | join => 'owner', |
25 | rows => 2, |
26 | order_by => 'me.title', |
27 | }); |
28 | |
29 | is_same_sql_bind( |
30 | $rs->as_query, |
31 | '( |
90ed89cb |
32 | SELECT me.id, me.source, me.owner, me.title, me.price, |
75f025cf |
33 | owner_name |
34 | FROM ( |
35 | SELECT me.id, me.source, me.owner, me.title, me.price, |
36 | owner.name AS owner_name |
37 | FROM books me |
38 | JOIN owners owner ON owner.id = me.owner |
39 | WHERE ( source = ? ) |
75f025cf |
40 | ) me |
41 | WHERE |
42 | ( |
43 | SELECT COUNT(*) |
44 | FROM books rownum__emulation |
45 | WHERE rownum__emulation.title < me.title |
fcb7fcbb |
46 | ) < ? |
318e3d94 |
47 | ORDER BY me.title ASC |
75f025cf |
48 | )', |
fcb7fcbb |
49 | [ |
50 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
51 | [ $ROWS => 2 ], |
52 | ], |
75f025cf |
53 | ); |
54 | |
55 | is_deeply ( |
56 | [ $rs->get_column ('title')->all ], |
57 | ['Best Recipe Cookbook', 'Dynamical Systems'], |
58 | 'Correct columns selected with rows', |
59 | ); |
60 | |
61 | $schema->storage->_sql_maker->quote_char ('"'); |
62 | $schema->storage->_sql_maker->name_sep ('.'); |
63 | |
64 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
65 | order_by => { -desc => 'title' }, |
66 | '+select' => ['owner.name'], |
67 | '+as' => ['owner.name'], |
68 | join => 'owner', |
69 | rows => 3, |
70 | offset => 1, |
71 | }); |
72 | |
73 | is_same_sql_bind( |
74 | $rs->as_query, |
75 | '( |
90ed89cb |
76 | SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price", |
75f025cf |
77 | "owner__name" |
78 | FROM ( |
79 | SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price", |
80 | "owner"."name" AS "owner__name" |
81 | FROM "books" "me" |
82 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
83 | WHERE ( "source" = ? ) |
75f025cf |
84 | ) "me" |
85 | WHERE |
86 | ( |
87 | SELECT COUNT(*) |
88 | FROM "books" "rownum__emulation" |
89 | WHERE "rownum__emulation"."title" > "me"."title" |
fcb7fcbb |
90 | ) BETWEEN ? AND ? |
318e3d94 |
91 | ORDER BY "me"."title" DESC |
75f025cf |
92 | )', |
fcb7fcbb |
93 | [ |
94 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
95 | [ $OFFSET => 1 ], |
96 | [ $TOTAL => 3 ], |
97 | ], |
75f025cf |
98 | ); |
99 | |
100 | is_deeply ( |
101 | [ $rs->get_column ('title')->all ], |
102 | [ 'Dynamical Systems', 'Best Recipe Cookbook' ], |
103 | 'Correct columns selected with rows', |
104 | ); |
105 | |
106 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
107 | order_by => 'title', |
108 | 'select' => ['owner.name'], |
109 | 'as' => ['owner_name'], |
110 | join => 'owner', |
111 | offset => 1, |
112 | }); |
113 | |
114 | is_same_sql_bind( |
115 | $rs->as_query, |
116 | '( |
117 | SELECT "owner_name" |
118 | FROM ( |
318e3d94 |
119 | SELECT "owner"."name" AS "owner_name", "me"."title" |
75f025cf |
120 | FROM "books" "me" |
121 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
122 | WHERE ( "source" = ? ) |
75f025cf |
123 | ) "me" |
124 | WHERE |
125 | ( |
126 | SELECT COUNT(*) |
127 | FROM "books" "rownum__emulation" |
128 | WHERE "rownum__emulation"."title" < "me"."title" |
fcb7fcbb |
129 | ) BETWEEN ? AND ? |
318e3d94 |
130 | ORDER BY "me"."title" ASC |
75f025cf |
131 | )', |
fcb7fcbb |
132 | [ |
133 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
134 | [ $OFFSET => 1 ], |
135 | [ $TOTAL => 2147483647 ], |
136 | ], |
75f025cf |
137 | ); |
138 | |
139 | is_deeply ( |
140 | [ $rs->get_column ('owner_name')->all ], |
141 | [ ('Newton') x 2 ], |
142 | 'Correct columns selected with rows', |
143 | ); |
144 | |
318e3d94 |
145 | $rs = $schema->resultset('CD')->search({}, { |
146 | columns => [qw( me.cdid me.title me.genreid me.year tracks.position tracks.title )], |
147 | join => 'tracks', |
148 | collapse => 1, |
149 | order_by => [ { -asc => 'me.genreid' }, { -desc => 'year' }, 'me.title', \ 'single_track DESC', { -desc => [qw( me.cdid tracks.position )] } ], |
150 | }); |
151 | |
152 | my @full_res = @{$rs->all_hri}; |
153 | |
154 | is (@full_res, 5, 'Expected amount of CDs'); |
155 | |
156 | is_deeply ( |
157 | \@full_res, |
158 | [ |
159 | { cdid => 2, genreid => undef, title => "Forkful of bees", year => 2001, tracks => [ |
160 | { position => 3, title => "Sticky Honey" }, |
161 | { position => 2, title => "Stripy" }, |
162 | { position => 1, title => "Stung with Success" }, |
163 | ] }, |
164 | { cdid => 4, genreid => undef, title => "Generic Manufactured Singles", year => 2001, tracks => [ |
165 | { position => 3, title => "No More Ideas" }, |
166 | { position => 2, title => "Boring Song" }, |
167 | { position => 1, title => "Boring Name" }, |
168 | ] }, |
169 | { cdid => 5, genreid => undef, title => "Come Be Depressed With Us", year => 1998, tracks => [ |
170 | { position => 3, title => "Suicidal" }, |
171 | { position => 2, title => "Under The Weather" }, |
172 | { position => 1, title => "Sad" }, |
173 | ] }, |
174 | { cdid => 3, genreid => undef, title => "Caterwaulin' Blues", year => 1997, tracks => [ |
175 | { position => 3, title => "Fowlin" }, |
176 | { position => 2, title => "Howlin" }, |
177 | { position => 1, title => "Yowlin" }, |
178 | ] }, |
179 | { cdid => 1, genreid => 1, title => "Spoonful of bees", year => 1999, tracks => [ |
180 | { position => 3, title => "Beehind You" }, |
181 | { position => 2, title => "Apiary" }, |
182 | { position => 1, title => "The Bees Knees" }, |
183 | ] }, |
184 | ], |
185 | 'Complex ordered gensubq limited cds and tracks in expected sqlite order' |
186 | ); |
187 | |
188 | for my $slice ( |
189 | [0, 10], |
190 | [3, 5 ], |
191 | [4, 6 ], |
192 | [0, 2 ], |
193 | [1, 3 ], |
194 | ) { |
195 | |
196 | my $rownum_cmp_op = $slice->[0] |
197 | ? 'BETWEEN ? AND ?' |
198 | : ' < ?' |
199 | ; |
200 | |
201 | is_deeply( |
202 | $rs->slice(@$slice)->all_hri, |
203 | [ @full_res[ $slice->[0] .. min($#full_res, $slice->[1]) ] ], |
204 | "Expected array slice on complex ordered limited gensubq ($slice->[0] : $slice->[1])", |
205 | ); |
206 | |
207 | is_same_sql_bind( |
208 | $rs->slice(@$slice)->as_query, |
209 | qq{( |
210 | SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", |
211 | "tracks"."position", "tracks"."title" |
212 | FROM ( |
213 | SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track" |
214 | FROM ( |
215 | SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track" |
216 | FROM cd "me" |
217 | LEFT JOIN "track" "tracks" |
218 | ON "tracks"."cd" = "me"."cdid" |
219 | GROUP BY "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track" |
220 | ) "me" |
221 | WHERE ( |
222 | SELECT COUNT( * ) |
223 | FROM cd "rownum__emulation" |
224 | WHERE ( |
225 | ( "me"."genreid" IS NOT NULL AND "rownum__emulation"."genreid" IS NULL ) |
226 | OR |
227 | ( |
228 | "rownum__emulation"."genreid" < "me"."genreid" |
229 | AND |
230 | "me"."genreid" IS NOT NULL |
231 | AND |
232 | "rownum__emulation"."genreid" IS NOT NULL |
233 | ) |
234 | OR |
235 | ( |
236 | ( |
237 | "me"."genreid" = "rownum__emulation"."genreid" |
238 | OR |
239 | ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL ) |
240 | ) |
241 | AND |
242 | "rownum__emulation"."year" > "me"."year" |
243 | ) |
244 | OR |
245 | ( |
246 | ( |
247 | "me"."genreid" = "rownum__emulation"."genreid" |
248 | OR |
249 | ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL ) |
250 | ) |
251 | AND |
252 | "me"."year" = "rownum__emulation"."year" |
253 | AND |
254 | "rownum__emulation"."title" < "me"."title" |
255 | ) |
256 | OR |
257 | ( |
258 | ( |
259 | "me"."genreid" = "rownum__emulation"."genreid" |
260 | OR |
261 | ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL ) |
262 | ) |
263 | AND |
264 | "me"."year" = "rownum__emulation"."year" |
265 | AND |
266 | "me"."title" = "rownum__emulation"."title" |
267 | AND |
268 | ( |
269 | ("me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NOT NULL ) |
270 | OR |
271 | ( |
272 | "rownum__emulation"."single_track" > "me"."single_track" |
273 | AND |
274 | "me"."single_track" IS NOT NULL |
275 | AND |
276 | "rownum__emulation"."single_track" IS NOT NULL |
277 | ) |
278 | ) |
279 | ) |
280 | OR |
281 | ( |
282 | ( |
283 | "me"."genreid" = "rownum__emulation"."genreid" |
284 | OR |
285 | ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL ) |
286 | ) |
287 | AND |
288 | "me"."year" = "rownum__emulation"."year" |
289 | AND |
290 | "me"."title" = "rownum__emulation"."title" |
291 | AND |
292 | ( |
293 | ( "me"."single_track" = "rownum__emulation"."single_track" ) |
294 | OR |
295 | ( "me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NULL ) |
296 | ) |
297 | AND |
298 | "rownum__emulation"."cdid" > "me"."cdid" |
299 | ) |
300 | ) |
301 | ) $rownum_cmp_op |
302 | ORDER BY "me"."genreid" ASC, "me"."year" DESC, "me"."title" ASC, "me"."single_track" DESC, "me"."cdid" DESC |
303 | ) "me" |
304 | LEFT JOIN "track" "tracks" |
305 | ON "tracks"."cd" = "me"."cdid" |
306 | ORDER BY "me"."genreid" ASC, "year" DESC, "me"."title", single_track DESC, "me"."cdid" DESC, "tracks"."position" DESC |
307 | )}, |
308 | [ |
309 | ( $slice->[0] ? [ $OFFSET => $slice->[0] ] : () ), |
310 | [ $TOTAL => $slice->[1] + ($slice->[0] ? 0 : 1 ) ], |
311 | ], |
312 | "Expected sql on complex ordered limited gensubq ($slice->[0] : $slice->[1])", |
313 | ); |
314 | } |
315 | |
d7632687 |
316 | { |
317 | $rs = $schema->resultset('Artist')->search({}, { |
528e717e |
318 | columns => 'artistid', |
d7632687 |
319 | offset => 1, |
528e717e |
320 | order_by => 'artistid', |
d7632687 |
321 | }); |
322 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
323 | |
324 | like ( |
325 | ${$rs->as_query}->[0], |
326 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
327 | 'Newlines/spaces preserved in final sql', |
328 | ); |
329 | } |
330 | |
75f025cf |
331 | done_testing; |