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
[EMAIL PROTECTED]
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
