byterock

It is still alias day here in the Moose-pen

So it is day two of looking at aliases and SQL and as part of this I had some time last night to have a peek about the SQL standard. So far I am doing things correctly, for table and field. Though in my first incarnation of table alias I used the 'AS' keyword in there, if you look at some of the check-in history you will see it. This 'AS' will work in a few SQL engines but not all so I was correct in dropping it out.

From my tests of yesterday’s post and last night's reading I got thinking again on the promise that Database::Accessor is making to a DAD;

'Whatever is passed into a DAD will be as valid as possible'.

What I have to look at now is one of the system rules that I want to enforce; Only 'elements' that match the name or alias of the current 'view' are passed into the DAD.
Given this input hash;

my $in_hash = {

    view     => { name => 'people',

                  alias=> 'sys_users' },

    elements => [

        {

            name => 'last_name',

            view => 'users'

        },

        {

            name => 'first_name',


        },

    ],  

};

The 'last_name' element should only be passed in on a 'retrieve/select' not the 'create' and 'update' function and of course we don't care about the 'delete' as it has not element/field clause.

Given the above I have added the following to the '15_alias.t' test case;

$in_hash->{elements}->[0]->{view} = 'users';

$da  = Database::Accessor->new($in_hash);

$da->create( $utils->connect(),$container);

ok($da->result()->query() eq "INSERT INTO people sys_users ( sys_users.first_name ) VALUES( ? )","create 2 SQL correct");

$da->retrieve( $utils->connect());

ok($da->result()->query() eq "SELECT users.last_name, sys_users.first_name FROM people sys_users","retrieve 2 SQL correct");

$da->update( $utils->connect(),$container);

ok($da->result()->query() eq "UPDATE people sys_users SET sys_users.first_name = ?","update 2 SQL correct");

nothing very special I have the same in hash which I just add a 'view' to on the first element and I just get a new '$da' with the '$in_hash' and my results are;

not ok 5 - create SQL correct

ok 6 - retrieve SQL correct

not ok 7 - update SQL correct

as I would expect. Now the SQL for the retrieve would fail on any DB as there is no join there for the 'users.last_name' field. I don't really care about that as I wound never test to see if the SQL will run just that it is valid. I never make that promise!

So to fix the above I have to back to Accessor.pm again and modify the get_dad_elements' sub like this;

    private_method get_dad_elements => sub {

        my $self = shift;

        my ( $action) = @_;

        my @allowed;

        foreach my $element (@{$self->elements}){

           next

             if ($action eq Database::Accessor::Constants::CREATE

                 and ($element->only_retrieve 

                 or  $element->no_create));

           next

             if ($action eq Database::Accessor::Constants::UPDATE

                 and ($element->only_retrieve 

                 or  $element->no_update));

           next

             if ($action eq Database::Accessor::Constants::RETRIEVE

                 and $element->no_retrieve);

++      unless($element->view()){

++             $element->view($self->view->name());

++             $element->view($self->view()->alias())

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

++         }

++         next 

++           if (($element->view ne $self->view->name 

++               and  $element->view ne $self->view->alias)

++            and ( $action eq Database::Accessor::Constants::CREATE

++                or $action eq Database::Accessor::Constants::UPDATE));

           push(@allowed,$element);

        } 

        return \@allowed;

    };

I noticed while I was playing in the code I could also eliminate the iteration over elements found in the 'around BUILDARGS' sub. All I needed to to was place the logic of that code here and that is the first part of the change; checking to ensure the 'view' attribute is set on each element.

I then check to see if the both the 'name' and the 'alias' or the 'view' do not match the 'view' attribute of the element and the action is either 'create' or 'update' and that screens out any elements that are not part of a view.

So I managed to kill two birds with on stone.

BTW; I have to clean up one other little bug that the above created; this call

 my $field = $self->get_element_by_name( $key);

will always return a value or at lease 'undef' so I had to add in

 my $field = $self->get_element_by_name( $key);

++      next

++         if(!$field); 

in the odd spot to account for that.