byterock

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.