7 use SQL::Abstract::Test qw/is_same_sql_bind/;
11 use_ok('SQL::Abstract');
13 #LDNOTE: renamed all "bind" into "where" because that's what they are
19 args => {logic => 'OR'},
20 # stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )'
21 # LDNOTE: modified the line above (changing the test suite!!!) because
22 # the test was not consistent with the doc: hashrefs should not be
23 # influenced by the current logic, they always mean 'AND'. So
24 # { a => 4, b => 0} should ALWAYS mean ( a = ? AND b = ? ).
25 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
30 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
34 args => {case => "upper"},
35 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
39 args => {case => "upper", cmp => "="},
40 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
44 args => {cmp => "=", logic => 'or'},
46 # stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )'
47 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
51 args => {cmp => "like"},
52 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )'
56 args => {logic => "or", cmp => "like"},
58 # stmt => 'SELECT * FROM test WHERE ( a LIKE ? OR b LIKE ? )'
59 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )'
63 args => {case => "lower"},
64 stmt => 'select * from test where ( a = ? and b = ? )'
68 args => {case => "lower", cmp => "="},
69 stmt => 'select * from test where ( a = ? and b = ? )'
73 args => {case => "lower", cmp => "like"},
74 stmt => 'select * from test where ( a like ? and b like ? )'
78 args => {case => "lower", convert => "lower", cmp => "like"},
79 stmt => 'select * from test where ( lower(a) like lower(?) and lower(b) like lower(?) )'
83 args => {convert => "Round"},
84 stmt => 'SELECT * FROM test WHERE ( ROUND(a) = ROUND(?) AND ROUND(b) = ROUND(?) )',
88 args => {convert => "lower"},
89 stmt => 'SELECT * FROM test WHERE ( ( LOWER(ticket) = LOWER(?) ) OR ( LOWER(hostname) = LOWER(?) ) OR ( LOWER(taco) = LOWER(?) ) OR ( LOWER(salami) = LOWER(?) ) )',
90 where => [ { ticket => 11 }, { hostname => 11 }, { taco => 'salad' }, { salami => 'punch' } ],
94 args => {convert => "upper"},
95 # LDNOTE : modified the test below, because modified the semantics
96 # of "e => { '!=', [qw(f g)] }" : generating "e != 'f' OR e != 'g'"
97 # is nonsense (will always be true whatever the value of e). Since
98 # this is a 'negative' operator, we must apply the Morgan laws and
99 # interpret it as "e != 'f' AND e != 'g'" (and actually the user
100 # should rather write "e => {-not_in => [qw/f g/]}".
102 # 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(?) ) ) )',
103 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(?) ) AND ( UPPER(e) != UPPER(?) ) ) AND UPPER(q) NOT IN ( UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?) ) ) )',
104 where => [ { ticket => [11, 12, 13],
105 hostname => { in => ['ntf', 'avd', 'bvd', '123'] } },
106 { tack => { between => [qw/tick tock/] } },
108 e => { '!=', [qw(f g)] },
109 q => { 'not in', [14..20] } } ],
113 for (@handle_tests) {
115 #print "creating a handle with args ($_->{args}): ";
116 my $sql = SQL::Abstract->new($_->{args});
117 my $where = $_->{where} || { a => 4, b => 0};
118 my($stmt, @bind) = $sql->select('test', '*', $where);
120 # LDNOTE: this original test suite from NWIGER did no comparisons
121 # on @bind values, just checking if @bind is nonempty.
122 # So here we just fake a [1] bind value for the comparison.
123 is_same_sql_bind($stmt, [@bind ? 1 : 0], $_->{stmt}, [1]);