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