byterock

It take back my words day here in the Moose-Pen

I have to adjust how the SQL is produced yet again. Today I was playing about with the '60_order_by.t' tast case of Driver::DBI and had it all nicely set up and on my first run I was getting errors like this;

# Expected SQL--> INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )
# Generated SQL-> INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )
 ORDER BY people.last_name, people.first_name

and even this

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

This all stems from this post where I must of missread somehing in thinking that it was part of the SQL standard the you could have an ORDER BY on anything other than a SELECT. Now it is true that some DB engines do support the extended use of ORDER BY but not many even Oracle SQL does not. What I was seeing was an action of SQLPlus doing the sort outside the actual DB command.

Anway Not a great deal of harm done. All I need to do is adjust where I make my call to the '_order_by_clause' sub in the 'execute' sub like this;

}
    else {
        $sql = $self->_select();
        $sql .= $self->_join_clause();
        $sql .= $self->_where_clause();
        $sql .= $self->_group_by_clause();
++      $sql .= $self->_order_by_clause();        
    }
    
--      $sql .= $self->_order_by_clause();

and now I get a full pass on 13 test.

I did forget to do one other thing with ORDER BY and that is add in the the 'ASC' and 'DESC' keywords as this;

SELECT people.first_name, people.last_name, people.user_id FROM people 

ORDER BY people.last_name, people.first_name DESC

and even this;

SELECT people.first_name, people.last_name, people.user_id FROM people 

ORDER BY people.last_name ASC, people.first_name DESC

are perfectly legitimate statements. All the spec states is that the ASC or DESC key word should follow an expression. In both the above cases the fields names are just expressions. Now how to do this in Database::Accessor

In Accessor my 'sorts' and 'dynamic_sorts' are 'ArrayRefofParams' so I might be able to create a new type for these two. The problem is where to a keep the 'ASC' or 'DESC' value?

Now that 'ArrayRefofParams' can be any one of 'Element|Param|Function|Expression' so what ever I do it has to cover all of those classes but only when it is part of a 'Sort'.

I can add that in as this attribute;

 has order => (

        is      => 'rw',

        isa     => 'SQLOrder',

        default => Database::Accessor::Constants::ASC,

    );

to the 'Database::Accessor::Roles::Element' role as it is common to all four classes. Now I need to add in a type for it in 'Database::Accessor::Types' just like I have done in the past for other custom types;

subtype 'SQLOrder',

  as 'Str',

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

  message { "The Order '$_', is not a valid Order!"

            ._try_one_of(Database::Accessor::Constants::ORDERS()) 

          };

Now I should be able to do this on my 'sort'

sorts => [{name=>'last_name',

                order=>'DESC'},

              {name=>'first_name',

               order=>'ASC'}

              ],

and I now have to change my Driver::DBI a little but it is here I run into a little bit of a sticky wicket;

sub _order_by_clause {

    my $self = shift;

    return ""

      unless ( $self->sort_count );

    return " "

      . join( " ",

        Database::Accessor::Driver::DBI::SQL::ORDER_BY,

        $self->_fields_sql( $self->sorts() ) );

}

I will have to either re-write that '_fields_sql' to take into account that it is being called from a 'sort' or re-write the whole sub. In this as the sorts are just simple expressions joined with ',' it might be best to just re-work this sub so it does not use the '_fields_sql' sub. Here is my first crack at it;

sub _order_by_clause {

    my $self = shift;

    return ""

      unless ( $self->sort_count );

    my @sorts;    

    foreach my $sort (@{$self->sorts()}){

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

      $sql .= " " . uc( $sort->order );

      push(@sorts,$sql);

    }

    return " "

      . join( " ",

        Database::Accessor::Driver::DBI::SQL::ORDER_BY,

        join(", ",@sorts)

        );

}

and Of course I added in a new test

…

    sorts => [{name=>'last_name',

                order=>'DESC'},

              {name=>'first_name',

               order=>'ASC'}

              ],

    caption => "Simple Order by with ASC and DESC",

    retrieve => {

            sql => "SELECT people.first_name, people.last_name, people.user_id 

                        FROM people 

                      ORDER BY people.last_name DESC, people.first_name ASC",

    },

and after a few runs to get rid of the odd little bug I get

...

ok 14 - Simple Order by with ASC and DESC create SQL correct

ok 15 - Simple Order by with ASC and DESC create params correct

ok 16 - Simple Order by with ASC and DESC retrieve SQL correct

ok 17 - Simple Order by with ASC and DESC update SQL correct

ok 18 - Simple Order by with ASC and DESC update params correct

ok 19 - Simple Order by with ASC and DESC delete SQL correct

but unfortunately now I am getting;

...

not ok 3 - Simple Order by  retrieve SQL correct

...

as that SQL now looks like this;

SELECT people.first_name, people.last_name, people.user_id

               FROM people 

               ORDER BY people.last_name ASC, people.first_name ASC

Oh well tomorrow's topic.