5 use DBIx::Class::SQL::Abstract;
\r
7 # Make sure to test the examples, since having them break is somewhat
\r
10 my @handle_tests = (
\r
13 requestor => 'inna',
\r
14 worker => ['nwiger', 'rcwe', 'sfz'],
\r
15 status => { '!=', 'completed' }
\r
17 stmt => "( requestor = ? AND status != ? AND ( ( worker = ? ) OR"
\r
18 . " ( worker = ? ) OR ( worker = ? ) ) )",
\r
19 bind => [qw/inna completed nwiger rcwe sfz/],
\r
25 status => 'completed'
\r
27 stmt => "( status = ? AND user = ? )",
\r
28 bind => [qw/completed nwiger/],
\r
34 status => { '!=', 'completed' }
\r
36 stmt => "( status != ? AND user = ? )",
\r
37 bind => [qw/completed nwiger/],
\r
42 status => 'completed',
\r
43 reportid => { 'in', [567, 2335, 2] }
\r
45 stmt => "( reportid IN ( ?, ?, ? ) AND status = ? )",
\r
46 bind => [qw/567 2335 2 completed/],
\r
51 status => 'completed',
\r
52 reportid => { 'not in', [567, 2335, 2] }
\r
54 stmt => "( reportid NOT IN ( ?, ?, ? ) AND status = ? )",
\r
55 bind => [qw/567 2335 2 completed/],
\r
60 status => 'completed',
\r
61 completion_date => { 'between', ['2002-10-01', '2003-02-06'] },
\r
63 stmt => "( completion_date BETWEEN ? AND ? AND status = ? )",
\r
64 bind => [qw/2002-10-01 2003-02-06 completed/],
\r
71 status => { 'in', ['pending', 'dispatched'] },
\r
75 status => 'unassigned',
\r
78 stmt => "( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",
\r
79 bind => [qw/pending dispatched nwiger unassigned robot/],
\r
84 priority => [ {'>', 3}, {'<', 1} ],
\r
85 requestor => \'is not null',
\r
87 stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null )",
\r
93 priority => [ {'>', 3}, {'<', 1} ],
\r
94 requestor => { '!=', undef },
\r
96 stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )",
\r
102 priority => { 'between', [1, 3] },
\r
103 requestor => { 'like', undef },
\r
105 stmt => "( priority BETWEEN ? AND ? AND requestor IS NULL )",
\r
118 stmt => "( id = ? AND num <= ? AND num > ? )",
\r
119 bind => [qw/1 20 10/],
\r
123 where => { foo => {-not_like => [7,8,9]},
\r
124 fum => {'like' => [qw/a b/]},
\r
125 nix => {'between' => [100,200] },
\r
126 nox => {'not between' => [150,160] },
\r
127 wix => {'in' => [qw/zz yy/]},
\r
128 wux => {'not_in' => [qw/30 40/]}
\r
130 stmt => "( ( ( 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 ( ?, ? ) )",
\r
131 bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'],
\r
134 # a couple of the more complex tests from S::A 01generate.t that test -nest, etc.
\r
136 where => { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},
\r
137 -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ],
\r
138 yob => {'<', 1976} ] ] },
\r
139 stmt => "( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) ) AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )",
\r
140 bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)],
\r
144 where => [-maybe => {race => [-and => [qw(black white asian)]]},
\r
145 {-nest => {firsttime => [-or => {'=','yes'}, undef]}},
\r
146 [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ],
\r
147 stmt => "( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) ) OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )",
\r
148 bind => [qw(black white asian yes candace jugs canyon towers)],
\r
152 for (@handle_tests) {
\r
156 for (my $i=0; $i < 2; $i++) {
\r
157 my($stmt, @bind) = DBIx::Class::SQL::Abstract->_cond_resolve($_->{where}, {});
\r
159 is($stmt, $_->{stmt}, 'SQL ok');
\r
160 cmp_ok(@bind, '==', @{$_->{bind}}, 'bind vars ok');
\r