Bumped revision number.
[dbsrgits/SQL-Abstract.git] / t / 07subqueries.t
CommitLineData
96449e8e 1#!/usr/bin/perl\r
2\r
3use strict;\r
4use warnings;\r
5use Test::More;\r
6\r
5aad8cf3 7use SQL::Abstract::Test import => ['is_same_sql_bind'];\r
96449e8e 8\r
9use SQL::Abstract;\r
10\r
11my $sql = SQL::Abstract->new;\r
12\r
13my (@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
22push @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
36push @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
49push @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
59push @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
74push @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
87push @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 94plan tests => scalar(@tests);\r
96449e8e 95\r
96for (@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