MySQL Workbench now using Casmine for unit and integration testing

Starting with version 8.0.18 the MySQL Workbench source package finally ships also all our GPL unit and integration tests, which we are using internally to control code quality. For that we had first to replace our old, outdated testing framework by something new and more appropriate. We evaluated quite a few C++ testing frameworks but found them either not to be sufficient or difficult to use. Instead we had something in mind that comes close to the Jasmine framework which is widely used among JS developers. The way it hides all the boring test management details and the clear structure it uses, was quite an inspiration for us and we decided to develop our own testing framework modeled after that.

Continue reading “MySQL Workbench now using Casmine for unit and integration testing”

Universal Code Completion using ANTLR

While reworking our initial code completion implementation in MySQL Workbench I developed an approach that can potentially be applied for many different situations/languages where you need code completion. The current implementation is made for the needs of MySQL Workbench, but with some small refactorings you can move out the MySQL specific parts and have a clean core implementation that you can easily customize to your needs.

Since this implementation is not only bound to MySQL Workbench I posted the full description on my private blog.

Parsing in MySQL Workbench: the ANTLR age

Some years ago I posted an article about the code size in the MySQL Workbench project and talked a bit about the different subprojects and modules. At that time the project consisted of ~400K LOC (including third-party code) and already then the parser was the second biggest part with nearly a forth of the size of the entire project. This parser project back then used the yacc grammar from the MySQL server codebase and was our base for all parsing tasks in the product. Well, things have changed a lot since these days and this blog post discusses the current parsing infrastructure in MySQL Workbench.
Continue reading “Parsing in MySQL Workbench: the ANTLR age”

For the brave: compiling Workbench 6.3 using Visual Studio 2013 on Windows

Compiling MySQL Workbench yourself is quite a common task for Linux users, even though the application is available precompiled for certain platforms ready from our download page and available in our yum and apt repositories. In this blog post we show you how to compile it on Windows.
Continue reading “For the brave: compiling Workbench 6.3 using Visual Studio 2013 on Windows”

MySQL Workbench 6.0: Help is on the way…

Do you know this scenario: you are writing down  a stored procedure but you can’t for the life of you remember the exact syntax of that CASE statement? Has it to end with CASE or not? Can I use more than one WHEN part and how should that be written? Usually you end up opening a web page and read through the excellent MySQL online docs. However, this might cost too much time if you quickly need different statements and other detail info. Here’s where MySQL Workbench’s context help jumps in.

Continue reading “MySQL Workbench 6.0: Help is on the way…”

ODBC and Unicode in Linux

Recently I’ve had to spend countless hours trying to debug a problem mixing various ODBC components. Since I couldn’t find any answers online, at least not in a way that was easy for a ODBC beginner like me to understand, I’ve decided to write it down here so it can maybe save some frustration to other people.

ODBC in Linux

ODBC Managers

An ODBC manager is a library that’s responsible for interfacing between a ODBC driver and the application. The app tells the ODBC manager to open a connection and run queries; the manager loads the correct driver and forwards the ODBC API calls to the driver and the driver talks to the server, which does whatever the app told it to.

There are two ODBC manager libraries in Linux. unixODBC and iODBC. Both seem to work well, but unixODBC is somehow more popular, while iODBC is shipped as part of Mac OS X. We picked iODBC.

ODBC for Python

If you want to use ODBC from Python, you’ll need a module that does the role of the ODBC manager. Most will be just wrappers over a ODBC manager written in C, meaning either unixODBC or iODBC. We picked pyodbc.

Compiling pyodbc against iODBC is not very straightforward, because it just assumes everyone uses unixODBC in Linux (although it works just fine with iODBC, since that’s what is assumed when it’s compiled for the Mac). I won’t go into details, but the 2 things that need to be done to compile pyodbc with iODBC are:

  • edit so that the settings[‘libraries’].append(‘odbc’) line for linux is changed to settings[‘libraries’].append(‘iodbc’), making pyodbc link to iodbc
  • set the CFLAGS and LDFLAGS environment variables to your iODBC install paths and run Example:

CFLAGS=-I/usr/include/libiodbc LDFLAGS=-L/usr/lib python install

Connecting to MS SQL Server (FreeTDS)

