byterock

Its where day in the Moos-Pen

Today I am going to expand on the '20_where_basic.t' test case going from a simple one level Element to Param 'where clause' such as

SELECT people.first_name, people.last_name, people.user_id FROM people WHERE ( people.first_name = ? AND people.last_name = ?

to the much more complex Function, Expression, nested and mixed clauses that I was using in the last few posts.

First I decided to re-factor my tests from the start and using the 'element_sql_ok' sub from this post I re-factored it to this sub

sub sql_param_ok {

    my $self = shift;

    my ( $dbh, $in_hash, $opts ) = @_;

    $in_hash->{ $opts->{key} }->[ $opts->{index} ] = $opts->{ $opts->{key} };

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

    $da->retrieve($dbh);

    ok(

        $da->result()->query() eq $opts->{sql},

        $opts->{caption} . " SQL correct"

    );

    cmp_deeply( $da->result()->params, $opts->{params},

        $opts->{caption} . " params correct" );

}

I will can now to the SQL and param tests on any sort of attribute I can thing of not just an 'Element', of course I will have to go back and change some tests but it is a small price to pay.

Now my first test is;

my $tests = [{

    index=>0,

    key  =>'conditions',

    conditions => { left =>{function => 'length',

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

                   right => { param =>3}},

    caption => "Where with function and 1 param",

    sql     => "SELECT people.first_name, people.last_name, people.user_id FROM people WHERE length(people.last_name) = ?",

    params  => [3]

}];

and the code to run it

$in_hash->{conditions} = [''];

my $dbh = $utils->connect();

foreach my$test (@{$tests}){

  $utils->sql_param_ok($dbh,$in_hash,$test);

}                  

So all I need to do if I want more tests is add in more hash-ref rows to the '$tests' array-ref.

On my first run I unfortunately got;

ok: plan before you test! at D:\GitHub\database-accessor-driver-dbi\t\lib/Test/Utils.pm line 46

Opps! will have to see what I am doing wrong in the Test::Utils Class and a quick look in there I see that I am trying to do this

my $utils =  Test::Utils->new();

use Test::More  tests => 12;

the fix was simple enough;

use Test::More  tests => 12;

my $utils =  Test::Utils->new();

I had to get the test count in there before I instantiated the class and now I get

ot ok 1 - Where with function and 1 param SQL correct

ok 2 - Where with function and 1 param params correct

a good start as a quick look at the SQL that is reruned I and see

SELECT people.first_name, people.last_name, people.user_id FROM people WHERE length(.last_name) = ?

so no table/view name on that '.last_name' so the same sort of error as before so back into Accessor.pm yet again.

I will have to do the same sort of check that I do on 'Elements' but I cannot re-use the 'check_elements' sub as that one cleans out items that should not go down to a DAD, such as on a 'delete'. I need a new sub and I want to make it as generic as possible from the start as I know I will be doing the same sort of checks on 'Links/Joins' as both use the same 'Database::Accessor::Predicate' role.

Here is my first stab at it

 private_method check_predicates => sub {

         my $self = shift;

         my ($items) = @_;

         foreach my $item (@{$items}){

           foreach my $predicate (@{$item->predicates()}){

             $self->check_view($predicate);             

         }

        }

         return $items;

    };

Simple enough I iterate over the incoming array ref and then the predicates under each array ref checking each with the check_view sub which should be able to handle a 'Predicate' class as has the same 'Database::Accessor::Roles::Comparators' role that 'Function' and 'Expression' have.

I will first need to call the 'check_predicates' here

  elements=> ($action ne Database::Accessor::Constants::DELETE) ? $self->get_dad_elements($action,$opt):[],

–-  conditions=>[@{$self->conditions},@{$self->dynamic_conditions}],

++  conditions=> $self->check_predicates([@{$self->conditions},@{$self->dynamic_conditions}]),

    links         => [@{$self->links},@{$self->dynamic_links}],

and on my first run I still get a fail on that SQL;

SELECT people.first_name, people.last_name, people.user_id FROM people WHERE length(.last_name) = ?

A quick check of the 'check_view' sub and I see that I am skipping 'Predicates' and this is the fix for that;

 return 

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

              and

                 (ref($element) ne "Database::Accessor::Expression")

++               and

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

and I get;

ok 1 - Where with function and 1 param SQL correct

ok 2 - Where with function and 1 param params correct

Now to add in a bunch more. But that is for another post.