#26
{
func => 'select',
- args => ['test', '*', {priority => [ -and => {'!=', 2}, {'!=', 1} ]}],
- stmt => 'SELECT * FROM test WHERE ( ( ( priority != ? ) AND ( priority != ? ) ) )',
- stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `priority` != ? ) AND ( `priority` != ? ) ) )',
- bind => [qw(2 1)],
+ args => ['test', '*', {priority => [ -and => {'!=', 2}, { -not_like => '3%'} ]}],
+ stmt => 'SELECT * FROM test WHERE ( ( ( priority != ? ) AND ( priority NOT LIKE ? ) ) )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `priority` != ? ) AND ( `priority` NOT LIKE ? ) ) )',
+ bind => [qw(2 3%)],
},
#27
{
#29
{
func => 'select',
- args => ['jeff', '*', { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},
+ args => ['jeff', '*', { name => {'ilike', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},
-nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ],
yob => {'<', 1976} ] ] } ],
stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) )'
- . ' AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )',
+ . ' AND name NOT IN ( ?, ?, ?, ? ) AND name ILIKE ? )',
stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( ( ( `age` BETWEEN ? AND ? ) AND ( `age` != ? ) ) ) OR ( `yob` < ? ) ) ) )'
- . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? )',
+ . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` ILIKE ? )',
bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)]
},
#30
stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )',
stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )',
bind => [[{dummy => 1} => '02/02/02'], [b => 8]],
- },
+ },
#57
{
func => 'select',
stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? ) OR ( c = ? AND d = ? )',
stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? AND `b` = ? ) OR ( `c` = ? AND `d` = ? )',
bind => [[a => 'a'], [b => 'b'], [ c => 'c'],[ d => 'd']],
- },
-
+ },
+ #58
+ {
+ func => 'select',
+ new => {bindtype => 'columns'},
+ args => ['test', '*', [ { a => 1, b => 1}, [ a => 2, b => 2] ] ],
+ stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? ) OR ( a = ? OR b = ? )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? AND `b` = ? ) OR ( `a` = ? OR `b` = ? )',
+ bind => [[a => 1], [b => 1], [ a => 2], [ b => 2]],
+ },
+ #59
+ {
+ func => 'select',
+ new => {bindtype => 'columns'},
+ args => ['test', '*', [ [ a => 1, b => 1], { a => 2, b => 2 } ] ],
+ stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? ) OR ( a = ? AND b = ? )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? OR `b` = ? ) OR ( `a` = ? AND `b` = ? )',
+ bind => [[a => 1], [b => 1], [ a => 2], [ b => 2]],
+ },
+ #60
+ {
+ func => 'insert',
+ args => ['test', [qw/1 2 3 4 5/], { returning => 'id' }],
+ stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id',
+ stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id`',
+ bind => [qw/1 2 3 4 5/],
+ },
+ #60
+ {
+ func => 'insert',
+ args => ['test', [qw/1 2 3 4 5/], { returning => 'id, foo, bar' }],
+ stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar',
+ stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id, foo, bar`',
+ bind => [qw/1 2 3 4 5/],
+ },
+ #61
+ {
+ func => 'insert',
+ args => ['test', [qw/1 2 3 4 5/], { returning => [qw(id foo bar) ] }],
+ stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar',
+ stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING `id`, `foo`, `bar`',
+ bind => [qw/1 2 3 4 5/],
+ },
+ #62
+ {
+ func => 'insert',
+ args => ['test', [qw/1 2 3 4 5/], { returning => \'id, foo, bar' }],
+ stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar',
+ stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING id, foo, bar',
+ bind => [qw/1 2 3 4 5/],
+ },
+ #63
+ {
+ func => 'insert',
+ args => ['test', [qw/1 2 3 4 5/], { returning => \'id' }],
+ stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?) RETURNING id',
+ stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?) RETURNING id',
+ bind => [qw/1 2 3 4 5/],
+ },
+ {
+ func => 'select',
+ new => {bindtype => 'columns'},
+ args => ['test', '*', [ Y => { '=' => { -max => { -LENGTH => { -min => 'x' } } } } ] ],
+ stmt => 'SELECT * FROM test WHERE ( Y = ( MAX( LENGTH( MIN ? ) ) ) )',
+ stmt_q => 'SELECT * FROM `test` WHERE ( `Y` = ( MAX( LENGTH( MIN ? ) ) ) )',
+ bind => [[Y => 'x']],
+ },
);
plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2
+ scalar(grep { $_->{warning_like} } @tests) * 4;
-for (@tests) {
+for my $t (@tests) {
local $"=', ';
- my $new = $_->{new} || {};
+ my $new = $t->{new} || {};
$new->{debug} = $ENV{DEBUG} || 0;
- # test without quoting labels
- {
- my $sql = SQL::Abstract->new(%$new);
+ for my $quoted (0, 1) {
- my $func = $_->{func};
- my($stmt, @bind);
- my $test = sub {
- ($stmt, @bind) = $sql->$func(@{$_->{args}})
- };
- if ($_->{exception_like}) {
- throws_ok { &$test } $_->{exception_like}, "throws the expected exception ($_->{exception_like})";
- } else {
- if ($_->{warning_like}) {
- warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})";
- } else {
- &$test;
- }
- is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind});
- }
- }
+ my $maker = SQL::Abstract->new(%$new, $quoted
+ ? (quote_char => '`', name_sep => '.')
+ : ()
+ );
- # test with quoted labels
- {
- my $sql_q = SQL::Abstract->new(%$new, quote_char => '`', name_sep => '.');
+ my($stmt, @bind);
- my $func_q = $_->{func};
- my($stmt_q, @bind_q);
- my $test = sub {
- ($stmt_q, @bind_q) = $sql_q->$func_q(@{$_->{args}})
+ my $cref = sub {
+ my $op = $t->{func};
+ ($stmt, @bind) = $maker->$op (@ { $t->{args} } );
};
- if ($_->{exception_like}) {
- throws_ok { &$test } $_->{exception_like}, "throws the expected exception ($_->{exception_like})";
+
+ if ($t->{exception_like}) {
+ throws_ok(
+ sub { $cref->() },
+ $t->{exception_like},
+ "throws the expected exception ($t->{exception_like})"
+ );
} else {
- if ($_->{warning_like}) {
- warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})";
- } else {
- &$test;
+ if ($t->{warning_like}) {
+ warning_like(
+ sub { $cref->() },
+ $t->{warning_like},
+ "issues the expected warning ($t->{warning_like})"
+ );
}
-
- is_same_sql_bind($stmt_q, \@bind_q, $_->{stmt_q}, $_->{bind});
+ else {
+ $cref->();
+ }
+ is_same_sql_bind(
+ $stmt,
+ \@bind,
+ $quoted ? $t->{stmt_q}: $t->{stmt},
+ $t->{bind}
+ );
}
}
}
projects / dbsrgits/SQL-Abstract.git / blobdiff