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