r1pp3rj4ck's tech blog

Because software is like sex… it's better when it's free – and even better with a penguin.

Raping MySQL – Implementing Designated ID Pools

The other day we faced an issue we never thought we ever would. The project we’ve been working on was developing new features to a courier service’s old system, originally written in PHP 5.2. We already did a lot of code in it but rewriting the whole stuff was out of the question so we had to do some ugly hacks now and then, but we always managed to do everything the customer wanted. I’m sure you know how it works. However, this courier service has made a contract with a new company, who needed an order ID pool assigned to them. It was a pretty reasonable request on their side, but a huge complication for us!

Imagine a project of over 80k LoC (and that’s just the PHP, no JavaScript, no HTML), pretty much all about handling orders. All of those orders have integer IDs, generated with MySQL auto-increment (I really don’t like auto-increment). There are many ways to submit an order and as it is a reaaaally old system, it doesn’t use an Order class or anything, the actual order-submitting logic is implemented God knows how many times, all of them are different. You might think that we were fucked. And we were.

It was clear that we needed to find some way to handle this without rewriting the whole code. But how? It took me a few days trying to find the best solution, but I finally realized that the only viable option was to rape MySQL. So we did it, and this is how:

First, we need to create a table for storing the pools. That table must be insert only, updates and deletes must be forbidden in order to avoid compromising the pools. That’s easy, can be done with just two “before” triggers. We can’t introduce a breaking change, so we need to support a “generic pool”, which works exactly like auto-increment, at least it seems so. It’s rather obvious that this table has to have two fields to mark the start and the end of the pool and one more to mark the user which the pool is assigned to. The latter has to be null-able in order to support the generic pools. If an order is submitted with an ID, we need to check if that ID is in one of the orderer’s pools before inserting the row. If not, we need to raise an error. Further checks are not needed, since the ID is a primary key in the orders table, so if the user tries to submit an order with an existing ID, MySQL will handle that for us.

There is another scenario, when the user doesn’t have pools (or doesn’t want to use them), when we need to replace the auto-increment functionality. That’s when we realize that we need a fourth column in our ID pool table, which should work like the auto-increment value of a table. For that, we need to enable incrementing that value on the ID pool, but just that, and just incrementing! We need to set the insert ID too, fortunately this can be done with a little hacking (as you can see in the code below) with the LAST_INSERT_ID() function.

We are almost good now, but we still need to automatically generate the generic pools when they are full. We don’t care about the custom ones. We also need to check for collisions when creating new pools.

I hope it helps somebody someday. Or at least makes an interesting story. Anyway, here is our implementation:

Advertisements

Single Post Navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: