Baby Moose Takes Charge

Its get very ambitious day here in the Moose-pen.

As things went just a little to smoothly over the past few post I though it would be a good day to try something that I know is going to cause me grief. In the vain I added this test;

use Database::Accessor;
my $is_bill = Database::Accessor->new({view=>{name=>'user'},
conditions=>{left =>{ name => 'username',
view => 'user'},
right =>{ value => 'Bill'}}});
$user->add_condition({left =>{ name => 'username',
view => 'user'},
right =>{ value => $other_user}
ok($user->retrieve($utils->connect()),"retrieve function");

In the above I first create another Database::Accessor instance of one element and a single condition, next I reset the conditions on the $user DA and then add a new condition where I use the new DA $$is_bill as the 'right' side of the predicate, finally, I do a retrieve on the $user DA.

What I am attempting to do is generate the following valid SQL

SELECT user.username,user.address
FROM users
WHERE user.username = (SELECT user.username
FROM user
WHERE user.username=?)

This is something the older version of Data::Accessor that I have yet to add to the Moose version so of course the first error I get is;

Attribute (value) does not pass the type constraint because: Validation failed for 'ArrayRef|Str|Undef' with value Database::Accessor{ _ld

The first thing to change is that 'value' attribute in a param in Database::Accessor;

has value => (
is => 'rw',
-- isa => 'Str|Undef|ArrayRef',
++ isa => 'Str|Undef|ArrayRef|Database::Accessor',
alias => 'param',

The good news is the above sort of worked as the retrieve ran but in my results I got

'params' => [
'query' => 'SELECT user.username, user.address FROM user WHERE user.username = ?',

which means just the scalar value of that predicate was passed in which would result in an empty set.

What I have to do is trigger the SQL generation and return it in place of the '?' in the above SQL and that will require a few changes to my DBI::Driver.

Now this where I discovered that I painted my-self into a corner a little when I made that decision many moons ago to keep the DAD out of the hands of the user of the DA. The fist change I must do is in the Driver::DBI '_elements_sql' sub.

my ($element,$use_alias) = @_;
if (ref($element) eq 'Database::Accessor::Param'){
++ if (ref($element->value) eq "Database::Accessor"){
++ my $da = $element->value;
++ }
-- if (ref($element->value) eq "ARRAY"){
++ elsif (ref($element->value) eq "ARRAY"){
return Database::Accessor::Driver::DBI::SQL::PARAM;

now I can get the DA but I can't get at the underlining DAD to generate the SQL I have to use the 'retrieve' function like this an I will get a 'results' class;

my ($element,$use_alias) = @_;
if (ref($element) eq 'Database::Accessor::Param'){
if (ref($element->value) eq "Database::Accessor"){
my $da = $element->value;
++ my $sql = $da->retrieve();

however that is not going to work as I need a connection object for that retrieve. So I had to add in a new attribute to the Driver::DBI to hold the current $dhb;

++has dbh => (
++ is => 'rw',
++ isa => 'DBI::db',
++ );
has is_exe_array => (

and as part of the above I will add in a bit of code to set it in the 'execute' sub

local $dbh->{RaiseError} = 1
++ $self->dbh($dbh);

and I can change my code to this;

my $da = $element->value;
++ my $sql = $da->retrieve($self->dbh);

Unfortunately that is not going to work as it stands above that will execute the SQL not something we want to happen at this time we just want the SQL. I can get that with the 'da_compose_only' flag but again I painted myself in a corner. In Database::Accessor that is a read-only attribute. So a quick change here;

has [
] => (
-- is => 'ro',
++ is => 'rw',
isa => 'Bool',
default => 0,
traits => ['ENV'],

and the final change is;

if ( ref($element) eq 'Database::Accessor::Param' ) {
if ( ref( $element->value ) eq "Database::Accessor" ) {
my $da = $element->value;
$da->retrieve( $self->dbh() );
my $sql = join( " ",
Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS );
foreach my $sub_param ( @{ $da->result->params() } ) {
Database::Accessor::Param->new( { value => $sub_param } ) );
return $sql;
elsif ( ref( $element->value ) eq "ARRAY" ) {
return Database::Accessor::Driver::DBI::SQL::PARAM;
else {

In the above I get my query back from the result class and format it with parentheses and then I have to account for any params that might have been in that DA so I iterate over the 'params' array from the 'result' class and add a new 'Param' class for each values.

So with the above changes in the end I get this;

DBD::DBM::st execute failed: You passed 1 parameters where 0 required [for Statement "SELECT user.username, user.address FROM user WHERE user.username = ( SELECT user.username FROM user WHERE user.username = ? )"]

Not as bad as it seems, as I have discovered that DBD::DBM does not support this sort of query in a query. But my generated SQL looks perfect. So good work for today.


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