ForeignKey.references is case-sensitive, fails for autoloaded MSSQL table

[Former user]

When attempting to autoload a table in an MSSQL database, the ForeignKey.references() method doesn't correctly match the table name in the metadata, and fails to match. See the attached test case.

Note that this situation arises in a real-world scenario where a given schema is created on both Oracle, Postgres, and MSSQL using another product. Oracle and Postgres throw away the case, but MSSQL preserves it, so changing the Table definitions to match the MSSQL case doesn't fix the problem because it breaks when connecting to the Oracle and Postgres instances.

I imagine this problem could be resolved a couple of ways:

1) When a table is autoloaded (from any dialect), copy the name of the table as the database knows it into the .name property of the table. It's possible this will break other things including 3rd-party implementations. 2) Require the each dialect provide a method used to compare table names... and use that in ForeignKey.references(). I notice there is already significant case handling in the MSSQL dialect. Perhaps this can be extended to address this issue.

I also suspect there may be another approach that is more appropriate. If there is a user-side suggestion, that would be appreciated as well.

I have considered a work-around whereby I use engine.get_tables() to determine the table names before declaring the Table objects, but I would rather address this from the core issue first, if appropriate.

[Former user]

This may be a duplicate/extension of #487.

[Former user]

Upon further investigation, I note that (with reference to the test case)

fk.column.table is not group_table and \ fk.column.table.name == 'Groups'

This indicates to me that another table ('Groups') is being created when the foreign key constraint is being loaded from the database as part of the autoload process.

This makes sense, although it may invalidate some of my proposed solutions.

Perhaps I will delve later into how autoload works and see if I can further suggest a fix.

[Michael Trier]

This one is going to need some discussion. Currently the way it works is the referenced table is autoloaded based on the foreign key information returned from MSSQL. So in your test case we actually end up with two "groups" tables in metadata. One that is called Groups and one that is called groups (case difference). We could make it so it would find the table regardless of case, but then we would break a situation where someone is using case-sensitive collation and desires such odd behavior. The current situation is also not ideal though because we end up with both tables in the metadata.

[Michael Trier]

Modified test to show what's going on

[Mike Bayer]

the way I'd prefer this to work, which is currently what Postgres does, but unfortunately not mysql, is:

group_table = Table('Groups', metadata, autoload=True)
user_table = Table('Users', metadata, autoload=True)

i.e. there is no implicit conversion from a case insensitive name to a case sensitive one. you need to know which one to use. that would mean this is really a mysql dialect bug.

[Mike Bayer]

Replying to guest:

Note that this situation arises in a real-world scenario where a given schema is created on both Oracle, Postgres, and MSSQL using another product. Oracle and Postgres throw away the case, but MSSQL preserves it,

Oracle and Postgres support case sensitive identifier names, they have to be quoted. I don't think we should support guessing to work around the limitation of a particular tool.

[jek]

Replying to zzzeek:

the way I'd prefer this to work, which is currently what Postgres does, but unfortunately not mysql, is:

{{{ group_table = Table('Groups', metadata, autoload=True) user_table = Table('Users', metadata, autoload=True) }}}

i.e. there is no implicit conversion from a case insensitive name to a case sensitive one. you need to know which one to use. that would mean this is really a mysql dialect bug.

Huh? Not following the example or the followup. Also MySQL identifiers are either case sensitive or case-insensitive, case-preserving depending on the server. But this is a MSSQL ticket?

[Mike Bayer]

this test fails on mysql, passes on postgres:

from sqlalchemy import *

uri = 'mysql://scott:tiger@localhost/test'
metadata = MetaData()
metadata.bind=create_engine(uri, echo='debug')

group_table = Table('Groups', metadata,
    Column('id', Integer, primary_key=True),
    mysql_engine='InnoDB')
user_table = Table('Users', metadata,
    Column('id', Integer, primary_key=True),
    Column('group_id', Integer, ForeignKey('Groups.id')),
    mysql_engine='InnoDB')

metadata.drop_all()
metadata.create_all()

del metadata
metadata = MetaData()
metadata.bind = uri

group_table = Table('Groups', metadata, autoload=True)
user_table = Table('Users', metadata, autoload=True)

constraints = user_table.constraints
foreign_constraints = filter(lambda c: isinstance(c, ForeignKeyConstraint), constraints)

constraint = foreign_constraints[0](0)
fk = iter(constraint.elements).next()
assert fk.references(group_table), "%s doesn't reference %s" % (fk, group_table)

change "Groups" and "Users" at the bottom to "groups" and "users", passes on mysql, fails on PG.

[jek]

any changes to the mysql dialect identifier handling would absolutely require confirmation of passing tests on all of windows, unix and osx prior to commit.

[Mike Bayer]

It's a bug that probably cannot be fixed. here is the conversation:

;; reflect the Groups table
SHOW CREATE TABLE `Groups`

CREATE TABLE `Groups` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1'

;; reflect the Users table
SHOW CREATE TABLE `Users`

;; uhoh, the "Groups" foreign key has been lowercased !
CREATE TABLE `Users` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `group_id` int(11) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `group_id` (`group_id`),\n  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

;; we have no choice here but to reflect 'groups', we have no other information
SHOW CREATE TABLE groups

;; double uhoh, MySQL *still* wont give us the casing :(
CREATE TABLE `groups` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

[Mike Bayer]

i.e. cannot be fixed for case un-preserving servers. like OSX. which means this whole ticket should just be closed.

[Michael Trier]

Agreed. :)

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)