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