Commit | Line | Data |
b31e9bb7 |
1 | use Test::More;\r |
2 | \r |
3 | plan tests => 56;\r |
4 | \r |
5 | use DBIx::Class::SQL::Abstract;\r |
6 | \r |
7 | # Make sure to test the examples, since having them break is somewhat\r |
8 | # embarrassing. :-(\r |
9 | \r |
10 | my @handle_tests = (\r |
11 | {\r |
12 | where => {\r |
13 | requestor => 'inna',\r |
14 | worker => ['nwiger', 'rcwe', 'sfz'],\r |
15 | status => { '!=', 'completed' }\r |
16 | },\r |
17 | stmt => "( requestor = ? AND status != ? AND ( ( worker = ? ) OR"\r |
18 | . " ( worker = ? ) OR ( worker = ? ) ) )",\r |
19 | bind => [qw/inna completed nwiger rcwe sfz/],\r |
20 | },\r |
21 | \r |
22 | {\r |
23 | where => {\r |
24 | user => 'nwiger',\r |
25 | status => 'completed'\r |
26 | },\r |
27 | stmt => "( status = ? AND user = ? )",\r |
28 | bind => [qw/completed nwiger/],\r |
29 | },\r |
30 | \r |
31 | {\r |
32 | where => {\r |
33 | user => 'nwiger',\r |
34 | status => { '!=', 'completed' }\r |
35 | },\r |
36 | stmt => "( status != ? AND user = ? )",\r |
37 | bind => [qw/completed nwiger/],\r |
38 | },\r |
39 | \r |
40 | {\r |
41 | where => {\r |
42 | status => 'completed',\r |
43 | reportid => { 'in', [567, 2335, 2] }\r |
44 | },\r |
45 | stmt => "( reportid IN ( ?, ?, ? ) AND status = ? )",\r |
46 | bind => [qw/567 2335 2 completed/],\r |
47 | },\r |
48 | \r |
49 | {\r |
50 | where => {\r |
51 | status => 'completed',\r |
52 | reportid => { 'not in', [567, 2335, 2] }\r |
53 | },\r |
54 | stmt => "( reportid NOT IN ( ?, ?, ? ) AND status = ? )",\r |
55 | bind => [qw/567 2335 2 completed/],\r |
56 | },\r |
57 | \r |
58 | {\r |
59 | where => {\r |
60 | status => 'completed',\r |
61 | completion_date => { 'between', ['2002-10-01', '2003-02-06'] },\r |
62 | },\r |
63 | stmt => "( completion_date BETWEEN ? AND ? AND status = ? )",\r |
64 | bind => [qw/2002-10-01 2003-02-06 completed/],\r |
65 | },\r |
66 | \r |
67 | {\r |
68 | where => [\r |
69 | {\r |
70 | user => 'nwiger',\r |
71 | status => { 'in', ['pending', 'dispatched'] },\r |
72 | },\r |
73 | {\r |
74 | user => 'robot',\r |
75 | status => 'unassigned',\r |
76 | },\r |
77 | ],\r |
78 | stmt => "( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",\r |
79 | bind => [qw/pending dispatched nwiger unassigned robot/],\r |
80 | },\r |
81 | \r |
82 | {\r |
83 | where => { \r |
84 | priority => [ {'>', 3}, {'<', 1} ],\r |
85 | requestor => \'is not null',\r |
86 | },\r |
87 | stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null )",\r |
88 | bind => [qw/3 1/],\r |
89 | },\r |
90 | \r |
91 | {\r |
92 | where => { \r |
93 | priority => [ {'>', 3}, {'<', 1} ],\r |
94 | requestor => { '!=', undef }, \r |
95 | },\r |
96 | stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )",\r |
97 | bind => [qw/3 1/],\r |
98 | },\r |
99 | \r |
100 | {\r |
101 | where => { \r |
102 | priority => { 'between', [1, 3] },\r |
103 | requestor => { 'like', undef }, \r |
104 | },\r |
105 | stmt => "( priority BETWEEN ? AND ? AND requestor IS NULL )",\r |
106 | bind => [qw/1 3/],\r |
107 | },\r |
108 | \r |
109 | \r |
110 | {\r |
111 | where => { \r |
112 | id => 1,\r |
113 | num => {\r |
114 | '<=' => 20,\r |
115 | '>' => 10,\r |
116 | },\r |
117 | },\r |
118 | stmt => "( id = ? AND num <= ? AND num > ? )",\r |
119 | bind => [qw/1 20 10/],\r |
120 | },\r |
121 | \r |
122 | {\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 |
129 | },\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 |
132 | },\r |
133 | \r |
134 | # a couple of the more complex tests from S::A 01generate.t that test -nest, etc.\r |
135 | {\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 |
141 | },\r |
142 | \r |
143 | {\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 |
149 | }\r |
150 | );\r |
151 | \r |
152 | for (@handle_tests) {\r |
153 | local $" = ', '; \r |
154 | \r |
155 | # run twice\r |
156 | for (my $i=0; $i < 2; $i++) {\r |
157 | my($stmt, @bind) = DBIx::Class::SQL::Abstract->_cond_resolve($_->{where}, {});\r |
158 | \r |
159 | is($stmt, $_->{stmt}, 'SQL ok');\r |
160 | cmp_ok(@bind, '==', @{$_->{bind}}, 'bind vars ok');\r |
161 | }\r |
162 | }\r |
163 | \r |
164 | \r |