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