byterock

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 = {

    da_compose_only=>1,

    update_requires_condition=>0,

    delete_requires_condition=>0,

    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(" ",

               $self->view()->name,

               $self->view()->alias)

    if $self->view()->alias();

  return $view;

              

}

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

$self->_view_sql();

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.