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