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