Joined Moose

Its get on with it day here in the Moose-Pen

After putting on my thinking Moose-cap and reading though some of my notes (and looking at a number of my older posts) the best solution I could come up with was to leave the ability to add a 'JOIN' to an 'UPDATE', 'CREATE' or 'DELETE' statement in.

My reason for this simple if someone wants to use a JOIN is something other than a 'SELECT' and it is perfectly valid command why should my application stop them.

The original DataAccessor code it is written in such a way that you can only do a JOIN on a SELECT as the concept was to have a class that was for 'CUD' actions and one for 'Retrieve'. I would still like something like that and the way I can establish this is with this attribute;

has retrieve_only => (
        is          => 'ro',
        isa         => 'Bool',
        default     => 0,
        traits      => ['MooseX::MetaDescription::Meta::Trait'],
        description => { not_in_DAD => 1 }
    );
I simply use it to make sure not 'CUD' actions can take place. There dilemma solved for now. Carrying on for today as I cleaned up the expected SQL in '40_joins.t' and I added in a new case where I was testing using an alias on the JOIN like this;

links => {
                type       => 'LEFT',
                to         => { name => 'address',
                                alias=>  'test' },
                conditions => [
                    {
                        left  => { name => 'id' },
                        right => {
                            name => 'user_id',
                        }
                    }
                ]
            },
and I was expecting this SQL back;

…
LEFT JOIN address test ON people.id = test.user_id 
…
but I got this;

…
LEFT JOIN address ON people.id = people.user_id 
…
so again missing the 'alias' and the fix for that is easy enough;

   ,Database::Accessor::Driver::DBI::SQL::JOIN
-- ,$self->_table_sql($join->to)
++ ,$self->_table_sql($join->to,1)
   ,Database::Accessor::Driver::DBI::SQL::ON
but now I am getting this;

LEFT JOIN address test ON people.id = people.user_id
so the 'user_id' field in my JOIN is inheriting its 'View/Table' from the DA's view not the View on the 'JOIN' I think I will have to fix that at the Database::Accessor level. In the '_elements_check' sub of Database::Accessor presently I am just doing this;

...
         foreach my $link ((@{ $self->links },@{ $self->dynamic_links })){
            push(@items,$link->conditions); 
        } 
…
just taking all the conditions from my links and adding them into the @items to be checked on mass. What I should be doing here is checking each of these '$links' to ensure that any 'right' elements in the 'conditions' have the correct alias set from the 'to' key. This patch required a little thinking as the way I have written things there could be a case where a 'JOIN' could have all sorts of predicates and each will have to be checked. In order to save even more iteration I simply adapted the '_check_element' sub to take another parameter '$alias' and with this little patch;

   private_method _check_element => sub {
        my $self = shift;
--      my ($element) = @_;
++      my ($element,$alias) = @_;

        if (ref($element) eq 'Database::Accessor::Element'){
          unless ( $element->view() ) {
--          $element->view( $self->view->name() );
--          $element->view( $self->view()->alias() )
--             if ( $self->view()->alias() );
++          $element->view( $self->view->name() );
++          $element->view( $self->view()->alias() )
++             if ( $self->view()->alias() );
++          $element->view($alias )
++             if ($alias);
         }
       }
       elsif (ref($element) eq 'Database::Accessor::Condition'){
           $element->predicates->operator($self->default_operator())
           $element->predicates->condition(undef)
             if ( $self->_add_condition<=1  );
           $self->_check_parentheses($element->predicates);
--         $self->_check_element($element->predicates->right);
++         $self->_check_element($element->predicates->right,$alias);
           $self->_check_element($element->predicates->left);
       }
       elsif (ref($element) eq 'ARRAY'){
           foreach my $sub_element (@{$element}){
               $self->_check_element($sub_element);
               $self->_check_element($sub_element,$alias);
            }
      }
       else {
           return 
              unless(does_role($element,"Database::Accessor::Roles::Comparators"));
           $self->_check_parentheses($element);
--         $self->_check_element($element->right);
++         $self->_check_element($element->right,$alias);
           $self->_check_element($element->left);
       }
...
and in the '_elements_check' sub I make this small change

...
--            push(@items,$link->conditions);  
++            my $view = $link->to;
++            my $alias = !$view->alias ? $view->name : $view->alias;
++           $self->_check_element($link->conditions,$alias);
…
that does the element check early and does not add any link into the @items to save on iteration and thankfully all my test pass, Though I will have to make a note to myself that this inherit is only on the right parts of a predicate and I will have to add yet another test into the '57_dad_elements.t test' case, but that is another post.

img5689a.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