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