This todo is active on the cpan 1.51 release, still needs resolving thus leaving...

[dbsrgits/SQL-Abstract.git] / t / 07subqueries.t

#!/usr/bin/perl\r
\r
use strict;\r
use warnings;\r
use Test::More;\r
\r
use SQL::Abstract::Test import => ['is_same_sql_bind'];\r
\r
use SQL::Abstract;\r
\r
my $sql = SQL::Abstract->new;\r
\r
my (@tests, $sub_stmt, @sub_bind, $where);\r
\r
#1\r
($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",\r
                          100, "foo%");\r
$where = {\r
    foo => 1234,\r
    bar => \["IN ($sub_stmt)" => @sub_bind],\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? )",\r
  bind => [100, "foo%", 1234],\r
};\r
\r
#2\r
($sub_stmt, @sub_bind)\r
     = $sql->select("t1", "c1", {c2 => {"<" => 100}, \r
                                 c3 => {-like => "foo%"}});\r
$where = {\r
    foo => 1234,\r
    bar => \["> ALL ($sub_stmt)" => @sub_bind],\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( bar > ALL (SELECT c1 FROM t1 WHERE ( c2 < ? AND c3 LIKE ? )) AND foo = ? )",\r
  bind => [100, "foo%", 1234],\r
};\r
\r
#3\r
($sub_stmt, @sub_bind) \r
     = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});\r
$where = {\r
    foo                  => 1234,\r
    -nest => \["EXISTS ($sub_stmt)" => @sub_bind],\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 > t0.c0 )) AND foo = ? )",\r
  bind => [1, 1234],\r
};\r
\r
#4\r
$where = {\r
    -nest => \["MATCH (col1, col2) AGAINST (?)" => "apples"],\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( MATCH (col1, col2) AGAINST (?) )",\r
  bind => ["apples"],\r
};\r
\r
\r
#5\r
($sub_stmt, @sub_bind) \r
  = $sql->where({age => [{"<" => 10}, {">" => 20}]});\r
$sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause\r
$where = {\r
    lname  => {-like => '%son%'},\r
    -nest  => \["NOT ( $sub_stmt )" => @sub_bind],\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( NOT ( ( ( ( age < ? ) OR ( age > ? ) ) ) ) AND lname LIKE ? )",\r
  bind => [10, 20, '%son%'],\r
};\r
\r
#6\r
($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",\r
                          100, "foo%");\r
$where = {\r
    foo => 1234,\r
    bar => { -in => \[$sub_stmt => @sub_bind] },\r
  };\r
push @tests, {\r
  where => $where,\r
  stmt => " WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? )",\r
  bind => [100, "foo%", 1234],\r
};\r
\r
\r
plan tests => scalar(@tests);\r
\r
for (@tests) {\r
\r
  my($stmt, @bind) = $sql->where($_->{where}, $_->{order});\r
  is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind});\r
}\r
\r
\r
\r
\r
\r