byterock

Its solipsism day here in the Moose Pen

Getting down to the last few operators on my list and today I will hopefully be an easy one 'Exists'

At least this one should be easy to validate as you can only ever use it with a sub-query so I will add that in from the start but as always before I hit the Driver::DBI code a test;

 {

        caption    => 'Exists Operator with Data::Accessor',

        key        => 'conditions',

        conditions => [

            {

                left => {value=>Test::Utils::in_da_sql()},

                operator => 'Exists',

              },

        ],

        create => {

            container => $container,

            sql =>

              "INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",

            params => $params

        },

        retrieve => {

            sql =>

"SELECT people.first_name, people.last_name, people.user_id FROM people WHERE EXISTS (SELECT address.user_id FROM address WHERE address.country = ?)",

            params => ['CA']

        },


        update => {

            container => $container,

            sql =>

"UPDATE people SET first_name = ?, last_name = ? WHERE people.id EXISTS (SELECT address.user_id FROM address WHERE address.country = ?)",

 params => [ 'Bill', 'Bloggings','CA' ]

        },

        delete => {

            sql    => "DELETE FROM people WHERE people.id EXISTS (SELECT address.user_id FROM address WHERE address.country = ?)",

            params => ['CA']

        },

    },

simple enough really just use the left as the place to put my sub-query in the form of a Database::Accessor object. Good thing I made that function I am really getting some use out of it.

Now this time the first thing I am going to check is to see if my 'EXISTS' and 'NOT EXISTS' are set up in my constants and I see that I have to add them into 'Database::Accessor::Driver::DBI::SQL'

use constant NOT_LIKE          =>'NOT LIKE';

++use constant EXISTS            =>'EXISTS';

++use constant NOT_EXISTS        =>'NOT EXISTS';

use constant IS_NULL           =>'IS NULL';

use constant IS                =>'IS';

and two places in 'Database::Accessor::Constants' as well;

...

use constant BETWEEN     => 'BETWEEN';

use constant EXISTS      => 'EXISTS';

use constant NOT_EXISTS  => 'NOT EXISTS';

…

use constant OPERATORS => {

    Database::Accessor::Constants::EXISTS      => 1,

    Database::Accessor::Constants::NOT_EXISTS  => 1,

    Database::Accessor::Constants::NOT_LIKE    => 1,

…

and now another 'elsif' in the '_predicate_sql'

...

    elsif ($predicate->operator eq Database::Accessor::Driver::DBI::SQL::EXISTS

           or $predicate->operator eq Database::Accessor::Driver::DBI::SQL::NOT_EXISTS) {


     die "$message '".$predicate->operator."' left must be a Database::Accessor not a ".ref($predicate->left)."!"

       unless(ref($predicate->left) eq "Database::Accessor");

       

      $clause .= join(" ",$predicate->operator 

                         ,$self->_field_sql($predicate->left,1)

                    );

    }

    elsif ($predicate->operator eq Database::Accessor::Driver::DBI::SQL::LIKE

…

and I should be good to go;

Database::Accessor::Driver::DBI::Error->Operator  'EXISTS' left must be a 

Database::Accessor not a Database::Accessor::Param! 

Opps! Maybe not. Keep forgetting that in this case the 'left' is a Param that has a vlaue of a DA. I think this will work better

--  die "$message '".$predicate->operator."' left must be a Database::Accessor not a ".ref($predicate->left)."!"
--       unless(ref($predicate->left) eq "Database::Accessor");
++     die "$message '".$predicate->operator."' left must be a Database::Accessor::Param with the value pointing to a Database::Accessor. Not a ".ref($predicate->left)."!"
 ++      unless(ref($predicate->left->value) eq "Database::Accessor");

and a quick test run and I get a full pass on that one. Now just add in the same test with a few changes to test for the 'NOT EXISTS' which passes and finally a quick 'exception' test like this

{
        caption    => 'Exists left must be a Param with a value of DA-ref',
        type       => 'exception',
        key        => 'conditions',
        conditions => [
          {
        left     => { name => 'cost' },
        operator => 'EXISTS',
        }],
        retrieve =>
          { message => 'left must be a Database::Accessor::Param with' },
    },

and that turned out to be a better test than I though as it failed with this;

'Can't locate object method "value" via package "Database::Accessor::Element"

Forgot that only a 'Param' class had a 'value' attribute will have to change that code again!

--    die "$message '".$predicate->operator."' left must be a Database::Accessor::Param with the value pointing to a Database::Accessor. Not a ".ref($predicate->left)."!"
--       unless(ref($predicate->left->value) eq "Database::Accessor");

++  die "$message '".$predicate->operator."' left must be a Database::Accessor::Param with the value pointing to a Database::Accessor. Not a ".ref($predicate->left)."!"

++       unless((ref($predicate->left) eq 'Database::Accessor::Param') and (ref($predicate->left->value) eq "Database::Accessor"));

and that test passe. All I need to do now is add in another 'exception' test this time passing in;

conditions => [

          {

        left     => { value=> 'cost' },

        operator => 'EXISTS',

        }],

so I can test the second part of the new 'unless' stament and if it fails it will pass the test.

That last one ran fine now my operator test suite had some 113 tests. No one is ever going to say I did not do enough testing on this module.