byterock

Its one more operator day here in the Moose-pen

Today I am finally going to get around to the last two of my operators the 'Like' and 'Not Like'. This is where we get into sort a sticky wicket and we are entering the realm of 'regular expression' queries which may work quite differently on the various SQL boxes and I know 100% different in MongoDB and other non SQL dbs. So what to do??

Well I might as well go with the standard SQL ones '%' and '_' meaning multiple and single characters, and the next question do I validate for their present in a 'Like' as this sql

WHERE  first_name LIKE 'Bob'

is perfectly valid though not a very good query. I guess no validation then, just pass in what I am given on on the param. Unfortunately like most predicates almost any combination is allowed as

SELECT * FROM Customers where CustomerName like (select CustomerName from Customers where CustomerID in (4,5,1))

is valid.

The key here is to provide good documentation on the Database::Accessor side and the DBI::Driver side of how it will work. The normal way you would use it is to pass the 'regular expression' as a param to prevent SQL injection and I will have to make that perfectly clear in the documents.

Adopting the SQL standard will make things for my Driver::DBI as I have no reason to parse or translate the passed in expression, though I may be in for a tough time when I have to do that for MongoDB or another non SQL one. That is a much later post.

As always I start with a test;

   {

        caption    => 'Like operator with regex',

        key        => 'conditions',

        conditions => [

            {

                left => {

                    name => 'last_name',

                    view => 'people'

                },

                operator => 'Like',

                right =>{value=>'Blog%'}

            },

        ],

        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 people.last_name LIKE ?",

            params => ['Blog%']

        },


        update => {

            container => $container,

            sql =>

"UPDATE people SET first_name = ?, last_name = ? WHERE people.last_name LIKE ?",

 params => [ 'Bill', 'Bloggings','Blog%' ]

        },

        delete => {

            sql    => "DELETE FROM people WHERE people.id WHERE people.last_name LIKE ?",

            params => ['Blog%']

        },

    },

and I think this code patch will handle both;

}

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

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

      

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

                         ,$predicate->operator

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

                    );

    }

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

Now I did get this error;

Bareword "Database::Accessor::Driver::DBI::SQL::NOT_LIKE" 

not allowed while "strict subs" in use 

I forgot to add 'NOT_LIKE' to my constants class so in it goes;

use constant LIKE              =>'LIKE';

++use constant NOT_LIKE          =>'NOT LIKE';

use constant IS_NULL           =>'IS NULL';

and after that on my first run I just need to fix a typo in my expected SQL

# Expected SQL--> DELETE FROM people WHERE people.id WHERE people.last_name LIKE ?

# Generated SQL-> DELETE FROM people WHERE people.last_name LIKE ?

And I get a full pass. The next test where all I did was copy the frst one and change

 -- operator => 'Like',

++  operator => 'Not Like',

and then of course my expected SQL and on my first run I got

Attribute (operator) does not pass the type constraint because: The Operator 'NOT Like', 

is not a valid  Accessor Operator! Try one of '!=', '<', '<=', '<>', '=', '>', '>=', 'AND', 'BETWEEN', 'IN', 

'IS NOT NULL', 'IS NULL', 'LIKE', 'NOT IN', 'OR' at 

opps need a little patch to Database::Accessor::Constants class as well;

use constant LIKE        => 'LIKE';

++use constant NOT_LIKE    => 'NOT LIKE';

use constant IS_NULL     => 'IS NULL';

…

use constant OPERATORS => {

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

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

…

and on this run I get a full pass.

I could at this point add in a bunch of other tests but really I am not accomplishing much by doing this as all I would be doing it retesting the '_field_sql' sub over and over again.

What I was wrong about is that the there is some constraints for this operator, the left and right cannot be an Array-Ref as in these SQLs

WHERE (last_name,first_name) like 'Blogs%';

WHERE last_name like ('Blogs%','BILL%)

though oddly enough this is valid

WHERE  'Thomas' LIKE ('Thom%')

but useless as it is the same as

WHERE 1=1

but that's another post;

To fix this I added in this code to Driver::DBI as who knows if some other sort of DB my allow this form of params.

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

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

      

++     die("$message '".$predicate->operator."' left can not be an Array Ref!") 

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

      

++      die("$message '".$predicate->operator."' right can not be an Array Ref!") 

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

and then 'exception' two tests to cover these off and I get

ok 94 - retrieve Like left must not be an array-ref

ok 95 - retrieve Like right must not be an array-ref

and I am done for the day.