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