Thursday, April 06, 2006

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.

7 comments:

Anonymous said...

Hi. What appens if you turn off the "Order Tables by Foreign Keys" option? In that case the algorythm to sort the tables should not be executed and you shouldn't get that error message but a correctly generated SQL file.

When I wrote the DBD4 I was not aware of the SET command to turn the FKs off (or it did not exist at that point in time). It is now used by all MySQL tools and we will also use it in the Workbench.


Add this to the beginning of the generated script.

-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

and this at the end.

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Hope this helps.
--
Michael Zinner, GUI Developer
MySQL AB, www.mysql.com

rpbouman said...

Hi Mike, yes, it's true: the checkbox you are referring to is even on the screenshot!

I admit I didn't notice it. When you uncheck it, SQL is generated as expected.

However, DBDesigner still generates just one SQL statement per table, and no separate statements for creating the constraints.

So, the FOREIGN_KEY_CHECKS system global can be used to force creation of these foreign key? Interesting...I thought it would only allow you to enter data that violates existing foreign key constraints.

I will try that, thanks for the insight.

Either way, I think I will remain playing around with XSLT and dbdesigner, and possibly Workbench models. For instance, it allows me to port the model to different rdbms-es, generate documentation (like you see in the sakila database) etc.

BTW, I saw some talk on the forum concerning the Workbench XML format. Are there any plans to restructure that? It is kinda flat right now, making it not really easy to make use of it in other applications.

Thanks for you comments and insights, and kind regards from

Roland.

Anonymous said...

Roland:

I am new to DBDesigner 4. Can you create DDL for Oracle? Please advise...

Rohan

rpbouman said...

Hi Rohan,

I don't think so. That is, I think DB is only capable for generating MySQL DDL.

Cheers, and good luck.

Roland

funckYtown said...

Hi,
Yes DBD is great , very GREAT

questions :
1/
I have a huge model... yes...
I saw I think somewhere hat it was possible to granulate the models and have a main and smalller one related..
Was I dreaming?
2/
Where can I change the size of the model, it is to small to contain my tables
I know that I could shrink them... but then it changes the presentation
also i think make them invisible.
Thanks

Anonymous said...

Hi,

it seems that the generated SQL fails if there are spaces or special characters in database, table, column or constraint names.
I'd suggest adding backticks [`] to quote those.

rpbouman said...

Hi NGuyen,

that is a good idea!

It will most likely take some time before I can dive into it.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...