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