Thursday, December 25, 2008

Case senstive SELECTs

While working with the legacy code and migration process to REST on Ruby on Rails (from the mix of shell scripts, C and other types of binaries), I've got into trouble debugging one part of the code that was ultimately going into loop.

What it worked out to, is that we had a number of case sensitive IDs, like JONHSMITH, JohnSmith and johnsmith, reflecting the different types of account information. I'm not going to guess you why did it happen, but it always does, if there is a possibility of human interaction with the naming process ;)

What was more important is how to make the code work and to stay away of any table alterations. While there a huge amount of discussion in the Internet about that, the easiest way that I found to be is just using BINARY operator for a SELECT clause.

For example, SELECT * FROM accounts WHERE BINARY user LIKE 'JohnSmith'; gives you exactly JohnSmith's record.

You can take your time and refactor the code and table structure later, after the things are working and nobody's trying to ask you the same question for many times per day ;)

No comments: