C.6.2 Tutorial: Generating Foreign Keys with MyISAM

EER Diagrams are useful for visualizing complex database schemata. They are often created for existing databases, to clarify their purpose or document them. MySQL Workbench provides facilities for reverse engineering existing databases, and then creating an EER Diagram automatically. In this case, relationship lines between foreign keys in the table will automatically be drawn. This graphical representation makes the relationships between the tables much easier to understand. However, the older MyISAM storage engine does not include support for foreign keys. This means that MyISAM tables that are reverse engineered will not automatically have the relationship lines drawn between tables, making the database harder to understand. The plugin created in this tutorial gets around this problem by using the fact that a naming convention is often used for foreign keys: tablename_primarykeyname. Using this convention, foreign keys can automatically be created after a database is reverse engineered, which will result in relationship lines being drawn in the EER diagram.

Algorithm

The basic algorithm for this task would be as follows:

for each table in the schema
   for each column in the table
      look for another table whose name and primary key name match the current column name
      if such a table is found, add a foreign key referencing it

As iterating the complete table list to find a match can be slow for models with a large number of tables, it is necessary to optimize by pre-computing all possible foreign key names in a given schema.

import grt

def auto_create_fks(schema):
   fk_name_format = "%(table)s_%(pk)s"
   possible_fks = {}
   # create the list of possible foreign keys from the list of tables
   for table in schema.tables:
      if table.primaryKey:
         format_args = {'table':table.name, 'pk':table.primaryKey.name}
         fkname = fk_name_format % format_args
         possible_fks[fkname] = table

   # go through all tables in schema, this time to find columns that may be a fk
   for table in schema.tables:
      for column in table.columns:
         if possible_fks.has_key(column.name):
            ref_table = possible_fks[column.name]
            if ref_table.primaryKey.formattedType != column.type:
               continue
            fk = table.createForeignKey(column.name+"_fk")
            fk.referencedTable = ref_table
            fk.columns.append(column)
            fk.referencedColumn.append(ref_table.primaryKey)
            print "Created foreign key %s from %s.%s to %s.%s" \
            % (fk.name, table.name, column.name, ref_table.name, ref_table.primaryKey.name)

auto_create_fks(grt.root.wb.doc.physicalModels[0].catalog.schemata[0])

Creating a Plugin from a Script

To create a plugin from an arbitrary script, it is first necessary to make the file a module, and export the required function from it. It is then necessary to declare the module as a plugin, and specify the return type and input arguments.

from wb import *
import grt

ModuleInfo = DefineModule(name="AutoFK", author="John Doe", version="1.0")

@ModuleInfo.plugin("sample.createGuessedForeignKeys",
  caption="Create Foreign Keys from ColumnNames",
  input=[wbinputs.objectOfClass("db.mysql.schema")],
  groups=["Overview/Utility"])

@ModuleInfo.export(grt.INT, grt.classes.db_mysql_Schema)
def auto_create_fks(schema):
   ...

With the addition of the preceding code, the auto_create_fks() function is exported and will be added to the schema context menu in the model overview. When invoked, it receives the currently selected schema as its input.