DBDeploy with MySQL

I started playing with dbdeploy on one of my php projects today because I’ve seen it used with success on another project I’m working on. The biggest differences in the projects are that one uses Postgres and the other uses MySQL. Converting all my sql scripts for table creation went just fine and they ran perfectly.

I was pretty excited and started converting my stored procedures over, and that’s when I ran into problems. I don’t know if they are to be attributed to dbdeploy or to mysql, but I have a feeling they are both to blame a little. In most RDBMS systems, the DB engine can detect when you’re creating a stored procedure by picking up the BEGIN keyword and waiting until it finds a matching END keyword to actually compile and store the procedure.

In mysql, the engine isn’t smart enough to detect the begin/end tags, so you have to change your delimiter. FOr example, you can execute “DELIMITER //” and then create a procedure like “CREATE PROCEDURE `myproc`() BEGIN statement1; statement2; END//” . This effectively makes mysql ignore the inner “;” so that you can store the procedure. Then you can set your delimiter back with “DELIMITER ;”.

The problem with this is that most database drivers (JDBC) are written for the norm – a database engine that can determine the difference between defining a procedure and executing the procedure. In ant, you can define a delimiter for the <sql /> task, such as // to get around this. Then in your sql scripts you use // where you would normally use ; to end a table creation or procedure statement.

Enter dbdeploy. DBDeploy only uses “;” as a delimiter and will not detect Ant’s override. It also doesn’t like having “//” as a delimiter in the scripts. As in, it fails miserably the first time it hits //. Also, if you use delimiter=”//” in Ant, the normal code doesn’t work either. The deltas are generated using ; instead of //.

Now, all of that together can’t be avoided. You wouldn’t want dbdeploy to be dependent on Ant (so as to detect the use of delimiter=”//”) and you wouldn’t want to modify the default behavoir of the <sql /> task. The problem here is mysql! If MySQL would go ahead and finish implementing real support for stored procedures and the logic to create them using the same syntax as all other statements, we would be just fine.

As a stop gap I’m hoping that there is a delimiter switch for dbdeploy that could change it’s scripts so that it would flow through the ant <sql /> task without a problem. In the end though I will most likely take the time to convert all of my SQL backend code to Postgres. Postgres is fast, enterprise ready, open source, and feature rich. I can’t same the same for MySQL.