4d98bb78516e28340922eb72429dd48fafc59102
[dbsrgits/SQL-Abstract.git] / t / 01generate.t
1 #!/usr/bin/perl
2
3 use strict;
4 use warnings;
5 use Test::More;
6
7 use SQL::Abstract::Test import => ['is_same_sql_bind'];
8
9 use SQL::Abstract;
10
11 my @tests = (
12       #1
13       {
14               func   => 'select',
15               args   => ['test', '*'],
16               stmt   => 'SELECT * FROM test',
17               stmt_q => 'SELECT * FROM `test`',
18               bind   => []
19       },
20       #2
21       {
22               func   => 'select',
23               args   => ['test', [qw(one two three)]],
24               stmt   => 'SELECT one, two, three FROM test',
25               stmt_q => 'SELECT `one`, `two`, `three` FROM `test`',
26               bind   => []
27       },
28       #3
29       {
30               func   => 'select',
31               args   => ['test', '*', { a => 0 }, [qw/boom bada bing/]],
32               stmt   => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY boom, bada, bing',
33               stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `boom`, `bada`, `bing`',
34               bind   => [0]
35       },             
36       #4             
37       {              
38               func   => 'select',
39               args   => ['test', '*', [ { a => 5 }, { b => 6 } ]],
40               stmt   => 'SELECT * FROM test WHERE ( ( a = ? ) OR ( b = ? ) )',
41               stmt_q => 'SELECT * FROM `test` WHERE ( ( `a` = ? ) OR ( `b` = ? ) )',
42               bind   => [5,6]
43       },             
44       #5             
45       {              
46               func   => 'select',
47               args   => ['test', '*', undef, ['id']],
48               stmt   => 'SELECT * FROM test ORDER BY id',
49               stmt_q => 'SELECT * FROM `test` ORDER BY `id`',
50               bind   => []
51       },             
52       #6             
53       {              
54               func   => 'select',
55               args   => ['test', '*', { a => 'boom' } , ['id']],
56               stmt   => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY id',
57               stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `id`',
58               bind   => ['boom']
59       },             
60       #7             
61       {              
62               func   => 'select',
63               args   => ['test', '*', { a => ['boom', 'bang'] }],
64               stmt   => 'SELECT * FROM test WHERE ( ( ( a = ? ) OR ( a = ? ) ) )',
65               stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `a` = ? ) OR ( `a` = ? ) ) )',
66               bind   => ['boom', 'bang']
67       },             
68       #8             
69       {              
70               func   => 'select',
71               args   => [[qw/test1 test2/], '*', { 'test1.a' => { 'In', ['boom', 'bang'] } }],
72               stmt   => 'SELECT * FROM test1, test2 WHERE ( test1.a IN ( ?, ? ) )',
73               stmt_q => 'SELECT * FROM `test1`, `test2` WHERE ( `test1`.`a` IN ( ?, ? ) )',
74               bind   => ['boom', 'bang']
75       },             
76       #9             
77       {              
78               func   => 'select',
79               args   => ['test', '*', { a => { 'between', ['boom', 'bang'] } }],
80               stmt   => 'SELECT * FROM test WHERE ( a BETWEEN ? AND ? )',
81               stmt_q => 'SELECT * FROM `test` WHERE ( `a` BETWEEN ? AND ? )',
82               bind   => ['boom', 'bang']
83       },             
84       #10            
85       {              
86               func   => 'select',
87               args   => ['test', '*', { a => { '!=', 'boom' } }],
88               stmt   => 'SELECT * FROM test WHERE ( a != ? )',
89               stmt_q => 'SELECT * FROM `test` WHERE ( `a` != ? )',
90               bind   => ['boom']
91       },             
92       #11            
93       {              
94               func   => 'update',
95               args   => ['test', {a => 'boom'}, {a => undef}],
96               stmt   => 'UPDATE test SET a = ? WHERE ( a IS NULL )',
97               stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` IS NULL )',
98               bind   => ['boom']
99       },             
100       #12            
101       {              
102               func   => 'update',
103               args   => ['test', {a => 'boom'}, { a => {'!=', "bang" }} ],
104               stmt   => 'UPDATE test SET a = ? WHERE ( a != ? )',
105               stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` != ? )',
106               bind   => ['boom', 'bang']
107       },             
108       #13            
109       {              
110               func   => 'update',
111               args   => ['test', {'a-funny-flavored-candy' => 'yummy', b => 'oops'}, { a42 => "bang" }],
112               stmt   => 'UPDATE test SET a-funny-flavored-candy = ?, b = ? WHERE ( a42 = ? )',
113               stmt_q => 'UPDATE `test` SET `a-funny-flavored-candy` = ?, `b` = ? WHERE ( `a42` = ? )',
114               bind   => ['yummy', 'oops', 'bang']
115       },             
116       #14            
117       {              
118               func   => 'delete',
119               args   => ['test', {requestor => undef}],
120               stmt   => 'DELETE FROM test WHERE ( requestor IS NULL )',
121               stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL )',
122               bind   => []
123       },             
124       #15            
125       {              
126               func   => 'delete',
127               args   => [[qw/test1 test2 test3/],
128                          { 'test1.field' => \'!= test2.field',
129                             user => {'!=','nwiger'} },
130                         ],
131               stmt   => 'DELETE FROM test1, test2, test3 WHERE ( test1.field != test2.field AND user != ? )',
132               stmt_q => 'DELETE FROM `test1`, `test2`, `test3` WHERE ( `test1`.`field` != test2.field AND `user` != ? )',  # test2.field is a literal value, cannnot be quoted.
133               bind   => ['nwiger']
134       },             
135       #16            
136       {              
137               func   => 'insert',
138               args   => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}],
139               stmt   => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)',
140               stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)',
141               bind   => [qw/1 2 3 4 5/],
142       },             
143       #17            
144       {              
145               func   => 'insert',
146               args   => ['test', [qw/1 2 3 4 5/]],
147               stmt   => 'INSERT INTO test VALUES (?, ?, ?, ?, ?)',
148               stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?)',
149               bind   => [qw/1 2 3 4 5/],
150       },             
151       #18            
152       {              
153               func   => 'insert',
154               args   => ['test', [qw/1 2 3 4 5/, undef]],
155               stmt   => 'INSERT INTO test VALUES (?, ?, ?, ?, ?, ?)',
156               stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?, ?)',
157               bind   => [qw/1 2 3 4 5/, undef],
158       },             
159       #19            
160       {              
161               func   => 'update',
162               args   => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}],
163               stmt   => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ?',
164               stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ?',
165               bind   => [qw/1 2 3 4 5/],
166       },             
167       #20            
168       {              
169               func   => 'update',
170               args   => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}, {a => {'in', [1..5]}}],
171               stmt   => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ? WHERE ( a IN ( ?, ?, ?, ?, ? ) )',
172               stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ? WHERE ( `a` IN ( ?, ?, ?, ?, ? ) )',
173               bind   => [qw/1 2 3 4 5 1 2 3 4 5/],
174       },             
175       #21            
176       {              
177               func   => 'update',
178               args   => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}],
179               stmt   => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )',
180               stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )',
181               bind   => [qw(1 02/02/02 1 2)],
182       },             
183       #22            
184       {              
185               func   => 'insert',
186               args   => ['test.table', {high_limit => \'max(all_limits)', low_limit => 4} ],
187               stmt   => 'INSERT INTO test.table (high_limit, low_limit) VALUES (max(all_limits), ?)',
188               stmt_q => 'INSERT INTO `test`.`table` (`high_limit`, `low_limit`) VALUES (max(all_limits), ?)',
189               bind   => ['4'],
190       },             
191       #23            
192       {              
193               func   => 'insert',
194               new    => {bindtype => 'columns'},
195               args   => ['test.table', {one => 2, three => 4, five => 6} ],
196               stmt   => 'INSERT INTO test.table (five, one, three) VALUES (?, ?, ?)',
197               stmt_q => 'INSERT INTO `test`.`table` (`five`, `one`, `three`) VALUES (?, ?, ?)',
198               bind   => [['five', 6], ['one', 2], ['three', 4]],  # alpha order, man...
199       },             
200       #24            
201       {              
202               func   => 'select',
203               new    => {bindtype => 'columns', case => 'lower'},
204               args   => ['test.table', [qw/one two three/], {one => 2, three => 4, five => 6} ],
205               stmt   => 'select one, two, three from test.table where ( five = ? and one = ? and three = ? )',
206               stmt_q => 'select `one`, `two`, `three` from `test`.`table` where ( `five` = ? and `one` = ? and `three` = ? )',
207               bind   => [['five', 6], ['one', 2], ['three', 4]],  # alpha order, man...
208       },             
209       #25            
210       {              
211               func   => 'update',
212               new    => {bindtype => 'columns', cmp => 'like'},
213               args   => ['testin.table2', {One => 22, Three => 44, FIVE => 66},
214                                           {Beer => 'is', Yummy => '%YES%', IT => ['IS','REALLY','GOOD']}],
215               stmt   => 'UPDATE testin.table2 SET FIVE = ?, One = ?, Three = ? WHERE '
216                        . '( Beer LIKE ? AND ( ( IT LIKE ? ) OR ( IT LIKE ? ) OR ( IT LIKE ? ) ) AND Yummy LIKE ? )',
217               stmt_q => 'UPDATE `testin`.`table2` SET `FIVE` = ?, `One` = ?, `Three` = ? WHERE '
218                        . '( `Beer` LIKE ? AND ( ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) ) AND `Yummy` LIKE ? )',
219               bind   => [['FIVE', 66], ['One', 22], ['Three', 44], ['Beer','is'],
220                          ['IT','IS'], ['IT','REALLY'], ['IT','GOOD'], ['Yummy','%YES%']],
221       },             
222       #26            
223       {              
224               func   => 'select',
225               args   => ['test', '*', {priority => [ -and => {'!=', 2}, {'!=', 1} ]}],
226               stmt   => 'SELECT * FROM test WHERE ( ( ( priority != ? ) AND ( priority != ? ) ) )',
227               stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `priority` != ? ) AND ( `priority` != ? ) ) )',
228               bind   => [qw(2 1)],
229       },             
230       #27            
231       {              
232               func   => 'select',
233               args   => ['Yo Momma', '*', { user => 'nwiger', 
234                                        -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ] }],
235               stmt   => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? ) OR ( geo = ? ) ) AND user = ? )',
236               stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? ) OR ( `geo` = ? ) ) AND `user` = ? )',
237               bind   => [qw(20 ASIA nwiger)],
238       },             
239       #28            
240       {              
241               func   => 'update',
242               args   => ['taco_punches', { one => 2, three => 4 },
243                                          { bland => [ -and => {'!=', 'yes'}, {'!=', 'YES'} ],
244                                            tasty => { '!=', [qw(yes YES)] },
245                                            -nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] },
246                         ],
247 # LDNOTE : modified the test below, same reasons as #14 in 00where.t
248               stmt   => 'UPDATE taco_punches SET one = ?, three = ? WHERE ( ( ( ( ( face = ? ) OR ( face IS NULL ) ) ) )'
249 #                      . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) OR ( tasty != ? ) ) )',
250                       . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) AND ( tasty != ? ) ) )',
251               stmt_q => 'UPDATE `taco_punches` SET `one` = ?, `three` = ? WHERE ( ( ( ( ( `face` = ? ) OR ( `face` IS NULL ) ) ) )'
252 #                      . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) OR ( `tasty` != ? ) ) )',
253                       . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) AND ( `tasty` != ? ) ) )',
254               bind   => [qw(2 4 mr.happy yes YES yes YES)],
255       },             
256       #29            
257       {              
258               func   => 'select',
259               args   => ['jeff', '*', { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},
260                                        -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ],
261                                                                    yob => {'<', 1976} ] ] } ],
262 # LDNOTE : original test below was WRONG with respect to the doc.
263 # [-and, [cond1, cond2], cond3] should mean (cond1 OR cond2) AND cond3
264 # instead of (cond1 AND cond2) OR cond3. 
265 # Probably a misconception because of '=>' notation 
266 # in [-and => [cond1, cond2], cond3].
267 # Also some differences in parentheses, but without impact on semantics.
268 #               stmt   => 'SELECT * FROM jeff WHERE ( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) )'
269 #                       . ' AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )',
270 #               stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( ( ( `age` BETWEEN ? AND ? ) AND ( `age` != ? ) ) ) OR ( `yob` < ? ) ) ) )'
271 #                       . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? )',
272               stmt   => 'SELECT * FROM jeff WHERE ( ( ( ( ( age BETWEEN ? AND ? ) OR ( age != ? ) ) AND ( yob < ? ) ) )'
273                       . ' AND ( name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? ) )',
274               stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( `age` BETWEEN ? AND ? ) OR ( `age` != ? ) ) AND ( `yob` < ? ) ) )'
275                       . ' AND ( `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? ) )',
276               bind   => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)]
277       },             
278       #30            
279       {              
280               func   => 'update',
281 # LDNOTE : removed the "-maybe", because we no longer admit unknown ops
282 #              args   => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]},
283               args   => ['fhole', {fpoles => 4}, [          {race => [-and => [qw(black white asian)]]},
284                                                             {-nest => {firsttime => [-or => {'=','yes'}, undef]}},
285                                                             [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ] ],
286               stmt   => 'UPDATE fhole SET fpoles = ? WHERE ( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) )'
287                       . ' OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )',
288               stmt_q => 'UPDATE `fhole` SET `fpoles` = ? WHERE ( ( ( ( ( ( ( `race` = ? ) OR ( `race` = ? ) OR ( `race` = ? ) ) ) ) ) )'
289                       . ' OR ( ( ( ( `firsttime` = ? ) OR ( `firsttime` IS NULL ) ) ) ) OR ( ( ( `firstname` NOT LIKE ? ) ) AND ( `lastname` IN ( ?, ?, ? ) ) ) )',
290               bind   => [qw(4 black white asian yes candace jugs canyon towers)]
291       },
292       #31
293       {
294               func   => 'insert',
295               args   => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
296               stmt   => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))',
297               stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))',
298               bind   => [qw(1 02/02/02)],
299       },
300       #32
301       {
302               func   => 'select',
303 # LDNOTE: modified test below because we agreed with MST that literal SQL
304 #         should not automatically insert a '='; the user has to do it
305 #              args   => ['test', '*', { a => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
306               args   => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}],
307               stmt   => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )},
308               stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )},
309               bind   => ['02/02/02'],
310       },
311       #33
312       {
313               func   => 'insert',
314               new    => {array_datatypes => 1},
315               args   => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
316               stmt   => 'INSERT INTO test (a, b) VALUES (?, ?)',
317               stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)',
318               bind   => [1, [1, 1, 2, 3, 5, 8]],
319       },
320       #34
321       {
322               func   => 'insert',
323               new    => {bindtype => 'columns', array_datatypes => 1},
324               args   => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
325               stmt   => 'INSERT INTO test (a, b) VALUES (?, ?)',
326               stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)',
327               bind   => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]],
328       },
329       #35
330       {
331               func   => 'update',
332               new    => {array_datatypes => 1},
333               args   => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
334               stmt   => 'UPDATE test SET a = ?, b = ?',
335               stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?',
336               bind   => [1, [1, 1, 2, 3, 5, 8]],
337       },
338       #36
339       {
340               func   => 'update',
341               new    => {bindtype => 'columns', array_datatypes => 1},
342               args   => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
343               stmt   => 'UPDATE test SET a = ?, b = ?',
344               stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?',
345               bind   => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]],
346       },
347 );
348
349
350 plan tests => scalar(@tests) * 2;
351
352 for (@tests) {
353   local $"=', ';
354
355   my $new = $_->{new} || {};
356   $new->{debug} = $ENV{DEBUG} || 0;
357   my $sql = SQL::Abstract->new(%$new);
358
359   #print "testing with args (@{$_->{args}}): ";
360   my $func = $_->{func};
361   my($stmt, @bind) = $sql->$func(@{$_->{args}});
362   is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind});
363
364   # test with quoted labels
365   my $sql_q = SQL::Abstract->new(%$new, quote_char => '`', name_sep => '.');
366
367   my $func_q = $_->{func};
368   my($stmt_q, @bind_q) = $sql_q->$func_q(@{$_->{args}});
369
370   is_same_sql_bind($stmt_q, \@bind_q, $_->{stmt_q}, $_->{bind});
371 }