FreeTDS is a free driver/library that talks the Sybase and the MS SQL Server protocols. Apparently you can use it directly or you can use it as a ODBC driver.

As with pyodbc, it is probably a good idea to compile it against the driver manager you’re using.

The big wchar_t mess

In ODBC, there are 2 string types: SQL_CHAR and SQL_WCHAR. The 1st is just a plain char* array representing a plain string string. The 2nd is a Unicode string.

In Windows, MS chose to hardcode the type of SQL_WCHAR to unsigned short (2 bytes per char). The driver assumes 2 bytes per char, the ODBC manager assumes 2 bytes per char and the app assumes 2 bytes per char, so everybody understands each other because they’re talking in 2 bytes per char.

In Linux, the default is to use wchar_t. wchar_t is 4 bytes.

By default, unixODBC uses unsigned short, like Windows. But you can also compile it with SQL_WCHART_CONVERT, which will define SQL_WCHAR as wchar_t instead.

iODBC uses wchar_t always.

You can see the mess brewing up, but there’s more.

FreeTDS uses 2 bytes per char, like Windows. No matter what the driver manager is used. So by now, you can see that every component of the ODBC stack can use either 2 bytes per char or 4 bytes per char depending on how you setup everything. It may not seem like a big deal, but if you’re a ODBC newbie and have no idea about how ODBC handles Unicode data and just assumed everything would just play together nicely and in the same way (because ODBC is supposed to be a standard after all), then you’re set up for the same countless hours of hairpulling and sleepless nights as me; trying to figure out why is the Unicode data from the server presented by Python as a series of characters with 0s sprinkled between each char or just fails with some MemoryError exception.

If you use FreeTDS with unixODBC the pair will work OK, because both use 2 bytes per char by default. But if you need to use pyodbc, then it won’t, because pyodbc assumes the ODBC manager talks wchar_t. That will cause Unicode resultset strings from the server to be displayed garbled, as if it had forgotten to decode from UCS-2. Or just crash, depending whether you’re running 32 or 64bits.

If you use FreeTDS with iODBC it will not work, because FreeTDS sends data as unsigned short and iODBC sends the data to Python as wchar_t. That will cause some error in Python and you’ll get some MemoryError exception, which says nothing at all about what is the prolem.

So by default, there’s no way that things will work out OK, except in some some few lucky combinations.

BUT it seems the FreeTDS developers realized that just assuming 2 bytes per char for SQL_WCHAR is not something that’s good enough for everyone and added “experimental” support for properly using wchar_t. Unfortunately that is not the default and there’s no auto-detection either. So if you’re having to use FreeTDS with iODBC (or unixODBC configured for SQL_WCHART_CONVERT), you’ll need to use the magic –enable-odbc-wide configure option when compiling it. Also, that only actually works in version 0.92, 0.91 apparently has the option too, but it doesn’t work correctly. Then everything will finally talk in wchar_t from end to end and you get to sleep without worrying about the deadline in the next day.



MySQL Workbench: Script for adding columns to all tables in a model

Here’s a quick Python script for adding columns for all tables in your EER model. This will create a create_time and update_time columns in all tables, but you can modify it to your needs. To execute:

  1. go to Scripting -> Scripting Shell…
  2. click the New Script toolbar icon at the top left corner
  3. select Python Script and specify some name for the script file
  4. click Create
  5. copy/paste the script there
  6. click the Execute toolbar button

Make sure to backup your model before running this!

The code

# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
# iterate through all tables
for table in schema.tables:
    # create a new column object and set its name
    column = grt.classes.db_mysql_Column() = "create_time"
    # add it to the table
    # set the datatype of the column
    column.setParseType("TIMESTAMP", None)
    column.defaultValue = "CURRENT_TIMESTAMP"

    # same thing for the update_time column
    column = grt.classes.db_mysql_Column() = "update_time"
    column.setParseType("TIMESTAMP", None)

MySQL Workbench: PHP development helper plugins

In the new MySQL Workbench 5.2.35, a plugin that will be of interest to PHP developers, both experienced and newbies, has been added.
The plugin contains a couple of functions that allows you to create PHP code straight out of your current work in the Workbench SQL Editor, ready to be pasted to your PHP program.

Screenshot of SQL Editor with PHP Plugins

