More Moose Steps

Its get more done day here in the Moose-pen.

Today I was working on new test, '15_alias.t' which I will use to test the various alias attributes found on 'tables' and 'fields' in SQL queries. To start I just have the very simple has with the 'update_requires_condition' and 'delete_requires_condition' flags set to 0 so I can work without a 'where/conditions' clause.

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

and with the above I can create a few tests that will check that the 'alias' for the table will work as expected in the SQL standard which is 'table_name table_alias' and any field in a statement should use the alias. Below is my first batch of tests;

my $container = {first_name=>'Bill',
last_name =>'Bloggings'};
my $da = Database::Accessor->new($in_hash);
ok($da->create( $utils->connect(),$container),"created something");
ok($da->result()->query() eq "INSERT INTO people sys_users ( sys_users.first_name, sys_users.last_name ) VALUES( ?, ? )","create SQL correct");
ok($da->retrieve( $utils->connect() ),"retrieved something");
ok($da->result()->query() eq "SELECT sys_users.last_name, sys_users.first_name FROM people sys_users","retrieve SQL correct");
ok($da->update( $utils->connect(),$container),"updated something");
ok($da->result()->query() eq "UPDATE people sys_users SET sys_users.first_name = ?, sys_users.last_name = ?","update SQL correct");
ok($da->delete( $utils->connect()),"delete something");
ok($da->result()->query() eq "DELETE FROM people sys_users","Delete SQL correct");

and on the first run I got a fail in most of them as I am not setting the alias on the field correctly as in this select;

'SELECT people.last_name, people.first_name FROM people sys_users';

That is a problem in Database::Accessor as the field is inheriting the view name not the alias you might remember that code from the 'around BUILDARGS' sub and the correction is;

my $view_name = $ops->{view}->{name};
++ $view_name = $ops->{view}->{alias}
++ if (exists($ops->{view}->{alias}));
foreach my $element (@{ $ops->{elements}}){

Now the 'Select/Retreive' test passes but all the other crund functions fail and to correct that I had to add in another new sub into Driver::DBI;

sub _view_sql {
my $self = shift;
my $view = $self->view()->name;
$view = join(" ",
if $self->view()->alias();
return $view;


and then make the swap in the four crud functions where I was setting the view name with


and now I get all my test passing. I also noticed that the tests like this one

ok($da->create( $utils->connect(),$container),"created something");

are not very useful because with the 'da_compose_only' they just return what the DAD driver does so I could but almost any valid garbage in there, so I will drop those tests part of those and now I have;

ok 1 - create SQL correct
ok 2 - retrieve SQL correct
ok 3 - update SQL correct
ok 4 - Delete SQL corret

Now onto part two of this test case, but that is another post.


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