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