Commit | Line | Data |
3da841f1 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | |
d7571c64 |
6 | local $TODO = 'Temporarily todo-ed for dq2eb'; |
7 | |
c61a0748 |
8 | use lib qw(t/lib); |
5e724964 |
9 | use DBICTest; |
c61a0748 |
10 | use DBIC::SqlMakerTest; |
3da841f1 |
11 | |
c216324a |
12 | my $schema = DBICTest->init_schema(); |
3da841f1 |
13 | |
c216324a |
14 | my $sql_maker = $schema->storage->sql_maker; |
3da841f1 |
15 | |
16 | $sql_maker->quote_char('`'); |
17 | $sql_maker->name_sep('.'); |
18 | |
9b459129 |
19 | my ($sql, @bind) = $sql_maker->select( |
3da841f1 |
20 | [ |
21 | { |
22 | 'me' => 'cd' |
23 | }, |
24 | [ |
25 | { |
26 | 'artist' => 'artist', |
27 | '-join_type' => '' |
28 | }, |
29 | { |
30 | 'artist.artistid' => 'me.artist' |
31 | } |
83e09b5b |
32 | ], |
33 | [ |
34 | { |
35 | 'tracks' => 'tracks', |
36 | '-join_type' => 'left' |
37 | }, |
38 | { |
39 | 'tracks.cd' => 'me.cdid' |
40 | } |
41 | ], |
3da841f1 |
42 | ], |
43 | [ |
83e09b5b |
44 | 'me.cdid', |
45 | { count => 'tracks.cd' }, |
c859e108 |
46 | { min => 'me.year', -as => 'minyear' }, |
3da841f1 |
47 | ], |
48 | { |
49 | 'artist.name' => 'Caterwauler McCrae', |
50 | 'me.year' => 2001 |
51 | }, |
a6b68a60 |
52 | {}, |
3da841f1 |
53 | undef, |
54 | undef |
55 | ); |
56 | |
9b459129 |
57 | is_same_sql_bind( |
58 | $sql, \@bind, |
83e09b5b |
59 | q/ |
c859e108 |
60 | SELECT `me`.`cdid`, COUNT( `tracks`.`cd` ), MIN( `me`.`year` ) AS `minyear` |
83e09b5b |
61 | FROM `cd` `me` |
62 | JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` ) |
63 | LEFT JOIN `tracks` `tracks` ON ( `tracks`.`cd` = `me`.`cdid` ) |
64 | WHERE ( `artist`.`name` = ? AND `me`.`year` = ? ) |
65 | /, |
66 | [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ], |
67 | 'got correct SQL and bind parameters for complex select query with quoting' |
9b459129 |
68 | ); |
3da841f1 |
69 | |
c80207cd |
70 | |
9b459129 |
71 | ($sql, @bind) = $sql_maker->select( |
3da841f1 |
72 | [ |
73 | { |
74 | 'me' => 'cd' |
75 | } |
76 | ], |
77 | [ |
78 | 'me.cdid', |
79 | 'me.artist', |
80 | 'me.title', |
81 | 'me.year' |
82 | ], |
83 | undef, |
a6b68a60 |
84 | { order_by => 'year DESC' }, |
c80207cd |
85 | undef, |
86 | undef |
87 | ); |
88 | |
89 | is_same_sql_bind( |
90 | $sql, \@bind, |
91 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`/, [], |
92 | 'scalar ORDER BY okay (single value)' |
93 | ); |
94 | |
95 | |
96 | ($sql, @bind) = $sql_maker->select( |
3da841f1 |
97 | [ |
c80207cd |
98 | { |
99 | 'me' => 'cd' |
100 | } |
101 | ], |
102 | [ |
103 | 'me.cdid', |
104 | 'me.artist', |
105 | 'me.title', |
106 | 'me.year' |
107 | ], |
108 | undef, |
a6b68a60 |
109 | { order_by => [ |
c80207cd |
110 | 'year DESC', |
111 | 'title ASC' |
a6b68a60 |
112 | ]}, |
3da841f1 |
113 | undef, |
114 | undef |
115 | ); |
116 | |
8682bb07 |
117 | is_same_sql_bind( |
118 | $sql, \@bind, |
c80207cd |
119 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`, `title ASC`/, [], |
120 | 'scalar ORDER BY okay (multiple values)' |
8682bb07 |
121 | ); |
122 | |
20ea616f |
123 | { |
89479564 |
124 | ($sql, @bind) = $sql_maker->select( |
125 | [ |
126 | { |
127 | 'me' => 'cd' |
128 | } |
129 | ], |
130 | [ |
131 | 'me.cdid', |
132 | 'me.artist', |
133 | 'me.title', |
134 | 'me.year' |
135 | ], |
136 | undef, |
a6b68a60 |
137 | { order_by => { -desc => 'year' } }, |
89479564 |
138 | undef, |
139 | undef |
140 | ); |
3da841f1 |
141 | |
89479564 |
142 | is_same_sql_bind( |
143 | $sql, \@bind, |
144 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, [], |
145 | 'hashref ORDER BY okay (single value)' |
146 | ); |
3da841f1 |
147 | |
c80207cd |
148 | |
89479564 |
149 | ($sql, @bind) = $sql_maker->select( |
150 | [ |
151 | { |
152 | 'me' => 'cd' |
153 | } |
154 | ], |
155 | [ |
156 | 'me.cdid', |
157 | 'me.artist', |
158 | 'me.title', |
159 | 'me.year' |
160 | ], |
161 | undef, |
a6b68a60 |
162 | { order_by => [ |
89479564 |
163 | { -desc => 'year' }, |
a6b68a60 |
164 | { -asc => 'title' }, |
165 | ]}, |
89479564 |
166 | undef, |
167 | undef |
168 | ); |
169 | |
170 | is_same_sql_bind( |
171 | $sql, \@bind, |
172 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC, `title` ASC/, [], |
173 | 'hashref ORDER BY okay (multiple values)' |
174 | ); |
c80207cd |
175 | |
89479564 |
176 | } |
c80207cd |
177 | |
178 | |
179 | ($sql, @bind) = $sql_maker->select( |
180 | [ |
181 | { |
182 | 'me' => 'cd' |
183 | } |
184 | ], |
185 | [ |
186 | 'me.cdid', |
187 | 'me.artist', |
188 | 'me.title', |
189 | 'me.year' |
3da841f1 |
190 | ], |
191 | undef, |
a6b68a60 |
192 | { order_by => \'year DESC' }, |
c80207cd |
193 | undef, |
3da841f1 |
194 | undef |
195 | ); |
196 | |
9b459129 |
197 | is_same_sql_bind( |
198 | $sql, \@bind, |
199 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, [], |
c80207cd |
200 | 'did not quote ORDER BY with scalarref (single value)' |
201 | ); |
202 | |
203 | |
204 | ($sql, @bind) = $sql_maker->select( |
205 | [ |
206 | { |
207 | 'me' => 'cd' |
208 | } |
209 | ], |
210 | [ |
211 | 'me.cdid', |
212 | 'me.artist', |
213 | 'me.title', |
214 | 'me.year' |
215 | ], |
216 | undef, |
a6b68a60 |
217 | { order_by => [ |
c80207cd |
218 | \'year DESC', |
219 | \'title ASC' |
a6b68a60 |
220 | ]}, |
c80207cd |
221 | undef, |
222 | undef |
223 | ); |
224 | |
225 | is_same_sql_bind( |
226 | $sql, \@bind, |
227 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC, title ASC/, [], |
228 | 'did not quote ORDER BY with scalarref (multiple values)' |
3da841f1 |
229 | ); |
230 | |
c80207cd |
231 | |
da562e65 |
232 | ($sql, @bind) = $sql_maker->select( |
233 | [ { me => 'cd' } ], |
234 | [qw/ me.cdid me.artist me.title /], |
235 | { cdid => \['rlike ?', [cdid => 'X'] ] }, |
236 | { group_by => 'title', having => \['count(me.artist) > ?', [ cnt => 2] ] }, |
237 | ); |
238 | |
239 | is_same_sql_bind( |
240 | $sql, \@bind, |
241 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike ? ) GROUP BY `title` HAVING count(me.artist) > ?/, |
242 | [ [ cdid => 'X'], ['cnt' => '2'] ], |
9611a147 |
243 | 'Quoting works with where/having arrayrefsrefs', |
244 | ); |
245 | |
246 | |
247 | ($sql, @bind) = $sql_maker->select( |
248 | [ { me => 'cd' } ], |
249 | [qw/ me.cdid me.artist me.title /], |
250 | { cdid => \'rlike X' }, |
251 | { group_by => 'title', having => \'count(me.artist) > 2' }, |
252 | ); |
253 | |
254 | is_same_sql_bind( |
255 | $sql, \@bind, |
256 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike X ) GROUP BY `title` HAVING count(me.artist) > 2/, |
257 | [], |
da562e65 |
258 | 'Quoting works with where/having scalarrefs', |
259 | ); |
260 | |
261 | |
9b459129 |
262 | ($sql, @bind) = $sql_maker->update( |
3da841f1 |
263 | 'group', |
264 | { |
265 | 'order' => '12', |
266 | 'name' => 'Bill' |
267 | } |
268 | ); |
269 | |
9b459129 |
270 | is_same_sql_bind( |
271 | $sql, \@bind, |
272 | q/UPDATE `group` SET `name` = ?, `order` = ?/, [ ['name' => 'Bill'], ['order' => '12'] ], |
273 | 'quoted table names for UPDATE' |
274 | ); |
3da841f1 |
275 | |
20ea616f |
276 | { |
949172b0 |
277 | ($sql, @bind) = $sql_maker->select( |
278 | [ |
279 | { |
280 | 'me' => 'cd' |
281 | } |
282 | ], |
283 | [ |
284 | 'me.*' |
285 | ], |
286 | undef, |
949172b0 |
287 | undef, |
a6b68a60 |
288 | undef, |
289 | undef, |
949172b0 |
290 | ); |
c80207cd |
291 | |
949172b0 |
292 | is_same_sql_bind( |
293 | $sql, \@bind, |
294 | q/SELECT `me`.* FROM `cd` `me`/, [], |
295 | 'select attr with me.* is right' |
296 | ); |
297 | } |
c80207cd |
298 | |
299 | |
3da841f1 |
300 | $sql_maker->quote_char([qw/[ ]/]); |
301 | |
9b459129 |
302 | ($sql, @bind) = $sql_maker->select( |
3da841f1 |
303 | [ |
304 | { |
305 | 'me' => 'cd' |
306 | }, |
307 | [ |
308 | { |
309 | 'artist' => 'artist', |
310 | '-join_type' => '' |
311 | }, |
312 | { |
313 | 'artist.artistid' => 'me.artist' |
314 | } |
315 | ] |
316 | ], |
317 | [ |
318 | { |
50136dd9 |
319 | max => 'rank', |
320 | -as => 'max_rank', |
321 | }, |
322 | 'rank', |
323 | { |
324 | 'count' => '*', |
325 | -as => 'cnt', |
3da841f1 |
326 | } |
327 | ], |
328 | { |
329 | 'artist.name' => 'Caterwauler McCrae', |
330 | 'me.year' => 2001 |
331 | }, |
3da841f1 |
332 | undef, |
a6b68a60 |
333 | undef, |
334 | undef, |
3da841f1 |
335 | ); |
336 | |
9b459129 |
337 | is_same_sql_bind( |
338 | $sql, \@bind, |
50136dd9 |
339 | q/SELECT MAX ( [rank] ) AS [max_rank], [rank], COUNT( * ) AS [cnt] FROM [cd] [me] JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/, [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ], |
9b459129 |
340 | 'got correct SQL and bind parameters for count query with bracket quoting' |
341 | ); |
3da841f1 |
342 | |
343 | |
9b459129 |
344 | ($sql, @bind) = $sql_maker->update( |
3da841f1 |
345 | 'group', |
346 | { |
347 | 'order' => '12', |
348 | 'name' => 'Bill' |
349 | } |
350 | ); |
351 | |
9b459129 |
352 | is_same_sql_bind( |
353 | $sql, \@bind, |
354 | q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ], |
355 | 'bracket quoted table names for UPDATE' |
356 | ); |
da562e65 |
357 | |
358 | done_testing; |