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