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