The Moose Case

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.

milch015s1b.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations