Commit | Line | Data |
4f30591b |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | use Test::More; |
6 | |
7 | use SQL::Abstract::Test import => ['is_same_sql_bind']; |
99ecc29e |
8 | $SQL::Abstract::Test::parenthesis_significant = 1; |
4f30591b |
9 | |
10 | use SQL::Abstract; |
11 | |
12 | my $sql = SQL::Abstract->new; |
13 | |
14 | my (@tests, $sub_stmt, @sub_bind, $where); |
15 | |
16 | #1 |
17 | ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?", |
18 | 100, "foo%"); |
19 | $where = { |
20 | foo => 1234, |
21 | bar => \["IN ($sub_stmt)" => @sub_bind], |
22 | }; |
23 | push @tests, { |
24 | where => $where, |
99ecc29e |
25 | stmt => " WHERE ( ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? ) )", |
4f30591b |
26 | bind => [100, "foo%", 1234], |
27 | }; |
28 | |
29 | #2 |
30 | ($sub_stmt, @sub_bind) |
31 | = $sql->select("t1", "c1", {c2 => {"<" => 100}, |
32 | c3 => {-like => "foo%"}}); |
33 | $where = { |
34 | foo => 1234, |
35 | bar => \["> ALL ($sub_stmt)" => @sub_bind], |
36 | }; |
37 | push @tests, { |
38 | where => $where, |
99ecc29e |
39 | stmt => " WHERE ( ( bar > ALL (SELECT c1 FROM t1 WHERE ( c2 < ? AND c3 LIKE ? )) AND foo = ? ) )", |
4f30591b |
40 | bind => [100, "foo%", 1234], |
41 | }; |
42 | |
43 | #3 |
44 | ($sub_stmt, @sub_bind) |
45 | = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"}); |
46 | $where = { |
47 | foo => 1234, |
99ecc29e |
48 | -paren => \["EXISTS ($sub_stmt)" => @sub_bind], |
4f30591b |
49 | }; |
50 | push @tests, { |
51 | where => $where, |
99ecc29e |
52 | stmt => " WHERE ( ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 > t0.c0 )) AND foo = ? ) )", |
4f30591b |
53 | bind => [1, 1234], |
54 | }; |
55 | |
56 | #4 |
57 | $where = { |
99ecc29e |
58 | -paren => \["MATCH (col1, col2) AGAINST (?)" => "apples"], |
4f30591b |
59 | }; |
60 | push @tests, { |
61 | where => $where, |
62 | stmt => " WHERE ( MATCH (col1, col2) AGAINST (?) )", |
63 | bind => ["apples"], |
64 | }; |
65 | |
66 | |
67 | #5 |
68 | ($sub_stmt, @sub_bind) |
69 | = $sql->where({age => [{"<" => 10}, {">" => 20}]}); |
70 | $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause |
71 | $where = { |
72 | lname => {-like => '%son%'}, |
99ecc29e |
73 | -paren => \["NOT ( $sub_stmt )" => @sub_bind], |
4f30591b |
74 | }; |
75 | push @tests, { |
76 | where => $where, |
99ecc29e |
77 | stmt => " WHERE ( ( NOT ( ( ( ( age < ? ) OR ( age > ? ) ) ) ) AND lname LIKE ? ) )", |
4f30591b |
78 | bind => [10, 20, '%son%'], |
79 | }; |
80 | |
81 | #6 |
82 | ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?", |
83 | 100, "foo%"); |
84 | $where = { |
85 | foo => 1234, |
86 | bar => { -in => \[$sub_stmt => @sub_bind] }, |
87 | }; |
88 | push @tests, { |
89 | where => $where, |
99ecc29e |
90 | stmt => " WHERE ( ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? ) )", |
4f30591b |
91 | bind => [100, "foo%", 1234], |
92 | }; |
93 | |
94 | |
95 | plan tests => scalar(@tests); |
96 | |
97 | for (@tests) { |
98 | |
99 | my($stmt, @bind) = $sql->where($_->{where}, $_->{order}); |
100 | is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind}); |
101 | } |