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