Bug #65133 Move Table(s) from schema to schema
Submitted: 27 Apr 2012 14:55 Modified: 27 Apr 2012 15:29
Reporter: Paul Schwarz Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Modeling Severity:S4 (Feature request)
Version:5.2.29 CE Revision 6756 OS:Windows (7)
Assigned to: CPU Architecture:Any
Tags: copy/paste, move, schema

[27 Apr 2012 14:55] Paul Schwarz
In my model there are two schemas, call them db1 and db2, both containing tables.

Later I added more tables which were supposed to go into db2 but I accidentally added them to db1.

The only option I had was to cut/paste each table individually from db1 to db2. In doing so foreign key references were lost. Then I'd have to switch over to the diagram view and drag the moved tables back onto the diagram which involves setting up all the properties again. It appears there is no easy/correct way to do this.

How to repeat:
Notice that there is no obvious way to move tables (or views, etc) from one schema to another.

Suggested fix:
"akojima" from the IRC forum #workbench was able to provide me with a high effective python script that simply changes ownership of each table in a given array of table names as below.

SUGGESTED FIX: mimic the functionality below by allowing the user to click and select tables under Physical Schemata > Tables (same goes for Views, etc) and then right click and choose "move to schema" from where the user can choose from a list of other schema. This action would simply change the owner of the table. Importantly, the script below handled foreign key references fine without corrupting them.

import grt
#import mforms

def find_schema(name):
    for s in grt.root.wb.doc.physicalModels[0].catalog.schemata:
        if s.name == name:
            return s
    return None

source = find_schema("db1")
target = find_schema("db2")

TABLES_TO_MOVE=["country", "inventory", "city"]

for i in reversed(range(len(source.tables))):
    table = source.tables[i]
    if table.name in TABLES_TO_MOVE:
        table.owner = target
        del source.tables[i]
[27 Apr 2012 15:29] Valeriy Kravchuk
Thank you for the feature request.