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