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