Still on a Moose Case
It case out day here in the Moose-Pen
After looking at some more examples of 'CASE' in SQL I cam across one I have not encountered yet;
SELECT CASE
WHEN Products.Price < 10 THEN 'under 10$'
WHEN (Products.Price >= 10
AND Products.Price <= 30)
OR (Products.Price >= 40
AND Products.Price <=50) THEN '10~30$ or 40~50$'
ELSE 'Over 50$'
END
FROM Products
and that is when there are parentheses used in a 'When' statement. Now I am hoping that my code is is a good enough state that I will not have to do any changes to make this work. To test this I am going to try this
{
caption => 'Retrieve with case parentheses',
key => 'elements',
elements => [
{
whens => [
{
left => { name => 'Price', },
right => { value => '10' },
operator => '<',
statement => { value => 'under 10$' }
},
[
{
left => { name => 'Price' },
right => { value => '10' },
operator => '>=',
open_parentheses =>1
},
{
condition => 'and',
left => { name => 'Price' },
right => { value => '30' },
operator => '<=',
close_parentheses=>1
},
{
left => { name => 'Price' },
right => { value => '40' },
operator => '>=',
condition => 'OR',
open_parentheses =>1
},
{
condition => 'and',
left => { name => 'Price' },
right => { value => '50' },
operator => '<=',
statement => { value => '10~30$ or 40~50$' },
close_parentheses=>1
}
],
{ statement => { value => 'Over 50' } },
]
}
],
retrieve => {
sql =>
"SELECT CASE WHEN Products.Price < ? THEN ? WHEN (Products.Price >= ? AND Products.Price <= ?) OR (Products.Price >= ? AND Products.Price <= ?) THEN ? ELSE ? END FROM Products",
params => [
10, 'under 10$', 10, 30,40,50,'10~30$ or 40~50$','Over 50$'
]
},
}
In fact I am testing not just parentheses but also if my present code will string together those two conditionals. Here is the first run result;
not ok 5 - Retrieve with case parentheses retrieve SQL correct
not ok 6 - Retrieve with case parentheses retrieve params correct
looking a little more closely I see that my SQL is only out on spacing issues;
Expected -> SELECT CASE WHEN Products.Price < ? THEN ? WHEN (Products.Price >=
Generated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN ( Products.Price >=
on the expected SQL so that is a error in my test easily fixed.
The param error is a simple fix as well as it is reporting
# got : 'Over 50'
# expect : 'Over 50$'
so I am just missing the '$' in that final statement of mine;
After those two fixes I now get;
ok 5 - Retrieve with case parentheses retrieve SQL correct
ok 6 - Retrieve with case parentheses retrieve params correct
That was easy.
I was thinking that I could add test for all the other extended operators it would be good coverage but at what point does test coverage become overkill.
Looking at the code in 'Driver::DBI' the if I did add all these tests in all I would be doing is retesting the '_predicate_sql' over again as I cover all the extended operators with the '32_where_operators.t' test case. No need for that level of repetition as it works for one it will work for all.
One thing I did not check was an SQL like this
SELECT CASE
WHEN Products.Sale_Price < Products.Price
THEN 'On Sale'
WHEN Products.Sale_Price > Products.Price
THEN 'Premium Price'
ELSE 'Normal Price' END FROM Products
This I should check by adding another test, no need add the full test here all that really matters is;
SELECT CASE
WHEN Products.Sale_Price < Products.Price
THEN ?
WHEN Products.Sale_Price > Products.Price
THEN ?
ELSE ?
END
FROM Products
which is my expected SQL. When I run it I get;
…
ok 7 - Retrieve with case using only elements retrieve SQL correct
ok 8 - Retrieve with case using only elements retrieve params correct
so that is working.
Again no reason to add in add in any more tests as again I am merely retesting code I have already tested though this time it is the '_field_sql' sub rather than the '_predicate_sql' sub.
As a final test for today I am going to give this SQL a try
SELECT CASE
WHEN Products.On_Sale = 1
THEN CASE
WHEN Products.Stock <= 10 THEN .2
WHEN Products.Stock > 10
AND Products.Stock < 100 THEN .1
ELSE .05
END
WHEN Products.On_Sale = 2
THEN .5
ELSE 1 Discount
FROM Products
This time round It might be best to show what the 'element' model looks like;
elements => [
{
whens => [
{
left => { name => 'On_Sale', },
right => { value => 1,},
operator => '=',
statement => {
whens=>[{left => {name =>'stock'},
right => {value=>10},
operator => '<=',
statement=> {value=>.2}},
[{left => {name =>'stock'},
right => {value=>10},
operator => '>'},
{left => {name =>'stock'},
right => {value=>100},
operator => '<=',
condition=> 'AND',
statement=> {value=>.1}}],
{statement=> {value=>.05}}] },
},
{
left => { name => 'On_Sale' },
right => { value=> 2,},
operator => '=',
statement => { value => .5 }
},
{ statement => { value => 1 } },
],
alias=>'Discount'
}
],
and on my first run I get a fail on the generated SQL
THEN CASE WHEN .Stock <= ? THEN ? WHEN .Stock >
I am missing the 'table/view' name on the CASE in CASE and that will lead me back out to Database::Accessor again for the fix.
In the case I see that I have expanded on my 'statement' making it into a 'case' where before I just had a param or alike.
Looking at the Accessor.pm all I needed to do was extent the reach of the '_check_element' sub by including the 'statement' attribute like this;
...
elsif (ref($element) eq 'Database::Accessor::Case::When'){
$self->_check_element($element->right,1,$alias);
$self->_check_element($element->left,0,$alias);
++ $self->_check_element($element->statement,0,$alias);
$element->condition(uc($element->condition))
if ($element->condition() );
++ $element->operator(uc($element->operator))
++ if ($element->operator() );
...
I also took the opportunity to upper case the operator as well if there happens to be one. Now on this run I get;
…
ok 9 - Retrieve with case in a case retrieve SQL correct
ok 10 - Retrieve with case in a case retrieve params correct
Enough for today.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment