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):
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.
PlanetMySQL Voting:
Vote UP /
Vote DOWN
A nice thing about this plugin, for this context here, is the function mysqlnd_qc_get_query_trace_log() which provides information about each query being executed. Not only the query string but also some timing (execution time, result storage time) and a stack trace so you can see where in the code a query was executed. I've added this functionality to the JSMysqlndBundle as you can see in the screenshot. I won't show a screenshot about what happens if you click the stacktrace link as this currently breaks the layout a bit, but maybe somebody wants to make this nicer? - Or maybe even feels motivated to make it even better using mysqlnd_uh (which, as of today, has docs, thanks to Ulf) Feel free to contact me to talk about ideas! 
