[Sqlalchemy-tickets] [sqlalchemy] #2784: Incorrect quoting of the column names during creation of the constraints

[sqlalchemy <mi...@zz...>]

#2784: Incorrect quoting of the column names during creation of the constraints
-----------------------------+-----------------------------------------
 Reporter:  adam.markiewicz  |           Owner:  zzzeek
     Type:  defect           |          Status:  new
 Priority:  medium           |       Milestone:  0.8.xx
Component:  engine           |        Severity:  no triage selected yet
 Keywords:                   |  Progress State:  awaiting triage
-----------------------------+-----------------------------------------
 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.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2784>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python