To automatically create Foreign Key relationships is a typical use case for developers working with the MyISAM storage engine. This has been a popular topic on the Blog and forums so we’re revisiting it here – with a new and improved plugin written in Python.
While the InnoDB storage engine supports foreign keys, MyISAM
doesn’t, but developers often pick it for various reasons and
leave the application to handle relationships itself. MySQL
Workbench can be used to reverse engineer a database to a model
to better visualize or maintain it. But since MyISAM doesn’t have
foreign keys, databases that use it will be lacking a very
important part of it’s structure in the diagrams. You can link
the columns manually, using the relationship tool to link
columns, but you can also automate that. Databases are
usually created so that columns that represent relationships have
names that follow some kind of convention or pattern. For
example, a city table that is related to a
country table, may have a column called
country_id, used as the foreign key. The
pattern there would be something
like <table_name>_id. We
can find all such pairs of columns between potential foreign keys
and primary keys and create a foreign key for the tables.
There are two core routines needed by this implementation:
- the first is to find candidate columns. That is, columns that could be foreign keys that reference primary keys of other tables, according to some pattern.
- the second is the code to actually create the foreign keys from the possible columns found previously.
Look for Candidates
The following is the code to find candidate columns:
def get_fk_candidate_list(schema, fk_name_format, match_types=False):
candidate_list = []
possible_fks = {}
# create the list of possible foreign keys out of the list of tables
for table in schema.tables:
if table.primaryKey and len(table.primaryKey.columns) == 1: # composite FKs not supported
format_args = {'table':table.name, 'pk':table.primaryKey.columns[0].name}
fkname = fk_name_format % format_args
possible_fks[fkname] = table
# go through all tables in schema again, this time to find columns that seem to 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]
ref_column = ref_table.primaryKey.columns[0].referencedColumn
if ref_column == column:
continue
if match_types and ref_column.formattedType != column.formattedType:
continue
candidate_list.append((table, column, ref_table, ref_column))
return candidate_list
First, it will go through the list of all tables in the given
schema and create a dictionary of possible foreign key column
names, according to a format string provided by the user. The
format string has the %(table)s and
%(pk)s variables replaced with the table name and
primary key column name.
With the dictionary of possible foreign key names at hand, it then goes through all columns of all tables looking for any column name that is in the dictionary. If a match is found, a tuple of table, column, referenced table and referenced column names are added to a list of candidates. If the match_types flag is True, it will also check if the column types match and discard anything that doesn’t.
Create Foreign Keys
With the list of candidate columns, we can create a foreign key object from the table column to its referenced column.
for table, column, ref_table, ref_column in candidates:
fk = table.createForeignKey(ref_column.name+"_fk")
fk.referencedTable = ref_table
fk.columns.append(column)
fk.referencedColumns.append(ref_column)
According to the db_Table documentation, table objects have a
convenient createForeignKey method, which takes the
foreign key name as an argument, and returns a new db_ForeignKey
object added to the table. The foreign key is empty, so we set
its referencedTable field and add the
column/referenced column pair to the columns and
referencedColumns lists, respectively.
Adding a GUI
Now, for a fancier version, we will create a dialog that takes the naming pattern from the user, shows the list of candidates and creates the foreign keys when a button is clicked:
This GUI version uses the internal mforms toolkit. It provides a native interface in any of the supported platforms. See the documentation for it here.
Here is the part of the code that creates the UI. You can use it as a template for your own plugin dialogs. Go to the end of the post for the full plugin code.
import mforms
class RelationshipCreator(mforms.Form):
def __init__(self):
mforms.Form.__init__(self, None, mforms.FormNone)
self.set_title("Create Relationships for Tables")
box = mforms.newBox(False)
self.set_content(box)
box.set_padding(12)
box.set_spacing(12)
label = mforms.newLabel(
"""This will automatically create foreign keys for tables that match
a certain column naming pattern, allowing you to visualize relationships
between MyISAM tables.
To use, fill the Column Pattern field with the naming convention used for
columns that are meant to be used as foreign keys. The %(table)s and %(pk)s
variable names will be substituted with the referenced table values.""")
box.add(label, False, True)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
box.add(hbox, False, True)
label = mforms.newLabel("Column Pattern:")
hbox.add(label, False, True)
self.pattern = mforms.newTextEntry()
hbox.add(self.pattern, True, True)
self.matchType = mforms.newCheckBox()
self.matchType.set_text("Match column types")
hbox.add(self.matchType, False, True)
self.matchType.set_active(True)
search = mforms.newButton()
search.set_text("Preview Matches")
search.add_clicked_callback(self.findMatches)
hbox.add(search, False, True)
self.pattern.set_value("%(table)s_id")
self.candidateTree = mforms.newTreeView(mforms.TreeShowHeader)
self.candidateTree.add_column(mforms.StringColumnType, "From Table", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "To Table", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
self.candidateTree.end_columns()
box.add(self.candidateTree, True, True)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
self.matchCount = mforms.newLabel("")
hbox.add(self.matchCount, False, True)
self.cancelButton = mforms.newButton()
self.cancelButton.set_text("Cancel")
hbox.add_end(self.cancelButton, False, True)
self.okButton = mforms.newButton()
self.okButton.set_text("Create FKs")
hbox.add_end(self.okButton, False, True)
self.okButton.add_clicked_callback(self.createFKs)
box.add(hbox, False, True)
self.set_size(700, 600)
The dialog is implemented as a subclass of the mforms.Form class. That is the class for creating a window.
Line 5 calls the __init__ method of mforms.Form. Nothing fancy here,
as we just want a plain normal window. Line 7 which follows, sets
the title of the window.
Line 9, mforms.newBox(False) is used to create a box layouter that is “not horizontal” (ie,
vertical). This is used to layout controls that are added to it
from top to bottom, in a single column. Line 10 makes the window
display this box as its first control. Anything else you want
displayed in the window must be added to this box, either as a
direct child or nested in other layouters children of this
one.
Lines 11 and 12 set a padding around the box and a spacing
between each item inside it, so we have a not so cluttered
appearance in our dialog.
Line 14 creates a text label control with some rather lengthy
description text, which is then added to the box we created
above. The 2nd argument to the add method tells the
layouter to not expand the added control. That is, the
label will allocate as much space as it needs to show all its
contents. If it was set to True, it would instead use all the
space left in its container. The 3rd argument tells the layouter
to fill the space allocated for it with the control.
Since expand is False in this case, this won’t make much
difference, but if it was True, it would toggle whether the label
should have the same size as the space allocated for it or not.
Note the difference between allocated space and actually used
space.
Line 24 creates another box, this time a horizontal one, which is then added to the previously created vertical box. Anything added to this box will be laid out as a single row inside the first box. Anything added to the first box after this point, will be added below the row created by this box.
Lines 28 to 39 creates a label, a text field, a checkbox and a
button, which are all laid in a row, using the horizontal box
above. For the search button, we’re setting a
callback which will be called when the user clicks it. The
callback is just a method in the same class, called findMatches.
It doesn’t take any argument.
A tree with 6 columns is then created from lines
43 to 50. The tree (which is just a plain list of rows) is set up
by adding as many columns are desired, with their types,
captions, default width and a flag telling whether the column is
editable or not. After the columns are added, the
end_columns() method must be called.
Finally, another row is added, starting from line 53. This row
contains a Cancel and OK (Create FKs) buttons. Instead of
add(), the add_end()method from Box is
used, so that the buttons are laid out from right to left,
instead of starting from the left to the right.
At last, the default size of the window is set.
This image shows rectangles around each of the boxes used to lay
out the dialog.
The Whole Thing
To make this a plugin, there’s a few more bureaucratic lines of code that must be added. This code is described in our previous plugin tutorials and in the documentation.
The full plugin code contains the complete
implementation. To install it, save it as
relationship_create_grt.py and Install it from the
Scripting -> Install Plugin/Module… menu item. After
restarting Workbench, a new item called Create Relationships
from Columns will appear in the Plugins ->
Catalog menu. Note: the plugin requires MySQL Workbench
5.2.29