Commit | Line | Data |
41751122 |
1 | #!/usr/bin/perl |
32eab2da |
2 | |
3 | use strict; |
41751122 |
4 | use warnings; |
5 | use Test::More; |
8a68b5be |
6 | use Test::Exception; |
32eab2da |
7 | |
96449e8e |
8 | use FindBin; |
9 | use lib "$FindBin::Bin"; |
10 | use TestSqlAbstract; |
11 | |
12 | plan tests => 15; |
32eab2da |
13 | |
14 | use SQL::Abstract; |
15 | |
16 | # Make sure to test the examples, since having them break is somewhat |
17 | # embarrassing. :-( |
18 | |
19 | my @handle_tests = ( |
20 | { |
21 | where => { |
22 | requestor => 'inna', |
23 | worker => ['nwiger', 'rcwe', 'sfz'], |
24 | status => { '!=', 'completed' } |
25 | }, |
26 | order => [], |
27 | stmt => " WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR" |
28 | . " ( worker = ? ) OR ( worker = ? ) ) )", |
29 | bind => [qw/inna completed nwiger rcwe sfz/], |
30 | }, |
31 | |
32 | { |
33 | where => { |
34 | user => 'nwiger', |
35 | status => 'completed' |
36 | }, |
37 | order => [qw/ticket/], |
38 | stmt => " WHERE ( status = ? AND user = ? ) ORDER BY ticket", |
39 | bind => [qw/completed nwiger/], |
40 | }, |
41 | |
42 | { |
43 | where => { |
44 | user => 'nwiger', |
45 | status => { '!=', 'completed' } |
46 | }, |
47 | order => [qw/ticket/], |
48 | stmt => " WHERE ( status != ? AND user = ? ) ORDER BY ticket", |
49 | bind => [qw/completed nwiger/], |
50 | }, |
51 | |
52 | { |
53 | where => { |
54 | status => 'completed', |
55 | reportid => { 'in', [567, 2335, 2] } |
56 | }, |
57 | order => [], |
58 | stmt => " WHERE ( reportid IN ( ?, ?, ? ) AND status = ? )", |
59 | bind => [qw/567 2335 2 completed/], |
60 | }, |
61 | |
62 | { |
63 | where => { |
64 | status => 'completed', |
65 | reportid => { 'not in', [567, 2335, 2] } |
66 | }, |
67 | order => [], |
68 | stmt => " WHERE ( reportid NOT IN ( ?, ?, ? ) AND status = ? )", |
69 | bind => [qw/567 2335 2 completed/], |
70 | }, |
71 | |
72 | { |
73 | where => { |
74 | status => 'completed', |
75 | completion_date => { 'between', ['2002-10-01', '2003-02-06'] }, |
76 | }, |
77 | order => \'ticket, requestor', |
96449e8e |
78 | #LDNOTE: modified parentheses |
79 | # stmt => " WHERE ( completion_date BETWEEN ? AND ? AND status = ? ) ORDER BY ticket, requestor", |
80 | stmt => " WHERE ( ( completion_date BETWEEN ? AND ? ) AND status = ? ) ORDER BY ticket, requestor", |
32eab2da |
81 | bind => [qw/2002-10-01 2003-02-06 completed/], |
82 | }, |
83 | |
84 | { |
85 | where => [ |
86 | { |
87 | user => 'nwiger', |
88 | status => { 'in', ['pending', 'dispatched'] }, |
89 | }, |
90 | { |
91 | user => 'robot', |
92 | status => 'unassigned', |
93 | }, |
94 | ], |
95 | order => [], |
96 | stmt => " WHERE ( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )", |
97 | bind => [qw/pending dispatched nwiger unassigned robot/], |
98 | }, |
99 | |
100 | { |
101 | where => { |
102 | priority => [ {'>', 3}, {'<', 1} ], |
103 | requestor => \'is not null', |
104 | }, |
105 | order => 'priority', |
106 | stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null ) ORDER BY priority", |
107 | bind => [qw/3 1/], |
108 | }, |
109 | |
110 | { |
111 | where => { |
112 | priority => [ {'>', 3}, {'<', 1} ], |
113 | requestor => { '!=', undef }, |
114 | }, |
115 | order => [qw/a b c d e f g/], |
116 | stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )" |
117 | . " ORDER BY a, b, c, d, e, f, g", |
118 | bind => [qw/3 1/], |
119 | }, |
120 | |
121 | { |
122 | where => { |
123 | priority => { 'between', [1, 3] }, |
124 | requestor => { 'like', undef }, |
125 | }, |
126 | order => \'requestor, ticket', |
96449e8e |
127 | #LDNOTE: modified parentheses |
128 | # stmt => " WHERE ( priority BETWEEN ? AND ? AND requestor IS NULL ) ORDER BY requestor, ticket", |
129 | stmt => " WHERE ( ( priority BETWEEN ? AND ? ) AND requestor IS NULL ) ORDER BY requestor, ticket", |
32eab2da |
130 | bind => [qw/1 3/], |
131 | }, |
132 | |
133 | |
134 | { |
135 | where => { |
136 | id => 1, |
137 | num => { |
138 | '<=' => 20, |
139 | '>' => 10, |
140 | }, |
141 | }, |
96449e8e |
142 | # LDNOTE : modified test below, just parentheses differ |
143 | # stmt => " WHERE ( id = ? AND num <= ? AND num > ? )", |
144 | stmt => " WHERE ( id = ? AND ( num <= ? AND num > ? ) )", |
32eab2da |
145 | bind => [qw/1 20 10/], |
146 | }, |
147 | |
148 | { |
149 | where => { foo => {-not_like => [7,8,9]}, |
150 | fum => {'like' => [qw/a b/]}, |
151 | nix => {'between' => [100,200] }, |
152 | nox => {'not between' => [150,160] }, |
153 | wix => {'in' => [qw/zz yy/]}, |
154 | wux => {'not_in' => [qw/30 40/]} |
155 | }, |
96449e8e |
156 | # LDNOTE: modified parentheses for BETWEEN (trivial). |
157 | # Also modified the logic of "not_like" (severe, same reasons as #14 in 00where.t) |
158 | # 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 ( ?, ? ) )", |
159 | stmt => " WHERE ( ( foo NOT LIKE ? AND foo NOT LIKE ? AND 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 ( ?, ? ) )", |
32eab2da |
160 | bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'], |
161 | }, |
162 | |
8a68b5be |
163 | { |
164 | where => { |
165 | id => [], |
166 | bar => {'!=' => []}, |
167 | }, |
168 | stmt => " WHERE ( 1=1 AND 0=1 )", |
169 | bind => [], |
170 | }, |
171 | |
96449e8e |
172 | |
173 | { |
174 | where => { |
175 | foo => \["IN (?, ?)", 22, 33], |
176 | bar => [-and => \["> ?", 44], \["< ?", 55] ], |
177 | }, |
178 | stmt => " WHERE ( (bar > ? AND bar < ?) AND foo IN (?, ?) )", |
179 | bind => [44, 55, 22, 33], |
180 | }, |
181 | |
32eab2da |
182 | ); |
183 | |
8a68b5be |
184 | for my $case (@handle_tests) { |
32eab2da |
185 | my $sql = SQL::Abstract->new; |
8a68b5be |
186 | my($stmt, @bind) = $sql->where($case->{where}, $case->{order}); |
96449e8e |
187 | is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind}) |
32eab2da |
188 | } |
189 | |
8a68b5be |
190 | dies_ok { |
191 | my $sql = SQL::Abstract->new; |
192 | $sql->where({ foo => { '>=' => [] }},); |
193 | } |