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