-#!/usr/bin/perl
-
use strict;
use warnings;
use Test::More;
use Test::Warn;
use Test::Exception;
-use SQL::Abstract::Test import => ['is_same_sql_bind'];
+use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper)];
use SQL::Abstract;
+#### WARNING ####
+#
+# -nest has been undocumented on purpose, but is still supported for the
+# foreseable future. Do not rip out the -nest tests before speaking to
+# someone on the DBIC mailing list or in irc.perl.org#dbix-class
+#
+#################
+
my @tests = (
{
func => 'select',
},
{
func => 'select',
- args => [[qw/test1 test2/], '*', { 'test1.a' => { 'In', ['boom', 'bang'] } }],
- stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a IN ( ?, ? ) )',
- stmt_q => 'SELECT * FROM `test1`, `test2` WHERE ( `test1`.`a` IN ( ?, ? ) )',
- bind => ['boom', 'bang']
- },
- {
- func => 'select',
- args => ['test', '*', { a => { 'between', ['boom', 'bang'] } }],
- stmt => 'SELECT * FROM test WHERE ( a BETWEEN ? AND ? )',
- stmt_q => 'SELECT * FROM `test` WHERE ( `a` BETWEEN ? AND ? )',
- bind => ['boom', 'bang']
- },
- {
- func => 'select',
args => ['test', '*', { a => { '!=', 'boom' } }],
stmt => 'SELECT * FROM test WHERE ( a != ? )',
stmt_q => 'SELECT * FROM `test` WHERE ( `a` != ? )',
bind => ['boom']
},
{
+ # this is maybe wrong but a single arg doesn't get quoted
+ func => 'select',
+ args => ['test', 'id', { a => { '!=', 'boom' } }],
+ stmt => 'SELECT id FROM test WHERE ( a != ? )',
+ stmt_q => 'SELECT id FROM `test` WHERE ( `a` != ? )',
+ bind => ['boom']
+ },
+ {
func => 'update',
args => ['test', {a => 'boom'}, {a => undef}],
stmt => 'UPDATE test SET a = ? WHERE ( a IS NULL )',
},
{
func => 'update',
+ args => ['test', {a => undef }, {a => 'boom'}],
+ stmt => 'UPDATE test SET a = ? WHERE ( a = ? )',
+ stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` = ? )',
+ bind => [undef,'boom']
+ },
+ {
+ func => 'update',
args => ['test', {a => 'boom'}, { a => {'!=', "bang" }} ],
stmt => 'UPDATE test SET a = ? WHERE ( a != ? )',
stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` != ? )',
bind => ['nwiger']
},
{
+ func => 'select',
+ args => [[\'test1', 'test2'], '*', { 'test1.a' => 'boom' } ],
+ stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a = ? )',
+ stmt_q => 'SELECT * FROM test1, `test2` WHERE ( `test1`.`a` = ? )',
+ bind => ['boom']
+ },
+ {
func => 'insert',
args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}],
stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)',
},
{
func => 'insert',
- args => ['test', [qw/1 2 3 4 5/]],
- stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?)',
- stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?)',
- bind => [qw/1 2 3 4 5/],
+ args => ['test', [1..30]],
+ stmt => 'INSERT INTO test VALUES ('.join(', ', ('?')x30).')',
+ stmt_q => 'INSERT INTO `test` VALUES ('.join(', ', ('?')x30).')',
+ bind => [1..30],
},
{
func => 'insert',
tasty => { '!=', [qw(yes YES)] },
-nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] },
],
+ warns => qr/\QA multi-element arrayref as an argument to the inequality op '!=' is technically equivalent to an always-true 1=1/,
+
stmt => 'UPDATE taco_punches SET one = ?, three = ? WHERE ( ( ( ( ( face = ? ) OR ( face IS NULL ) ) ) )'
. ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) OR ( tasty != ? ) ) )',
stmt_q => 'UPDATE `taco_punches` SET `one` = ?, `three` = ? WHERE ( ( ( ( ( `face` = ? ) OR ( `face` IS NULL ) ) ) )'
},
{
func => 'update',
-# LDNOTE : removed the "-maybe", because we no longer admit unknown ops
-#
-# acked by RIBASUSHI
-# args => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]},
args => ['fhole', {fpoles => 4}, [
{ race => [qw/-or black white asian /] },
{ -nest => { firsttime => [-or => {'=','yes'}, undef] } },
},
{
func => 'select',
-# LDNOTE: modified test below because we agreed with MST that literal SQL
-# should not automatically insert a '='; the user has to do it
-#
-# acked by MSTROUT
-# args => ['test', '*', { a => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}],
stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )},
stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )},
stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)',
stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)',
bind => [qw/1 2 3 4/, { answer => 42}],
- warning_like => qr/HASH ref as bind value in insert is not supported/i,
+ warns => qr/HASH ref as bind value in insert is not supported/i,
},
{
func => 'update',
func => 'insert',
new => {bindtype => 'columns'},
args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
- exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
},
{
func => 'update',
new => {bindtype => 'columns'},
args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}],
- exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
},
{
func => 'select',
new => {bindtype => 'columns'},
args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}],
- exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
},
{
func => 'select',
new => {bindtype => 'columns'},
args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }],
- exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ },
+ {
+ func => 'select',
+ args => ['test', '*', { foo => { '>=' => [] }} ],
+ throws => qr/\Qoperator '>=' applied on an empty array (field 'foo')/,
},
{
func => 'select',
func => 'select',
new => {bindtype => 'columns'},
args => ['test', '*', { a => {-in => \["(SELECT d FROM to_date(?, 'MM/DD/YY') AS d)", '02/02/02']}, b => 8 }],
- exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
+ throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/,
},
{
func => 'insert',
stmt_q => 'SELECT * FROM `test` WHERE ( `Y` = ( MAX( LENGTH( MIN ? ) ) ) )',
bind => [[Y => 'x']],
},
+ {
+ func => 'select',
+ args => ['test', '*', { a => { '=' => undef }, b => { -is => undef }, c => { -like => undef } }],
+ stmt => 'SELECT * FROM test WHERE ( a IS NULL AND b IS NULL AND c IS NULL )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NULL AND `b` IS NULL AND `c` IS NULL )',
+ bind => [],
+ warns => qr/\QSupplying an undefined argument to 'LIKE' is deprecated/,
+ },
+ {
+ func => 'select',
+ args => ['test', '*', { a => { '!=' => undef }, b => { -is_not => undef }, c => { -not_like => undef } }],
+ stmt => 'SELECT * FROM test WHERE ( a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NOT NULL AND `b` IS NOT NULL AND `c` IS NOT NULL )',
+ bind => [],
+ warns => qr/\QSupplying an undefined argument to 'NOT LIKE' is deprecated/,
+ },
+ {
+ func => 'select',
+ args => ['test', '*', { a => { IS => undef }, b => { LIKE => undef } }],
+ stmt => 'SELECT * FROM test WHERE ( a IS NULL AND b IS NULL )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NULL AND `b` IS NULL )',
+ bind => [],
+ warns => qr/\QSupplying an undefined argument to 'LIKE' is deprecated/,
+ },
+ {
+ func => 'select',
+ args => ['test', '*', { a => { 'IS NOT' => undef }, b => { 'NOT LIKE' => undef } }],
+ stmt => 'SELECT * FROM test WHERE ( a IS NOT NULL AND b IS NOT NULL )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NOT NULL AND `b` IS NOT NULL )',
+ bind => [],
+ warns => qr/\QSupplying an undefined argument to 'NOT LIKE' is deprecated/,
+ },
+ {
+ func => 'select',
+ args => ['`test``table`', ['`test``column`']],
+ stmt => 'SELECT `test``column` FROM `test``table`',
+ stmt_q => 'SELECT ```test````column``` FROM ```test````table```',
+ bind => [],
+ },
+ {
+ func => 'select',
+ args => ['`test\\`table`', ['`test`\\column`']],
+ stmt => 'SELECT `test`\column` FROM `test\`table`',
+ stmt_q => 'SELECT `\`test\`\\\\column\`` FROM `\`test\\\\\`table\``',
+ esc => '\\',
+ bind => [],
+ },
+ {
+ func => 'update',
+ args => ['mytable', { foo => 42 }, { baz => 32 }, { returning => 'id' }],
+ stmt => 'UPDATE mytable SET foo = ? WHERE baz = ? RETURNING id',
+ stmt_q => 'UPDATE `mytable` SET `foo` = ? WHERE `baz` = ? RETURNING `id`',
+ bind => [42, 32],
+ },
+ {
+ func => 'update',
+ args => ['mytable', { foo => 42 }, { baz => 32 }, { returning => \'*' }],
+ stmt => 'UPDATE mytable SET foo = ? WHERE baz = ? RETURNING *',
+ stmt_q => 'UPDATE `mytable` SET `foo` = ? WHERE `baz` = ? RETURNING *',
+ bind => [42, 32],
+ },
+ {
+ func => 'update',
+ args => ['mytable', { foo => 42 }, { baz => 32 }, { returning => ['id','created_at'] }],
+ stmt => 'UPDATE mytable SET foo = ? WHERE baz = ? RETURNING id, created_at',
+ stmt_q => 'UPDATE `mytable` SET `foo` = ? WHERE `baz` = ? RETURNING `id`, `created_at`',
+ bind => [42, 32],
+ },
+ {
+ func => 'delete',
+ args => ['test', {requestor => undef}, {returning => 'id'}],
+ stmt => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING id',
+ stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING `id`',
+ bind => []
+ },
+ {
+ func => 'delete',
+ args => ['test', {requestor => undef}, {returning => \'*'}],
+ stmt => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING *',
+ stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING *',
+ bind => []
+ },
+ {
+ func => 'delete',
+ args => ['test', {requestor => undef}, {returning => ['id', 'created_at']}],
+ stmt => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING id, created_at',
+ stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING `id`, `created_at`',
+ bind => []
+ },
+ {
+ func => 'delete',
+ args => ['test', \[ undef ] ],
+ stmt => 'DELETE FROM test',
+ stmt_q => 'DELETE FROM `test`',
+ bind => []
+ },
);
+# check is( not) => undef
+for my $op (qw(not is is_not), 'is not') {
+ (my $sop = uc $op) =~ s/_/ /gi;
-plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2
- + scalar(grep { $_->{warning_like} } @tests) * 4;
+ $sop = 'IS NOT' if $sop eq 'NOT';
-for my $t (@tests) {
- local $"=', ';
+ for my $uc (0, 1) {
+ for my $prefix ('', '-') {
+ push @tests, {
+ func => 'where',
+ args => [{ a => { ($prefix . ($uc ? uc $op : lc $op) ) => undef } }],
+ stmt => "WHERE a $sop NULL",
+ stmt_q => "WHERE `a` $sop NULL",
+ bind => [],
+ };
+ }
+ }
+}
+
+# check single-element inequality ops for no warnings
+for my $op (qw(!= <>)) {
+ for my $val (undef, 42) {
+ push @tests, {
+ func => 'where',
+ args => [ { x => { "$_$op" => [ $val ] } } ],
+ stmt => "WHERE x " . ($val ? "$op ?" : 'IS NOT NULL'),
+ stmt_q => "WHERE `x` " . ($val ? "$op ?" : 'IS NOT NULL'),
+ bind => [ $val || () ],
+ } for ('', '-'); # with and without -
+ }
+}
+
+# check single-element not-like ops for no warnings, and NULL exception
+# (the last two "is not X" are a weird syntax, but mebbe a dialect...)
+for my $op (qw(not_like not_rlike), 'not like', 'not rlike', 'is not like','is not rlike') {
+ (my $sop = uc $op) =~ s/_/ /gi;
+
+ for my $val (undef, 42) {
+ push @tests, {
+ func => 'where',
+ args => [ { x => { "$_$op" => [ $val ] } } ],
+ $val ? (
+ stmt => "WHERE x $sop ?",
+ stmt_q => "WHERE `x` $sop ?",
+ bind => [ $val ],
+ ) : (
+ stmt => "WHERE x IS NOT NULL",
+ stmt_q => "WHERE `x` IS NOT NULL",
+ bind => [],
+ warns => qr/\QSupplying an undefined argument to '$sop' is deprecated/,
+ ),
+ } for ('', '-'); # with and without -
+ }
+}
+
+# check all multi-element inequality/not-like ops for warnings
+for my $op (qw(!= <> not_like not_rlike), 'not like', 'not rlike', 'is not like','is not rlike') {
+ (my $sop = uc $op) =~ s/_/ /gi;
+
+ push @tests, {
+ func => 'where',
+ args => [ { x => { "$_$op" => [ 42, 69 ] } } ],
+ stmt => "WHERE x $sop ? OR x $sop ?",
+ stmt_q => "WHERE `x` $sop ? OR `x` $sop ?",
+ bind => [ 42, 69 ],
+ warns => qr/\QA multi-element arrayref as an argument to the inequality op '$sop' is technically equivalent to an always-true 1=1/,
+ } for ('', '-'); # with and without -
+}
+
+# check all like/not-like ops for empty-arrayref warnings
+for my $op (qw(like rlike not_like not_rlike), 'not like', 'not rlike', 'is like', 'is not like', 'is rlike', 'is not rlike') {
+ (my $sop = uc $op) =~ s/_/ /gi;
+
+ push @tests, {
+ func => 'where',
+ args => [ { x => { "$_$op" => [] } } ],
+ stmt => ( $sop =~ /NOT/ ? "WHERE 1=1" : "WHERE 0=1" ),
+ stmt_q => ( $sop =~ /NOT/ ? "WHERE 1=1" : "WHERE 0=1" ),
+ bind => [],
+ warns => qr/\QSupplying an empty arrayref to '$sop' is deprecated/,
+ } for ('', '-'); # with and without -
+}
+
+# check emtpty-lhs in a hashpair and arraypair
+for my $lhs (undef, '') {
+ no warnings 'uninitialized';
+##
+## hard exceptions - never worked
+ for my $where_arg (
+ ( map { $_, { @$_ } }
+ [ $lhs => "foo" ],
+ [ $lhs => { "=" => "bozz" } ],
+ [ $lhs => { "=" => \"bozz" } ],
+ [ $lhs => { -max => \"bizz" } ],
+ ),
+ [ -and => { $lhs => "baz" }, bizz => "buzz" ],
+ [ foo => "bar", { $lhs => "baz" }, bizz => "buzz" ],
+ { foo => "bar", -or => { $lhs => "baz" } },
+
+ # the hashref forms of these work sadly - check for warnings below
+ { foo => "bar", -and => [ $lhs => \"baz" ], bizz => "buzz" },
+ { foo => "bar", -or => [ $lhs => \"baz" ], bizz => "buzz" },
+ [ foo => "bar", [ $lhs => \"baz" ], bizz => "buzz" ],
+ [ foo => "bar", $lhs => \"baz", bizz => "buzz" ],
+ [ foo => "bar", $lhs => \["baz"], bizz => "buzz" ],
+ [ $lhs => \"baz" ],
+ [ $lhs => \["baz"] ],
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ throws => qr/\QSupplying an empty left hand side argument is not supported/,
+ };
+ }
+
+##
+## deprecations - sorta worked, likely abused by folks
+ for my $where_arg (
+ # the arrayref forms of this never worked and throw above
+ { foo => "bar", -or => { $lhs => \"baz" }, bizz => "buzz" },
+ { foo => "bar", -and => { $lhs => \"baz" }, bizz => "buzz" },
+ { foo => "bar", $lhs => \"baz", bizz => "buzz" },
+ { foo => "bar", $lhs => \["baz"], bizz => "buzz" },
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ stmt => 'WHERE baz AND bizz = ? AND foo = ?',
+ stmt_q => 'WHERE baz AND `bizz` = ? AND `foo` = ?',
+ bind => [qw( buzz bar )],
+ warns => qr/\QHash-pairs consisting of an empty string with a literal are deprecated/,
+ };
+ }
+
+ for my $where_arg (
+ { $lhs => \"baz" },
+ { $lhs => \["baz"] },
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ stmt => 'WHERE baz',
+ stmt_q => 'WHERE baz',
+ bind => [],
+ warns => qr/\QHash-pairs consisting of an empty string with a literal are deprecated/,
+ }
+ }
+}
+
+# check false lhs, silly but possible
+{
+ for my $where_arg (
+ [ { 0 => "baz" }, bizz => "buzz", foo => "bar" ],
+ [ -or => { foo => "bar", -or => { 0 => "baz" }, bizz => "buzz" } ],
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ stmt => 'WHERE 0 = ? OR bizz = ? OR foo = ?',
+ stmt_q => 'WHERE `0` = ? OR `bizz` = ? OR `foo` = ?',
+ bind => [qw( baz buzz bar )],
+ };
+ }
+
+ for my $where_arg (
+ { foo => "bar", -and => [ 0 => \"= baz" ], bizz => "buzz" },
+ { foo => "bar", -or => [ 0 => \"= baz" ], bizz => "buzz" },
+
+ { foo => "bar", -and => { 0 => \"= baz" }, bizz => "buzz" },
+ { foo => "bar", -or => { 0 => \"= baz" }, bizz => "buzz" },
+
+ { foo => "bar", 0 => \"= baz", bizz => "buzz" },
+ { foo => "bar", 0 => \["= baz"], bizz => "buzz" },
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ stmt => 'WHERE 0 = baz AND bizz = ? AND foo = ?',
+ stmt_q => 'WHERE `0` = baz AND `bizz` = ? AND `foo` = ?',
+ bind => [qw( buzz bar )],
+ };
+ }
+
+ for my $where_arg (
+ [ -and => [ 0 => \"= baz" ], bizz => "buzz", foo => "bar" ],
+ [ -or => [ 0 => \"= baz" ], bizz => "buzz", foo => "bar" ],
+ [ 0 => \"= baz", bizz => "buzz", foo => "bar" ],
+ [ 0 => \["= baz"], bizz => "buzz", foo => "bar" ],
+ ) {
+ push @tests, {
+ func => 'where',
+ args => [ $where_arg ],
+ stmt => 'WHERE 0 = baz OR bizz = ? OR foo = ?',
+ stmt_q => 'WHERE `0` = baz OR `bizz` = ? OR `foo` = ?',
+ bind => [qw( buzz bar )],
+ };
+ }
+}
+
+for my $t (@tests) {
my $new = $t->{new} || {};
- $new->{debug} = $ENV{DEBUG} || 0;
for my $quoted (0, 1) {
- my $maker = SQL::Abstract->new(%$new, $quoted
- ? (quote_char => '`', name_sep => '.')
- : ()
+ my $maker = SQL::Abstract->new(
+ %$new,
+ ($quoted ? (
+ quote_char => '`',
+ name_sep => '.',
+ ( $t->{esc} ? (
+ escape_char => $t->{esc},
+ ) : ())
+ ) : ())
);
my($stmt, @bind);
my $cref = sub {
my $op = $t->{func};
- ($stmt, @bind) = $maker->$op (@ { $t->{args} } );
+ ($stmt, @bind) = $maker->$op(@{ $t->{args} });
};
- if ($t->{exception_like}) {
+ if (my $e = $t->{throws}) {
throws_ok(
sub { $cref->() },
- $t->{exception_like},
- "throws the expected exception ($t->{exception_like})"
- );
- } else {
- if ($t->{warning_like}) {
- warning_like(
+ $e,
+ ) || diag dumper({ args => $t->{args}, result => $stmt });
+ }
+ else {
+ lives_ok(sub {
+ alarm(1); local $SIG{ALRM} = sub {
+ no warnings 'redefine';
+ my $orig = Carp->can('caller_info');
+ local *Carp::caller_info = sub { return if $_[0] > 20; &$orig };
+ print STDERR "ARGH ($SQL::Abstract::Default_Scalar_To): ".Carp::longmess();
+ die "timed out";
+ };
+ warnings_like(
sub { $cref->() },
- $t->{warning_like},
- "issues the expected warning ($t->{warning_like})"
- );
- }
- else {
- $cref->();
- }
+ $t->{warns} || [],
+ ) || diag dumper({ args => $t->{args}, result => $stmt });
+ }) || diag dumper({ args => $t->{args}, result => $stmt, threw => $@ });
+
is_same_sql_bind(
$stmt,
\@bind,
$quoted ? $t->{stmt_q}: $t->{stmt},
$t->{bind}
- );
+ ) || diag dumper({ args => $t->{args}, result => $stmt });;
}
}
}
+
+done_testing;