byterock

Its another case day here in the Moose-Pen

Yesterday I left off with this result

Expected--> SELECT CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END AS price_group FROM Products


Generated-> SELECT CASE WHEN Price < ? THEN ? WHEN Price >= ?andPrice <= ? THEN ? WHEN Price > ?andPrice <= ? THEN ? ELSE ? END price_group FROM Products

In the above I see at least fourt problems;

1. No space between my 'AND' conditionals
2. The conditional is in lower case it should be coming into the DAD in upper case and
3. There is no 'AS' between the 'END' and the 'Alias' 'price_group'

The first one should be no problem as this little patch fixes that

          my $condition_sql;

          my $statement;

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

--            $condition_sql .= $condition->condition

++            $condition_sql .= " ".$condition->condition.” ”

              if ($condition->condition);

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

Now to upper case that and I will have to go back into Database::Accessor and play about with the '_check_element' sub and this little patch fixes that

...

        elsif (ref($element) eq 'Database::Accessor::Case'){

            foreach my $sub_element (@{$element->whens()}){

                $self->_check_element($sub_element,0,$alias);

            }

        }

        elsif (ref($element) eq 'Database::Accessor::Case::When'){

            $self->_check_element($element->right,1,$alias);

            $self->_check_element($element->left,0,$alias);

            $element->condition(uc( $element->condition))

             if ($element->condition() );

         

        }

…

Finally the last one that 'AS' and as I was looking at my code I remembered that the 'AS' is not needed in standard SQL and a quick look in '15_alias.t' confirmed that for me so all I need to do is change the expected SQL by dropping that 'AS' and I get

ok 1 - Retrieve with case statement in elements retrieve SQL correct

ok 2 - Retrieve with case statement in elements retrieve params correct

even all my params are correct so things are looking up.

It was going to be a short post today until I noticed that I did this;

    my $last   = pop(@{$element->whens()});

    foreach my $when (@{$element->whens()}){

        if (ref($when) eq "Database::Accessor::Case::When"){ 

which will work fine on one run but if I ran it twice the next time I ran it I would be missing the last item on that array as I never add it back after poping it off the bottom. Well I do not like the idea of destroying my data in situ and then trying to fix it later so I will clean that up as well.

Fortunately good old Moose makes this easy. All I have to do is add in some native traits to the 'whens' attribute;

 has 'whens' => (

            isa         => 'ArrayRefofWhens',#|ArrayRefofArrayRefofWhens',

            is           => 'ro',

            required => 1,

++          traits  => ['Array'],

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

++                       when_count=> 'count' },

        );

and then change the problematic code a little;

–    my $last   = pop(@{$element->whens()});

++    my $last   = $element->get_when(-1);

–    foreach my $when (@{$element->whens()}){

++    for (my $index=0; $index<= $element->when_count()-2; $index++) {

++    my $when = $element->get_when($index);

      if (ref($when) eq "Database::Accessor::Case::When"){

All I am doing above is getting the last item on the list with a '-1', gotta love perl sometimes, then rater than iterating over the full list, I do not want the last one, I do an incrementing for loop starting at '0' and ending when I get to the penultimate index which is always 2 less than the count of elements, remember 0 based index here. Then I just get the when I want an use it via the $when and carry on as before and all my tests still pass.

Not too bad today.