The Last Moose Sort

It is more order code day here in the Moose-Pen

Yesterday I left off with the choice of either a change to do to my tests or a change to my code to solve this error;

# Expected SQL-> ORDER BY people.last_name, people.first_name
# Generated SQL-> ORDER BY people.last_name ASC, people.first_name ASC

Now this comes from how how I define the 'order' attribute in the 'Database::Accessor::Roles::Element' role;

has order => (
is => 'rw',
isa => 'SQLOrder',
default => Database::Accessor::Constants::ASC,

Reading though the SQL standard I am correct the default is 'ascending' but again I am not trying to set SQL standards in the Database::Accessor so I will just cut that out of there and fix that 'SQLOrder' type as well as that is a little too SQLish to be in; In the end I took out that Attribute and just added it as a boolean;

has [
++ descending
] => (
is => 'rw',
isa => 'Bool',
-- has order => (
-- is => 'rw',
-- isa => 'SQLOrder',
-- default => Database::Accessor::Constants::ASC,
-- );

I can get rid of that SQLOrder type as well as the ASC and DESC Constants as everyone know less code is better, no need to show that delete here, next I had to update the '60_order_by.t' test case;
sorts => [{name=>'last_name',
sql=>{“SELECT people.first_name, people.last_name, people.user_id
FROM people
ORDER BY people.last_name DESC, people.first_name }

and finally the Driver::DBI code;

foreach my $sort (@{$self->sorts()} ){
my $sql = $self->_field_sql($sort,1);
– $sql .= " " . uc( $sort->order );
++ $sql .= " " . Database::Accessor::Driver::DBI::SQL::DESC
++ if $sort->descending;

and now all the tests in '60_order_by.t' pass.

I was thinking it was going to be a very short post today but then I ran into this;

Bareword "Database::Accessor::Constants::ASC" not allowed while "strict subs" in
use at GitHub\database-accessor\lib/Database/ line 1186.

As I have this class hanging about in “

use Moose;
extends 'Database::Accessor::Element';
use namespace::autoclean;
has order => (
is => 'rw',
isa => 'Order',
default => Database::Accessor::Constants::ASC

and a test case '17_sort.t' for it as well.

I guess at one time I was thinking of having a separate class for sort elements. Looking at it I can have any valid expression, function, element or even parameter in the SQL 'Order By' so out it goes as it in no longer needed.

The last thing I have to do is expand my test a little so it covers the 'descending' flag for the other types 'function', 'expression' and a 'value' like this;

sorts => [
name => 'last_name',
descending => 1
name => 'first_name',
order => 'ASC'
function => 'left',
left => { name => 'username' },
right => { param => 11 },
descending => 1
expression => '*',
left => { name => 'salary' },
right => { param => .1 },
descending => 1
{ value => -1,
descending => 1 }

and an expected SQL of

ORDER BY people.last_name DESC, people.first_name, left(people.username,?)
DESC, people.salary * ? DESC, ? DESC

and after a few typo-cleanups I get a 100% pass

Not too bad.


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