The GRT, or Generic RunTime, is the internal system used by Workbench to hold model document data. It is also the mechanism by which Workbench can interact with Modules and Plugins. Workbench model data, such as diagrams, schemata and tables, is stored in a hierarchy of objects that can be accessed by any plugin. The data is represented using standard data types: integers, doubles, strings, dicts, lists and objects.

The GRT can be accessed using external scripting languages such as Lua and Python. Awareness is required of how the GRT data types map into the scripting language. In Python, for example, the GRT data types integers, doubles and strings are seen as corresponding Python data types. Lists and dicts are kept in their internal representation, but can however generally be treated as Python lists and dicts, and accessed in the usual way. Objects contain data fields and methods, but the GRT only recognizes objects from a pre-registered class hierarchy.

It is possible to fully examine the classes contained within the GRT using the Workbench Scripting Shell. Note that dots in class names are changed to underscores in their Python counterparts. For example, db.mysql.Table becomes db_mysql_Table in Python.

The Application Objects Tree (GRT Tree)

As mentioned previously the Workbench document data is stored in an object hierarchy. This hierarchy is known as the GRT Tree. The GRT Tree can be accessed and modified from supported external scripting languages such as Python. Care should be taken when modifiying the GRT Tree, in case a mistake leads to corruption of the document. Backups should be made before manipulating the tree. Read-only access to the tree is the safest approach, and is sufficient in most cases.

The main nodes in the Application Object Tree

In the GRT Modules are libraries containing a list of functions that are exported for use by code in other modules, scripts or Workbench itself. Modules can currently be written in C++, Lua or Python, but the data types used for arguments and the return value must be GRT types.

GRT modules are similar to Python modules. They are however imported from the built-in grt module, instead of directly from an external file. The list of modules loaded into the grt module is obtained grt.modules. Modules can be imported in Python using statements such as from grt.modules import WbModel.

To export functions as a module from Python code you need to carry out the following:

The following code snippet illustrates declaring a module that exports a single function:

from wb import *
import grt

ModuleInfo = DefineModule(name='MyModule', author="your name", version='1.0')

@ModuleInfo.export(grt.DOUBLE, grt.STRING, (grt.LIST, grt.DOUBLE))
def printListSum(message, doubleList):
   sum = 0
   for d in doubleList:
      sum = sum + d
   print message, sum
   return sum   

Plugins are special Modules that are exposed to the user through the Workbench GUI. This is typically done using the main menu, or the context-sensitive menu. Much of the MySQL Workbench functionality is implemented using plugins, for example, tabel, view, and routine editors are native C++ plugins, as are the forward and reverse engineering wizards. The Administrator facility in MySQL Workbench is implemented entirely as a plugin in Python.

A plugin can be a simple function that performs some action on an input, and ends without further interaction with the user. Examples of this include auto-arranging a diagram, or making batch changes to objects. To create a simple plugin, the function needs to be located in a module and then declare it as a plugin using the plugin decorator of the ModuleInfo object.

Plugins can have an indefinite runtime, such as when they are driven by the user through a graphical user interface. This is the case for the various object editors and wizards within MySQL Workbench. Although this latter type of plugin will need to be declared in the usual way, only the entry point of the plugin will need to be executed in the plugin function, as most of the additional functionality will be invoked as a result of the user interacting with the GUI.

The syntax for declaring a plugin is as follows:

@ModuleInfo.plugin(plugin_name, caption, [input], [groups], [pluginMenu])

These parameters are defined as follows:

MySQL Workbench is implemented with a C++ core back-end, and a native front-end for each supported platform. Currently the front-end is implemented with Windows Forms on Microsoft Windows, GTK+ on Linux, and Cocoa on Mac OS X. This approach allows the application to have a native look and feel, while reducing the amount of work required to maintain the project. However, the GUI functionality required by MySQL Workbench can be met by a subset of graphical operations. These are implemented in a cross-platform GUI library, MForms. This further reduces the development effort, as plugin developers can use MForms, rather than having to write front-end specific code for each supported platform. This also helps consistency of operation across all platforms. MForms is coded in C++, but provides a Python interface. To use it the Python code needs to import the mforms module.

MForms Containers

Given problems of using an absolute co-ordinate system across different platforms, MForms employs containers that perform automatic layout. The basic containers that MForms provides include:

The Workbench Scripting Shell provides a means for entering and executing scripts. Through the use of the scripting shell, MySQL Workbench can support new behavior and data sources using code written in Lua and Python. The shell can also be used to explore the current Workbench GRT (Generic Runtime) facilities.

The scripting shell is not only useful for expanding MySQL Workbench. You can use a script file from the scripting shell command line to perform repetitive tasks programmatically.

The default development language is Lua, a lightweight scripting language expressly designed for extending applications. For more information about this language see lua.org.

The Python language is also supported, further details of this language can be found from the official Python site.

The programming language to be used in Workbench Scripting Shell can be selected from the General tab of the Workbench Preferences dialog. The Workbench Preferences dialog can be displayed using the main menu item Edit, Preferences.

9.5.1. Exploring the Workbench Scripting Shell

To open the Workbench Scripting Shell, select Scripting, Scripting Shell from the main menu. You can also open the Workbench Scripting Shell using the Ctrl+F3 key combination on Windows and Linux, cmd F3 on Mac OS X, or by clicking the shell button above the EER diagram navigator. The Workbench Scripting Shell will then open in a new dialog.

The following screenshot shows the Workbench Scripting Shell dialog:

Figure 9.1. The Workbench Scripting Shell

  Shell Command - shell.run
  -------------------------
  Load and execute a lua script file.

  run filename

  Parameters:
  filename       File that should be loaded and executed.

  Examples:
  run scripts/test.lua
  Runs the script scripts/test.lua.

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.