Copy as PHP Code (Connect to Server) This first plugin will take the parameters from your currently open connection to MySQL and create PHP code to connect to it.


$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());


Not a big deal, but saves some typing for getting something going quickly.

Copy as PHP Code (Iterate SELECT Results) This one will get your query and generate code to execute it and then iterates through the results. It will also parse the SQL and substitute any SQL @variables you use in it with PHP variables that will be bound to the statement before execution. Resultset rows will be bound to PHP variables with the same name as the field (or alias, if your query specified one).

So for the following query:

set @before_date = '1990-01-01';
set @after_date = '1980-01-01';

    emp_no, first_name, last_name, hire_date
    `hire_date` < @before_date AND `hire_date` > @after_date;

you would get this back:

$query = "SELECT      emp_no, first_name, last_name, hire_date FROM     `employees`.`employees` WHERE     `hire_date` < ? AND `hire_date` > ?";
$before_date = '';
$after_date = '';

$stmt->bind_param('ss', $before_date, $after_date); //FIXME: param types: s- string, i- integer, d- double, b- blob

if ($stmt = $con->prepare($query)) {
    $stmt->bind_result($emp_no, $first_name, $last_name, $hire_date);
    while ($stmt->fetch()) {
        //printf("%s, %s, %s, %s\n", $emp_no, $first_name, $last_name, $hire_date);

This should be enough for letting you quickly create a PHP program for doing something with the results of a parameterized query, straight out from your normal SQL development workflow and as a bonus, and be safe from injection bugs as a bonus.

Adding your own plugins

The plugins are simple, but more along these lines will be added in the future. And, more importantly, you can modify it to support your own needs. Here’s how:

First of all, find where’s the plugin file. The filename is and you should be able to find it searching in the WB installation folder. To have your own version, rename it to something else like, change a few identifiers so it won’t collide with the original built-in plugin as described below and use Scripting -> Install Plugin/Module… to install it to the correct place.

You can use the plugins there as a starting point for your own or modify them to match your coding style, choice of PHP driver etc

The important things you need to change in the plugin copy before installing are:

  1. the plugin name from CodeUtils to something else.
  2. ModuleInfo = DefineModule(name= "CodeUtils", author= "Oracle Corp.", version="1.0")
  3. the individual plugin names and identifiers (or just comment them out) and maybe the function names.
  4. @ModuleInfo.plugin("wb.sqlide.copyAsPHPConnect", caption= "Copy as PHP Code (Connect to Server)", input= [wbinputs.currentSQLEditor()], pluginMenu= "SQL/Utilities")
    @ModuleInfo.export(grt.INT, grt.classes.db_query_Editor)
    def copyAsPHPConnect(editor):

    The first parameter in @ModuleInfo.plugin is the plugin name and the 2nd is the caption. You can leave everything else, especially the metadata about the input parameters etc.

Here’s a sample plugin that you can use as a template. It’s stripped to the basics for easier understanding:

@ModuleInfo.plugin("wb.sqlide.copyAsPHPQuery", caption= "Copy as PHP Code (Run Query)", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def copyAsPHPQuery(qbuffer):
    sql= qbuffer.selectedText or qbuffer.script

    text = 'print "the query is %s\n"' % sql.replace('"', r'\"')


    mforms.App.get().set_status_text("Copied PHP code to clipboard")
    return 0

MySQL Workbench: Introducing Utilities

MySQL has the well earned reputation for ease-of-use and “15-minutes-to-success”, since we continually focus making the server easy to use. MySQL Workbench provides the visual tools for database design, development, and administration. However, many DBAs prefer using the command-line, and there are many tasks that require the creation scripts for doing the job.

To make it easier to work with the server, the latest release of the MySQL Workbench—version 5.2.31—contain a set of Python scripts intended to make the life easier for DBAs by providing easy-to-use utilities for common tasks, which were introduced in the blog MySQL Workbench: Utilities. The set currently consists of just a few utilities, but will expand over time.

The utilities available in the Workbench are:

<a href="">mysqldbcopy</a>
Copy databases between servers.
<a href="">mysqldbexport</a>
Export databases to a file in different formats, including: SQL, comma-separated files, and tab-separated files (and some more).
<a href="">mysqldbimport</a>
Import object definitions and/or data from a file—in different formats, similar to <a href="">mysqldbexport</a>—into a database.
<a href="">mysqlindexcheck</a>
Check for redundant or duplicate indexes on a list of tables or databases. It can also generate DROP statements to
remove redundant indexes.
<a href="">mysqlmetagrep</a>

Search MySQL servers for objects containing fields matching a pattern.

<a href="">mysqlprocgrep</a>

Search MySQL servers for processes matching a pattern and perform actions.

<a href="">mysqlreplicate</a>

Setup replication between two servers.

<a href="">mysqlserverclone</a>

Start a new instance of a server to experiment with. This is used to test the utilities, but can be used whenever you need to set up a scratch server to test something.
<a href="">mysqluserclone</a>

Copy a MySQL user to one or more new users on another server

Finding stuff on servers with ease

In order to search for various things on servers—for example, searching objects and processes—there are two commands that can be used: <a href="">mysqlprocgrep</a> and <a href="">mysqlmetagrep</a>. The name “grep” is borrowed from Unix where the grep(1) commands that can be used to search inside files, but in this case you can search among processes and inside metadata on a server.

Example: searching processes using <a href="">mysqlprocgrep</a>

With <a href="">mysqlprocgrep</a> you can find all processes that match certain conditions and either just print them out, or kill either the connection or the query. So, for instance, to see all connections that have been idle for more than 2 minutes, we can use:

$ mysqlprocgrep --server=root:password@localhost --match-command=sleep --age=+2m
| Connection             | Id  | User  | Host       | Db    | Command  | Time  | State  | Info  |
| root:*@localhost:3306  | 39  | mats  | localhost  | None  | Sleep    | 248   |        | None  |

In the example above, I would especially like you to note how the server connection information is provided. To provide information on what server to connect to and how, we have introduced a new syntax to represent the connection information which we call a connection specification:


In the connection specification, the password, port, and socket are optional and can be excluded, which will make the default being used instead.

This is used instead of having separate switches (for example, –host and –user) that the server client programs are using. In addition to being easier to write, it also means that it is possible to provide multiple servers to command (where it makes sense, of course). You might be interested in knowing that both mysqlprocgrep and mysqlmetagrep accepts multiple servers.

If you now want to kill these idle connections, you can just add the –kill-connection option to the command, and the connection of all matching processes will be killed.

$ mysqlprocgrep --server=root:password@localhost \
> --match-command=sleep --age=+2m

In a similar way, if you have a long-running update from a special user (say, www-data), you can kill all the queries in one shot using the command:

$ mysqlprocgrep --server=root:password@localhost \
> --match-user=www-data --match-command=query    \
> --match-state=updating --age=+1m               \
> --kill-query

Example: finding objects using <a href="">mysqlmetagrep</a>

At times, you also find some odd reference to a column or index, you’re not quite sure, or you want to find out what objects are using a column named ‘db’. In those cases, <a href="">mysqlmetagrep</a> comes in handy.

The utility is used to find all objects that contain a field that matches the provided pattern. The pattern can be given either as a SQL simple pattern as defined by the SQL standard (this is what you usually use with LIKE), or using POSIX regular expressions (which is what you usually use with REGEXP in SQL). The default is to use the SQL simple pattern. So, to search for any objects having a column ‘host’, we can
use the command:

$ mysqlmetagrep --server=root:password@localhost --pattern=host --search=column
| Connection             | Object Type  | Object Name   | Database            | Field Type  | Matches  |
| root:*@localhost:3306  | TABLE        | PROCESSLIST   | information_schema  | COLUMN      | HOST     |
| root:*@localhost:3306  | TABLE        | columns_priv  | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | db            | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | host          | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | host          | mysql               | TABLE       | host     |
| root:*@localhost:3306  | TABLE        | procs_priv    | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | servers       | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | tables_priv   | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | user          | mysql               | COLUMN      | Host     |

Since the SQL simple patterns are default, this require an exact match and you will only find objects with columns exactly named ‘host’. To find all column containing the word ‘host’, you have to add wildcards to the pattern:

$ mysqlmetagrep --server=root:password@localhost --pattern=%host% --search=column
| Connection             | Object Type  | Object Name   | Database            | Field Type  | Matches    |
| root:*@localhost:3306  | TABLE        | PROCESSLIST   | information_schema  | COLUMN      | HOST       |
| root:*@localhost:3306  | TABLE        | columns_priv  | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | db            | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | general_log   | mysql               | COLUMN      | user_host  |
| root:*@localhost:3306  | TABLE        | host          | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | procs_priv    | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | servers       | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | slow_log      | mysql               | COLUMN      | user_host  |
| root:*@localhost:3306  | TABLE        | tables_priv   | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | user          | mysql               | COLUMN      | Host       |

Creating and configuring servers with ease

There are three utilites that I will just mention briefly, because they are not very complicated to use: mysqlserverclone, mysqlreplicate, and mysqluserclone.

To create a scratch servers using <a href="">mysqlserverclone</a> for testing something, it is as easy as:

$ mysqlserverclone --server=root:password@localhost \
> --new-data=/tmp/data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!

It will create a new server from the original, copy the existing databases, and and start the server. You can supply a new port using the –new-port, but if you do not do that, it will pick the default port 3307.

If you want to set up replication quickly and easily, you can do that using mysqlreplicate:

$ mysqlreplicate --master=root:password@localhost \
> --slave=root@localhost:3307 --rpl-user=repl_user:xyzzy
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

When setting up replication, the <a href="">mysqlreplicate</a> does some basic checking to ensure that replication will work. It checks that there is a server ID assigned and also checks that binary logging is enabled. If something is not right, it will abort the setup and report error.

The last utility that is useful in setting servers up is <a href="">mysqluserclone</a>. The utility is used to create new users based on an existing one. So, to create a new user ‘chuck’ with password ‘xyzzy’ on localhost from an existing user ‘mats@localhost’,
you can use the command:

$ mysqluserclone --source=root:password@localhost \
> --destination=root:password@localhost \
> mats@localhost chuck:xyzzy@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning mats@localhost to user chuck:xyzzy@localhost
# ...done.

Moving stuff around with ease

There are three utilities that can be used to move data around: <a href="">mysqldbcopy</a>, <a href="">mysqldbexport</a>, and <a href="">mysqldbimport</a>.

With <a href="">mysqldbcopy</a>, you can copy a database from one server to another, or several databases from one server to another. When copying a database, it not only copies the table definitions, but all associated objects such as triggers, events, routines, and also database-level grants.

With <a href="">mysqldbexport</a> you can export one or more databases into various formats, including (but not limited to) pure SQL, comma- and tab-separated values, and also a nice human-readable table.

With <a href="">mysqldbimport</a> you can import data in files into a database. In contast to using LOAD DATA INFILE, this will generate the INSERT statements to inject the data into the server.

The road ahead

The current set of utilities are just a small start, and we expect more utilities to be added over time and also improve on the existing utilities, so if you want to help, you can do that by:

We are very interested in feedback of any form—bug reports, suggestions for new utilities, suggestions for improving the existing utilities—so if you are a MySQL Expert DBA:

  • Let us know how to improve our utilities
  • Send us suggestions or ideas for new utilities
  • Write your own utilities
  • Contribute patches and/or new utilities

and if you are a Python programmer and/or developer:

  • Let us know how to be more Pythonic
  • Suggest improvements of the code
  • Build and/or contribute additional utilities on top of ones we provide

MySQL Workbench Plugin: Auto-Create Foreign Keys

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><strong>_id. </strong>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', '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(
                ref_table = possible_fks[]
                ref_column = ref_table.primaryKey.columns[0].referencedColumn
                if ref_column == column:
                if match_types and ref_column.formattedType != column.formattedType:

                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("_fk")
    fk.referencedTable = ref_table

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)

    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)
    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)
    search = mforms.newButton()
    search.set_text("Preview Matches")
    hbox.add(search, False, True)


    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)
    box.add(self.candidateTree, True, True)

    hbox = mforms.newBox(True)
    self.matchCount = mforms.newLabel("")
    hbox.add(self.matchCount, False, True)
    self.cancelButton = mforms.newButton()
    hbox.add_end(self.cancelButton, False, True)
    self.okButton = mforms.newButton()
    self.okButton.set_text("Create FKs")
    hbox.add_end(self.okButton, False, True)
    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 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


UPDATE: Fixed link to full plugin code