[Rt-devel] Searchbuilder 1.24 + postgres + quoted identifiers = boom

[David Kerry]

Hello,

Just a big heads-up...

I notice the latest version of DBIx-SearchBuilder (v1.24) now uses
dbh->quote_identifier() everywhere when it builds queries.

Unfortunately, this totally busts the postgres and Oracle compatibility
(other databases too, probably).  Normally (as far as I'm aware,
anyways), most databases treat column names as case-independent,
*unless* you go to the trouble of quoting them *everywhere*.

For example, a table created like this in postgres (and Oracle):

create table principals (
        id INTEGER DEFAULT nextval('principals_id_seq') not null,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer, 
        Disabled integer NOT NULL DEFAULT 0 ,
        PRIMARY KEY (id)
);

Actually comes out like this:

rt34test=> \d principals
                                  Table "public.principals"
    Column     |         Type          |                      Modifiers                      
---------------+-----------------------+-----------------------------------------------------
 id            | integer               | not null default nextval('principals_id_seq'::text)
 principaltype | character varying(16) | not null
 objectid      | integer               | 
 disabled      | integer               | not null default 0
Indexes:
    "principals_pkey" primary key, btree (id)
    "principals2" btree (objectid)


Note that all the column names are now lowercase.  In oracle, they'd
all be uppercase.

Now, a query like this:

select * from principals where disabled = 0;

will work fine.

So will this:

select * from principals where DiSaBlEd = 0;

However, this *won't* work:

select * from principals where "Disabled" = 0;

Postgres replies - column doesn't exist (so will oracle).

As soon as you quote all the columns - it forces case-sensitivity
by the database.

The reason I found this one - I can no longer install the latest
RT 3.4.1 on postgres because it can't insert the initial data
set.

It seems like quoting everything like this is not such a good
idea.  It really only needs to be done when the columns have
spaces or special characters in them, and even then, the case
must match still.

-- 
David Kerry