Re: [SQLObject] Reserved Words

[Ian B. <ia...@co...>]

Ian Sparks wrote:
>> Does SQL-Object check for reserved words prior to the creation of a
>>  table? Not only those in the SQL standards, but those specific to
>> a database. If so, this list should include words from all
>> supported databases and cross check these.
> 
> 
> We talked in the past about reserved words for databases. It was felt
> that it would be better to use fieldname quoting. For instance you
> can't have fieldname called SELECT in Firebird but you can have one
> called 'select'.
> 
> The problem with quoting is that it makes your fieldnames case
> sensitive..

I think in most cases that would be mostly okay, since all the databases 
(AFAIK) do case-folding when you don't have quoting.  So the change in 
semantics isn't too great, unless you were relying on the way SQLObject 
created tables, which is easier to control.

I'm not 100% sure that's true.  The test could be something like:

CREATE TABLE test (
     "Col" INT
);

SELECT col FROM test
SELECT "col" FROM test
SELECT Col FROM test
SELECT "Col" FROM test

I would expect all but the last query to fail.  But if the database were 
truly case-insensitive when there was no quoting, then everything but 
"col" should work.

> But I don't know whether there was ever a concensus to actually
> implement the quoting scheme (maybe not all DB's support it? I know
> that Firebird, Postgresql and Oracle? do).

I think they all do.  That would be okay.  I've loosened the 
restrictions on column names at the moment, so you can give a name like 
Col(dbName='"binary"') (in Postgres, for instance).  That, however, is 
clearly a hack.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org