Better Moose Testing

Its go back and fix day here in the Moose-Pen

In a recent post I mentioned that I went out and read though a number of SQL syntax books to make sure I was doing things correctly in which SQL clauses (Where, Sort, Link) goes where. That go me to thinking was the system I was using to test the SQL on a real DB using standard SQL?

Normally I test with Oracle App Express as it work well on a Windows box and has a nice UI but I just noticed that I have some odd SQL in one of my test cases 15_alias.t;

I was starting with this hash;

my $in_hash = {
view => { name => 'people',
alias=> 'sys_users' },
elements => [
name => 'last_name',
name => 'first_name',

And from my playing about I would generate this SQL

INSERT INTO people sys_users ( sys_users.first_name, sys_users.last_name ) VALUES( ?, ? )
using the 'sys_users' as an alias. Now this works fine on SQL Command page of Oracle App express and that is what I coded Driver::DBI to do.

However, from what I read in a number of docs, even the Oracle ones I did not find any reference to one being able to use an alias in an insert. I seems this is just a very old quirk in the Oracle SQL*Plus command line parser. It works when that interface is used but actually fails on a normal DBI call with Perl. So an alias on an insert is not part of standard SQL so I better review my tests and when I check something on a DB will stop using SQL*Plus and stick with a pure Perl app.

Since I had to re-write most of the code in the test I took the opportunity to expand on my 'Tests::Utils::sql_param_ok' sub. I expanded to to to all four CRUD actions on a given set. So now my test hash-ref looks like this

my $tests = [{
caption =>'Basic Table Alias',
create =>{container=>{first_name=>'Bill',
last_name =>'Bloggings'},
sql =>"INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",
params =>['Bill','Bloggings']},

retrieve=>{sql =>"SELECT sys_users.last_name, sys_users.first_name FROM people sys_users"},
update =>{container=>{first_name=>'Robert'},
sql =>"UPDATE people SET first_name = ?"},
params =>['robert'],
delete =>{sql =>"DELETE FROM people"},

and a test call like this


I did do a compete re-write of the sub to;

sub sql_param_ok {
my $self = shift;
my ( $in_hash, $tests ) = @_;
foreach my $test ( @{$tests} ) {
foreach my $action ((qw(create retrieve update delete))){
my $sub_test = $test->{$action};
if ( exists( $sub_test->{index} ) ) {
$in_hash->{ $sub_test->{key} }->[ $sub_test->{index} ] =
$sub_test->{ $sub_test->{key} };
elsif ( exists( $sub_test->{key} ) ) {
$in_hash->{ $sub_test->{key} } = $sub_test->{ $sub_test->{key} };
my $da = Database::Accessor->new($in_hash);
$da->$action( $self->connect(), $sub_test->{container});
my $ok = ok(
$da->result()->query() eq $sub_test->{sql},
$test->{caption} . " $action SQL correct"
unless ($ok) {
diag( "Expected SQL--> "
. $sub_test->{sql}
. "\nGenerated SQL-> "
. $da->result()->query() );
cmp_deeply( $da->result()->params, $sub_test->{params},
$test->{caption} . " $action params correct" )
if ( exists( $sub_test->{params} ) );

Which will generate a bunch of fails for me until I update the tests but I will do those updates later.

As for the SQL I have gone with the simplest solution to my problem taking UPDATE for example, this

UPDATE people sys_users SET sys_users.first_name = ?
and this
UPDATE people SET people.first_name = ?

works in Oracle but fails in a number of other DB but

UPDATE people SET first_name = ?

works in all of them. So that is what I am going with.

Now I have to fix these three fails;

not ok 1 - Basic Table Alias SQL correct … not ok 4 - Basic Table Alias SQL correct not ok 5 - Basic Table Alias SQL correct
The changes to Driver::DBI turned out to be rather simple; First I had to modify the '_field_sql' sub a little

sub _field_sql {
  my $self = shift;
--  my ($element,$use_alias) = @_;
++  my ($element,$use_view) = @_; 
--    my $sql = $element->view
++    my $sql = $element->name;
++    $sql = $element->view
--           .$element->name;
--    $sql .= join(" ",
--                 "",
--                 Database::Accessor::Driver::DBI::SQL::AS, 
--                 $element->alias())
--       if ($element->alias and $use_alias );
++           .$element->name
++      if ($use_view );
    return $sql;
and the '_elements_sql ' sub a little

++    my $sql = $self->_field_sql($field,1);
++    $sql .= join(" ",
++         "",
++         Database::Accessor::Driver::DBI::SQL::AS, 
++         $field->alias())
++      if ($field->alias());
and a little change to the '_table_sql' sub

sub _table_sql {
  my $self = shift;
--  my ($view) = @_;
++  my ($view,$use_alias) = @_;
  my $sql = $view->name;
  $sql = join(" ",
--    if $view->alias();
++    if ($use_alias and $view->alias());
  return $sql;              
and now I am getting what I want;
ok 1 - Basic Table Alias create SQL correct ok 2 - Basic Table Alias create params correct ok 3 - Basic Table Alias retrieve SQL correct ok 4 - Basic Table Alias update SQL correct ok 5 - Basic Table Alias delete SQL correct ok 5 - Basic Table Alias SQL correct
onto other things; hp2811.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