byterock

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.