Ever More Testy Moose

It is go back again day here in the Moose-pen

In my last bunch of posts I though I had finally got all my element view, parentheses and 'AND' ; checking coming out all OK so I started today by going back to here I left off in Driver::DBI. The first thing I did was re-run the test suite and I ran into this

#   Failed test 'Where with function and 1 param SQL correct'
#   at D:\GitHub\database-accessor-driver-dbi\t\lib/Test/Utils.pm line 56.
# Expected SQL--> SELECT people.first_name, people.last_name, people.user_id
 FROM people WHERE length(people.last_name) = ?
# Generated SQL-> SELECT people.first_name, people.last_name, people.user_id
 FROM people WHERE  AND length(people.last_name) = ?

in the '20_where_basic.t' case and similar one all over the rest of the test cases. I guess I am adding an 'AND' in there when there is only one condition. Back to Database::Accessor I go.

The first thing I forgot to do it put in a test in '58_parenthes.t' that tests for no add of 'AND' when there is only one condition. This leads to this question what if the user adds in only one conditions and leaves in a 'condition' attribute, like this;

$da->add_condition(
    {
        left      => $expression,
        right     => { value => '201' },
        operator  => '=',
        condition => 'AND',
    }
);
Now do I fail the above or do I play smarty pants and just drop the 'AND' before I send it to the DAD. As I am doing the same sort of thing with 'view', by inheriting the DA's view I think I will do the same sort of thing here. Now my test looks like this;

$da = Database::Accessor->new($in_hash);
$da->add_condition(
    {
        left      => $expression,
        right     => { value => '201' },
        operator  => '=',       
    }
);
ok(
    $da->retrieve( Data::Test->new(), $return ),
    "Balanced nested elements on condition"
);
$dad = $da->result->error();
ok( !$da->result->error->conditions->[0]->predicates->condition() ,
    'AND condition not added when only 1 condition in conditions array' );
$da->reset_conditions();
$da->add_condition(
    {
        left      => $expression,
        right     => { value => '201' },
        operator  => '=',       
        condition => 'AND',
    }
);
$da->retrieve( Data::Test->new(), $return );
ok( !$da->result->error->conditions->[0]->predicates->condition(),
    'AND condition ignored when present and only 1 condition in conditions array' );
$da->reset_conditions();
$da->add_condition([
    {
        left      => $expression,
        right     => { value => '201' },
        operator  => '=',       
    },
    {
        left      => $expression,
        right     => { value => '201' },
        operator  => '=',       
    }]
);
$da->retrieve( Data::Test->new(), $return );
ok(! $da->result->error->conditions->[0]->predicates->condition(),
    'AND not present on first condition predicate' );
ok( $da->result->error->conditions->[1]->predicates->condition() eq 'AND',
    'AND present on second condition predicate' );
...
which should cover all the possibilities. Of course when I run the above I get I will get one pass and three expected fails;
not ok 14 - AND condition not added when only 1 condition in conditions array not ok 15 - AND condition ignored when present and only 1 condition in conditions array not ok 16 - AND not present on first condition predicate ok 17 - AND present on second condition predicate
After a little while tracing this back the first thing that I notices was that my '_has_conditions' count was accumulating way off between 'executes' and I fixed that by adding this in

...
    private_method _elements_check => sub {
        my $self = shift;
        my ($action) = @_;
        $self->_reset_parens();
++      $self->_reset_conditions(); 
…
That did not solve my problem directly but it was the first step as next all I had to do was check to see if my count is two or more like this;

...
  elsif (ref($element) eq 'Database::Accessor::Condition'){
           $element->predicates->condition(Database::Accessor::Constants::AND)
--             if ( $self->_has_conditions and !$element->predicates->condition() );
++             if ( $self->_has_conditions>=2 and !$element->predicates->condition() );
…
and that squishes two fails
ok 14 - AND condition not added when only 1 condition in conditions array not ok 15 - AND condition ignored when present and only 1 condition in conditions array ok 16 - AND not present on first condition predicate ok 17 - AND present on second condition predicate
Next a line to take out the 'AND', or any other condition, if there is only one condition is present;

...
           $element->predicates->condition(Database::Accessor::Constants::AND)
             if ( $self->_has_conditions>=2 and !$element->predicates->condition() );
++           $element->predicates->condition(undef)
++             if ( $self->_has_conditions<=1);
           $self->_check_parentheses($element->predicates);
…
and now
... ok 14 - AND condition not added when only 1 condition in conditions array ok 15 - AND condition ignored when present and only 1 condition in conditions array ok 16 - AND not present on first condition predicate ok 17 - AND present on second condition predicate …
When I go back and run '20_where_basic.t' from Driver::DBI I now get the correct

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

Next will be to add in a bunch more tests the check for the same sort of thing where other conditions are used but I am not going to bore you with those!


sa96-2.jpg

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