SQL-Abstract
view release on metacpan - search on metacpan
view release on metacpan or search on metacpan
- Retire script/format-sql - the utility needs more work to be truly
end-user convenient
revision 1.77 2014-01-17
----------------------------
- Reintroduce { -not => undef } column operator (regression from 1.75)
revision 1.75 2013-12-27
----------------------------
- *UPCOMING INCOMPATIBLE BUGFIX*: SQLA used to generate incorrect SQL
on undef-containing lists fed to -in and -not_in. An exception will
be raised for a while before properly fixing this, to avoid quiet
but subtle changes to query results in production
- Deprecate and warn when supplying an empty arrayref to like/not_like
operators (likely to be removed before 2.0)
- Warn when using an inequality operator with a multi-value array to
arrive at what amounts to a 1=1 condition (no pre-2.0 plans to fix
this behavior due to backwards comp concerns)
- Fix false negative comparison of ORDER BY <function> ASC
- More improvements of incorrect parsing (placeholder at end of list
element)
- Fix typos in POD and comments (RT#87776)
- Augment -not_bool example with nesting (RT#89601)
revision 1.74 2013-06-04
----------------------------
- Fix insufficient parenthesis unroll during operator comparison
- 'ORDER BY foo' and 'ORDER BY foo ASC' are now considered equal
by default (with a switch to reenable old behavior when necessary)
- Change parser to not eagerly slurp RHS expressions it doesn't recognize
revision 1.73 2012-07-10
----------------------------
lib/SQL/Abstract.pm view on Meta::CPAN
expand => {
bool => '_expand_bool',
row => '_expand_row',
op => '_expand_op',
func => '_expand_func',
values => '_expand_values',
list => '_expand_list',
},
expand_op => {
(map +($_ => __PACKAGE__->make_binop_expander('_expand_between')),
qw(between not_between)),
(map +($_ => __PACKAGE__->make_binop_expander('_expand_in')),
qw(in not_in)),
(map +($_ => '_expand_op_andor'), ('and', 'or')),
(map +($_ => '_expand_op_is'), ('is', 'is_not')),
(map +($_ => __PACKAGE__->make_unop_expander("_expand_${_}")),
qw(ident value nest)),
bind => __PACKAGE__->make_unop_expander(sub { +{ -bind => $_[2] } }),
},
render => {
(map +($_, "_render_$_"),
qw(op func bind ident literal row values keyword)),
},
render_op => {
(map +($_ => '_render_op_between'), 'between', 'not_between'),
(map +($_ => '_render_op_in'), 'in', 'not_in'),
(map +($_ => '_render_unop_postfix'),
'is_null', 'is_not_null', 'asc', 'desc',
),
(not => '_render_unop_paren'),
(map +($_ => '_render_op_andor'), qw(and or)),
',' => '_render_op_multop',
},
clauses_of => {
delete => [ qw(target where returning) ],
update => [ qw(target set where returning) ],
insert => [ qw(target fields from returning) ],
select => [ qw(select from where order_by) ],
lib/SQL/Abstract/Reference.pm view on Meta::CPAN
Not as special case parenthesised unop:
# expr
{ -op => [ 'not', { -ident => 'explosive' } ] }
# query
(NOT explosive)
[]
Postfix unop: (is_null, is_not_null, asc, desc)
# expr
{ -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
# query
bobby IS NULL
[]
AND and OR:
t/01generate.t view on Meta::CPAN
{Beer => 'is', Yummy => '%YES%', IT => ['IS','REALLY','GOOD']}],
stmt => 'UPDATE testin.table2 SET FIVE = ?, One = ?, Three = ? WHERE '
. '( Beer LIKE ? AND ( ( IT LIKE ? ) OR ( IT LIKE ? ) OR ( IT LIKE ? ) ) AND Yummy LIKE ? )',
stmt_q => 'UPDATE `testin`.`table2` SET `FIVE` = ?, `One` = ?, `Three` = ? WHERE '
. '( `Beer` LIKE ? AND ( ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) ) AND `Yummy` LIKE ? )',
bind => [['FIVE', 66], ['One', 22], ['Three', 44], ['Beer','is'],
['IT','IS'], ['IT','REALLY'], ['IT','GOOD'], ['Yummy','%YES%']],
},
{
func => 'select',
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%)],
},
{
func => 'select',
args => ['Yo Momma', '*', { user => 'nwiger',
-nest => [ workhrs => {'>', 20}, geo => 'ASIA' ] }],
stmt => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? ) OR ( geo = ? ) ) AND user = ? )',
stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? ) OR ( `geo` = ? ) ) AND `user` = ? )',
t/02where.t view on Meta::CPAN
use strict;
use warnings;
use Test::More;
use Test::Warn;
use Test::Exception;
use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper) ];
use SQL::Abstract;
my $not_stringifiable = bless {}, 'SQLA::NotStringifiable';
my @handle_tests = (
{
where => 'foo',
order => [],
stmt => ' WHERE foo',
bind => [],
},
{
where => {
t/05in_between.t view on Meta::CPAN
\Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
\Qversion of SQL::Abstract will emit the logically correct SQL \E
\Qinstead of raising this exception)\E
/x,
where => { x => { -in => [ 1, undef, 2, 3, undef ] } },
stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )",
bind => [ 1, 2, 3 ],
test => '-in with multiple undef elements',
},
{
where => { a => { -in => 42 }, b => { -not_in => 42 } },
stmt => ' WHERE a IN ( ? ) AND b NOT IN ( ? )',
bind => [ 42, 42 ],
test => '-in, -not_in with scalar',
},
{
where => { a => { -in => [] }, b => { -not_in => [] } },
stmt => ' WHERE ( 0=1 AND 1=1 )',
bind => [],
test => '-in, -not_in with empty arrays',
},
{
throws => qr/
\QSQL::Abstract before v1.75 used to generate incorrect SQL \E
\Qwhen the -IN operator was given an undef-containing list: \E
\Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
\Qversion of SQL::Abstract will emit the logically correct SQL \E
\Qinstead of raising this exception)\E
/x,
where => { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } },
stmt => ' WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )',
bind => [ 42, 42 ],
test => '-in, -not_in with undef among elements',
},
{
throws => qr/
\QSQL::Abstract before v1.75 used to generate incorrect SQL \E
\Qwhen the -IN operator was given an undef-containing list: \E
\Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
\Qversion of SQL::Abstract will emit the logically correct SQL \E
\Qinstead of raising this exception)\E
/x,
where => { a => { -in => [undef] }, b => { -not_in => [undef] } },
stmt => ' WHERE ( a IS NULL AND b IS NOT NULL )',
bind => [],
test => '-in, -not_in with just undef element',
},
{
where => { a => { -in => undef } },
throws => qr/Argument passed to the 'IN' operator can not be undefined/,
test => '-in with undef argument',
},
{
where => { -in => [ 'bob', 4, 2 ] },
stmt => ' WHERE (bob IN (?, ?))',
t/22op_value.t view on Meta::CPAN
{
local $SIG{__WARN__} = sub { warn @_ unless $_[0] =~ /Supplying an undefined argument to '(?:NOT )?LIKE'/ };
($sql, @bind) = $sql_maker->where({
c1 => undef,
c2 => { -value => undef },
c3 => { '=' => { -value => undef } },
c4 => { '!=' => { -value => undef } },
c5 => { '<>' => { -value => undef } },
c6 => { '-like' => { -value => undef } },
c7 => { '-not_like' => { -value => undef } },
c8 => { 'is' => { -value => undef } },
c9 => { 'is not' => { -value => undef } },
});
is_same_sql_bind (
$sql,
\@bind,
"WHERE ${q}c1${q} IS NULL
AND ${q}c2${q} IS NULL
AND ${q}c3${q} IS NULL
t/80extra_clauses.t view on Meta::CPAN
is_same_sql_bind(
$sql, \@bind,
q{INSERT INTO eh VALUES (?, ?), (?, ?), (?, ?)},
[ 1..6 ],
);
is_same_sql(
$sqlac->select({
select => '*',
from => 'foo',
where => { -not_exists => {
-select => {
select => \1,
from => 'bar',
where => { 'foo.id' => { -ident => 'bar.foo_id' } }
},
} },
}),
q{SELECT * FROM foo
WHERE NOT EXISTS (SELECT 1 FROM bar WHERE foo.id = bar.foo_id)},
);
view release on metacpan - search on metacpan
( run in 1.656 second using v1.00-cache-2.02-grep-82fe00e-cpan-f5108d614456 )