byterock

Its rethink my case day here in the Moose-Pen.

Some of my loyal readers (if there are any) my remember when I started playing with the 'Case' statement I had a few iteration with the naming convention for my API. I finally settled on 'whens' that holds all the case conditions and 'statement' for the 'then' part of the case like this;

 whens => [

                    {

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

                        right     => { value => '100' },

                        operator  => '<',

                        statement => { value => 'under 100$' }

                    },

                    { statement => { value => 'Over 100$' } },

                ]

I was never really satisfied with the above API as it is a little unclear. I did a little research into the various SQL and Non-SQL DB that I want to one day write a DAD for and in the end I think I might be better to change my API to this

 ifs => [

                    {

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

                        right     => { value => '100' },

                        operator  => '<',

                        then        => { value => 'under 100$' }

                    },

                    { else => { value => 'Over 100$' } },

                ]

as it it a little more generic and logical in form.

Now the above looks more like an 'if' statement so but it is easier to read and hopefully does not cause that much confusion. I know there is such thing as an 'inline if' in SQL and in MongoDB which is a little different than the case so I will have to make sure I explain clearly in my POD what the 'ifs' is suppose to do.

As it stands any 'inline if' can be handled by my 'Function' Class as it is just a function with three parameters. It varies from SQL to SQL 'IF' in MySQL and 'IIF'' so using that function may cause DB transportability problems so I will have add a blurb on that as well.

As a little bit of history the 'Case' statement is a later addition to the SQL and MongoDB APIs. The case was developed to cover a number of situations where the DBs worked differently.

A good example is the Oracle 'decode' function;

DECODE (warehouse_id, 1, 'Southlake', 

                             2, 'San Francisco', 

                                'Non domestic') 

which now can be expressed in a case as

CASE WHEN  warehouse_id=1

           THEN  'Southlake'

           WHEN  warehouse_id=2

           THEN  'San Francisco'

           ELSE   'Non Domestic'

or in my new API as

ifs => [

                    {

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

                        right     => { value => '1' },

                        operator  => '=',

                        then        => { value => 'Southlake' }

                    },

                   {

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

                        right     => { value => '2' },

                        operator  => '=',

                        then        => { value => 'San Francisco' }

                    },

                    { else => { value => Non Domestic' } },

                ]

Now to put this in practice I will have to change code in a number of places starting with Accessor.pm and renaming 'Database::Accessor::Case' to 'Database::Accessor::If'. Now there is no coding reason to change this as it is invisible to the end user but any other person than me who is going to write up a DAD may get confuses ad the 'Class' is named one thing and the API another.

After changing that 34 time and in 8 files, Padre makes this a little easier, I will have to change the 'whens' attribute like this

--has 'whens' => (

++has 'ifs' => (

--          isa         => 'ArrayRefofWhens',

++          isa         => 'ArrayRefofThens',

            is           => 'ro',

            required => 1,

            traits  => ['Array'],

--          handles => { get_when => 'get',

++          handles => { get_if => 'get',

--                       when_count=> 'count' },

++                       if_count=> 'count' },

        );

which proved a little more tricky as doing a global replace of 'whens' for 'ifs' might cause some bade replacements. To avoid this I started with 'ArrayRefofWhens'->'ArrayRefofIfs', 'get_when'->'get_if', 'when_count'->'if_count' and finally 'whens'

Next it was the turn of the 'Database::Accessor::If::When' class which I renamed to 'Database::Accessor::If::Then' and next the 'statement' attributes that I changed like this

– has 'statement' => (

++ has 'then' => (

--     isa      => 'Expression|Param|Element|Function|Case',

++     isa      => 'Expression|Param|Element|Function|If',

       is       => 'rw',

++     alias => 'else'            

);

now I added that 'alias' in there just so my API looks good.

Finally I had to find and replace the 'Case' and 'When' that I used as type names and rename the '_when_array_or_object' in the 'Types' class to '_then_array_or_object' and I am ready for my first test run I get

Attribute (ifs) does not pass the type constraint because: Validation failed for 'ArrayRefofThens' with value [ Database::Accessor::If::Then{ 

                  close_parentheses: 0, 

                  condition: undef, 

                  left: Database::Accessor::Element=HASH(0x4346adc), 

                  open_parentheses: 0, 

                  operator: "<", 

                  right: Database::Accessor::Param=HASH(0x434fc34) },

            Database::Accessor::If::Then{ 

                  close_parentheses: 0, 

                  condition: undef, 

                  open_parentheses: 0 } ] 

at D:\GitHub\database-accessor\lib/Database/Accessor/Types.pm line 183# Looks like your test exited with 255 just after 11

on the '15_case.t' case. A closer look at that and I see I forgot to swap this 'statement' out for 'then' so a quick change for that one but I still got the same error. In the end I had made a bad swap in the 'Types' class I had

subtype 'ArrayRefofThens'           => as 'ArrayRef[If|ArrayRef]'; 

when I should have had

subtype 'ArrayRefofThens'           => as 'ArrayRef[Then|ArrayRef]';  

and now all my tests pass.

Now onto Driver::DBI and I make the same set of changes though mostly it was a swap from 'when' to 'then' and of course a great number of changes to the tests; some 68 changes for 'statement' and about 8 times for 'whens'

When it came to testing I only had one little goof

Can't locate object method "get_then" via package "Database::Accessor::If" at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 397.

# Looks like your test exited with 255 before it could output anything.

I should of renamed the 'get_then' to 'get_if' once I fixed that I did not expect this;

Expected-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN 

Products.Price >= ? AND Products.Price <= ? THEN ? WHEN Products.Price > ? 

AND Products.Price <= ? THEN ? ELSE ? END price_group FROM Products


Generated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN  THEN  

WHEN  THEN  ELSE ? END price_group FROM Products

Something is up in there; After some time trying to debug the above I found the problems in the '_field_sql' sub. I had made this change

 else {

          my $condition_sql;

          my $then;

          foreach my $condition (@{$then}){

so that '$then' would kill the $then array-ref in the next line; This little patch fixed that;

 else {

          my $condition_sql;

++        my $else;

--        my $then;

          foreach my $condition (@{$then}){

            $condition_sql .= $self->_field_sql($condition,0);

++          $else = $condition->then()

--          $ then = $condition->then()

               if ( $condition->then());

          } 

          push(@thens,join(" ",Database::Accessor::Driver::DBI::SQL::WHEN

                               ,$condition_sql

                               ,Database::Accessor::Driver::DBI::SQL::THEN

--                             ,$self->_field_sql($then,0)));

++                             ,$self->_field_sql($else,0)));

        }

and now I am getting 100% pass for that is it for today.