Ambitious Baby Moose

Its get back into Driver::DBI day today here in the Moose-Pen

I decided to get a little more ambitious and see it I can get Driver::DBI to handle a function call in an SQL select, like this;


SELECT user.username,
left(user.username,11),
user.address
FROM user
WHERE user.username = 'Bill'

The way I have planned to do this is Database::Accessor with the 'Function' class, which has a simple string attribute for the function name and it is used with 'Comparator' role attributes. Thus I can break

left(user.username,11)

into the following attributes

+-----------+---------------+
| Attribute | Value |
+-----------+---------------+
| function | left |
+-----------+---------------+
| left | user.username |
+-----------+---------------+
| right | 11 |
+-----------+---------------+

which would look like this when calling a DA

{ function => 'left',
left => { name => 'username' },
right => { param =>11} },

and from that I can write up this test;

$da = Database::Accessor->new(
{
da_compose_only => 1,
view => { name => 'user', },
elements => [
{ name => 'username', },
{ function => 'left',
left => { name => 'username' },
right =>{ param =>11}},
{ name => 'address', },
],
conditions => {
left => {
name => 'username',
view => 'user'
},
right => { value => 'Bill' }
}
}
);
$da->retrieve( $utils->connect() );
ok(
$da->result()->query() eq
"SELECT user.username, left(user.username,?), user.address FROM user WHERE user.username = ?",
"Function with 1 param bind SQL correct"
);
cmp_deeply(
$da->result()->params,
[11,'Bill'],
"Function params correct"
);

and I am ready to start codeing.

Now the first thing I have to do is to add a change Accessor.pm that will allow me to pass a function down to the DAD. In SQL you can use a function as a substitute for a field so I should allow it with the same rules as the 'Element' class that means I have to get the sub 'get_dad_elements' to work.

First I created a new role for the following 'Element' attributes that are now shared with the 'Function' class;


{
package
Database::Accessor::Roles::Element;
use Moose::Role;
use namespace::autoclean;
has [
qw(no_create
no_retrieve
no_update
only_retrieve
)
] => (
is => 'rw',
isa => 'Bool',
);
}

Then of course I just had to include them via the 'with' command

...
Database::Accessor::Element;
use Moose;
extends 'Database::Accessor::Base';
with qw(Database::Accessor::Roles::Alias
++ Database::Accessor::Roles::Element );

package
Database::Accessor::Function;
use Moose;
extends 'Database::Accessor::Base';
with qw(Database::Accessor::Roles::Comparators
++ Database::Accessor::Roles::Element);
...


Next I made the 'check_view' sub a little recursive;

private_method check_view => sub {
my $self = shift;
my ($element) = @_;

++ if (ref($element) eq 'Database::Accessor::Element'){
unless ( $element->view() ) {
$element->view( $self->view->name() );
$element->view( $self->view()->alias() )
if ( $self->view()->alias() );
}
++ }
++ else {
++ $self->check_view($element->right)
++ if (ref($element->right) eq 'Database::Accessor::Element');
++ $self->check_view($element->left)
++ if (ref($element->left) eq 'Database::Accessor::Element');
++ }

};


and then I got past the Accessor.pm code and into Driver ::DBI;

Can't locate object method "view" via package "Database::Accessor::Function" at D:\GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 417

All I need to do now is expand the '_element_sql' to allow for an 'element' that is a function and my first cut was;


if (ref($element) eq "Database::Accessor::Function"){
      my $left_sql = $self->_element_sql($element->left());
      my @right_sql;
      my @params;
       if (ref($element->right()) eq "Array"){
        @params= @{$element->right()};
      }
      else {
        push(@params,$element->right());
      }
      foreach my $param (@params){
        push(@right_sql,$self->_element_sql($param));
      }        
      my $right_sql = join(',',@right_sql);
      return $element->function
             .Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
             .$left_sql,
             .','
             .$right_sql
             .Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS;
                          
  }
which was almost perfect as I get this SQL generated;

SELECT user.username, left(user.username, user.address FROM user WHERE user.username = ?
and after a little debugging I found the bug in the above code;

               .Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
--             .$left_sql,
++             .$left_sql
               .','
silly extra ',' got in there and my test output is

ok 1 - Function with 1 param bind SQL correct
ok 2 - Function params correct
Now one thing about functions is they can have many options that I why I added in that iteration over the '@params' above. I also tried to make the code recursive so I can have a function buried in a function;

Tomorrow lets see if that will work.

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