Being more productive with DBDesigner 4

I'm using DBDesigner 4 quite a lot. I think the successor, MySQL Workbench, is very promising, but it does not meet my requirements (yet).

Having DBDesigner Generate SQL

Those that've worked with DBDesigner have probably noticed the particular strategy it uses to generate SQL code from the model. First of all, the user needs to specify what kind of SQL script is to be generated: a DROP, CREATE or optimization script. Then, DBDesigner does it's thing. In case of a DROP or CREATE script, exactly one statement is generated for each table in the diagram (or in the current selection if you choose to).

In case of the CREATE script, each CREATE TABLE statement contains not only the column definitions, but also the PRIMARY KEY, UNIQUE and FOREIGN KEY constraint definitions corresponding to that table.

DBDesigner tries to put the statements into a particular sequence in order to end up with a script that can be run immediately without generating errors due unresolved dependencies. For example, if a particular table T1 has a foreign key referencing another table T2, DBDesigner will place the CREATE TABLE statement for T1 after the CREATE TABLE statement for T2. For the DROP script, the sequence is reversed for the same reasons.

DB Designer Problems

Usually this all works out fine. However, occasionally this approach leads to problems. Consider this example:

Here, we have to tables that are referencing each other. It's not that common, but I think the example I made up here is not that artificial. My real world case is a little bit more complicated, but the relationships are equivalent.

Well, it does not take long to figure out that a structure like this can never be realized using only CREATE TABLE statements. At least, two CREATE TABLE statements and one ALTER TABLE ADD CONSTRAINT statement are needed in order to create the tables before creating the dependency (either one of the foreign key relationships).

DBDesigner seems to take a rather principal point of view, because it decides not to generate any SQL at all. This is BTW rather nasty, because if you were so unlucky to specify the hitherto working script file as target for the SQL export action, you will discover that your script now amounts to 0 bytes...ouch!

There is another reason why DB Designer is not so good anymore for generating code. It has nothing to do with DB Designer. Rather, it has to do with mysql 5.1.7.

MySQL 5.1.7 does not accept the previously deprecated TYPE= option in CREATE TABLE statements. (You must use ENGINE= instead nowadays.)

Now, don't get me wrong: I'm not complaining - I think DBDesigner is a mighty cool tool, and I like it very much. It's just that these little quirks do take some time to adjust to, and occasionally they cost time too. And of course, DB Designer never could've foreseen that the TYPE= option would be disallowed in the future.

How to cope

All this just means that we need to think of a trick to keep on using the DB Designer GUI while generating the SQL in another way. Well, this is actually not too hard. DB Designer saves it's model in an XML format. XML is easily parsed and translated using XSLT.

So, that's what I did. I'm will discus it here briefly. If someone needs it, feel free to use it. Of couse, you will need a utility to apply the xslt stylesheet to the Db Designer xml output, but there are literally hundreds of tools that do that for you - most of them are freely obtainable. (I use a little msxml based javasript utility I - for no particular reason - hacked up myself)

The XSLT stylesheet

My stylesheet is an ordinary xslt stylesheet:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet 
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
    <xsl:output
        method="text"
    />

    <!-- 

       Some variables/parameters go here

    -->
    <xsl:variable name="tables" select="/DBMODEL/METADATA/TABLES/TABLE"/>
    <xsl:variable name="foreign-keys" select="/DBMODEL/METADATA/RELATIONS/RELATION"/>

    <xsl:param name="PARAM_SCRIPT_TYPE" select="'CREATE'"/>

    <!-- 

       Some templates go here

    -->
    ..
</xsl:stylesheet>

The entry point for the transformation is formed by a single matching template. The template contents are controlled by a global parameter value (which is set from the environment when invoking the XSLT processor):

    <xsl:param name="PARAM_SCRIPT_TYPE" select="'CREATE'"/>

    <xsl:template match="/">
        <xsl:choose>
            <xsl:when test="$PARAM_SCRIPT_TYPE='DROP'">
                <xsl:call-template name="drop-foreign-keys"/>
                <xsl:call-template name="drop-tables"/>
            </xsl:when>
            <xsl:when test="$PARAM_SCRIPT_TYPE='CREATE'">
                <xsl:call-template name="create-tables"/>
                <xsl:call-template name="create-foreign-keys"/>
            </xsl:when>
        </xsl:choose>
    </xsl:template>

What happens is that the xslt processor will start off with the node that represents the entire document - I mean the document that contains our DB Designer model. Looking in the xslt stylesheet, the xslt processor will discover that this template matches the document node: the match attribute on the xsl:template element has the XPath expression / as value, which is a shorthand nodetest for the entire document.
So, the contents of our template are processed. The xsl:choose element selects one of the options according to the value of the PARAM_SCRIPT_TYPE parameter.
If the value equals DROP, the named templates drop-foreign-keys and drop-tables are executed. If it equals CREATE, then create-tables and create-foreign-keys are executed.
Well, you can imagine what these do, can't you? That's right, these generate the SQL statments to drop foreign keys, drop tables, create tables and create foreign keys. Of course, this sequence of executing the DDL statements can never offend any dependencies: dropping a table can never fail because there are no more foreign keys at that point that might prevent it, and creating a foreign key kan never fail because all the tables have already been created.

Well, the rest is just work: putting together little XPath queries and have the data that they retrieve drive static template text. For convenience, I declared two global variables. one nodeset containing all tables in the model and one containing all foreign keys:

    <xsl:variable name="tables" select="/DBMODEL/METADATA/TABLES/TABLE"/>
    <xsl:variable name="foreign-keys" select="/DBMODEL/METADATA/RELATIONS/RELATION"/>

These are conveniently used by the several templates. For example, look at the implementation of the drop-foreign-keys named template:

    <xsl:template name="drop-foreign-keys">
        <xsl:for-each select="$foreign-keys">
            <xsl:call-template name="drop-foreign-key">
                <xsl:with-param name="foreign-key" select="."/>
            </xsl:call-template>
        </xsl:for-each>
    </xsl:template>

    <xsl:template name="drop-foreign-key">
        <xsl:param name="foreign-key"/>
        <xsl:variable name="DstTableID" select="$foreign-key/@DestTable"/>
        <xsl:variable name="DstTable" select="$tables[@ID=$DstTableID]"/>
        ALTER TABLE <xsl:value-of select="$DstTable/@Tablename"/>
        DROP FOREIGN KEY <xsl:value-of select="$foreign-key/@RelationName"/>
        ;
    </xsl:template>

The drop-foreign-keys template itself just loops through each of the nodes from the nodeset stored in the global foreign-keys variable, passing the current node through to the drop-foreign-key template.
This does the actual work of generating the ALTER TABLE statement.

Getting to know where all the info is stored in the DBDesigner model is just a matter of common sense, and some trial and error. Anyway, it does not take much effort. The entire stylesheet cost me about 2 hours to write, and that includes getting to know the source format (of course ignoring all the stuff in there I don't need at this point).

If anyone's interested, download the stylesheet at: http://www.xcdsql.org/MySQL/DBDesigner4/dbdesigner.xslt.
The utility I use to transform xml with xslt is available for dowload here (MS Windows only Im afraid): http://www.xcdsql.org/Misc/xslt/xslt.zip.

License info is not included (i think) but you can use all that stuff in any way you like. Always nice to give me credit should you like it, or should you want to pass it on to somebody else.