| Subject: | Example in documentation broken! |
I'm trying the example in the manual:
my @where = ( {
user => 'nwiger',
status => ['pending', 'dispatched'],
}, {
user => 'robot',
status => 'unassigned',
} );
Unfortunately, the generated SQL is coming back wrong. I'm getting:
WHERE ( ( ( ( status = ? ) OR ( status = ? ) ) OR user = ? ) OR ( status = ? OR user = ? ) )
Instead of the correct:
WHERE ( ( ( ( status = ? ) OR ( status = ? ) ) AND user = ? ) OR ( status = ? AND user = ? ) )
The fix seems easy: Change line 345 of Abstract.pm so that the 2nd argument is left off. I'm not convinced that's the correct fix though...
Anyway, to help you, I've attached a test file which will demonstrate the bug, amongst other things. You'll need Test::More installed for it to work.
Thanks,
-Dom
use strict;
use Test::More tests => 12;
BEGIN { use_ok( "SQL::Abstract" ) }
my $sql = SQL::Abstract->new;
isa_ok( $sql, 'SQL::Abstract' );
# Each example on the man page should be here.
#
# XXX These tests are problematic because there is no order defined for
# a hash.
my @tests = (
{
name => 'first example',
input => {
worker => [ 'nwiger', 'bob', 'jim' ],
status => { '!=', 'completed' }
},
expected_sql => ' WHERE ( status != ? AND ( ( worker = ? ) OR ( worker = ? ) OR ( worker = ? ) ) )',
expected_bind => [ 'completed', 'nwiger', 'bob', 'jim' ],
},
{
name => 'simple hash',
input => {
user => 'nwiger',
status => 'completed'
},
expected_sql => ' WHERE ( status = ? AND user = ? )',
expected_bind => [ 'completed', 'nwiger' ],
},
{
name => 'simple hash with sub or clause',
input => {
user => 'nwiger',
status => ['assigned', 'in-progress', 'pending'],
},
expected_sql => ' WHERE ( ( ( status = ? ) OR ( status = ? ) OR ( status = ? ) ) AND user = ? )',
expected_bind => [ 'assigned', 'in-progress', 'pending', 'nwiger' ],
},
{
name => 'different operator',
input => {
user => 'nwiger',
status => { '!=' => 'completed' },
},
expected_sql => ' WHERE ( status != ? AND user = ? )',
expected_bind => [ 'completed', 'nwiger' ],
},
{
name => 'lots of and/or sub clauses',
input => [
{
user => 'nwiger',
status => ['pending', 'dispatched'],
},
{
user => 'robot',
status => 'unassigned',
},
],
expected_sql => ' WHERE ( ( ( ( status = ? ) OR ( status = ? ) ) AND user = ? ) OR ( status = ? AND user = ? ) )',
expected_bind => [ 'pending', 'dispatched', 'nwiger', 'unassigned', 'robot' ],
},
);
foreach my $t ( @tests ) {
my ($sql, @bind) = $sql->where( $t->{input} );
is( $sql, $t->{expected_sql}, "where( $t->{name} ) SQL" );
is_deeply( \@bind, $t->{expected_bind}, "where( $t->{name} ) bind" );
}
# vim: set ai et sw=4 syntax=perl :