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