Commit | Line | Data |
cf02fc47 |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | use Test::More; |
6 | use Test::Exception; |
7 | use SQL::Abstract::Test import => ['is_same_sql_bind']; |
8 | |
9 | use Data::Dumper; |
10 | use SQL::Abstract; |
11 | |
cf02fc47 |
12 | my @in_between_tests = ( |
13 | { |
14 | where => { x => { -between => [1, 2] } }, |
15 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
16 | bind => [qw/1 2/], |
17 | test => '-between with two placeholders', |
18 | }, |
19 | { |
20 | where => { x => { -between => [\"1", 2] } }, |
21 | stmt => 'WHERE (x BETWEEN 1 AND ?)', |
22 | bind => [qw/2/], |
23 | test => '-between with one literal sql arg and one placeholder', |
24 | }, |
25 | { |
26 | where => { x => { -between => [1, \"2"] } }, |
27 | stmt => 'WHERE (x BETWEEN ? AND 2)', |
28 | bind => [qw/1/], |
29 | test => '-between with one placeholder and one literal sql arg', |
30 | }, |
31 | { |
32 | where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } }, |
33 | stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)', |
34 | bind => [], |
35 | test => '-between with two literal sql arguments', |
36 | }, |
37 | { |
4d8b3dc4 |
38 | where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } }, |
39 | stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)', |
40 | bind => [1, 0], |
41 | test => '-between with two literal sql arguments with bind', |
42 | }, |
43 | { |
cf02fc47 |
44 | where => { x => { -between => \['? AND ?', 1, 2] } }, |
45 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
46 | bind => [1,2], |
47 | test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])', |
48 | }, |
49 | { |
50 | where => { x => { -between => \["'something' AND ?", 2] } }, |
51 | stmt => "WHERE (x BETWEEN 'something' AND ?)", |
52 | bind => [2], |
53 | test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])', |
54 | }, |
55 | { |
56 | where => { x => { -between => \["? AND 'something'", 1] } }, |
57 | stmt => "WHERE (x BETWEEN ? AND 'something')", |
58 | bind => [1], |
59 | test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])', |
60 | }, |
61 | { |
4d8b3dc4 |
62 | where => { x => { -between => \"'this' AND 'that'" } }, |
cf02fc47 |
63 | stmt => "WHERE (x BETWEEN 'this' AND 'that')", |
64 | bind => [], |
4d8b3dc4 |
65 | test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")', |
cf02fc47 |
66 | }, |
e41c3bdd |
67 | { |
68 | where => { |
69 | start0 => { -between => [ 1, 2 ] }, |
70 | start1 => { -between => \["? AND ?", 1, 2] }, |
71 | start2 => { -between => \"lower(x) AND upper(y)" }, |
72 | start3 => { -between => [ |
73 | \"lower(x)", |
74 | \["upper(?)", 'stuff' ], |
75 | ] }, |
76 | }, |
77 | stmt => "WHERE ( |
78 | ( start0 BETWEEN ? AND ? ) |
79 | AND ( start1 BETWEEN ? AND ? ) |
80 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
81 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
82 | )", |
83 | bind => [1, 2, 1, 2, 'stuff'], |
84 | test => '-between POD test', |
85 | }, |
4a1f01a3 |
86 | |
87 | { |
88 | parenthesis_significant => 1, |
89 | where => { x => { -in => [ 1 .. 3] } }, |
90 | stmt => "WHERE ( x IN (?, ?, ?) )", |
91 | bind => [ 1 .. 3], |
92 | test => '-in with an array of scalars', |
93 | }, |
94 | { |
95 | parenthesis_significant => 1, |
e41c3bdd |
96 | where => { x => { -in => [] } }, |
97 | stmt => "WHERE ( 0=1 )", |
98 | bind => [], |
99 | test => '-in with an empty array', |
100 | }, |
101 | { |
102 | parenthesis_significant => 1, |
4a1f01a3 |
103 | where => { x => { -in => \'( 1,2,lower(y) )' } }, |
b9a4fdae |
104 | stmt => "WHERE ( x IN ( 1,2,lower(y) ) )", |
4a1f01a3 |
105 | bind => [], |
106 | test => '-in with a literal scalarref', |
107 | }, |
108 | { |
109 | parenthesis_significant => 1, |
110 | where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } }, |
b9a4fdae |
111 | stmt => "WHERE ( x IN ( ?,?,lower(y) ) )", # note that outer parens are opened even though literal was requested (RIBASUSHI) |
4a1f01a3 |
112 | bind => [1, 2], |
113 | test => '-in with a literal arrayrefref', |
114 | }, |
e41c3bdd |
115 | { |
116 | parenthesis_significant => 1, |
117 | where => { |
171a709f |
118 | status => { -in => \"(SELECT status_codes\nFROM states)" }, |
119 | }, |
120 | # failed to open outer parens on a multi-line query in 1.61 (semifor) |
121 | stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ", |
122 | bind => [], |
123 | test => '-in multi-line subquery test', |
124 | }, |
125 | { |
126 | parenthesis_significant => 1, |
127 | where => { |
e41c3bdd |
128 | customer => { -in => \[ |
129 | 'SELECT cust_id FROM cust WHERE balance > ?', |
130 | 2000, |
131 | ]}, |
132 | status => { -in => \'SELECT status_codes FROM states' }, |
133 | }, |
134 | stmt => " |
135 | WHERE (( |
136 | customer IN ( SELECT cust_id FROM cust WHERE balance > ? ) |
137 | AND status IN ( SELECT status_codes FROM states ) |
138 | )) |
139 | ", |
140 | bind => [2000], |
141 | test => '-in POD test', |
142 | }, |
cf02fc47 |
143 | ); |
144 | |
4d8b3dc4 |
145 | plan tests => @in_between_tests*4; |
cf02fc47 |
146 | |
147 | for my $case (@in_between_tests) { |
148 | TODO: { |
149 | local $TODO = $case->{todo} if $case->{todo}; |
4a1f01a3 |
150 | local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant}; |
cf02fc47 |
151 | |
152 | local $Data::Dumper::Terse = 1; |
153 | |
4d8b3dc4 |
154 | lives_ok (sub { |
155 | |
156 | my @w; |
157 | local $SIG{__WARN__} = sub { push @w, @_ }; |
158 | my $sql = SQL::Abstract->new ($case->{args} || {}); |
159 | lives_ok (sub { |
160 | my ($stmt, @bind) = $sql->where($case->{where}); |
161 | is_same_sql_bind( |
162 | $stmt, |
163 | \@bind, |
164 | $case->{stmt}, |
165 | $case->{bind}, |
166 | ) |
167 | || diag "Search term:\n" . Dumper $case->{where}; |
168 | }); |
169 | is (@w, 0, $case->{test} || 'No warnings within in-between tests') |
170 | || diag join "\n", 'Emitted warnings:', @w; |
171 | }, "$case->{test} doesn't die"); |
cf02fc47 |
172 | } |
173 | } |