1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use Test::More;
6
7use SQL::Abstract::Test import => ['is_same_sql_bind'];
8
9use SQL::Abstract;
10
11my $sql = SQL::Abstract->new;
12
13my (@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  };
22push @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  };
36push @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  };
49push @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  };
59push @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  };
74push @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  };
87push @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
94plan tests => scalar(@tests);
95
96for (@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
106