Appendix C Extending Workbench

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 information is represented using standard data types: integers, doubles, strings, dicts, lists, and objects.

The GRT can be accessed using the Python scripting language. Awareness is required of how the GRT data types map into Python. For example, the GRT integer, double, and string data types are seen as corresponding Python data types. Lists and dicts are kept in their internal representation, but can generally be treated as Python lists and dicts, and accessed in the usual way. Objects contain data fields and methods, but the GRT recognizes only objects from a pre-registered class hierarchy.

It is possible to fully examine the classes contained within the GRT using the Workbench Scripting Shell. 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, 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 using Python or C++. Be careful when modifying the GRT Tree as mistakes can lead to document corruption. 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 be written in C++ or Python, but the data types used for arguments and the return value must be GRT types.

GRT modules are similar to Python modules, but are 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 from 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 must carry out the following steps:

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, table, 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 must be located in a module and declared 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 object editors and wizards within MySQL Workbench. Although the wizard type of plugin must 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.

Imported plugin files (and their compiled counterparts) are stored here:

Declare a plugin using this syntax:

@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 OS X. This approach permits 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 because plugin developers can use MForms rather than writing 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 must import the mforms module.

MForms Containers

Given the problems of using an absolute coordinate 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 Python scripts. Through the use of the scripting shell, MySQL Workbench can support new behavior and data sources using code written in 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.

C.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 Control + F3 key combination on Windows and Linux, Command + F3 on 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 C.1 The Workbench Scripting Shell

C.5.2 The Shell Window

The Workbench Scripting Shell is primarily used for running Python scripts, or directly typing commands in Python. However, you can also use it to access the Workbench Scripting Shell Scripting Library functions and global functions and objects. To see the available commands, type “?”. You can also cut and paste text to and from the shell window.

The Snippets tab is a scratch pad for saving code snippets. This makes it easy to reuse and execute code in MySQL Workbench.

Figure C.2 The Workbench Scripting Shell: Snippets

Opened script file tabs are to the right of the Snippets tab. Script tabs are labeled with the script's filename, or Unnamed for snippets without a name. You can cut-and-paste to and from the tabs, or right-click on a snippet to open a context menu with options to Execute Snippet, Send to Script Editor, or Copy To Clipboard.

While individual commands can be entered into the shell, it is also possible to run a longer script, stored in an external file, using the main menu item Scripting, Run Workbench Script File. When scripts are run outside of the shell, to see the output use the main menu item View, Output.

It is also possible to run script files directly from the shell. For details on running script files, type ? run at the Workbench Scripting Shell prompt. The following message is displayed:

Help Topics
-----------
grt        General information about the Workbench runtime
scripting  Practical information when working on scripts and modules for Workbench
wbdata     Summary about Workbench model data organization
modules    Information about Workbench module usage
plugins    Information about writing Plugins and Modules for Workbench
Type '? [topic]' to get help on the topic.

Custom Python Modules
---------------------
   grt         Module to work with Workbench runtime (grt) objects
   grt.root    The root object in the internal Workbench object hierarchy
   grt.modules Location where Workbench modules are available
   grt.classes List of classes known to the GRT system
   mforms      A Module to access the cross-platform UI toolkit used in some Workbench features
   wb          Utility module for creating Workbench plugins

Type 'help(module/object/function)' to get information about a module, object or function.
Type 'dir(object)'                  to get a quick list of methods an object has.

For an introductory tutorial on the Python language,    visit http://docs.python.org/tutorial/
For general Python and library reference documentation, visit http://python.org/doc/

Within the Workbench Scripting Shell, there are five tabs on the top of the left side panel: Files, Globals, Classes, and Modules, and Notifications.

Note

An exception is thrown while attempting to use input() or read from stdin.

C.5.3 The Files, Globals, Classes, Modules, and Notifications Tabs

The Workbench Scripting Shell features the Files, Globals, Classes, Modules, and Notifications tabs, in addition to the main Shell tab.

The Files Tab

Lists folders and files for user-defined (custom) script files. The categories are User Scripts, User Modules, and User Libraries.

Figure C.3 The Workbench Scripting Shell tab: Files

By default, scripts are stored in the scripts/ folder of your MySQL Workbench configuration folder. These default locations are:

Table C.3 Default Scripts Location

Operating System	Default scripts/ path
Linux	~/.mysql/workbench/scripts
OS X	~/Library/Application\ Support/MySQL/Workbench/scripts/
Windows 7	C:\Users\[user]\AppData\Roaming\MySQL\Workbench\scripts\

The Globals Tab

At the top of the window is a list that is used to select the starting point, or root, of the GRT Globals tree displayed beneath it. By default, this starting point is the root of the tree, that is, '/'. You can expand or collapse the GRT Globals tree as desired. The GRT Globals tree is the structure in which MySQL Workbench stores document data. Clicking any item results in its name and value being displayed in the panel below the tree.

Figure C.4 The Workbench Scripting Shell tab: Globals

The Classes Tab

A class is a user-defined data type formed by combining primitive data types: integers, doubles, strings, dicts, lists, and objects. This tab shows the definitions of the classes used by the objects in the Modules tab. Clicking a class causes a brief description of the class to be displayed in a panel below the classes explorer.

Figure C.5 The Workbench Scripting Shell tab: Classes

When the Classes tab is selected, the list displays the following items:

• Group by Name: Group by the object name

• Group by Hierarchy: Group by inheritance

• Group by Package: Group by functionality

The default view for this tab is Group By Name. This view shows all the different objects arranged alphabetically. Click the + icon or double-click a package to show the properties of the struct.

If you switch to the hierarchical view, you will see GrtObject: the parent object from which all other objects are derived.

The Modules Tab

The Modules tab enables you to browse the MySQL Workbench installed modules and their functions. Clicking a module within the explorer causes its details to be displayed in a panel below the explorer. This facility is useful for exploring the available modules, and their supported functions. It is also a way to check whether custom modules have been correctly installed.

Figure C.6 The Workbench Scripting Shell tab: Modules

The Notifications Tab

The set of notification classes used by MySQL Workbench modules. Click a notification class for a description of its use.

Figure C.7 The Workbench Scripting Shell tab: Notifications

This tutorial shows 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 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.