byterock

Its get off my case day here in the Moose-Pen

Well I am getting close to running out of things to add into Driver ::DBI as I have all the operators covered, functions, expressions, elements, etc etc so I do see and end in sight maybe I won't get a year of blog posts in after all??

Today I am going to try and tackle on of the much abused SQL commands the 'CASE' statement. The question is where to test this one as it can appear anywhere a predicate can so in a 'sort/order by', 'element/field', even a 'link/join'. I was thinking the best spot for it would be back in 32_where_operators.t but the most common form of case I have seen is in SQL like this

SELECT ProductName,

       CASE WHEN Price < 10 THEN 'under 10$'

            WHEN Price >=10 AND Price <= 30 THEN '10~30'

            WHEN Price >30 and Price <= 100 THEN '30~100'

            ELSE 'Over 100' END AS price_group

  FROM Products

when it is used in a field. In the end I went with a new test case for this one 22_extended_fields.t as there may be a few other things I might put in there.

Now that that is settled lets start with my first test and this is when I got into a little bit of a pickle. I decided I might as well use the above SQL as my test but I as I was trying to converting into my little abstraction language I kept running into dead ends.

I started it out as a function but only got this far;

{function=>'CASE',

          left=>{name=>Price},

        right=>{expression=>”>”,

                                  left=>{name=>Price},

                                  right=>{value=>10}},

then I ran out of places to add the next 'when' in. I then tried to start with an 'expression' and only got

{expression=>'CASE',

             left=>{expression=>'<',

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

                                  right=>{param=>10}}}

which is again much the same thing. I then had a look at what else was available and it seems I had added on the 'predicate' attribute to element at some time. Now with a simple predicate I have both an 'operator' and a 'condition' attribute and I was not able to get much further;

{name=>'price',

                       predicate=>{condtion=>'case'

                                   operator=>'<',

                                   left=>'price'

                                   right=>{value=>10}

                                   }

                       }

I then had a close look at what a CASE statement is and broke it down into its parts

+-----------+-----------+-----------+--------------+--------+------------+

|   Clause  | Predicate | Condition |   Predicate  | Clause | Expression |

+-----------+-----------+-----------+--------------+--------+------------+

| CASE WHEN | Price <10 |                          |  THEN  | 'under 10$' |

|    WHEN   | Price >10 |    and    | Price <= 30  |  THEN  |  '10~30'   |

|    WHEN   | Price >30 |    and    | Price <= 100 |  THEN  |  '30~100'  |

|    ELSE   |                                               | 'Over 100' |

|    END    |                                                             |

+-----------+------------------------------------------------------------+

I could analyze it a little better. First I really do not have to worry about the 'Clause' parts I just need some sort of trigger to start the ball rolling. Second I notices that I have 'predicates' with or without a 'condition' and then an 'expression'

Now if I just treated these items as an array ref of 'conditions' perhaps things will work out;

[ { left      => { name => 'Price', },
    right     => { value => '10' },
    operator  => '<',
    condition => 'case',
  },
  { condition => 'case',
    left      => { 'Price'}
    right     => { value => '10' },
   operator   => '>',
  },
  { condition => 'and',
    left      => { 'Price'}
    right     => { value => '30' },
    operator  => '<=',
  },
  { condition => 'case',
    left      => { 'Price'}
    right     => { value => '30' },
    operator  => '>',
  },
  { condition => 'and',
    left      => { 'Price'}
    right     => { value => '100' },
    operator  => '<=',
  }
]

well a little better but I am still missing the expression at the end of each condition. I could just add that in

[{ left      => { name => 'Price', },
    right     => { value => '10' },
    operator  => '<',
    condition => 'case',
   expression=>{value=>'under 10$'}
  },
…

ok that will work now I will need some way to finish it off for the final expressions

[{ left      => { name => 'Price', },
    right     => { value => '10' },
    operator  => '<',
    condition => 'case',
   expression=>{value=>'under 10$'}
  },
…
 { condition => 'end',
    left      => { 'Price'}
    right     => { value => '100' },
    operator  => '>',
    expression=>{value=>'Over 100'}
  }
]

that may work but this may be a little easier on the brain

…
 { condition => 'end',
    expression=>{value=>'Over 100'}
  }
]

The problem is I do not have a class that will express the above as I do not have an expression attribute on my Condition class, as well using the keywords 'and' and 'case' as a flag though workable does not lend itself to an elegant solution. Finally that 'End' will be problematic would be much nicer if I did this

[ { left      => { name => 'Price', },
    right     => { value => '10' },
    operator  => '<',
    condition => 'case',
    expression=>{value=>'under 10$'}
  },
  [{ condition => 'case',
    left      => { 'Price'}
    right     => { value => '10' },
   operator   => '>',
  },
  { condition => 'and',
    left      => { 'Price'}
    right     => { value => '30' },
    operator  => '<=',
    expression=>{value=>'10~30'}
  }],
  { condition => 'case',
    left      => { 'Price'}
    right     => { value => '30' },
    operator  => '>',
  },
...

where I wrap that multi-condition in its own array, but now that 'expression' is kinda in the way.

Oh well looks like I have to do more more thinking but I can see a new class for Database::Accessor may be in the stars.