1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
use strict;
use warnings;
no warnings qw/qw/;
use Test::More;
use SQL::Abstract::More;
use SQL::Abstract::Test import => ['is_same_sql_bind'];
my ($sql, @bind);
# test multicols with SQL support
my $sqla = SQL::Abstract::More->new(
# multicols_sep => '/',
multicols_sep => qr[\s*/\s*],
has_multicols_in_SQL => 1,
);
($sql, @bind) = $sqla->where({
one_col => 999,
"foo/bar" => {-in => ["1/a", "2/b"]},
"x/y/z" => {-not_in => ["X/Y/Z"]},
});
my @expected = ("WHERE (foo, bar) IN ((?, ?), (?, ?)) "
."AND one_col = ? "
."AND (x, y, z) NOT IN ((?, ?, ?))",
[qw/1 a 2 b 999 X Y Z/]);
is_same_sql_bind($sql, \@bind, @expected);
# same test, but with values passed as arrayrefs
($sql, @bind) = $sqla->where({
one_col => 999,
"foo/bar" => {-in => [[1, "a"], [2, "b"]]},
"x/y/z" => {-not_in => [[qw/X Y Z/]]},
});
is_same_sql_bind($sql, \@bind, @expected);
# right-hand side as a subquery
($sql, @bind) = $sqla->where({
one_col => 999,
"foo/bar" => {-in => \"SELECT (a, b) FROM FOOBAR"},
});
is_same_sql_bind($sql, \@bind,
"WHERE (foo, bar) IN (SELECT (a, b) FROM FOOBAR)"
."AND one_col = ? ",
[999]);
# right-hand side as a subquery with bind values
($sql, @bind) = $sqla->where({
one_col => 999,
"foo/bar" => {-in => \["SELECT (a, b) FROM FOOBAR WHERE a > ?", 1234]},
});
is_same_sql_bind($sql, \@bind,
"WHERE (foo, bar) IN (SELECT (a, b) FROM FOOBAR WHERE a > ?)"
."AND one_col = ? ",
[1234, 999]);
# test multicols without SQL support
$sqla = SQL::Abstract::More->new(
# multicols_sep => '/',
multicols_sep => qr[\s*/\s*],
has_multicols_in_SQL => 0,
);
($sql, @bind) = $sqla->where({
one_col => 999,
"foo/bar" => {-in => ["1/a", "2/b"]},
"x/y/z" => {-not_in => ["X/Y/Z"]},
});
is_same_sql_bind(
$sql, \@bind,
"WHERE ((foo = ? AND bar = ?) OR (foo = ? AND bar = ?)) "
."AND one_col = ? "
."AND NOT (x = ? AND y = ? AND z = ?)",
[qw/1 a 2 b 999 X Y Z/],
);
done_testing();
|
