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,
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


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() );
$da->result()->query() eq
"SELECT user.username, left(user.username,?), user.address FROM user WHERE user.username = ?",
"Function with 1 param bind SQL correct"
"Function params correct"

and I am ready to start codeing.

Now the first thing I have to do is to add a change 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;

use Moose::Role;
use namespace::autoclean;
has [
] => (
is => 'rw',
isa => 'Bool',

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

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

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 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/ 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 {
      foreach my $param (@params){
      my $right_sql = join(',',@right_sql);
      return $element->function
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;

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


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