1#!/usr/bin/perl 2 3use strict; 4use warnings; 5use Test::More; 6 7use SQL::Abstract::Test import => ['is_same_sql_bind']; 8 9#LDNOTE: renamed all "bind" into "where" because that's what they are 10 11my @handle_tests = ( 12 #1 13 { 14 args => {logic => 'OR'}, 15# stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )' 16# LDNOTE: modified the line above (changing the test suite!!!) because 17# the test was not consistent with the doc: hashrefs should not be 18# influenced by the current logic, they always mean 'AND'. So 19# { a => 4, b => 0} should ALWAYS mean ( a = ? AND b = ? ). 20# 21# acked by RIBASUSHI 22 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' 23 }, 24 #2 25 { 26 args => {}, 27 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' 28 }, 29 #3 30 { 31 args => {case => "upper"}, 32 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' 33 }, 34 #4 35 { 36 args => {case => "upper", cmp => "="}, 37 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' 38 }, 39 #5 40 { 41 args => {cmp => "=", logic => 'or'}, 42# LDNOTE idem 43# stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )' 44# acked by RIBASUSHI 45 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' 46 }, 47 #6 48 { 49 args => {cmp => "like"}, 50 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )' 51 }, 52 #7 53 { 54 args => {logic => "or", cmp => "like"}, 55# LDNOTE idem 56# stmt => 'SELECT * FROM test WHERE ( a LIKE ? OR b LIKE ? )' 57# acked by RIBASUSHI 58 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )' 59 }, 60 #8 61 { 62 args => {case => "lower"}, 63 stmt => 'select * from test where ( a = ? and b = ? )' 64 }, 65 #9 66 { 67 args => {case => "lower", cmp => "="}, 68 stmt => 'select * from test where ( a = ? and b = ? )' 69 }, 70 #10 71 { 72 args => {case => "lower", cmp => "like"}, 73 stmt => 'select * from test where ( a like ? and b like ? )' 74 }, 75 #11 76 { 77 args => {case => "lower", convert => "lower", cmp => "like"}, 78 stmt => 'select * from test where ( lower(a) like lower(?) and lower(b) like lower(?) )' 79 }, 80 #12 81 { 82 args => {convert => "Round"}, 83 stmt => 'SELECT * FROM test WHERE ( ROUND(a) = ROUND(?) AND ROUND(b) = ROUND(?) )', 84 }, 85 #13 86 { 87 args => {convert => "lower"}, 88 stmt => 'SELECT * FROM test WHERE ( ( LOWER(ticket) = LOWER(?) ) OR ( LOWER(hostname) = LOWER(?) ) OR ( LOWER(taco) = LOWER(?) ) OR ( LOWER(salami) = LOWER(?) ) )', 89 where => [ { ticket => 11 }, { hostname => 11 }, { taco => 'salad' }, { salami => 'punch' } ], 90 }, 91 #14 92 { 93 args => {convert => "upper"}, 94 stmt => 'SELECT * FROM test WHERE ( ( UPPER(hostname) IN ( UPPER(?), UPPER(?), UPPER(?), UPPER(?) ) AND ( ( UPPER(ticket) = UPPER(?) ) OR ( UPPER(ticket) = UPPER(?) ) OR ( UPPER(ticket) = UPPER(?) ) ) ) OR ( UPPER(tack) BETWEEN UPPER(?) AND UPPER(?) ) OR ( ( ( UPPER(a) = UPPER(?) ) OR ( UPPER(a) = UPPER(?) ) OR ( UPPER(a) = UPPER(?) ) ) AND ( ( UPPER(e) != UPPER(?) ) OR ( UPPER(e) != UPPER(?) ) ) AND UPPER(q) NOT IN ( UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?) ) ) )', 95 where => [ { ticket => [11, 12, 13], 96 hostname => { in => ['ntf', 'avd', 'bvd', '123'] } }, 97 { tack => { between => [qw/tick tock/] } }, 98 { a => [qw/b c d/], 99 e => { '!=', [qw(f g)] }, 100 q => { 'not in', [14..20] } } ], 101 }, 102); 103 104 105plan tests => (1 + scalar(@handle_tests)); 106 107use_ok('SQL::Abstract'); 108 109for (@handle_tests) { 110 local $" = ', '; 111 #print "creating a handle with args ($_->{args}): "; 112 my $sql = SQL::Abstract->new($_->{args}); 113 my $where = $_->{where} || { a => 4, b => 0}; 114 my($stmt, @bind) = $sql->select('test', '*', $where); 115 116 # LDNOTE: this original test suite from NWIGER did no comparisons 117 # on @bind values, just checking if @bind is nonempty. 118 # So here we just fake a [1] bind value for the comparison. 119 is_same_sql_bind($stmt, [@bind ? 1 : 0], $_->{stmt}, [1]); 120} 121 122 123