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