[Sqlalchemy-tickets] [sqlalchemy] #2913: Table.tometadata() foreign key copying behavior with different schemas

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

#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