Moose Works Harder
Its move along day here in the Moose-Pen
Today I managed to get the time to create '25_exe_array.t' which tests DBI execute array and it went quite well. No real story on that one just with my new 'Test::Utils::sql_param_ok' function I was able to quickly expand it out from just 3 tests to 6 in a few minutes.
I then moved on to '30_where_basic.t' where the idea was to test the basic parts of a 'condition', The basic parts being simple single level predicates mixing elements, functions and expressions.
After creating some 64 tests and giving it a few goes to get rid of the usual typos I ended up with 24 fails out of the 64 and all of them where much like this example;
# Expected SQL--> DELETE FROM people WHERE people.first_name != ? AND
people.bonus * ? <= ? AND left(people.first_name, ?) != ?
# Generated SQL-> DELETE FROM people WHERE first_name != ? AND bonus * ?
<= ? AND left(first_name,?) != ?
on my conditions I was missing the 'view/table' name. Same sort of error I have seen before and agin I am fairly sure the default 'view/table' is being passed down to the Driver::DBI from Accessor.pm I started looking in Driver::DBI.
As all of the errors where happening on the conditions I first had a look at the '_predicate_sql' sub and noticed where I call the '_field_sql' sub I have yet to add in the fact I should use the alias so I added that in;
-- $clause .= join(" ",$self->_field_sql($predicate->left),
++ $clause .= join(" ",$self->_field_sql($predicate->left,1),
$predicate->operator,
-- $self->_field_sql($predicate->right));
++ $self->_field_sql($predicate->right,1));
and now I get only 6 fails and a quick scan of those I took out a few typos in the expected sql values of my test data and now I get a full pass on the test case.
Now that that one is done I noticed I did not do any testing with an alias tagged onto my fields as I had this;
my $in_hash = {
da_compose_only=>1,
view => { name => 'people' },
elements => [
{
name => 'first_name',
view => 'people'
},
{
name => 'last_name',
view => 'people'
},
{
name => 'user_id',
view => 'people'
}
],
};
as my in hash.
Well I was a little ahead of the game so I decided I could take a chance and expand that 'Test::Utils::sql_param_ok' so I am able to change more than just one key.
So in my final test looks like this
{
caption =>
'Elements with alias and Element, expression and a function condition',
keys => [ 'elements', 'conditions' ],
elements => [
{
name => 'first_name',
alias=> 'First',
},
{
name => 'last_name',
view => 'people',
alias=> 'Last'
},
{
name => 'user_id',
view => 'people',
alias=> 'User ID'
}
],
conditions => [
{
left => {
name => 'first_name',
view => 'people'
},
right => { value => 'test1' },
operator => '!=',
},
{
condition => 'AND',
left => {
expression => '*',
left => { name => 'bonus' },
right => { param => '0.15' }
},
right => { value => '1500' },
operator => '<=',
},
{
condition => 'AND',
left => {
function => 'left',
left => { name => 'first_name' },
right => [ { param => 1 } ]
},
right => { value => 'b' },
operator => '!=',
}
],
create => {
container => $container,
sql =>
"INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",
params => $params
},
retrieve => {
sql =>
'SELECT people.first_name First, people.last_name Last, people.user_id "User ID" FROM people WHERE people.first_name != ? AND people.bonus * ? <= ? AND left(people.first_name,?) != ?',
params => [ 'test1', '0.15', '1500', '1', 'b' ]
},
update => {
container => $container,
sql =>
"UPDATE people SET first_name = ?, last_name = ? WHERE people.first_name != ? AND people.bonus * ? <= ? AND left(people.first_name,?) != ?",
params => [ 'Bill', 'Bloggings', 'test1', '0.15', '1500', '1', 'b' ]
},
delete => {
sql =>
"DELETE FROM people WHERE people.first_name != ? AND people.bonus * ? <= ? AND left(people.first_name,?) != ?",
params => [ 'test1', '0.15', '1500', '1', 'b' ]
},
},
];
I next had to change the 'sql_param_ok' sub a little;
elsif ( exists( $test->{key} ) ) {
$in_hash->{ $test->{key} } = $test->{ $test->{key} };
}
++ elsif ( exists( $test->{keys} ) ) {
++ foreach my $key (@{$test->{keys}}) {
++ $in_hash->{ $test->{key} } = $test->{ $test->{key} };
++ }
}
And I run my case again and I get a fail on that
# Expected SQL--> SELECT people.first_name First, people.last_name Last, people.user_id "User ID"
# Generated SQL-> SELECT people.first_name, people.last_name, people.user_id FROM people
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment