[orm-devel] select problem

[Ross J. Reedstrom]

On Sat, Mar 01, 2003 at 12:32:39PM +0100, Diedrich Vorberg wrote:
> Hi Werner,
> 
> >- Column names in the db are in capital letters.
> >- Column names in the class definition are in lower case
> this is odd: as I remember SQL requires case*in*sensitivity. But I 
> don't know for sure to be honest.

Yup, all identifiers (table, column, function names, etc.) are case
insensitive, unless quoted (with "). The part that's catching you here is
that theat's usually implemented by them not being case-preserving, either:
Oracle (and FB, apparently) upcase all unquoted identifiers. PostgreSQL,
on the other hand, downcases everything, the way C programmers like it. ;-)

The problem arised, then, when the case-altered value is returned, and
compared in python (which, of course, is case sensitive)

> >If no, what do you suggest to get over this (using lower case in db does 
> >not work, as somewhere along the line they get converted to upper).

You can probably quote everythngi, but it's a pain: I did a whole DB schema
with MixedCase once, and I almost went mad with things like:

select "Table1Name"."Column1Name", "Table2Name".* from "Table1", "Table2"
where ...

You get the idea.

> If upper case columns names are a rule of the RDBMs it should be put
> into the adapter's datasource module. The datasource_base class has a
> method selectColumns() which returns a string as "col1, col2, col3"
> for any SQL SELECT query generated by orm. The firebird datasource
> should probably overload it like this:
> 
>     def selectColumns(self, dbclass):
>         """
>         Firebird expects columns names to be upper case in general.
>         """
>         return string.upper(datasource_base.dbclass.selectColumns(self))
> 

Hmm, it's not FireBird that's having the problem with column names, I think:
it's orm. Be careful about up/down casing, since the SQL spec says these
names can be _anything_ if quoted properly: 

SELECT "#" from "My Really silly-tablename"

is perfectly valid SQL.

Ross