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