1#!/usr/bin/perl 2 3use strict; 4use warnings; 5use Test::More; 6use Test::Warn; 7use Test::Exception; 8 9use SQL::Abstract::Test import => ['is_same_sql_bind']; 10 11use SQL::Abstract; 12 13my @tests = ( 14 #1 15 { 16 func => 'select', 17 args => ['test', '*'], 18 stmt => 'SELECT * FROM test', 19 stmt_q => 'SELECT * FROM `test`', 20 bind => [] 21 }, 22 #2 23 { 24 func => 'select', 25 args => ['test', [qw(one two three)]], 26 stmt => 'SELECT one, two, three FROM test', 27 stmt_q => 'SELECT `one`, `two`, `three` FROM `test`', 28 bind => [] 29 }, 30 #3 31 { 32 func => 'select', 33 args => ['test', '*', { a => 0 }, [qw/boom bada bing/]], 34 stmt => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY boom, bada, bing', 35 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `boom`, `bada`, `bing`', 36 bind => [0] 37 }, 38 #4 39 { 40 func => 'select', 41 args => ['test', '*', [ { a => 5 }, { b => 6 } ]], 42 stmt => 'SELECT * FROM test WHERE ( ( a = ? ) OR ( b = ? ) )', 43 stmt_q => 'SELECT * FROM `test` WHERE ( ( `a` = ? ) OR ( `b` = ? ) )', 44 bind => [5,6] 45 }, 46 #5 47 { 48 func => 'select', 49 args => ['test', '*', undef, ['id']], 50 stmt => 'SELECT * FROM test ORDER BY id', 51 stmt_q => 'SELECT * FROM `test` ORDER BY `id`', 52 bind => [] 53 }, 54 #6 55 { 56 func => 'select', 57 args => ['test', '*', { a => 'boom' } , ['id']], 58 stmt => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY id', 59 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `id`', 60 bind => ['boom'] 61 }, 62 #7 63 { 64 func => 'select', 65 args => ['test', '*', { a => ['boom', 'bang'] }], 66 stmt => 'SELECT * FROM test WHERE ( ( ( a = ? ) OR ( a = ? ) ) )', 67 stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `a` = ? ) OR ( `a` = ? ) ) )', 68 bind => ['boom', 'bang'] 69 }, 70 #8 71 { 72 func => 'select', 73 args => [[qw/test1 test2/], '*', { 'test1.a' => { 'In', ['boom', 'bang'] } }], 74 stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a IN ( ?, ? ) )', 75 stmt_q => 'SELECT * FROM `test1`, `test2` WHERE ( `test1`.`a` IN ( ?, ? ) )', 76 bind => ['boom', 'bang'] 77 }, 78 #9 79 { 80 func => 'select', 81 args => ['test', '*', { a => { 'between', ['boom', 'bang'] } }], 82 stmt => 'SELECT * FROM test WHERE ( a BETWEEN ? AND ? )', 83 stmt_q => 'SELECT * FROM `test` WHERE ( `a` BETWEEN ? AND ? )', 84 bind => ['boom', 'bang'] 85 }, 86 #10 87 { 88 func => 'select', 89 args => ['test', '*', { a => { '!=', 'boom' } }], 90 stmt => 'SELECT * FROM test WHERE ( a != ? )', 91 stmt_q => 'SELECT * FROM `test` WHERE ( `a` != ? )', 92 bind => ['boom'] 93 }, 94 #11 95 { 96 func => 'update', 97 args => ['test', {a => 'boom'}, {a => undef}], 98 stmt => 'UPDATE test SET a = ? WHERE ( a IS NULL )', 99 stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` IS NULL )', 100 bind => ['boom'] 101 }, 102 #12 103 { 104 func => 'update', 105 args => ['test', {a => 'boom'}, { a => {'!=', "bang" }} ], 106 stmt => 'UPDATE test SET a = ? WHERE ( a != ? )', 107 stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` != ? )', 108 bind => ['boom', 'bang'] 109 }, 110 #13 111 { 112 func => 'update', 113 args => ['test', {'a-funny-flavored-candy' => 'yummy', b => 'oops'}, { a42 => "bang" }], 114 stmt => 'UPDATE test SET a-funny-flavored-candy = ?, b = ? WHERE ( a42 = ? )', 115 stmt_q => 'UPDATE `test` SET `a-funny-flavored-candy` = ?, `b` = ? WHERE ( `a42` = ? )', 116 bind => ['yummy', 'oops', 'bang'] 117 }, 118 #14 119 { 120 func => 'delete', 121 args => ['test', {requestor => undef}], 122 stmt => 'DELETE FROM test WHERE ( requestor IS NULL )', 123 stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL )', 124 bind => [] 125 }, 126 #15 127 { 128 func => 'delete', 129 args => [[qw/test1 test2 test3/], 130 { 'test1.field' => \'!= test2.field', 131 user => {'!=','nwiger'} }, 132 ], 133 stmt => 'DELETE FROM test1, test2, test3 WHERE ( test1.field != test2.field AND user != ? )', 134 stmt_q => 'DELETE FROM `test1`, `test2`, `test3` WHERE ( `test1`.`field` != test2.field AND `user` != ? )', # test2.field is a literal value, cannnot be quoted. 135 bind => ['nwiger'] 136 }, 137 #16 138 { 139 func => 'insert', 140 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}], 141 stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)', 142 stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)', 143 bind => [qw/1 2 3 4 5/], 144 }, 145 #17 146 { 147 func => 'insert', 148 args => ['test', [qw/1 2 3 4 5/]], 149 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?)', 150 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?)', 151 bind => [qw/1 2 3 4 5/], 152 }, 153 #18 154 { 155 func => 'insert', 156 args => ['test', [qw/1 2 3 4 5/, undef]], 157 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?, ?)', 158 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?, ?)', 159 bind => [qw/1 2 3 4 5/, undef], 160 }, 161 #19 162 { 163 func => 'update', 164 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}], 165 stmt => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ?', 166 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ?', 167 bind => [qw/1 2 3 4 5/], 168 }, 169 #20 170 { 171 func => 'update', 172 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}, {a => {'in', [1..5]}}], 173 stmt => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ? WHERE ( a IN ( ?, ?, ?, ?, ? ) )', 174 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ? WHERE ( `a` IN ( ?, ?, ?, ?, ? ) )', 175 bind => [qw/1 2 3 4 5 1 2 3 4 5/], 176 }, 177 #21 178 { 179 func => 'update', 180 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}], 181 stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )', 182 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )', 183 bind => [qw(1 02/02/02 1 2)], 184 }, 185 #22 186 { 187 func => 'insert', 188 args => ['test.table', {high_limit => \'max(all_limits)', low_limit => 4} ], 189 stmt => 'INSERT INTO test.table (high_limit, low_limit) VALUES (max(all_limits), ?)', 190 stmt_q => 'INSERT INTO `test`.`table` (`high_limit`, `low_limit`) VALUES (max(all_limits), ?)', 191 bind => ['4'], 192 }, 193 { 194 func => 'insert', 195 args => ['test.table', [ \'max(all_limits)', 4 ] ], 196 stmt => 'INSERT INTO test.table VALUES (max(all_limits), ?)', 197 stmt_q => 'INSERT INTO `test`.`table` VALUES (max(all_limits), ?)', 198 bind => ['4'], 199 }, 200 #23 201 { 202 func => 'insert', 203 new => {bindtype => 'columns'}, 204 args => ['test.table', {one => 2, three => 4, five => 6} ], 205 stmt => 'INSERT INTO test.table (five, one, three) VALUES (?, ?, ?)', 206 stmt_q => 'INSERT INTO `test`.`table` (`five`, `one`, `three`) VALUES (?, ?, ?)', 207 bind => [['five', 6], ['one', 2], ['three', 4]], # alpha order, man... 208 }, 209 #24 210 { 211 func => 'select', 212 new => {bindtype => 'columns', case => 'lower'}, 213 args => ['test.table', [qw/one two three/], {one => 2, three => 4, five => 6} ], 214 stmt => 'select one, two, three from test.table where ( five = ? and one = ? and three = ? )', 215 stmt_q => 'select `one`, `two`, `three` from `test`.`table` where ( `five` = ? and `one` = ? and `three` = ? )', 216 bind => [['five', 6], ['one', 2], ['three', 4]], # alpha order, man... 217 }, 218 #25 219 { 220 func => 'update', 221 new => {bindtype => 'columns', cmp => 'like'}, 222 args => ['testin.table2', {One => 22, Three => 44, FIVE => 66}, 223 {Beer => 'is', Yummy => '%YES%', IT => ['IS','REALLY','GOOD']}], 224 stmt => 'UPDATE testin.table2 SET FIVE = ?, One = ?, Three = ? WHERE ' 225 . '( Beer LIKE ? AND ( ( IT LIKE ? ) OR ( IT LIKE ? ) OR ( IT LIKE ? ) ) AND Yummy LIKE ? )', 226 stmt_q => 'UPDATE `testin`.`table2` SET `FIVE` = ?, `One` = ?, `Three` = ? WHERE ' 227 . '( `Beer` LIKE ? AND ( ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) ) AND `Yummy` LIKE ? )', 228 bind => [['FIVE', 66], ['One', 22], ['Three', 44], ['Beer','is'], 229 ['IT','IS'], ['IT','REALLY'], ['IT','GOOD'], ['Yummy','%YES%']], 230 }, 231 #26 232 { 233 func => 'select', 234 args => ['test', '*', {priority => [ -and => {'!=', 2}, {'!=', 1} ]}], 235 stmt => 'SELECT * FROM test WHERE ( ( ( priority != ? ) AND ( priority != ? ) ) )', 236 stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `priority` != ? ) AND ( `priority` != ? ) ) )', 237 bind => [qw(2 1)], 238 }, 239 #27 240 { 241 func => 'select', 242 args => ['Yo Momma', '*', { user => 'nwiger', 243 -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ] }], 244 stmt => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? ) OR ( geo = ? ) ) AND user = ? )', 245 stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? ) OR ( `geo` = ? ) ) AND `user` = ? )', 246 bind => [qw(20 ASIA nwiger)], 247 }, 248 #28 249 { 250 func => 'update', 251 args => ['taco_punches', { one => 2, three => 4 }, 252 { bland => [ -and => {'!=', 'yes'}, {'!=', 'YES'} ], 253 tasty => { '!=', [qw(yes YES)] }, 254 -nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] }, 255 ], 256 stmt => 'UPDATE taco_punches SET one = ?, three = ? WHERE ( ( ( ( ( face = ? ) OR ( face IS NULL ) ) ) )' 257 . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) OR ( tasty != ? ) ) )', 258 stmt_q => 'UPDATE `taco_punches` SET `one` = ?, `three` = ? WHERE ( ( ( ( ( `face` = ? ) OR ( `face` IS NULL ) ) ) )' 259 . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) OR ( `tasty` != ? ) ) )', 260 bind => [qw(2 4 mr.happy yes YES yes YES)], 261 }, 262 #29 263 { 264 func => 'select', 265 args => ['jeff', '*', { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']}, 266 -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ], 267 yob => {'<', 1976} ] ] } ], 268 stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) )' 269 . ' AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )', 270 stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( ( ( `age` BETWEEN ? AND ? ) AND ( `age` != ? ) ) ) OR ( `yob` < ? ) ) ) )' 271 . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? )', 272 bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)] 273 }, 274 #30 275 { 276 func => 'update', 277# LDNOTE : removed the "-maybe", because we no longer admit unknown ops 278# 279# acked by RIBASUSHI 280# args => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]}, 281 args => ['fhole', {fpoles => 4}, [ {race => [-and => [qw(black white asian)]]}, 282 {-nest => {firsttime => [-or => {'=','yes'}, undef]}}, 283 [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ] ], 284 stmt => 'UPDATE fhole SET fpoles = ? WHERE ( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) )' 285 . ' OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )', 286 stmt_q => 'UPDATE `fhole` SET `fpoles` = ? WHERE ( ( ( ( ( ( ( `race` = ? ) OR ( `race` = ? ) OR ( `race` = ? ) ) ) ) ) )' 287 . ' OR ( ( ( ( `firsttime` = ? ) OR ( `firsttime` IS NULL ) ) ) ) OR ( ( ( `firstname` NOT LIKE ? ) ) AND ( `lastname` IN ( ?, ?, ? ) ) ) )', 288 bind => [qw(4 black white asian yes candace jugs canyon towers)] 289 }, 290 #31 291 { 292 func => 'insert', 293 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}], 294 stmt => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))', 295 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))', 296 bind => [qw(1 02/02/02)], 297 }, 298 #32 299 { 300 func => 'select', 301# LDNOTE: modified test below because we agreed with MST that literal SQL 302# should not automatically insert a '='; the user has to do it 303# 304# acked by MSTROUT 305# args => ['test', '*', { a => \["to_date(?, 'MM/DD/YY')", '02/02/02']}], 306 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}], 307 stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )}, 308 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )}, 309 bind => ['02/02/02'], 310 }, 311 #33 312 { 313 func => 'insert', 314 new => {array_datatypes => 1}, 315 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}], 316 stmt => 'INSERT INTO test (a, b) VALUES (?, ?)', 317 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)', 318 bind => [1, [1, 1, 2, 3, 5, 8]], 319 }, 320 #34 321 { 322 func => 'insert', 323 new => {bindtype => 'columns', array_datatypes => 1}, 324 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}], 325 stmt => 'INSERT INTO test (a, b) VALUES (?, ?)', 326 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)', 327 bind => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]], 328 }, 329 #35 330 { 331 func => 'update', 332 new => {array_datatypes => 1}, 333 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}], 334 stmt => 'UPDATE test SET a = ?, b = ?', 335 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?', 336 bind => [1, [1, 1, 2, 3, 5, 8]], 337 }, 338 #36 339 { 340 func => 'update', 341 new => {bindtype => 'columns', array_datatypes => 1}, 342 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}], 343 stmt => 'UPDATE test SET a = ?, b = ?', 344 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?', 345 bind => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]], 346 }, 347 #37 348 { 349 func => 'select', 350 args => ['test', '*', { a => {'>', \'1 + 1'}, b => 8 }], 351 stmt => 'SELECT * FROM test WHERE ( a > 1 + 1 AND b = ? )', 352 stmt_q => 'SELECT * FROM `test` WHERE ( `a` > 1 + 1 AND `b` = ? )', 353 bind => [8], 354 }, 355 #38 356 { 357 func => 'select', 358 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }], 359 stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )', 360 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )', 361 bind => ['02/02/02', 8], 362 }, 363 #39 364 { #TODO in SQLA >= 2.0 it will die instead (we kept this just because old SQLA passed it through) 365 func => 'insert', 366 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => { answer => 42 }}], 367 stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)', 368 stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)', 369 bind => [qw/1 2 3 4/, { answer => 42}], 370 warning_like => qr/HASH ref as bind value in insert is not supported/i, 371 }, 372 #40 373 { 374 func => 'update', 375 args => ['test', {a => 1, b => \["42"]}, {a => {'between', [1,2]}}], 376 stmt => 'UPDATE test SET a = ?, b = 42 WHERE ( a BETWEEN ? AND ? )', 377 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = 42 WHERE ( `a` BETWEEN ? AND ? )', 378 bind => [qw(1 1 2)], 379 }, 380 #41 381 { 382 func => 'insert', 383 args => ['test', {a => 1, b => \["42"]}], 384 stmt => 'INSERT INTO test (a, b) VALUES (?, 42)', 385 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, 42)', 386 bind => [qw(1)], 387 }, 388 #42 389 { 390 func => 'select', 391 args => ['test', '*', { a => \["= 42"], b => 1}], 392 stmt => q{SELECT * FROM test WHERE ( a = 42 ) AND (b = ? )}, 393 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = 42 ) AND ( `b` = ? )}, 394 bind => [qw(1)], 395 }, 396 #43 397 { 398 func => 'select', 399 args => ['test', '*', { a => {'<' => \["42"]}, b => 8 }], 400 stmt => 'SELECT * FROM test WHERE ( a < 42 AND b = ? )', 401 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < 42 AND `b` = ? )', 402 bind => [qw(8)], 403 }, 404 #44 405 { 406 func => 'insert', 407 new => {bindtype => 'columns'}, 408 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [dummy => '02/02/02']]}], 409 stmt => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))', 410 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))', 411 bind => [[a => '1'], [dummy => '02/02/02']], 412 }, 413 #45 414 { 415 func => 'update', 416 new => {bindtype => 'columns'}, 417 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [dummy => '02/02/02']]}, {a => {'between', [1,2]}}], 418 stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )', 419 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )', 420 bind => [[a => '1'], [dummy => '02/02/02'], [a => '1'], [a => '2']], 421 }, 422 #46 423 { 424 func => 'select', 425 new => {bindtype => 'columns'}, 426 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", [dummy => '02/02/02']]}], 427 stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )}, 428 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )}, 429 bind => [[dummy => '02/02/02']], 430 }, 431 #47 432 { 433 func => 'select', 434 new => {bindtype => 'columns'}, 435 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", [dummy => '02/02/02']]}, b => 8 }], 436 stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )', 437 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )', 438 bind => [[dummy => '02/02/02'], [b => 8]], 439 }, 440 #48 441 { 442 func => 'insert', 443 new => {bindtype => 'columns'}, 444 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}], 445 exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, 446 }, 447 #49 448 { 449 func => 'update', 450 new => {bindtype => 'columns'}, 451 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}], 452 exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, 453 }, 454 #49 455 { 456 func => 'select', 457 new => {bindtype => 'columns'}, 458 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}], 459 exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, 460 }, 461 #50 462 { 463 func => 'select', 464 new => {bindtype => 'columns'}, 465 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }], 466 exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, 467 }, 468 #51 469 { 470 func => 'select', 471 new => {bindtype => 'columns'}, 472 args => ['test', '*', { a => {-in => \["(SELECT d FROM to_date(?, 'MM/DD/YY') AS d)", [dummy => '02/02/02']]}, b => 8 }], 473 stmt => 'SELECT * FROM test WHERE ( a IN (SELECT d FROM to_date(?, \'MM/DD/YY\') AS d) AND b = ? )', 474 stmt_q => 'SELECT * FROM `test` WHERE ( `a` IN (SELECT d FROM to_date(?, \'MM/DD/YY\') AS d) AND `b` = ? )', 475 bind => [[dummy => '02/02/02'], [b => 8]], 476 }, 477 #52 478 { 479 func => 'select', 480 new => {bindtype => 'columns'}, 481 args => ['test', '*', { a => {-in => \["(SELECT d FROM to_date(?, 'MM/DD/YY') AS d)", '02/02/02']}, b => 8 }], 482 exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, 483 }, 484 #53 485 { 486 func => 'insert', 487 new => {bindtype => 'columns'}, 488 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']]}], 489 stmt => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))', 490 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))', 491 bind => [[a => '1'], [{dummy => 1} => '02/02/02']], 492 }, 493 #54 494 { 495 func => 'update', 496 new => {bindtype => 'columns'}, 497 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']]}, {a => {'between', [1,2]}}], 498 stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )', 499 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )', 500 bind => [[a => '1'], [{dummy => 1} => '02/02/02'], [a => '1'], [a => '2']], 501 }, 502 #55 503 { 504 func => 'select', 505 new => {bindtype => 'columns'}, 506 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']]}], 507 stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )}, 508 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )}, 509 bind => [[{dummy => 1} => '02/02/02']], 510 }, 511 #56 512 { 513 func => 'select', 514 new => {bindtype => 'columns'}, 515 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']]}, b => 8 }], 516 stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )', 517 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )', 518 bind => [[{dummy => 1} => '02/02/02'], [b => 8]], 519 }, 520 #57 521 { 522 func => 'select', 523 new => {bindtype => 'columns'}, 524 args => ['test', '*', { -or => [ -and => [ a => 'a', b => 'b' ], -and => [ c => 'c', d => 'd' ] ] }], 525 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? ) OR ( c = ? AND d = ? )', 526 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? AND `b` = ? ) OR ( `c` = ? AND `d` = ? )', 527 bind => [[a => 'a'], [b => 'b'], [ c => 'c'],[ d => 'd']], 528 }, 529 #58 530 { 531 func => 'select', 532 new => {bindtype => 'columns'}, 533 args => ['test', '*', [ { a => 1, b => 1}, [ a => 2, b => 2] ] ], 534 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? ) OR ( a = ? OR b = ? )', 535 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? AND `b` = ? ) OR ( `a` = ? OR `b` = ? )', 536 bind => [[a => 1], [b => 1], [ a => 2], [ b => 2]], 537 }, 538 #59 539 { 540 func => 'select', 541 new => {bindtype => 'columns'}, 542 args => ['test', '*', [ [ a => 1, b => 1], { a => 2, b => 2 } ] ], 543 stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? ) OR ( a = ? AND b = ? )', 544 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? OR `b` = ? ) OR ( `a` = ? AND `b` = ? )', 545 bind => [[a => 1], [b => 1], [ a => 2], [ b => 2]], 546 }, 547 #60 548 { 549 func => 'insert', 550 args => ['test', [qw/1 2 3 4 5/], { returning => 'id' }], 551 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id', 552 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id`', 553 bind => [qw/1 2 3 4 5/], 554 }, 555 #60 556 { 557 func => 'insert', 558 args => ['test', [qw/1 2 3 4 5/], { returning => 'id, foo, bar' }], 559 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar', 560 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id, foo, bar`', 561 bind => [qw/1 2 3 4 5/], 562 }, 563 #61 564 { 565 func => 'insert', 566 args => ['test', [qw/1 2 3 4 5/], { returning => [qw(id foo bar) ] }], 567 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar', 568 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id`, `foo`, `bar`', 569 bind => [qw/1 2 3 4 5/], 570 }, 571 #62 572 { 573 func => 'insert', 574 args => ['test', [qw/1 2 3 4 5/], { returning => \'id, foo, bar' }], 575 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar', 576 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar', 577 bind => [qw/1 2 3 4 5/], 578 }, 579 #63 580 { 581 func => 'insert', 582 args => ['test', [qw/1 2 3 4 5/], { returning => \'id' }], 583 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id', 584 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING id', 585 bind => [qw/1 2 3 4 5/], 586 }, 587); 588 589 590plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2 591 + scalar(grep { $_->{warning_like} } @tests) * 4; 592 593for (@tests) { 594 local $"=', '; 595 596 my $new = $_->{new} || {}; 597 $new->{debug} = $ENV{DEBUG} || 0; 598 599 # test without quoting labels 600 { 601 my $sql = SQL::Abstract->new(%$new); 602 603 my $func = $_->{func}; 604 my($stmt, @bind); 605 my $test = sub { 606 ($stmt, @bind) = $sql->$func(@{$_->{args}}) 607 }; 608 if ($_->{exception_like}) { 609 throws_ok { &$test } $_->{exception_like}, "throws the expected exception ($_->{exception_like})"; 610 } else { 611 if ($_->{warning_like}) { 612 warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})"; 613 } else { 614 &$test; 615 } 616 is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind}); 617 } 618 } 619 620 # test with quoted labels 621 { 622 my $sql_q = SQL::Abstract->new(%$new, quote_char => '`', name_sep => '.'); 623 624 my $func_q = $_->{func}; 625 my($stmt_q, @bind_q); 626 my $test = sub { 627 ($stmt_q, @bind_q) = $sql_q->$func_q(@{$_->{args}}) 628 }; 629 if ($_->{exception_like}) { 630 throws_ok { &$test } $_->{exception_like}, "throws the expected exception ($_->{exception_like})"; 631 } else { 632 if ($_->{warning_like}) { 633 warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})"; 634 } else { 635 &$test; 636 } 637 638 is_same_sql_bind($stmt_q, \@bind_q, $_->{stmt_q}, $_->{bind}); 639 } 640 } 641} 642