MySQL and the inheritance problem

So early on, I had decided to go with MySQL and InnoDB for my database of choice. Recently however I decided I needed to change this. As part of adding an audit trail for the TruSafe Vault, I wanted to inherit multiple encrypted items from a single parent, and add logging to that parent object.

In WebObjects, this means choosing an inheritance model, whether it is Single-table, Horizontal, or Vertical inheritance. I chose the Vertical inheritance model since it closely models after the object model. There may be performance issues later, but it would be a relatively trivial migration to move to the single table model. Horizontal wasn't an option, as it didn't allow for foreign key relationships at the parent level.

This introduced a new problem. MySQL checks all constraints at statement time, not commit time! Coming from the Oracle world, I wasn't used to this. This put a huge dependency on me to order the object creation and saving properly or else things would just fail. With WebObjects hiding much of the inheritance object creation, this became quite difficult.

Rather than peppering my code with various hacks to overcome this, or changing the whole data model to adhere to this limitation, I decided to switch databases. Sounds reasonable, right? Well I didn't have any data, and was a little concerned about the licensing of the MySQL JDBC drivers anyway.

So out with MySQL, and in with Postgresql. 9.1 to be exact. Installed in on my MacBook Air, change the WebObjects database driver from MySQL to Postgresql and off I was. In actuality, it really was that simple.

And because Project Wonder, which is a huge framework built on extending WebObjects, is so wonder-ful, if I did need to change databases, there is a convenient way to do this.

If you plan on doing a WebObjects app, don't do it without Project Wonder and WOLips.