Dealing with Case Sensitivity of Database Table Names at the Persistance Layer with Hibernate

It's been a while since my last post so it's about time I got back to it since I'm backlogged with all sort of awesomeness. Recently, I took over a pretty big Spring project. Lucky for me, it was written largely in Spring 3.0 so my periodic upgrades to 3.1 haven't been too difficult. I'm the only developer on the project but it's not that bad (anymore) and it's given me a chance to really focus on my Spring skills.

Since I develop locally, I backed up the production database on the server and brought it down to my laptop. Before I could work on it, I needed to run the data through a conversion routine that migrates the database model and its data into the new and improved model for the next release.

The Problem

The problem is that the server is Linux and my computer is a Mac.

Why does this matter? It matters because each OS has different case sensitivity. The conversion routine uses Hibernate as the persistance provider and it's configured to use the DefaultComponentSafeNamingStrategy which maintains the same case as the managed entities. This is fine except that the application uses a custom naming strategy that converts everything to lower case and acts as a wrapper around the DefaultComponentSafeNamingStrategy. The only time this would ever cause a problem is when the operating system the application is run on is case sensitive. Linux is but Windows is not case sensitive and neither is Mac. I didn't know this. In fact, I was under the impression Mac was a good OS.

Any of my collegues reading this right now are smiling because I'm constantly listing the ways Windows is terrible and Macs are better as programming tools.

The Effect

This problem wasn't actually a problem on Windows or Mac. It manifested itself when I moved my converted data up to the server and restored it into the database. When I fired up the application, all the data was gone! Pretty sure a pink slip was in my future, I scrambled to figure out what happened. This was the first time the data was updated since my firm took over the project and they had put their faith in my ability to not blow the database away. I had just blown the database away, or so I thought. It took a while to figure out what happened.

As it turns out, the data was there but with camel casing but since the app uses a lowercase naming strategy, it wasn't able to find any of the tables.

For example, a table named MyWidgetTable was being looked for under the name mywidgettable.

Finding the Best Solution

This might seem like an easy fix, just update the naming strategy for the conversion routine. It's true, that's the best solution and the one I selected but, it's important to realize why it's the best solution.

For instance, another option would be to configure the database - in this case, MySQL - to use lowercase naming when creating objects. People do this. You can read more about that here http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html.

Although this is an option, I don't recommend it.  Doing so will more tightly couple the database implementation to the application and whenever possible we like to make each tier in an architecture as pluggable and removable as possible.  Changing databases has more side effects when you start introducing configurations that directly impact the design of a system.

In general, when you need to deal with filesystem objects, try to choose an implementation that will work across the big players to keep your code as portable as possible even when you think you're environment won't change. It will and in this field, crap changes every couple of days! The previous developer knew this and that's probably why he wrote the custom lower case strategy to begin with.