1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use Test::More;
6use Test::Exception;
7use SQL::Abstract::Test import => ['is_same_sql_bind'];
8
9use Data::Dumper;
10use SQL::Abstract;
11
12# Make sure to test the examples, since having them break is somewhat
13# embarrassing. :-(
14
15my $not_stringifiable = bless {}, 'SQLA::NotStringifiable';
16
17my @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);
313
314plan tests => ( @handle_tests * 2 ) + 1;
315
316for my $case (@handle_tests) {
317    local $Data::Dumper::Terse = 1;
318    my $sql = SQL::Abstract->new;
319    my($stmt, @bind);
320    lives_ok (sub { 
321      ($stmt, @bind) = $sql->where($case->{where}, $case->{order});
322      is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind})
323        || diag "Search term:\n" . Dumper $case->{where};
324    });
325}
326
327dies_ok {
328    my $sql = SQL::Abstract->new;
329    $sql->where({ foo => { '>=' => [] }},);
330};
331