byterock

Its advance my code day here in the Moose-Pen.

Seems I have hit a milestone in the Database::Accessor/Driver::DBI project I have completed all the easy stuff and I am moving over to the more complicated or at least ignored till now parts.

I decided to work on some of the more advance operators found in conditional statements namely 'Between', 'In', 'Not In', 'Like', 'Is Null' and 'Is Not Null'. Now one problem with these six operators is they are very SQLish, epically the 'Like' operator, and despite me trying to come up with a more generic set of operator names just looked too MongoDB specific. I wonder if one of the devlopers of MongoDB had the same problem to solve?

To start off I created a new test case '32_where_operators.t' that is basically a clone of '30_where_basic.t' but I stripped out all the tests and to start I will try this one;

conditions => [

            {

                left => {

                    name => 'salary',

                    view => 'people'

                },

                right     => [{ value => 35000 },

                              { value => 50000 }

                             ],

                operator  => 'BETWEEN',

            },

        ],

which will create the SQL 'Where' like this;

WHERE people.salary BETWEEN ? AND ?

Now to make the change to Driver::DBI I will have to have a look at the '_predicate_sql' sub where I do the SQL for a predicate. (remember a predicate?) and I see that I must have been looking ahead as I have this;

    

    if (Database::Accessor::Driver::DBI::SQL::SIMPLE_OPERATORS->{ $predicate->operator }){

        $clause .= join(" ",$self->_field_sql($predicate->left,1),

                $predicate->operator,

                $self->_field_sql($predicate->right,1));

     }

and the SIMPLE_OPERATORS are defined as '=', '!=' , '<>' etc. All I need to do is extend that if with an else if like this;

         $self->_field_sql($predicate->right,1));

     }

     elsif ($predicate->operator eq Database::Accessor::Driver::DBI::SQL::BETWEEN) {

to handle the Between operator. Now for the code in this path I am going to try

        $self->_field_sql($predicate->right,1));

     }

     elsif (uc($predicate->operator) eq 

              Database::Accessor::Driver::DBI::SQL::BETWEEN) {


         $clause .= join(" ",$self->_field_sql($predicate->left,1)

                          ,join(" ",Database::Accessor::Driver::DBI::SQL::BETWEEN

                                   ,$self->_field_sql($predicate->right->[0],1)

                                   ,Database::Accessor::Driver::DBI::SQL::AND

                                   ,$self->_field_sql($predicate->right->[1],1)

                               )

                    );

    }

and on my first run I get only little typo in my expected SQL for update test and after that is corrected I get a full pass;

ok 1 - Between Operator Params create SQL correct

ok 2 - Between Operator Params create params correct

ok 3 - Between Operator Params retrieve SQL correct

ok 4 - Between Operator Params retrieve params correct

ok 5 - Between Operator Params update SQL correct

ok 6 - Between Operator Params update params correct

ok 7 - Between Operator Params delete SQL correct

ok 8 - Between Operator Params delete params correct

I carried on from here and added in other tests cases with the various combinations of left and right elements until I had some 46 in total.

After I had completed all those tests I got thinking what would happen is my end user did this;

conditions => [

            {

                left => {

                    name => 'salary',

                    view => 'people'

                },

                right     =>{ value => 35000 },

                operator  => 'BETWEEN',

            },

        ],

or this

conditions => [

            {

                left => [{ value => 35000 },

                              { value => 50000 }

                             ],

                right     => [{ value => 35000 },

                              { value => 50000 }

                             ],

                operator  => 'BETWEEN',

            },

        ],

obviously both are wrong and I should warn about this well before it gets to the DB engine. I could do this check at the Accessor.pm level but I want to avoid making Accessor.pm too SQL specific so the best place for this is in the Driver::DBI code.

Now the interesting thing is I have not come across any error conditions similar to this in Driver::DBI, I do capture DBI errors and return them but I want to stop this error from ever getting to the DBI. I first need a generic message I can re-use;

++  my $message = "Database::Accessor::Driver::DBI::Error->Operator ";

    if (Database::Accessor::Driver::DBI::SQL::SIMPLE_OPERATORS->{ $predicate->operator }){

…

and then two 'die' statements, one for the right and one for the left;

    elsif (uc($predicate->operator) eq Database::Accessor::Driver::DBI::SQL::BETWEEN) {

++      die("$message 'BETWEEN' right must be an Array Ref of two parameters!") 

++         if ( ref( $predicate->right()) ne 'ARRAY' 

++             and scalar( @{ $predicate->right() } ) != 2 );

++      die("$message 'BETWEEN' left can not be an Array Ref!") 

++         if (  ref( $predicate->left()) eq 'ARRAY' );

and a few more of tests using my old friend 'Test::Fatal' like this one;

$in_hash->{conditions} = [

    {

        left => {

            name => 'salary',

            view => 'people'

        },

        right    => { value => 35000 },

        operator => 'BETWEEN',

    },

];

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

like(

    exception { $da->retrieve( Test::Utils->connect() ) },

    qr /right must be an Array Ref of two paramameters/,

    "Right must be an array-ref "

);

I got only partiality what I expected, namely this

  'Not an ARRAY reference at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 293.

which I fixed with this;

die("$message 'BETWEEN' right must be an Array Ref of two parameters!") 

--          if ( ref( $predicate->right()) ne 'ARRAY' 

--             and scalar( @{ $predicate->right() } ) != 2 );

++          if ( (ref( $predicate->right()) ne 'ARRAY') 

++               or scalar( @{ $predicate->right() } ) != 2 );

and now I get;

...

ok 47 - Right must be an array-ref 

ok 48 - Right must be an array-ref of two parameters

ok 49 - Left must not be an array-ref 

so not a bad day's work;