byterock

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'},

                                          elements=>[{name=>'username'}],

                                          conditions=>{left  =>{ name  => 'username',

                                view  => 'user'},

                      right =>{ value => 'Bill'}}});

$user->reset_conditions();

$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' => [

 $VAR1->{'dynamic_conditions'}[0]{'predicates'}[0]{'right'}{'value'}

         ],

    '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"){

      $self->is_exe_array(1);

    }

    $self->add_param($element);

    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

      unless($self->da_raise_error_off);

++    $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 [

        qw(da_compose_only

           da_no_effect

         )

        ] => (

--          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->da_compose_only();

            $da->retrieve( $self->dbh() );

            my $sql = join( " ",

                Database::Accessor::Driver::DBI::SQL::OPEN_PARENS,

                $da->result->query(),

                Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS );

            foreach my $sub_param ( @{ $da->result->params() } ) {

                $self->add_param(

                    Database::Accessor::Param->new( { value => $sub_param } ) );

            }

            return $sql;

        }

        elsif ( ref( $element->value ) eq "ARRAY" ) {

            $self->is_exe_array(1);

        }

        $self->add_param($element);

        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.