Incorrect quoting of the column names during creation of the constraints

Hello,

I've experienced that in DB2 and verified for SQLite and MySQL. The problem I have is: Names with all lower-case letters are considered case-insensitive. DB2 is dumb and stores those internally in all upper-case. (!!!) Case-sensitive names are supposed to be quoted inside SQL statements. In my project I have columns like: 'firstUser' and 'address'. So the first one is considered case-sensitive, the second case-insensitive. Having lots of unexpected problems I wanted to remain consistent and enforce quoting of all names. I've found in the SQL Alchemy documentation that I can do that with the appropriate Table and Column argument named ''quote''. The problem is that it affects quoting of the names for specifying the columns themselves, not while specifying constraints on them - at least not on all types.

Example:

users = Table('users', metadata,
    Column('boolq', Boolean, nullable=False, unique=True, quote=True),
    Column('bool', Boolean, nullable=False, unique=True),
    Column('boolC', Boolean, nullable=False, unique=True),
    quote=True
)


CREATE TABLE "users" (
    "boolq" BOOLEAN NOT NULL, 
    bool BOOLEAN NOT NULL, 
    "boolC" BOOLEAN NOT NULL, 
    UNIQUE ("boolq"), 
    CHECK (boolq IN (0, 1)), 
    UNIQUE (bool), 
    CHECK (bool IN (0, 1)), 
    UNIQUE ("boolC"), 
    CHECK ("boolC" IN (0, 1))
)

2013-07-17 13:17:21,328 INFO sqlalchemy.engine.base.Engine ()
2013-07-17 13:17:21,358 INFO sqlalchemy.engine.base.Engine COMMIT
2013-07-17 13:17:21,358 INFO sqlalchemy.engine.base.Engine CREATE UNIQUE INDEX ix_users_boolq ON "users" ("boolq")
2013-07-17 13:17:21,358 INFO sqlalchemy.engine.base.Engine ()
2013-07-17 13:17:21,390 INFO sqlalchemy.engine.base.Engine COMMIT
2013-07-17 13:17:21,390 INFO sqlalchemy.engine.base.Engine CREATE UNIQUE INDEX "ix_users_boolC" ON "users" ("boolC")
2013-07-17 13:17:21,390 INFO sqlalchemy.engine.base.Engine ()
2013-07-17 13:17:21,437 INFO sqlalchemy.engine.base.Engine COMMIT
2013-07-17 13:17:21,437 INFO sqlalchemy.engine.base.Engine CREATE UNIQUE INDEX ix_users_bool ON "users" (bool)
2013-07-17 13:17:21,437 INFO sqlalchemy.engine.base.Engine ()
2013-07-17 13:17:21,467 INFO sqlalchemy.engine.base.Engine COMMIT

In the above case DB2 rejects the command complaining that it cannot apply ''CHECK (boolq IN (0, 1))'' to the table ''users'', as the table does not contain the column ''BOOLQ''.

However neither SQLite nor MySQL do complain during the execution, although the command generated isn't correct.

I'm also wondering if the names of the generated indexes should not follow the quoting restrictions of the appropriate table and column, so if the name 'ix_users_boolq' shouldn't be quoted at least because column boolq was quoted and 'ix_users_bool' shouldn't be quoted because the table 'users' was quoted (and the name of the table would affect all its indexes really). But this is not an issue for me at the moment.

Comments (2)