[Sqlalchemy-tickets] [sqlalchemy] #2913: Table.tometadata() foreign key copying behavior with diffe
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2014-01-19 16:36:05
|
#2913: Table.tometadata() foreign key copying behavior with different schemas
-------------------------+-----------------------------------------
Reporter: dtheodor | Owner: zzzeek
Type: enhancement | Status: new
Priority: medium | Milestone:
Component: schema | Severity: no triage selected yet
Keywords: tometadata | Progress State: awaiting triage
-------------------------+-----------------------------------------
I think the behavior of Table.tometadata()'s copying of foreign keys is
unexpected when it comes to copying foreign keys that reference tables in
a schema different than the schema of the table being copied. This
occurred to me in the discussion at
https://groups.google.com/forum/#!topic/sqlalchemy-alembic/FA8wpmPJc7U
For instance, when we have two tables in different schemas
{{{#!python
m = Metadata()
t = Table('t', m,
Column('x', Integer, ForeignKey('a.x')),
schema="shared")
t1 = Table('t1', m,
Column('t_x', Integer, ForeignKey('shared.t.x')),
schema="schema1")
m2 = Metadata()
t1_copy = t1.tometadata(m2)
t1_copy_schema2 = t1.tometadata(m2, schema="schema2")
}}}
After this, `t1_copy.t_x` now references a column `schema1.t.x`, while
`t1_copy_schema2.t_x` references a `schema2.t.x`, both of which are wrong.
I think the correct behavior would be to only modify the schema of the
foreign key's reference '''only if''' that schema is the same as the
table's original schema. To be more precise:
1) If referenced_schema == previous_schema, update as is the current
behavior
2) If referenced_schema != previous_schema, then need to decide how/if to
update the referenced column's schema. I would leave the schema unchanged,
or pass a dictionary that maps previous schemas to the new ones.
In code, something like this:
{{{#!python
def tometadata(self, metadata, schema=RETAIN_SCHEMA,
fk_schema_replace={}):
# ....
#section on constraints copy
previous_schema = self.schema
#by default, modify FK's schema if the schema is the same as the
table's
if previous_schema not in fk_schema_replace:
fk_schema_replace.update({previous_schema: schema})
for c in self.constraints:
constraint_schema = schema
if isinstance(c, ForeignKeyConstraint):
fk_schema = c.elements[0].column.table.schema
constraint_schema = fk_schema_replace.get(fk_schema)
#if a replacement is found it is used, otherwise schema is
unchanged
table.append_constraint(c.copy(schema=constraint_schema,
target_table=table))
}}}
With this change, I can do
{{{#!python
t1_copy = t1.tometadata(m2)
t1_copy_schema2 = t1.tometadata(m2, schema="schema2",
fk_schema_replace={'shared':'schema2'})
}}}
Then `t1_copy.t_x` will reference `shared.t.x`, while
`t1_copy_schema2.t_x` will reference `schema2.t.x`
Thoughts?
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2913>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|