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