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