Commit | Line | Data |
41751122 |
1 | #!/usr/bin/perl |
32eab2da |
2 | |
3 | use strict; |
41751122 |
4 | use warnings; |
5 | use Test::More; |
32eab2da |
6 | |
32eab2da |
7 | |
41751122 |
8 | plan tests => 15; |
9 | |
10 | use_ok('SQL::Abstract'); |
32eab2da |
11 | |
12 | my @handle_tests = ( |
13 | #1 |
14 | { |
15 | args => {logic => 'OR'}, |
16 | stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )' |
17 | }, |
18 | #2 |
19 | { |
20 | args => {}, |
21 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
22 | }, |
23 | #3 |
24 | { |
25 | args => {case => "upper"}, |
26 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
27 | }, |
28 | #4 |
29 | { |
30 | args => {case => "upper", cmp => "="}, |
31 | stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )' |
32 | }, |
33 | #5 |
34 | { |
35 | args => {cmp => "=", logic => 'or'}, |
36 | stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )' |
37 | }, |
38 | #6 |
39 | { |
40 | args => {cmp => "like"}, |
41 | stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )' |
42 | }, |
43 | #7 |
44 | { |
45 | args => {logic => "or", cmp => "like"}, |
46 | stmt => 'SELECT * FROM test WHERE ( a LIKE ? OR b LIKE ? )' |
47 | }, |
48 | #8 |
49 | { |
50 | args => {case => "lower"}, |
51 | stmt => 'select * from test where ( a = ? and b = ? )' |
52 | }, |
53 | #9 |
54 | { |
55 | args => {case => "lower", cmp => "="}, |
56 | stmt => 'select * from test where ( a = ? and b = ? )' |
57 | }, |
58 | #10 |
59 | { |
60 | args => {case => "lower", cmp => "like"}, |
61 | stmt => 'select * from test where ( a like ? and b like ? )' |
62 | }, |
63 | #11 |
64 | { |
65 | args => {case => "lower", convert => "lower", cmp => "like"}, |
66 | stmt => 'select * from test where ( lower(a) like lower(?) and lower(b) like lower(?) )' |
67 | }, |
68 | #12 |
69 | { |
70 | args => {convert => "Round"}, |
71 | stmt => 'SELECT * FROM test WHERE ( ROUND(a) = ROUND(?) AND ROUND(b) = ROUND(?) )', |
72 | }, |
73 | #13 |
74 | { |
75 | args => {convert => "lower"}, |
76 | stmt => 'SELECT * FROM test WHERE ( ( LOWER(ticket) = LOWER(?) ) OR ( LOWER(hostname) = LOWER(?) ) OR ( LOWER(taco) = LOWER(?) ) OR ( LOWER(salami) = LOWER(?) ) )', |
77 | bind => [ { ticket => 11 }, { hostname => 11 }, { taco => 'salad' }, { salami => 'punch' } ], |
78 | }, |
79 | #14 |
80 | { |
81 | args => {convert => "upper"}, |
82 | 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(?) ) ) )', |
83 | bind => [ { ticket => [11, 12, 13], hostname => { in => ['ntf', 'avd', 'bvd', '123'] } }, |
84 | { tack => { between => [qw/tick tock/] } }, |
85 | { a => [qw/b c d/], e => { '!=', [qw(f g)] }, q => { 'not in', [14..20] } } ], |
86 | }, |
87 | ); |
88 | |
89 | for (@handle_tests) { |
90 | local $" = ', '; |
91 | #print "creating a handle with args ($_->{args}): "; |
92 | my $sql = SQL::Abstract->new($_->{args}); |
93 | my $bind = $_->{bind} || { a => 4, b => 0}; |
94 | my($stmt, @bind) = $sql->select('test', '*', $bind); |
41751122 |
95 | ok($stmt eq $_->{stmt} && @bind); |
32eab2da |
96 | } |
97 | |
98 | |