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