In Moose

Its is in day here in the Moose-pen

Now that I have a few little API things worked out from yesterday's post I better carry on and do the next two 'operators' 'In' and 'Not In'

My original though was these two operators would now require a little parameter validation like I did for the 'Between' as most of us know the very common form of an SQL 'In' queries one like these two;


SELECT * FROM users WHERE id in (SELECT user_id FROM drivers) ;
SELECT * FROM users WHERE id in (109,100,22) ;

should just be handled by the present type and coercion code in the system but I just read that this form of SQL

SELECT * FROM users WHERE id in (SELECT user_id FROM drivers,22) ;

may be valid on some DBs though not on my Oracle or Postgres and I think the above is just an anomaly of the SQL parser in question as I could find nothing like the above in the spec. I did discover that this is a valid SQL though

SELECT * FROM users WHERE id in (parent_id)

and I have never seen or used that one before and even this one is valid

SELECT * FROM users WHERE id in (parent_id,1002)

Than means I can have a mix of 'views/fields' and 'params' or a SQL query I am going to create a test;

conditions => [
{
left => {
name => 'salary',
view => 'people'
},
operator => 'In',
right => [{value=>'10000'},
{value=>'10010'},
{value=>'10020'},]
},
],

which should generate SQL 'where' like this

WHERE people.salary IN (?,?,?)

and I am going to go with this patch to start;

elsif ( $predicate->operator eq Database::Accessor::Driver::DBI::SQL::IN
|| $predicate->operator eq Database::Accessor::Driver::DBI::SQL::NOT_IN )
{

die("$message '".$predicate->operator."' left can not be an Array Ref!")
if ( ref( $predicate->left()) eq 'ARRAY' );

die("$message '".$predicate->operator."' right can only be a Database::Accessor left can not be an Array Ref!")
if ( ref( $predicate->right()) ne "ARRAY"
and ref($predicate->right()) ne "Database::Accessor" );


$clause .= join(" ",$self->_field_sql($predicate->left,1)
,$predicate->operator()
,Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
,$self->_field_sql($predicate->right,1)
,Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS
);
}

Now lets see what we get?

Can't call method "name" on unblessed reference at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 437.

hmm that one is telling me that my '_field_sql' is falling though to the bottom of the if/else and failing on the element passed down to that sub. What is being passed in is this;

[
bless( {
'value' => '10000'
}, 'Database::Accessor::Param' ),
bless( {
'value' => '10010'
}, 'Database::Accessor::Param' ),
bless( {
'value' => '10020'
}, 'Database::Accessor::Param' )
];

which is the 'right' part of my condition what I am not handling is an array-ref coming down into that sub. Now the question is how to handle it? I think I can get way with just this little add on to that 'if/else' block

...
}
elsif (ref($element) eq "ARRAY"){

my @clauses = "";
foreach my $item (@{$element}){
push(@clauses,$self->_field_sql($item,$use_view));
}
return join(",",@clauses);

}
else {
my $sql = $element->name;
...

and this time round I get this little error to start

# Expected SQL--> WHERE people.salary IN (?, ?, ?)
# Generated SQL-> WHERE people.salary IN ( ,?,?,? )

but at lease my SQL prams are correct

ok 4 - In Operator Params retrieve params correct

The fix is simple me thinks just this small change;

$clause .= join(" ",$self->_field_sql($predicate->left,1)
,$predicate->operator()
,Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
,Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
-- ,$self->_field_sql($predicate->right,1)
++ .$self->_field_sql($predicate->right,1)
-- ,Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS
++ .Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS
);

opps same mistke

# Expected SQL--> WHERE people.salary IN (?, ?, ?)
# Generated SQL-> WHERE people.salary IN (,?,?,?)

here is the offending code

elsif (ref($element) eq "ARRAY"){

-- my @clauses = “”;
++ my @clauses = ();

and I keep that other one as well and now I get a full pass.

The next test is a little tricky as I have to pass in an instantiated Database::Accessor that only returns on field and I think I will add in a new sub on Test::Utils that will do that for me;


my $da = Database::Accessor->new(
{
view => {name=>'address' },
elements => [ { name => 'user_id' } ],
conditions => [
{
left => {
name => 'country',
view => 'address'
},
right => { value => 'CA' },
operator => '=',
},
],
}
);
return $da;
}

and now the conditions part of my next test will be;

{
left => {
name => 'id',
view => 'people'
},
operator => 'In',
right =>{value=>Test::Utils::in_da_sql}
},

and that should just give me this SQL;

WHERE people.id in (SELECT address.user_id FROM address WHERE address.country = ?)

Now I do not need to add new code to Driver::DBI as I had add the code to handle a param that was a Database::Accessor some time ago but lets see what I get with this;

Database::Accessor::Driver::DBI::Error->Operator 'IN' right can only be a Database::Accessor left can not be an Array Ref! at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 330.
#

I had my validation a little wrong this patch fixed that;

die("$message '".$predicate->operator."' right can only be a Database::Accessor or an Array Ref!")
-- if ( ref( $predicate->right()) ne "ARRAY"
-- and ref($predicate->right()) ne "Database::Accessor" );
++ unless ( ref( $predicate->right()) ne "ARRAY"
++ or ref($predicate->right()) ne "Database::Accessor" );

and now I get;

WHERE people.id IN ((SELECT address.id FROM address WHERE address.country = ?))

and that is just a quick change;

-- my $sql = join(" ",
-- Database::Accessor::Driver::DBI::SQL::OPEN_PARENS,
-- $da->result->query(),
-- Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS );
++ my $sql = $da->result->query();

I am getting this though

DBD::DBM::st execute failed: Cannot open D:\GitHub\database-accessor-driver-dbi\t\db\test\address.lck: No such file or directory (2) at C:/Dwimperl/perl/site/lib/DBI/DBD/SqlEngine.pm line 1624 at C:/Dwimperl/perl/site/lib/DBI/DBD/SqlEngine.pm line 1271
[for Statement "SELECT address.user_id FROM address WHERE address.country = ?"] at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 114.

which means that Database::Accessor I am passing in is actually trying to run the query which is not a good thing. I did attempt to put the 'da_compose_only' flag on but I only called it and didn't set it. This patch fixes that;

elsif (ref($element) eq "Database::Accessor::Param"){

if (ref($element->value) eq "Database::Accessor"){
my $da = $element->value;
-- $da->da_compose_only();
++ $da->da_compose_only(1);
$da->retrieve($self->dbh());
my $sql = $da->result->query();

and now I get 100% pass

Not bad for one day.


_MG_2180a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations