[Dbix-class] quote_char, order_by and DESC

[Adam Sjøgren]

  Hi.


I am accessing a database that may have reserved sql-words as
schema/table/field-names, with DBIx::Class 0.08007, SQL::Abstract 1.21.

To have the names quoted, I go:

  $schema->storage->sql_maker->quote_char('"');
  $schema->storage->sql_maker->name_sep('.');

This works fine until a search needs to be ordered descending; passing
order_by=>'name DESC' results in:

 DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: ERROR:  column "name DESC" does not exist
  [for Statement "SELECT "me"."id", "me"."name" FROM "user" "me" ORDER BY "name DESC""] at ./test.pl line 12

(order_by=>'me.name DESC' fails in the same way as well).


How should this be handled (besides renaming schema/table/field-names)?


Here is a simple test-case, I have used:

,----[ init.sql ]
| CREATE TABLE "user" (
|   id integer NOT NULL,
|   "name" text NOT NULL,
|   PRIMARY KEY (id)
| );
| 
| INSERT INTO "user" (id, "name") VALUES (1, 'Axel');
`----

,----[ UserDB.pm ]
| package UserDB;
| 
| use strict;
| use warnings;
| 
| use base qw(DBIx::Class::Schema);
| 
| __PACKAGE__->load_classes();
| 
| 1;
`----

,----[ UserDB/User.pm ]
| package UserDB::User;
| 
| use strict;
| use warnings;
| 
| use base qw(DBIx::Class);
| 
| __PACKAGE__->load_components('PK::Auto', 'Core');
| __PACKAGE__->table('user');
| __PACKAGE__->add_columns(qw(id name));
| __PACKAGE__->set_primary_key('id');
| 
| 1;
`----

,----[ test.pl ]
| #!/usr/bin/perl
| 
| use warnings;
| use strict;
| 
| use UserDB;
| 
| my $schema=UserDB->connect('dbi:Pg:dbname=userdb', 'adsj', 'adsj');
| $schema->storage->sql_maker->quote_char('"'); # We may have reserved words in schema/table-names
| $schema->storage->sql_maker->name_sep('.');
| 
| my @users=$schema->resultset('UserDB::User')->search(undef, { order_by=>'name DESC' });
`----

And a test-run:

 $ createdb userdb
 CREATE DATABASE
 $ psql userdb < init.sql 
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "user_pkey" for table "user"
 CREATE TABLE
 INSERT 0 1
 $ psql userdb --command 'SELECT * FROM "user" ORDER BY "name" DESC'
  id | name 
 ----+------
   1 | Axel
 (1 row)

 $ ./test.pl
 DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: ERROR:  column "name DESC" does not exist
  [for Statement "SELECT "me"."id", "me"."name" FROM "user" "me" ORDER BY "name DESC""] at ./test.pl line 12
 $ 


  Best regards,

     Adam

-- 
                                                          Adam Sjøgren
                                                    adsj at novozymes.com