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