byterock

It another gather day here in the Moose-Pen.

Now that I did the re-factoring for gather it is time to move on the Driver::DBI and do to code to generate the SQL. Of course I always start with a test case and this time it is a new one 50_having.t. It is much the same as the other test cases so no need to repeat that here, the only important bit is this hash;

my $tests = [{

    index=>0,

    key  =>'gather',

    gather =>{

        elements => [

            {

                name => 'first_name',

            },

            {

                name => 'last_name',

                view => 'people'

            },

            {

                name => 'user_id',

                view => 'people'

            }

        ],

        conditions => [

            {

                left => {

                    name => 'last_name',

                },

                right             => { value => 'Bloggings' },

                operator          => '=',

            },

        ]

      },

    caption => "Group bu with 1 param",

    sql     => "SELECT people.first_name, people.last_name, people.user_id  FROM people GROUP BY people.first_name, people.last_name, people.user_id HAVING people.last_name = ?",

params  => ['Bloggings']

}];

The fist thing I need to do is add in a call to generate the SQL;

...

    $sql .= $self->_where_clause();

++    $sql .= $self->_group_by_clause();

...

This time I checked and the ISO standard way to formulate SQL is 'WHERE' before 'GROUP BY' though some SQL engines let you do it either other way round.

For the '_group_by_clause' sub I think I can get away with something as simple as

sub _group_by_clause {

    my $self = shift;

    return ""

      unless ( $self->gather );

    my $having = $self->gather;

    return " ".join(" "

                ,Database::Accessor::Driver::DBI::SQL::GROUP_BY

                ,$self->_element_sql($having->elements())

                ,$having->condition_count >=1 

                  ? join(" "

                        ,Database::Accessor::Driver::DBI::SQL::HAVING

                        ,$self->_predicate_clause( Database::Accessor::Driver::DBI::SQL::GROUP_BY,

                                                  $having->conditions ) )

                  : "");

}

I gave it the old one~two and my test case gave me

Attribute (gather) does not pass the type constraint because: Validation failed for 'Gather|Undef' with value [ { conditions: ARRAY(0x422b4e4), elements: ARRAY(0x422b01c) } 

and that is because I test is assuming I want to put an element in an array not a hash; A little adjustment for that

--      $in_hash->{ $opts->{key} }->[ $opts->{index} ] = $opts->{ $opts->{key} };

    if (exists($opts->{index})) {

        $in_hash->{ $opts->{key} }->[ $opts->{index} ] = $opts->{ $opts->{key} };

      }

      else {

        $in_hash->{ $opts->{key} } = $opts->{ $opts->{key} };

      }

and now I get

Can't call method "view" on unblessed reference at GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 508.

a little progress, I guess? What is happening here is I am passing an array-ref of elements down into '_element_sql' sub and it is designed to take only a scalar. A little re-factoring is in order me thinks;

First I created a new sub;

sub _elements_sql {

  my $self = shift;

  my ($elements) = @_;

  my @fields = ();   

  foreach my $field ( @{$elements} ) {

     push(@fields,$self->_element_sql($field,1));

  }

  my $sql = join(", ",@fields);

  return $sql;

}

for formatting up a set of elements/field and then changed the code where this was to be used, first in the '_select_clause' sub

--  my @fields           = ();

--    foreach my $field ( @{$self->elements()} ) {

--        push(@fields,join(" ",

--                        $self->_element_sql($field,1)));

--     }

--   my $select_clause    = join(" ",

--                              Database::Accessor::Driver::DBI::SQL::SELECT,

--                               join(", ",@fields));

++    my $select_clause    = join(" "

++                               ,Database::Accessor::Driver::DBI::SQL::SELECT

++                               ,$self->_elements_sql($self->elements())); 

and then in the '_group_by_clause' sub;

...

    ,Database::Accessor::Driver::DBI::SQL::GROUP_BY

--    ,$self->_element_sql($having->elements())

++    ,$self->_elements_sql($having->elements())

    ,$having->condition_count >=1 

and I get an error in the SQL

 GROUP BY .first_name, people.last_name, people.user_id HAVING people.last_name = ?

'people' missing off of 'first_name' for now I will stub that in the test hash, but the real problem is in Database::Accessor as it should set that value before it gets to the Driver::DBI. After a quick stub in I now get

ok 1 - Having with 1 param SQL correct

ok 2 - Having with 1 param params correct

Again mostly painless. Now to go back and fix that problem in Database::Accessor but that is for another post.