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.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment