},
{
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',
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 => []
+ },
);
# check is( not) => undef
} 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"] ],
+
+ # except for this one, that is automagically arrayified
+ { foo => "bar", -or => { $lhs => \"baz" }, bizz => "buzz" },
+ ) {
+ 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", -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} || {};
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);