Torkil Johnsen

MySQL Workbench is a great tool for developers to map out and visualise databases. What many people know is that it’s also a great way to make upgrading and writing upgrade scripts for your components really painless too.

Let’s say you created a small component that became popular over night. Tens of thousands of downloads from on the Joomla Extensions Directory. So you get inspired and put some more work into improving and bugfixing this component even more, and while doing that you change and add on to the database schema.

Now you need to create and distribute an update script with the next version of your component, and MySQL Workbench makes creating this update script really easy. Here is what you need to do:

Install MySQL Workbench

I’ll not help you with this. Just go to the MySQL Workbench homepage and follow the instructions from there. Multiple platforms supported! It’s the same tool I used to create the Joomla 1.6 database schema.

After installation, start MySQL Workbench and we’re off!

Create a new model

From the main menu, click File -> New Model, then double click Add Diagram. Save your diagram right away and give your file a name. It always sucks to lose your work.

Basic settings

First some settings that are handy:

To the right, under the tab “catalog”, you’ll find your schema with the default name “mydb”. The schema name is the database name by the way. Double click it and change it’s default collation to utf8 – utf8_general_ci. UTF8 is what Joomla uses by default.

Open MySQL Workbench Preferences and set the column default names and values for the Model configuration. That way you can make it easier for yourself to follow the standards and conventions you want to be following. For those of you who are using Nooku, make sure to look up the Nooku naming conventions.

For my example I’ll be setting up a small blog database.

The articles table

Click the Place a New Table-button in the left toolbar and click anywhere on the canvas to place a new table. Double click the table to start editing. You’ll see a line of tabs appearing at the bottom of the screen, one of which being columns, which you can edit to add columns to your table.

I created an example table structure for a blog articles table that you can look at on the screenshot to the right. Very basic stuff, just an id, a title, some content and a publish date for every article.

Comments for my blog articles

I then added a comments table, so that people can add comments to my blog. Basic stuff here too: Comment id, author name, author email, author homepage and comment contents. Note: I added no relation between the two tables just yet.

Now, I need to set up a relation between the two tables, since each comment should belong to a blog article. I click the button for 1:n non-identifying relationship, because each blog post will have potentially many comments attached to it. Non-identifying just means that the relation does not identify the comments, in this case that’s the job of the comment id.

So after clicking 1:n non identifying, I then click the comments table and proceed to click the articles table to set up the relation. You’ll see the line representing the relationship popping up immediately. A new column, blog_article_id is also added to the comments table automatically. This is of course the foreign key.

I then click and drag the new column, blog_article_id, up in the comments table, so that it’s ordered as the second column. Not necessary, but I like to keep my keys close. Note that you can also double click the relation indicator itself to edit the relation and manually change it or indicate if it’s mandatory or not. A comment needs to belong to a blog post, so in my example the relation is mandatory.

There, all ready to go!

Create the components install SQL script

First, I forgot, my tables need to be able to have their names converted to use any db prefix according to the Joomla standards, so I need them to have the db prefix nameholder #__. So blog_articles becomes #__blog_articles and blog_comments becomes #__blog_comments.

Ok, now let’s create an install script. Go to the main menu and click File -> Export -> Forward engeneer SQL CREATE script. For Output SQL scriptfile, put in a the desired filename for your SQL file, install.sql would fit the bill. Select “Omit schema qualifier in object names”, or else the database name (mydb) will be included in all SQL statements in the script. You can also skip creation of foreign keys if you want to since they have no effect in MyISAM, but it will do no harm to leave them in there. In my opinion they just makes the SQL structure easier to read for people interested in checking out it’s details.

In the next step, choose to export table objects. You can also add default data to your tables if you want to, and would then wish to also export data for the tables. Click continue, then finish, and the SQL file will be created for you.

Creating version 2 of my component, and the upgrade SQL script

Then I wanted to add an author name to my blog articles, so I went ahead and did that. Now I have a new version of my database, and I need to update my component. So my components version two will have to include an update script. For this I’ll need some SQL to modify the table structure, and this is where MySQL Workbench comes in very handy.

After having modified the schema, from the main menu click File -> Export -> Forward engineer SQL ALTER script. In the Input file-field, select the SQL CREATE script you created earlier, and in Output file you name the resulting file. This will take the SQL file you just created (version 1), compare it to the current updated schema (version 2) which includes the author_name, and then produce an SQL ALTER script that creates a bridge between versions 1 and 2. that you can run on existing installations to update their schemas. Pretty neat, huh? Takes five seconds, and it’s easy to include in your upcoming new product release!

This example might not be much, but imagine having done multiple small and big changes. Having easy version creation and automation of upgrade scripts at hand then really comes in handy.

Streamlining the process

All we need now is some creative soul to write some Lua scripts to automate the creation of update scripts for all old versions every time a new version is saved. When you create version 20 and run the plugin, then all update scripts for all previous 19 versions are automatically set up for you.

Automatic insertion of $Id$ tag for SVN keywords would also be nice of course. Validation of Joomla compatibility or Nooku conformity are other ideas. Anything to prevent potential errors and automate tedious tasks really.

There exists a plugin for doing Doctrine exports from MWB, so someone might get some inspiration from that.