The purpose of this tutorial is to show you how to extend MySQL Workbench by creating a plugin.
The sample plugin
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, one of the most popular storage
engines for MySQL, MyISAM, 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 that will be
created in this tutorial gets around this problem by using the fact
that a naming convention is very 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 above code, the
auto_create_fks()
function is exported and will
be added to the schema context menu in the model overview. When
invoked it will receive the currently selected schema as its input.