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