Support arrayrefref for table names
[dbsrgits/SQL-Abstract.git] / t / 02where.t
1 use strict;
2 use warnings;
3 use Test::More;
4 use Test::Warn;
5 use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where) ];
6
7 use SQL::Abstract;
8
9 my $not_stringifiable = bless {}, 'SQLA::NotStringifiable';
10
11 my @handle_tests = (
12     {
13         where => {
14             requestor => 'inna',
15             worker => ['nwiger', 'rcwe', 'sfz'],
16             status => { '!=', 'completed' }
17         },
18         order => [],
19         stmt => " WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR"
20               . " ( worker = ? ) OR ( worker = ? ) ) )",
21         bind => [qw/inna completed nwiger rcwe sfz/],
22     },
23
24     {
25         where  => [
26             status => 'completed',
27             user   => 'nwiger',
28         ],
29         stmt => " WHERE ( status = ? OR user = ? )",
30         bind => [qw/completed nwiger/],
31     },
32
33     {
34         where  => {
35             user   => 'nwiger',
36             status => 'completed'
37         },
38         order => [qw/ticket/],
39         stmt => " WHERE ( status = ? AND user = ? ) ORDER BY ticket",
40         bind => [qw/completed nwiger/],
41     },
42
43     {
44         where  => {
45             user   => 'nwiger',
46             status => { '!=', 'completed' }
47         },
48         order => [qw/ticket/],
49         stmt => " WHERE ( status != ? AND user = ? ) ORDER BY ticket",
50         bind => [qw/completed nwiger/],
51     },
52
53     {
54         where  => {
55             status   => 'completed',
56             reportid => { 'in', [567, 2335, 2] }
57         },
58         order => [],
59         stmt => " WHERE ( reportid IN ( ?, ?, ? ) AND status = ? )",
60         bind => [qw/567 2335 2 completed/],
61     },
62
63     {
64         where  => {
65             status   => 'completed',
66             reportid => { 'not in', [567, 2335, 2] }
67         },
68         order => [],
69         stmt => " WHERE ( reportid NOT IN ( ?, ?, ? ) AND status = ? )",
70         bind => [qw/567 2335 2 completed/],
71     },
72
73     {
74         where  => {
75             status   => 'completed',
76             completion_date => { 'between', ['2002-10-01', '2003-02-06'] },
77         },
78         order => \'ticket, requestor',
79         stmt => "WHERE ( ( completion_date BETWEEN ? AND ? ) AND status = ? ) ORDER BY ticket, requestor",
80         bind => [qw/2002-10-01 2003-02-06 completed/],
81     },
82
83     {
84         where => [
85             {
86                 user   => 'nwiger',
87                 status => { 'in', ['pending', 'dispatched'] },
88             },
89             {
90                 user   => 'robot',
91                 status => 'unassigned',
92             },
93         ],
94         order => [],
95         stmt => " WHERE ( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",
96         bind => [qw/pending dispatched nwiger unassigned robot/],
97     },
98
99     {
100         where => {
101             priority  => [ {'>', 3}, {'<', 1} ],
102             requestor => \'is not null',
103         },
104         order => 'priority',
105         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null ) ORDER BY priority",
106         bind => [qw/3 1/],
107     },
108
109     {
110         where => {
111             requestor => { '!=', ['-and', undef, ''] },
112         },
113         stmt => " WHERE ( requestor IS NOT NULL AND requestor != ? )",
114         bind => [''],
115     },
116
117     {
118         where => {
119             priority  => [ {'>', 3}, {'<', 1} ],
120             requestor => { '!=', undef },
121         },
122         order => [qw/a b c d e f g/],
123         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )"
124               . " ORDER BY a, b, c, d, e, f, g",
125         bind => [qw/3 1/],
126     },
127
128     {
129         where => {
130             priority  => { 'between', [1, 3] },
131             requestor => { 'like', undef },
132         },
133         order => \'requestor, ticket',
134         stmt => " WHERE ( ( priority BETWEEN ? AND ? ) AND requestor IS NULL ) ORDER BY requestor, ticket",
135         bind => [qw/1 3/],
136         warns => qr/Supplying an undefined argument to 'LIKE' is deprecated/,
137     },
138
139
140     {
141         where => {
142           id  => 1,
143           num => {
144            '<=' => 20,
145            '>'  => 10,
146           },
147         },
148         stmt => " WHERE ( id = ? AND ( num <= ? AND num > ? ) )",
149         bind => [qw/1 20 10/],
150     },
151
152     {
153         where => { foo => {-not_like => [7,8,9]},
154                    fum => {'like' => [qw/a b/]},
155                    nix => {'between' => [100,200] },
156                    nox => {'not between' => [150,160] },
157                    wix => {'in' => [qw/zz yy/]},
158                    wux => {'not_in'  => [qw/30 40/]}
159                  },
160         stmt => " WHERE ( ( ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) ) AND ( ( fum LIKE ? ) OR ( fum LIKE ? ) ) AND ( nix BETWEEN ? AND ? ) AND ( nox NOT BETWEEN ? AND ? ) AND wix IN ( ?, ? ) AND wux NOT IN ( ?, ? ) )",
161         bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'],
162         warns => qr/\QA multi-element arrayref as an argument to the inequality op 'NOT LIKE' is technically equivalent to an always-true 1=1/,
163     },
164
165     {
166         where => {
167             bar => {'!=' => []},
168         },
169         stmt => " WHERE ( 1=1 )",
170         bind => [],
171     },
172
173     {
174         where => {
175             id  => [],
176         },
177         stmt => " WHERE ( 0=1 )",
178         bind => [],
179     },
180
181
182     {
183         where => {
184             foo => \["IN (?, ?)", 22, 33],
185             bar => [-and =>  \["> ?", 44], \["< ?", 55] ],
186         },
187         stmt => " WHERE ( (bar > ? AND bar < ?) AND foo IN (?, ?) )",
188         bind => [44, 55, 22, 33],
189     },
190
191     {
192         where => {
193           -and => [
194             user => 'nwiger',
195             [
196               -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
197               -or => { workhrs => {'<', 50}, geo => 'EURO' },
198             ],
199           ],
200         },
201         stmt => "WHERE ( user = ? AND (
202                ( workhrs > ? AND geo = ? )
203             OR ( geo = ? OR workhrs < ? )
204           ) )",
205         bind => [qw/nwiger 20 ASIA EURO 50/],
206     },
207
208    {
209        where => { -and => [{}, { 'me.id' => '1'}] },
210        stmt => " WHERE ( ( me.id = ? ) )",
211        bind => [ 1 ],
212    },
213
214    {
215        where => { foo => $not_stringifiable, },
216        stmt => " WHERE ( foo = ? )",
217        bind => [ $not_stringifiable ],
218    },
219
220    {
221        where => \[ 'foo = ?','bar' ],
222        stmt => " WHERE (foo = ?)",
223        bind => [ "bar" ],
224    },
225
226    {
227        where => [ \[ 'foo = ?','bar' ] ],
228        stmt => " WHERE (foo = ?)",
229        bind => [ "bar" ],
230    },
231
232    {
233        where => { -bool => \'function(x)' },
234        stmt => " WHERE function(x)",
235        bind => [],
236    },
237
238    {
239        where => { -bool => 'foo' },
240        stmt => " WHERE foo",
241        bind => [],
242    },
243
244    {
245        where => { -and => [-bool => 'foo', -bool => 'bar'] },
246        stmt => " WHERE foo AND bar",
247        bind => [],
248    },
249
250    {
251        where => { -or => [-bool => 'foo', -bool => 'bar'] },
252        stmt => " WHERE foo OR bar",
253        bind => [],
254    },
255
256    {
257        where => { -not_bool => \'function(x)' },
258        stmt => " WHERE NOT function(x)",
259        bind => [],
260    },
261
262    {
263        where => { -not_bool => 'foo' },
264        stmt => " WHERE NOT foo",
265        bind => [],
266    },
267
268    {
269        where => { -and => [-not_bool => 'foo', -not_bool => 'bar'] },
270        stmt => " WHERE (NOT foo) AND (NOT bar)",
271        bind => [],
272    },
273
274    {
275        where => { -or => [-not_bool => 'foo', -not_bool => 'bar'] },
276        stmt => " WHERE (NOT foo) OR (NOT bar)",
277        bind => [],
278    },
279
280    {
281        where => { -bool => \['function(?)', 20]  },
282        stmt => " WHERE function(?)",
283        bind => [20],
284    },
285
286    {
287        where => { -not_bool => \['function(?)', 20]  },
288        stmt => " WHERE NOT function(?)",
289        bind => [20],
290    },
291
292    {
293        where => { -bool => { a => 1, b => 2}  },
294        stmt => " WHERE a = ? AND b = ?",
295        bind => [1, 2],
296    },
297
298    {
299        where => { -bool => [ a => 1, b => 2] },
300        stmt => " WHERE a = ? OR b = ?",
301        bind => [1, 2],
302    },
303
304    {
305        where => { -not_bool => { a => 1, b => 2}  },
306        stmt => " WHERE NOT (a = ? AND b = ?)",
307        bind => [1, 2],
308    },
309
310    {
311        where => { -not_bool => [ a => 1, b => 2] },
312        stmt => " WHERE NOT ( a = ? OR b = ? )",
313        bind => [1, 2],
314    },
315
316 # Op against internal function
317    {
318        where => { bool1 => { '=' => { -not_bool => 'bool2' } } },
319        stmt => " WHERE ( bool1 = (NOT bool2) )",
320        bind => [],
321    },
322    {
323        where => { -not_bool => { -not_bool => { -not_bool => 'bool2' } } },
324        stmt => " WHERE ( NOT ( NOT ( NOT bool2 ) ) )",
325        bind => [],
326    },
327
328 # Op against random functions (these two are oracle-specific)
329    {
330        where => { timestamp => { '!=' => { -trunc => { -year => \'sysdate' } } } },
331        stmt => " WHERE ( timestamp != TRUNC (YEAR sysdate) )",
332        bind => [],
333    },
334    {
335        where => { timestamp => { '>=' => { -to_date => '2009-12-21 00:00:00' } } },
336        stmt => " WHERE ( timestamp >= TO_DATE ? )",
337        bind => ['2009-12-21 00:00:00'],
338    },
339
340 # Legacy function specs
341    {
342        where => { ip => {'<<=' => '127.0.0.1/32' } },
343        stmt => "WHERE ( ip <<= ? )",
344        bind => ['127.0.0.1/32'],
345    },
346    {
347        where => { foo => { 'GLOB' => '*str*' } },
348        stmt => " WHERE foo GLOB ? ",
349        bind => [ '*str*' ],
350    },
351    {
352        where => { foo => { 'REGEXP' => 'bar|baz' } },
353        stmt => " WHERE foo REGEXP ? ",
354        bind => [ 'bar|baz' ],
355    },
356
357 # Tests for -not
358 # Basic tests only
359     {
360         where => { -not => { a => 1 } },
361         stmt  => " WHERE ( (NOT a = ?) ) ",
362         bind => [ 1 ],
363     },
364     {
365         where => { a => 1, -not => { b => 2 } },
366         stmt  => " WHERE ( ( (NOT b = ?) AND a = ? ) ) ",
367         bind => [ 2, 1 ],
368     },
369     {
370         where => { -not => { a => 1, b => 2, c => 3 } },
371         stmt  => " WHERE ( (NOT ( a = ? AND b = ? AND c = ? )) ) ",
372         bind => [ 1, 2, 3 ],
373     },
374     {
375         where => { -not => [ a => 1, b => 2, c => 3 ] },
376         stmt  => " WHERE ( (NOT ( a = ? OR b = ? OR c = ? )) ) ",
377         bind => [ 1, 2, 3 ],
378     },
379     {
380         where => { -not => { c => 3, -not => { b => 2, -not => { a => 1 } } } },
381         stmt  => " WHERE ( (NOT ( (NOT ( (NOT a = ?) AND b = ? )) AND c = ? )) ) ",
382         bind => [ 1, 2, 3 ],
383     },
384     {
385         where => { -not => { -bool => 'c', -not => { -not_bool => 'b', -not => { a => 1 } } } },
386         stmt  => " WHERE ( (NOT ( c AND (NOT ( (NOT a = ?) AND (NOT b) )) )) ) ",
387         bind => [ 1 ],
388     },
389 );
390
391 for my $case (@handle_tests) {
392     my $sql = SQL::Abstract->new;
393     my ($stmt, @bind);
394     warnings_exist {
395       ($stmt, @bind) = $sql->where($case->{where}, $case->{order});
396     } $case->{warns} || [];
397
398     is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind})
399       || diag_where ( $case->{where} );
400 }
401
402 done_testing;