Commit | Line | Data |
32eab2da |
1 | use strict; |
41751122 |
2 | use warnings; |
3 | use Test::More; |
3cdadcbe |
4 | use Test::Warn; |
32eab2da |
5 | |
46dc2f3e |
6 | use SQL::Abstract::Test import => ['is_same_sql']; |
7 | use SQL::Abstract; |
96449e8e |
8 | |
32eab2da |
9 | my @handle_tests = ( |
10 | #1 |
11 | { |
12 | args => {logic => 'OR'}, |
96449e8e |
13 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
32eab2da |
14 | }, |
15 | #2 |
16 | { |
17 | args => {}, |
18 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
19 | }, |
20 | #3 |
21 | { |
22 | args => {case => "upper"}, |
23 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
24 | }, |
25 | #4 |
26 | { |
27 | args => {case => "upper", cmp => "="}, |
28 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
29 | }, |
30 | #5 |
31 | { |
32 | args => {cmp => "=", logic => 'or'}, |
96449e8e |
33 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
32eab2da |
34 | }, |
35 | #6 |
36 | { |
37 | args => {cmp => "like"}, |
38 | stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )' |
39 | }, |
40 | #7 |
41 | { |
42 | args => {logic => "or", cmp => "like"}, |
96449e8e |
43 | stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )' |
32eab2da |
44 | }, |
45 | #8 |
46 | { |
47 | args => {case => "lower"}, |
48 | stmt => 'select * from test where ( a = ? and b = ? )' |
49 | }, |
50 | #9 |
51 | { |
52 | args => {case => "lower", cmp => "="}, |
53 | stmt => 'select * from test where ( a = ? and b = ? )' |
54 | }, |
55 | #10 |
56 | { |
57 | args => {case => "lower", cmp => "like"}, |
58 | stmt => 'select * from test where ( a like ? and b like ? )' |
59 | }, |
60 | #11 |
61 | { |
62 | args => {case => "lower", convert => "lower", cmp => "like"}, |
63 | stmt => 'select * from test where ( lower(a) like lower(?) and lower(b) like lower(?) )' |
64 | }, |
65 | #12 |
66 | { |
67 | args => {convert => "Round"}, |
68 | stmt => 'SELECT * FROM test WHERE ( ROUND(a) = ROUND(?) AND ROUND(b) = ROUND(?) )', |
69 | }, |
70 | #13 |
71 | { |
72 | args => {convert => "lower"}, |
73 | stmt => 'SELECT * FROM test WHERE ( ( LOWER(ticket) = LOWER(?) ) OR ( LOWER(hostname) = LOWER(?) ) OR ( LOWER(taco) = LOWER(?) ) OR ( LOWER(salami) = LOWER(?) ) )', |
96449e8e |
74 | where => [ { ticket => 11 }, { hostname => 11 }, { taco => 'salad' }, { salami => 'punch' } ], |
32eab2da |
75 | }, |
76 | #14 |
77 | { |
78 | args => {convert => "upper"}, |
e30faf88 |
79 | 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(?) ) ) )', |
428975b0 |
80 | where => [ { ticket => [11, 12, 13], |
96449e8e |
81 | hostname => { in => ['ntf', 'avd', 'bvd', '123'] } }, |
32eab2da |
82 | { tack => { between => [qw/tick tock/] } }, |
428975b0 |
83 | { a => [qw/b c d/], |
84 | e => { '!=', [qw(f g)] }, |
96449e8e |
85 | q => { 'not in', [14..20] } } ], |
3cdadcbe |
86 | warns => qr/\QA multi-element arrayref as an argument to the inequality op '!=' is technically equivalent to an always-true 1=1/, |
32eab2da |
87 | }, |
88 | ); |
89 | |
90 | for (@handle_tests) { |
46dc2f3e |
91 | my $sqla = SQL::Abstract->new($_->{args}); |
3cdadcbe |
92 | my $stmt; |
93 | warnings_exist { |
94 | $stmt = $sqla->select( |
95 | 'test', |
96 | '*', |
97 | $_->{where} || { a => 4, b => 0} |
98 | ); |
99 | } $_->{warns} || []; |
96449e8e |
100 | |
46dc2f3e |
101 | is_same_sql($stmt, $_->{stmt}); |
32eab2da |
102 | } |
103 | |
10e6c946 |
104 | done_testing; |