When using "Overriding Reflected Columns" feature, foreign key constraints fails when (auto-)generating relationship properties

[Armin Gruner]

Hi,

if I change some property names distinct from the table column names according to

http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html?highlight=column%20reflect#sqlalchemy.engine.reflection.Inspector.get_columns,

I get an error later when preparing the automap base:

@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
  name = column_info["name"]
  column_info["key"] = name.lower()

def setup():
  Base.metadata.reflect(engine, extend_existing=True, autoload_replace=False)
  Base.prepare(engine)

NoReferencedColumnError: Could not initialize target column for ForeignKey 'ABSTRACT_FORWARD_DESTINATION.ID' on table 'TRUNK': table 'ABSTRACT_FORWARD_DESTINATION' has no column named 'ID'

If I disable the code inside the column reflection listener, SQLAlchemy 0.9.8 properly reflects all tables, creates object classes and relations.

[Mike Bayer]

this might require adjustment on your end, because this use case is covered. The ForeignKey and ForeignKeyConstraint objects refer to a target column using the ".key" name of the Column, unless the link_to_name flag is set. The reflection process sets this flag, so that when a table is reflected, the ForeignKeyConstraint objects generated will refer to their target by name and not .key which might be modified.

So in this case, questions are:

1. why are your columns coming back as ALL_UPPERCASE, is this Oracle and/or Firebird, otherwise were these names actually created as quoted such that they are case sensitive names? Note that SQLAlchemy column names should always be declared in all_lowercase, including on Oracle/Firebird, as the casing conventions are automatically converted.

2. what is going on with the in-Python schema defintiions, are there in-Python ForeignKey(Constraint) objects that are referring to the UPPERCASE names but are not setting link_to_name?

This is why full test cases are absolutely important and without them, it creates work where I have to guess. Here's my test case, it passes. Show me yours.

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy import event

e = create_engine("sqlite://", echo=True)
e.execute("""
create table foo (
    x integer primary key,
    y varchar(20)
)
""")

e.execute("""
create table bar (
    b integer primary key,
    q integer,
    foreign key (q) references foo(x)
)
""")


@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
    name = column_info["name"]
    column_info["key"] = "SOME_COLUMN_%s" % name

m1 = MetaData()
foo = Table('foo', m1, autoload=True, autoload_with=e)
bar = Table('bar', m1, autoload=True, autoload_with=e)

assert bar.c.SOME_COLUMN_q.references(foo.c.SOME_COLUMN_x)

[Mike Bayer]

need more information from the user.

[Armin Gruner]

Hi Mike, thanks for getting back so quickly!

Actually, ALL definitions are coming from an [legacy] MySQL database where both all table names AND column names are in upper-case. Excerpt:

CREATE TABLE `ABSTRACT_FORWARD_DESTINATION` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CREATED_AT` datetime NOT NULL,
  `UPDATED_AT` datetime DEFAULT NULL,
  `DISCRIMINATOR` varchar(255) DEFAULT NULL,
  `DISPLAY_NAME` varchar(50) NOT NULL,
...

I'm reflecting everything (tables and thus, foreign key constraints) via a MYSQL engine from the database. From what I understand, table names and column names in MYSQL are case sensitive if defined this way?

Cheers, Armin

[Mike Bayer]

okey doke, test case passes, if you can't give me a test case that shows your problem i'll have to close this, thanks!

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy import event

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
e.execute("""
CREATE TABLE IF NOT EXISTS `ABSTRACT_FORWARD_DESTINATION` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CREATED_AT` datetime NOT NULL,
  `UPDATED_AT` datetime DEFAULT NULL,
  `DISCRIMINATOR` varchar(255) DEFAULT NULL,
  `DISPLAY_NAME` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`)
)   ENGINE=InnoDB

""")

e.execute("""
create table IF NOT EXISTS `TRUNK` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AFD_ID` bigint(20),
  PRIMARY KEY (`ID`),
  FOREIGN KEY (`AFD_ID`) REFERENCES `ABSTRACT_FORWARD_DESTINATION`(`ID`)
)   ENGINE=InnoDB

""")


@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
    name = column_info["name"]
    column_info["key"] = name.lower()

m1 = MetaData()
foo = Table('ABSTRACT_FORWARD_DESTINATION', m1, autoload=True, autoload_with=e)
bar = Table('TRUNK', m1, autoload=True, autoload_with=e)

assert bar.c.afd_id.references(foo.c.id)

[Mike Bayer]

From what I understand, table names in MYSQL are case sensitive.

these are quoted identifiers so they are case sensitive for that reason. whether identifiers are case sensitive in the absense of quoting depends on storage engine, settings, and OS in use: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

[Armin Gruner]

Thanks for your time, Mike. I indeed have to craft up some example. Obviously there is something "more" going on at my site, the database schema is somewhat complicated, and probably self-references cause the failure.. I will investigate further to get a working example why the automapping isn't working in my case.

Thanks so far and Greets from Munich! Armin

[Armin Gruner]

Okay, please find the attached "test6.py":

sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'LYNC_AFD_OPTIONS.ID' on table 'ABSTRACT_FORWARD_DESTINATION': table 'LYNC_AFD_OPTIONS' has no column named 'ID'

As mentioned, it's a legacy database, I have to live with the schema (and stuff like cross-referencing stuff between A_F_D and LYNC_AFD_OPTIONS). Probably automap cannot (should not?) support it because it may be to complicated to handle this corner case..

Greetings Armin

[Armin Gruner]

It looks like it's not a problem with modifying the column key in the column reflect listener at all; it seems that pre-defining

class LyncAFDOptions(Base)

is the culprit: reflection doesn't seem to (or cannot?) create the remaining column properties (and the predefined class does not appear in Base.classes at all).

Funnily the exception mentioned above is only shown if the column_reflect listener is set..

[Mike Bayer]

confirmed

[Mike Bayer]

• Fixed bug in 0.9's foreign key setup system, such that the logic used to link a :class:.ForeignKey to its parent could fail when the foreign key used "link_to_name=True" in conjunction with a target :class:.Table that would not receive its parent column until later, such as within a reflection + "useexisting" scenario, if the target column in fact had a key value different from its name, as would occur in reflection if column reflect events were used to alter the .key of reflected :class:.Column objects so that the link_to_name becomes significant. Also repaired support for column type via FK transmission in a similar way when target columns had a different key and were referenced using link_to_name. fixes #3298

→ <<cset 3712e35c329c>>

[Mike Bayer]

• Fixed bug in 0.9's foreign key setup system, such that the logic used to link a :class:.ForeignKey to its parent could fail when the foreign key used "link_to_name=True" in conjunction with a target :class:.Table that would not receive its parent column until later, such as within a reflection + "useexisting" scenario, if the target column in fact had a key value different from its name, as would occur in reflection if column reflect events were used to alter the .key of reflected :class:.Column objects so that the link_to_name becomes significant. Also repaired support for column type via FK transmission in a similar way when target columns had a different key and were referenced using link_to_name. fixes #3298

(cherry picked from commit 3712e35c329cc3b5106f026be90e04f65412586d)

→ <<cset 8a744a468486>>

[Mike Bayer]

fixed for 0.9.9 thanks