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!
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: