byterock

Its plan but do not code days here in Moose Pen

So I was all prepped and ready to start on the aggregate command for SQL as I had research the MongoDB and other similar non-sql dbs and they all shared the same basic set of commands. I even started writing up some tests starting with this SQL

SELECT person.last_name,SUM(person.pay)  FROM person GROUP BY person.last_name

all well and good you need to use the group by with that aggregate. However I forgot that about 90% of the dbs out there let you do this

SELECT SUM(person.pay)  FROM person

that is use an aggregate on a single field of a single table. It is only when you add in more that one field that you need the group by. After a little more playing about I could do this SQL

SELECT person.last_name,SUM(person.pay*1.1)  FROM person GROUP BY person.last_name

so not my aggregate is being applied to an expression.

Now where is this leading to?

In the end the 'aggregate' commands are really just the same as my present 'function' class. The only real caveat it that they should be used in a 'group by', or in DA terminology a gather statement, when more than on field is selected. Therefore I do not need to write any special code for handling the aggregates I am just going to treat them as any other function.

Looking at Database::Accessor I have a few things to clean up it. Once upon a time I must have been keen on having aggregates in my code someplace as have them in a number of places.

Therefor I should take them out if I am not going to use them. To start I can get rid of

--       has 'aggregate' => (

--            is  => 'rw',

--            isa => 'Aggregate',

--       );

from Database::Accessor::Element and I see that I have an 'Aggregate' type there and I can clean this code

--subtype 'Aggregate',

--  as 'Str',

--  where { exists( Database::Accessor::Constants::AGGREGATES->{ uc($_) } ) },

--  message {

--   "The Aggrerate '$_', is not a valid Accessor Aggregate!"

--      . _try_one_of( Database::Accessor::Constants::AGGREGATES() );

--  };

out of 'Database::Accessor::Types' and finally I can drop all of these;

...

-- use constant AVG         => 'AVG';

-- use constant COUNT       => 'COUNT';

-- use constant MEDIAN      => 'MEDIAN';

-- use constant MAX         => 'MAX';

-- use constant MIN         => 'MIN';

-- use constant SUM         => 'SUM';

...

-- use constant AGGREGATES => {

–     Database::Accessor::Constants::AVG    => 1,

--     Database::Accessor::Constants::COUNT  => 1,

--     Database::Accessor::Constants::MEDIAN => 1,

--     Database::Accessor::Constants::MAX    => 1,

--     Database::Accessor::Constants::MIN    => 1,

--    Database::Accessor::Constants::SUM    => 1,

-- };

...

out of 'atabase::Accessor::Constants' and that will clean up the code nicely. I will also have to spend a little time cleaning up some tests as they are no longer needed but I will not included that here.

Now that that is all done I was wondering if I should but in jut a little validation at the Driver::DBI level as all I would have to do is check to see if the entered function attribute is in a set of Aggregate functions and if it is then 'die' if there is no Gather for the Database::Accessor.. In the end I think this would not be practical or even workable while it might be correct there is a gather on the DA it might have nothing to do with the eaggregate function that is being called. Secondly there is really no end to the differing flavours of SQL aggregates that are out there. Seems each DB provider includes a few costume ones. So I am just going to make Database::Accessor as open as possible and let the DB engine decide if the generated query is any good.

Now before I stop for the day there is one truism for aggregates, in SQL at least and that is you cannot have an aggregate in an aggregate like this

 SELECT SUM(AVG(person.pay))  FROM person 

though this

 SELECT ABS(AVG(person.pay))  FROM person 

is valid. So maybe a little validation is required after all, but that is another post.