byterock

Its little validation day here in the Moose-Pen

Since I didn't put any new code in yesterday just take out I figure I might as well do that little validation I talked about in my last post namely you can only have one aggregate function inside another aggregate. As usual I start with a test;

      {

        caption    => 'Can not have a an aggregate in an aggregate',

        type       => 'exception',

        key        => 'elements',

        elements => [

            {

                function => 'count',

                left     => {

                    function => 'avg',

                    left       => { name => 'bonus' },

                },

            }],

        retrieve =>

          { message => 'An Element can have only one Aggregate function!' },

    },

and now I need someplace to stick it and I will go back to '20_fields.t' as I think it fits in there best as it really is a field validation. I already have the correct constants in place in 'Database::Accessor::Driver::DBI::SQL' and I am only going to cover the five basic 'AVG', 'COUNT','MIN','MAX' and 'SUM' to start as there are something like twenty in the latest SQL standard and many DBs have their own flavours. If it ever does become a complaint the patch for this would be easy anyway.

Now the question lies where to add in the validation? I think it will have to be a two part validation with a counter like I do for the 'parenthesis' validation at the Database::Accessor level. To start I will need a counter;

    has _aggregate_count => (

        traits  => ['Counter'],

        is      => 'rw',

        default => 0,

        isa     => 'Int',

        handles => {

            _inc_aggregate   => 'inc',

            _dec_ aggregate  => 'dec',

            _reset_aggregate => 'reset'

        }

    );

What I have to watch out for is this SQL;

SELECT COUNT(country_id), AVG(pay) FROM people GROUP BY country_id

where I do have two aggregates but they are in separate elements;

First I will have to start in the '_fields_sql' sub and reset that counter;

 sub _fields_sql {

  my $self = shift;

  my ($elements) = @_;

  my @fields = ();   

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

++    $self->_reset_aggregate();

    my $sql = $self->_field_sql($field,1);

...

and now in '_field_sql' I decrement that counter if my field is a 'function' class and it is one of my watched constants;

…

  elsif (ref($element) eq "Database::Accessor::Function"){

++      $self->_inc_aggregate()

++        if (exists(Database::Accessor::Driver::DBI::SQL::AGGREGATES->{$element->function}));

...

and right after that I do the die if there is more that onew;

...

  elsif (ref($element) eq "Database::Accessor::Function"){

      $self->_inc_aggregate()

        if (exists(Database::Accessor::Driver::DBI::SQL::AGGREGATES->{$element->function}));

++      die("Database::Accessor::Driver::DBI::Error->Element! An Element can have only one Aggregate function! ".$element->function." is not valid") 

++         if ( $self->_aggregate_count() >=2 );

And now lets see what we get when I run the test;

not ok 1 - retrieve Can not have a an aggregate in an aggregate

so something not right there. After a few mins of debugging I found;

bless( {

                                    'left' => bless( {

                                                       'view' => 'people',

                                                       'name' => 'bonus'

                                                     }, 'Database::Accessor::Element' ),

                                    'function' => 'avg',

      …


that the 'function' attribute is in lower case so to fix that I will have to go back to Database::Accessor sigh! 

I think all I need to do here it modity the '_element_coerce' sub in my 'Database::Accessor::Types' class with a single line to uppercase any function that is passed in;

sub _element_coerce {

    my ($hash) = @_;

    my $object;

    if ( exists( $hash->{expression} ) ) {

++    $hash->{expression} = uc($hash->{expression});

        $object = Database::Accessor::Expression->new( %{$hash} );

    }

    elsif ( exists( $hash->{function} ) ) {

++      $hash->{function} = uc($hash->{function});

        $object = Database::Accessor::Function->new( %{$hash} );

    }

and to boot I did the same for any expression that may be passed in as well. Now when I run my test I get;

ok 1 - retrieve Can not have a an aggregate in an aggregate

now I better add in one other test case to prove I can have two aggregates in a query;

   {

        caption =>

'2 Fields and two Aggregate',

        key      => 'elements',

        elements => [

            {

                name  => 'first_name',

                alias => 'first'

            },

            {

                name  => 'last_name',

                alias => 'last'

            },

            {

                function => 'count',

                left       => { name => 'first_name' },

            },

            {

                function => 'count',

                left     => {name => 'last_name'},

            }

        ],

        retrieve => {

            sql =>

'SELECT people.first_name first, people.last_name last, COUNT(people.first_name), COUNT(people.last_name) FROM people',

         },

    },

and I get;

ok 1 - 2 Fields and two Aggregate retrieve SQL correct

however when I run the full test case I get a big whack of fails;

Looks like you failed 8 tests of 87 run.

as now that my 'Functions' are all uppercase and my expected results would be mixed or lowercase, after a few quick changes I am not getting 100% pass.

I guess onto something else.