One of the applications I maintain currently only runs against Microsoft SQL Server. For the most part this hasn’t proven to be that much of a problem as everyone who uses the application also runs MS SQL. The problem comes when I want to deploy and test it on one of the company servers because they all run Linux and there is no way to run MS SQL under Linux (see note 1).

I could tackle this problem by setting up Windows machine and running a copy of MS SQL on that but we already have more servers than we probably should have and I don’t want to have to dedicate a whole machine to this one task. It’s probably worth pointing out that I wouldn’t be making sufficient use of the database to justify it living in it’s own box, in production I would almost always have the database running on a separate machine.

The alternative therefore seems to be to make the application run on another database and in my case that would be MySQL. I’ve toyed with the idea of doing this before but I’ve always shied away from it, the application makes heavy use of stored procedures and the SQL makes moderate use of MS SQL syntax. The real problem though is the code, it’s a bit of a mess. I’m concerned that if I jump into this project I’ll end up introducing dozens of new bugs which is ironic because one of the reasons I want to get it working against MySQL is so that I can easily run unit and integration tests on the CI server.

The first issue i faced was what to do about the GUID keys in the MS SQL databaes. MySQL has a UUID function which will return an identifier but there no column type of UniqueIdentifier as there is with MS SQL. There are several ways of storing a UUID in MySQL and I’ve opted for what I feel is the best match with MS SQL which is a char(36) column. This stores the complete UUID as a text string with dashes. Alternatively you can store it as a char(32) without dashes, as a 16-byte binary or a couple of other types. The style I have chosen is the slowest and takes up the most space but is also, arguably, the most human readable and is the best match for what is currently happening in the application.

For my sins I then had to address what to do about inserts. Most of the tables in the original database have an ID column which has a default value of newid(). If your like me you’ll have thought ah-ha but I have the UUID function I’ll just set that as default value. Nope, for some completely unknown reason MySQL can’t use the result of a function as the default value for a column – yep, you read that correctly. Apparently in 5.6.5 you can use the ‘now’ function for datetime columns but as far as I can see that is the only bug fix. Oh, did I accidentally call this well documented feature limitation a bug, yes, because it’s such a glaring omission it falls squarely into bug category for me.

So, rant over, the solution is to use a trigger to populate the key field automatically. This isn’t the greatest solution as triggers are slow but it’s the only way now and for the foreseeable future to achieve the desired result. At the same time I often need to store the creation time for a row, since I want to use a datetime column that also needs to be populated by the trigger. Additionally the columns populated by the trigger also need to accept having their values set programmatically as the code is somewhat inconsistent in this respect. The SQL below is and example showing the solution I came up with.

USE example;

DROP TABLE IF EXISTS TriggerExample;

CREATE TABLE `TriggerExample` (
    `ID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
    `Foo` varchar(100) NOT NULL,
    `Created` datetime NULL DEFAULT NULL,
    PRIMARY KEY (`ID`)
);

CREATE TRIGGER TriggerExample_OnInsert
BEFORE INSERT ON TriggerExample 
FOR EACH ROW
SET NEW.ID = IF( NEW.ID = '00000000-0000-0000-0000-000000000000', UUID(), NEW.ID ),
NEW.Created = IFNULL( NEW.Created, NOW() );

INSERT INTO TriggerExample( Foo ) 
VALUES ( 'Bar_1' );

INSERT INTO TriggerExample( ID, Foo, Created  ) 
VALUES ( '00000000-0000-0000-0000-000000000001', 'Bar_2', '2099-01-01 02:03:04' );

In reality the ‘Created’ field is set to not null but for the example I’ve shown is as accepting null so that you can see a shorthand way of accepting a user defined value. When set to not null you need to use an if as with the ID. The table resulting from the above script is shown below.

As you can see the first row contains the user defined values and the second row the auto-inserted values. Over all I’m fairly pleased with this result. It’s not as tidy as it is with MS SQL but it’s close enough that I think I can probably port the application over with minimal changes. The problem I now have is finding the time to actually do the work!

Notes

1. It’s certainly not possible to run any recent version of MS SQL under Wine however it might be possible to run it inside a virtual machine. Neither are exactly fantastic solutions though.