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