byterock

It carry on with recursion day here in the Moose-Pen

Carrying on from where I left off in yesterday's post I was about to add in this new test;

$in_hash->{elements}->[0] = { function => 'substr',

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

                                 right => [{ param =>3},{ param =>5}] };

my $da = Database::Accessor->new($in_hash);

$da->retrieve( $utils->connect() );

ok(

    $da->result()->query() eq

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

      "Function with 2 params bind SQL correct"

);

cmp_deeply(

           $da->result()->params,

           [3,5,'Bill'],

           "Function params correct"

          );

This time round I am testing to see if my new code to handle a 'Function' element will handle a function with more than one option. In this case 'substr(user.username,3,5). Once I got the test in there I gave it a try and got;

Can't call method "view" on unblessed reference at database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 436.

So no free pass today.

After a little debugging I traced it back to these lines

 if (ref($element->right()) eq "Array"){

        @params= @{$element->right()};

      }

      else {

        push(@params,$element->right());

      }

in 'sub _element_sql'. When I do the above I get an array-ref in an array-ref and a little later on when I do this;

foreach my $param (@params){

        push(@right_sql,$self->_element_sql($param));

      }        

that $param I am passing in is an Array-ref that gets processed incorrectly on the recursion. The fix was easy enough

--      my @params;

--    if (ref($element->right()) eq "Array"){

--      @params= @{$element->right()};

++    if (ref($element->right()) ne "Array"){

++       my $param = $element->right();

++       $element->right([$param]);

++      }

--    else {

--      push(@params,$element->right());

--     }

--    foreach my $param (@params){

++    foreach my $param (@{$element->right()}){ 

Just check for no array then add that to itself as an array-ref and then iterate over the '$element->right()' . On my first try I got this as a SQL result;

   'params' => [3,5,11,'Bill'],

    'query' => 'SELECT substr(user.username,?,?), left(user.username,?), user.address FROM user WHERE user.username = ?',

which is all correct but the tests failed as I did not expect that extra 'left' in there. Fortunately that was just a mistake in how I was changing the $in_hash should have been index '1' not '0' and when I corrected that I got;

ok 1 - Function with 1 param bind SQL correct

ok 2 - Function params correct

ok 3 - Function with 2 param binds SQL correct

ok 4 - Function params correct

Well that went well so it is time to get a little cocky and I gave this a try

$in_hash->{elements}->[1] = { function => 'substr',

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

                                 right => [{ param =>3},

                                           {  function => 'left',

                                                 left  => { name => 'address' },

                                                 right => { param =>4}}] };

my $da = Database::Accessor->new($in_hash);

$da->retrieve( $utils->connect() );

ok(

    $da->result()->query() eq

      "SELECT user.username, substr(user.username,?,left(user.address,?)), user.address FROM user WHERE user.username = ?",

      "Function within a function SQL correct"

);

cmp_deeply(

           $da->result()->params,

           [3,4,'Bill'],

           "Function withing a function params correct"

          );

so a function within a function; My result was a fail as the SQL was

 'SELECT user.username, substr(user.username,?,left(.address,?)), user.address FROM user WHERE user.username = ?',

Missing the table name on 'left(.address,?)' so I will have to put a little more recursion in my Accessor.pm code. I already have the test to check the first layer of a function in the 'check_view' sub

}

else{

     $self->check_view($element->right)

       if (ref($element->right) eq 'Database::Accessor::Element');

     $self->check_view($element->left)

       if (ref($element->left) eq 'Database::Accessor::Element');  

}

and after a bit of playing about I came up with this;

}

else{

++          return 

++            if (ref($element) ne "Database::Accessor::Function");

++          map( $self->check_view($_),@{$element->left})              

++            if (ref($element->left) eq "ARRAY");

++          map( $self->check_view($_),@{$element->right})

++             if (ref($element->right) eq "ARRAY");

            $self->check_view($element->right)

 …

first I check to ensure I have a function (as it is the only one so far with a left and right), then I use a map to recourse down into 'check_view' each item in a left and right if they happen to be an array and it worked

ok 1 - Function within a function SQL correct

ok 2 - Function withing a function params correct

Now I did try one more test a function in a function in a function in a function and I got a fail but that was because my last two old lines where limiting my recursion so with this quick change;

          map( $self->check_view($_),@{$element->right})

             if (ref($element->right) eq "ARRAY");

++          $self->check_view($element->right);

--          $self->check_view($element->right)

--            if (ref($element->right) eq 'Database::Accessor::Element');

++          $self->check_view($element->left);

--          $self->check_view($element->left)

--            if (ref($element->left) eq 'Database::Accessor::Element');  

I got it working and for fun I successfully tested it to ten levels of recursion. Though I am not going to bother to leave that test in.

Now onto something new;