The Moose When
It is another head case day here in the Moose Pen.
So yesterday I left you hanging on how I was going to incorporate the 'case' statement into Database::Accessor hopefully today I will find some solution for it.
To start off my attempts to use the present class structure ended in failure and from my analysys of the problem I have one of two choices.
- Add in new attributes
- Add in a new class
The main disadvantage of the first solution is I will have a number of attributes that will be useless 99.99% of the time. The advantage is this will most likely be the least disruptive to my code base and use the least amount of code.
The second solution has no disadvantages that I can see but it will increase my code base a little.
Therefore I have decided to go with a new class called 'Database::Accessor::Case', I did play with the idea to give it another name like the very Perl-ish 'Given' or Java-ish 'Switch' but in the end most DBs, SQL and Non-SQL, have a 'Case' so I am just going to go with that one.
The first thing I did was complete the prototype Hash-ref that will be used to pass data into my new 'Case' class;
{case=>[{ left => { name => 'Price', },
right => { value => '10' },
operator => '<',
expression=>{value=>'under 10$'}},
[{left => { 'Price'},
right => { value => '10' },
operator => '>=',
},
{ condition => 'and',
left => {name=>'Price'}
right => { value => '30' },
operator => '<=',
expression=>{value=>'10~30$'}}
],
[{left => { 'Price'},
right => { value => '30' },
operator => '>',
},
{ condition => 'and',
left => {name=>'Price'}
right => { value => '100' },
operator => '<=',
expression=>{value=>'30~100$'}}
],
{ expression=>{value=>'Over 100$'}},
]}
The above was a good first start but looking at the originating SQL,
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
I have an alias at the end of it so I might want to add that in as well;
{case=>[{ left => { name => 'Price', },
…
alias=>'price_group'
};
The plan is to handle this in the same way one would handle an expression or function that 'case' keyword is what I will use to capture then input and coerce it into my class.
The class that I am starting with is just a copy of 'Function' and renamed appropriately;
{
package
Database::Accessor::Case;
use Moose;
extends 'Database::Accessor::Base';
with qw(Database::Accessor::Roles::Comparators
Database::Accessor::Roles::Element
);
has 'case' => (
isa => 'Str',
is => 'rw',
required => 1,
);
1;
}
and I think I can get rid of the 'Comparators' role as at this level there is no 'predicate' here. The 'case' attribute will as to change as well to an Array Ref but I still need something to fill that and I think I will need a new class to go in that array ref. Ouch two new classes! This second class I think I will call ' 'Database::Accessor::Case::When' for now. That gives me this;
{
package
Database::Accessor::Case;
use Moose;
extends 'Database::Accessor::Base';
with qw( Database::Accessor::Roles::Element
);
has 'case' => (
isa => 'ArrayRefofWhens',
is => 'ro',
required => 1,
coerce => 1,
);
1;
}
Now I will need to define that 'When' class and I can use the 'Comparators' role which will give me my 'left', 'right' and 'open', ' close' parentheses attributes but I sill need 'operator', 'condition' and 'expression' which I think I will change to 'message' as I use 'expression' elsewhere.
I already have 'operator' and 'condition' on 'Database::Accessor::Predicate' and I would like to re-use that if I could so how about just
{ package
Database::Accessor::Case::When;
use Moose;
extends 'Database::Accessor::Predicate';
has 'message' => (
isa => 'Str',
is => 'rw',
);
1;
}
now that may work for me.
Now before I get too far into the game I am going to create a new test case '15_case.t', lucky I left some space in those test numbers, for this.
I start with the basics
use 'Database::Accessor';
BEGIN {
use_ok('Database::Accessor::Case');
use_ok('Database::Accessor::Case::When');
}
Now it this stage in the game all I expect at least the above to pass;
ok 1 - use Database::Accessor::Case;
ok 2 - use Database::Accessor::Case::When;
now my next two tests;
my $when = Database::Accessor::Case::When->new({ left => { name => 'Price', },
right => { value => '10' },
operator => '<',
expression=>{value=>'under 10$'}} );
ok( ref($when) eq ' Database::Accessor::Case::When',
"when is a when" );
ok(
does_role( $when, "Database::Accessor::Roles::Comparators" ) eq 1,
"when does role Database::Accessor::Roles::Comparators"
);
which should pass as I have no need for coercion on that Class and I get;
ok 1 - use Database::Accessor::Case;
ok 2 - use Database::Accessor::Case::When;
ok 3 - when is a when
ok 4 - when does role Database::Accessor::Roles::Comparators
So far so good lets add in another 2 tests;
my $case = Database::Accessor::Case->new(
{
case => [
{
left => { name => 'Price', },
right => { value => '10' },
operator => '<',
expression => { value => 'under 10$' }
},
[
{
left => {'Price'},
right => { value => '10' },
operator => '>=',
},
{
condition => 'and',
left => { name => 'Price' },
right => { value => '30' },
operator => '<=',
expression => { value => '10~30$' }
},
],
[
{
left => {'Price'},
right => { value => '30' },
operator => '>',
},
{
condition => 'and',
left => { name => 'Price' },
right => { value => '100' },
operator => '<=',
expression => { value => '30~100$' }
},
],
{ expression => { value => 'Over 100$' } },
]
}
);
ok( ref($case) eq ' Database::Accessor::Case',
"when is a when" );
ok( ref($case->cases->[0]) eq ' Database::Accessor::Case::When',
"Cases[0] is a when" );
and these I would think are going to fail as I have not set up any coercion for them yet and as expected I get;
Attribute (case) does not pass the type constraint because:
Validation failed for 'ArrayRefofWhens' with value ARRAY(0x3cbba9c)
(not isa ArrayRefofWhens) at...
So I guess I have my post for tomorrow.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment