Gather Up Baby Moose

Its gather in the dream day here in the Moose-pen

Well going to skip a post on creating a gaggle of new tests today and instead do some core coding in Driver::DBI and Database::Accessor. Now believe it or not I had a dream last night that something was wrong in the way I set the architecture of Grathers and Filters, GROUP BY and , HAVING in SQL, in Database::Accessor. Boy this does sound corny!. So today I started to look at that part of my code.

In the original Data::Accessor a 'GROUP BY' and 'HAVING' looked like this


{ table => { name => 'people },
fields => [ {name => 'first_name', } ],
group_by =>[{name=>'first_name'},
{name=>'last_name'}, ],
having => [{ field => { name => 'last_name' }, param => 'Blog',}],
}

and in my new incarnation it aped it to be much the same like this

{ view => { name => 'people' },
elements => [ {name => 'first_name', } ],
gather =>[{name=>'first_name'},
{name=>'last_name'}],
filter => [{ left => { name => 'last_name' }, right=>{value => 'Blog',}],
}

expecting and SQL like this

SELECT people.first_name FROM people GROUP BY people.first_name,people.last_name HAVING people.last_name = ?

Yesterday I was all set to code it as above but the brainstorm I had last night showed me the error of my ways. In SQL you can only ever have one 'Group By' and one 'Having' but you cannot have a 'Having' clause with out a 'Group by'. The way I set it up above I imply that I could have a 'filter' without 'gather' I could fix this my making 'gather' required if filter is present but that will be very tricky an awkward to implement.

Much better to go with this new 'Gather' Class that takes this profile


...
gather =>{elements=> [{name=> 'first_name'},
{name=> 'last_name'}]},
conditions=> [ {left=> { name => 'last_name' },
right=> {value => 'Blog',}]},
...

The re-write on Accessor.pm starts with dropping the 'gathers' and 'filters' attributes from 'Database::Accessor::Roles::Common' and the 'dynamic_gathers' and 'dynamic_filters' attributes from Database::Accessor and replacing them with

has gather => (
is => 'ro',
isa => 'Gather|Undef',

);


and

has dynamic_gather => (
isa => 'Gather',
traits => ['MooseX::MetaDescription::Meta::Trait'],
description => { not_in_DAD => 1 },
is => 'rw',
);

and a new class 'Database::Accessor::Gather'

package
Database::Accessor::Gather;
use Moose;
extends 'Database::Accessor::Base';

has elements => (
isa => 'ArrayRefofElements',
is => 'rw',
traits => ['Array'],
handles => { element_count => 'count',
},
required=>1,
);
has conditions => (
isa => 'ArrayRefofConditions',
is => 'rw',
traits => ['Array'],
handles => { condition_count => 'count', },
default => sub { [] },
);
1;

and some 'Type' and coercion code for the above;


class_type 'Expression', { class => 'Database::Accessor::Expression' };
++class_type 'Gather', { class => 'Database::Accessor::Gather' };

coerce 'Gather', from 'HashRef', via { Database::Accessor::Gather->new( %{$_} ) };

On more thing I have to add are a few extra subs so my API is consistent;

sub add_gather {
my $self = shift;
my ($gather) = @_;
$self->dynamic_gather($gather);
}
sub reset_gather {
my $self = shift;
$self->dynamic_gather(undef);

}
sub dynamic_gather_count{
my $self = shift;
return 1
if $self->dynamic_gather();
}

and then I have to munged any 'static' and 'dynamic' gathers into one when passing to the DAD, which I can do like this;

my $gather = undef;
if ($action eq Database::Accessor::Constants::RETRIEVE and ($self->gather() || $self->dynamic_gather()) ){
my @elements;
my @conditions;
if ($self->gather()) {
push(@elements,map($self->check_view($_), @{$self->gather()->elements()}));
push(@conditions,@{$self->gather()->conditions});
}
if ($self->dynamic_gather()){
push(@elements,map($self->check_view($_), @{$self->dynamic_gather()->elements()}));
push(@conditions,@{$self->dynamic_gather()->conditions});
}
$gather = Database::Accessor::Gather->new({elements=>\@elements,
conditions=>$self->check_predicates(\@conditions)});
}

and then I just pass the value of '$gather' into the DAD;

links => $self->check_predicates([@{$self->links},@{$self->dynamic_links}]),
++ gather => $gather,
sorts => ($action eq Database::Accessor::Constants::RETRIEVE) ? [@{ $self->sorts } ,@{ $self->dynamic_sorts }] : [],

Now I will rewrite my Database::Accessor tests but I won't bore you with the details as you can look it at here. On my first run I got;

Can't use string ("People") as a HASH ref while "strict refs" in use at GitHub\database-accessor\lib/Database/Accessor/Types.pm line 147.

A problem in the coercion perhaps. After some debugging I found that the problem was not there but how I was munging the 'gather' together it seems this arrangement fixed that;

my $gather = undef;
if ($action eq Database::Accessor::Constants::RETRIEVE and ($self->gather() || $self->dynamic_gather()) ){
my @elements;
my @conditions;
if ($self->gather()) {
push(@elements,@{$self->gather()->elements()});
push(@conditions,@{$self->gather()->conditions});
}
if ($self->dynamic_gather()){
push(@elements, @{$self->dynamic_gather()->elements()});
push(@conditions,@{$self->dynamic_gather()->conditions});
}
$gather = Database::Accessor::Gather->new(
{elements=>\@elements,
conditions=>$self->check_predicates(\@conditions)});
}

but I still got this error;

Can't locate object method "gather_count" via package "Database::Accessor::Driver::Test" at 37_gathers.t line 118.

and that was because I did not have a 'gather_count' sub in my 'Database::Accessor::Roles::Common' role so I added it in

sub gather_count{
my $self = shift;
return 1
if $self->gather();
}

and all my tests passed. So the day ends on a high.
IMG_5994a.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