1#!/usr/bin/perl 2 3use strict; 4use warnings; 5use Test::More; 6use Test::Exception; 7use SQL::Abstract::Test import => ['is_same_sql_bind']; 8 9use Data::Dumper; 10use SQL::Abstract; 11 12# Make sure to test the examples, since having them break is somewhat 13# embarrassing. :-( 14 15my $not_stringifiable = bless {}, 'SQLA::NotStringifiable'; 16 17my @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 status => 'completed', 33 user => 'nwiger', 34 ], 35 stmt => " WHERE ( status = ? OR user = ? )", 36 bind => [qw/completed nwiger/], 37 }, 38 39 { 40 where => { 41 user => 'nwiger', 42 status => 'completed' 43 }, 44 order => [qw/ticket/], 45 stmt => " WHERE ( status = ? AND user = ? ) ORDER BY ticket", 46 bind => [qw/completed nwiger/], 47 }, 48 49 { 50 where => { 51 user => 'nwiger', 52 status => { '!=', 'completed' } 53 }, 54 order => [qw/ticket/], 55 stmt => " WHERE ( status != ? AND user = ? ) ORDER BY ticket", 56 bind => [qw/completed nwiger/], 57 }, 58 59 { 60 where => { 61 status => 'completed', 62 reportid => { 'in', [567, 2335, 2] } 63 }, 64 order => [], 65 stmt => " WHERE ( reportid IN ( ?, ?, ? ) AND status = ? )", 66 bind => [qw/567 2335 2 completed/], 67 }, 68 69 { 70 where => { 71 status => 'completed', 72 reportid => { 'not in', [567, 2335, 2] } 73 }, 74 order => [], 75 stmt => " WHERE ( reportid NOT IN ( ?, ?, ? ) AND status = ? )", 76 bind => [qw/567 2335 2 completed/], 77 }, 78 79 { 80 where => { 81 status => 'completed', 82 completion_date => { 'between', ['2002-10-01', '2003-02-06'] }, 83 }, 84 order => \'ticket, requestor', 85#LDNOTE: modified parentheses 86# 87# acked by RIBASUSHI 88 stmt => "WHERE ( ( completion_date BETWEEN ? AND ? ) AND status = ? ) ORDER BY ticket, requestor", 89 bind => [qw/2002-10-01 2003-02-06 completed/], 90 }, 91 92 { 93 where => [ 94 { 95 user => 'nwiger', 96 status => { 'in', ['pending', 'dispatched'] }, 97 }, 98 { 99 user => 'robot', 100 status => 'unassigned', 101 }, 102 ], 103 order => [], 104 stmt => " WHERE ( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )", 105 bind => [qw/pending dispatched nwiger unassigned robot/], 106 }, 107 108 { 109 where => { 110 priority => [ {'>', 3}, {'<', 1} ], 111 requestor => \'is not null', 112 }, 113 order => 'priority', 114 stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null ) ORDER BY priority", 115 bind => [qw/3 1/], 116 }, 117 118 { 119 where => { 120 requestor => { '!=', ['-and', undef, ''] }, 121 }, 122 stmt => " WHERE ( requestor IS NOT NULL AND requestor != ? )", 123 bind => [''], 124 }, 125 126 { 127 where => { 128 priority => [ {'>', 3}, {'<', 1} ], 129 requestor => { '!=', undef }, 130 }, 131 order => [qw/a b c d e f g/], 132 stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )" 133 . " ORDER BY a, b, c, d, e, f, g", 134 bind => [qw/3 1/], 135 }, 136 137 { 138 where => { 139 priority => { 'between', [1, 3] }, 140 requestor => { 'like', undef }, 141 }, 142 order => \'requestor, ticket', 143#LDNOTE: modified parentheses 144# 145# acked by RIBASUSHI 146 stmt => " WHERE ( ( priority BETWEEN ? AND ? ) AND requestor IS NULL ) ORDER BY requestor, ticket", 147 bind => [qw/1 3/], 148 }, 149 150 151 { 152 where => { 153 id => 1, 154 num => { 155 '<=' => 20, 156 '>' => 10, 157 }, 158 }, 159# LDNOTE : modified test below, just parentheses differ 160# 161# acked by RIBASUSHI 162 stmt => " WHERE ( id = ? AND ( num <= ? AND num > ? ) )", 163 bind => [qw/1 20 10/], 164 }, 165 166 { 167# LDNOTE 23.03.09 : modified test below, just parentheses differ 168 where => { foo => {-not_like => [7,8,9]}, 169 fum => {'like' => [qw/a b/]}, 170 nix => {'between' => [100,200] }, 171 nox => {'not between' => [150,160] }, 172 wix => {'in' => [qw/zz yy/]}, 173 wux => {'not_in' => [qw/30 40/]} 174 }, 175 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 ( ?, ? ) )", 176 bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'], 177 }, 178 179 { 180 where => { 181 bar => {'!=' => []}, 182 }, 183 stmt => " WHERE ( 1=1 )", 184 bind => [], 185 }, 186 187 { 188 where => { 189 id => [], 190 }, 191 stmt => " WHERE ( 0=1 )", 192 bind => [], 193 }, 194 195 196 { 197 where => { 198 foo => \["IN (?, ?)", 22, 33], 199 bar => [-and => \["> ?", 44], \["< ?", 55] ], 200 }, 201 stmt => " WHERE ( (bar > ? AND bar < ?) AND foo IN (?, ?) )", 202 bind => [44, 55, 22, 33], 203 }, 204 { 205 where => { -and => [{}, { 'me.id' => '1'}] }, 206 stmt => " WHERE ( ( me.id = ? ) )", 207 bind => [ 1 ], 208 }, 209 210 { 211 where => { foo => $not_stringifiable, }, 212 stmt => " WHERE ( foo = ? )", 213 bind => [ $not_stringifiable ], 214 }, 215 216 { 217 where => \[ 'foo = ?','bar' ], 218 stmt => " WHERE (foo = ?)", 219 bind => [ "bar" ], 220 }, 221 222 { 223 where => [ \[ 'foo = ?','bar' ] ], 224 stmt => " WHERE (foo = ?)", 225 bind => [ "bar" ], 226 }, 227 228 { 229 where => { -bool => \'function(x)' }, 230 stmt => " WHERE function(x)", 231 bind => [], 232 }, 233 234 { 235 where => { -bool => 'foo' }, 236 stmt => " WHERE foo", 237 bind => [], 238 }, 239 240 { 241 where => { -and => [-bool => 'foo', -bool => 'bar'] }, 242 stmt => " WHERE foo AND bar", 243 bind => [], 244 }, 245 246 { 247 where => { -or => [-bool => 'foo', -bool => 'bar'] }, 248 stmt => " WHERE foo OR bar", 249 bind => [], 250 }, 251 252 { 253 where => { -not_bool => \'function(x)' }, 254 stmt => " WHERE NOT function(x)", 255 bind => [], 256 }, 257 258 { 259 where => { -not_bool => 'foo' }, 260 stmt => " WHERE NOT foo", 261 bind => [], 262 }, 263 264 { 265 where => { -and => [-not_bool => 'foo', -not_bool => 'bar'] }, 266 stmt => " WHERE (NOT foo) AND (NOT bar)", 267 bind => [], 268 }, 269 270 { 271 where => { -or => [-not_bool => 'foo', -not_bool => 'bar'] }, 272 stmt => " WHERE (NOT foo) OR (NOT bar)", 273 bind => [], 274 }, 275 276 { 277 where => { -bool => \['function(?)', 20] }, 278 stmt => " WHERE function(?)", 279 bind => [20], 280 }, 281 282 { 283 where => { -not_bool => \['function(?)', 20] }, 284 stmt => " WHERE NOT function(?)", 285 bind => [20], 286 }, 287 288 { 289 where => { -bool => { a => 1, b => 2} }, 290 stmt => " WHERE a = ? AND b = ?", 291 bind => [1, 2], 292 }, 293 294 { 295 where => { -bool => [ a => 1, b => 2] }, 296 stmt => " WHERE a = ? OR b = ?", 297 bind => [1, 2], 298 }, 299 300 { 301 where => { -not_bool => { a => 1, b => 2} }, 302 stmt => " WHERE NOT (a = ? AND b = ?)", 303 bind => [1, 2], 304 }, 305 306 { 307 where => { -not_bool => [ a => 1, b => 2] }, 308 stmt => " WHERE NOT ( a = ? OR b = ? )", 309 bind => [1, 2], 310 }, 311 312); 313 314plan tests => ( @handle_tests * 2 ) + 1; 315 316for my $case (@handle_tests) { 317 local $Data::Dumper::Terse = 1; 318 my $sql = SQL::Abstract->new; 319 my($stmt, @bind); 320 lives_ok (sub { 321 ($stmt, @bind) = $sql->where($case->{where}, $case->{order}); 322 is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind}) 323 || diag "Search term:\n" . Dumper $case->{where}; 324 }); 325} 326 327dies_ok { 328 my $sql = SQL::Abstract->new; 329 $sql->where({ foo => { '>=' => [] }},); 330}; 331