I’ve been working with Rails for the past few weeks. But seriously, folks, it’s been great. Anyway, what are we all here for? To hear about my first big trouble with Rails.
We had a simple request at work to make a database field case-insensitive. Which is all to say, we had to make this touch field’s once defaultly true case sensitivity:
into a blatant lie:
validates_uniqueness_of :a_touchy_field, :case_sensitive => false
And this is why Rails is so great. We’re done!
BUT WAIT. WHAT ABOUT THE BIG TROUBLE?
Oh right! Well, this field is very touchy. Touchy like… millions of rows touchy. And it didn’t like all this insensitivity we we’re giving it. And neither did MySQL. There’s a lighthouse ticket all about it.
The issue is that to keep things DB-neutral, Rails is having MySQL perform a
LOWER() on that field on each row to do a binary comparison for the uniqueness validation. How unkind to our conveniently indexed database field.
By default, MySQL does a case-insensitive string comparison. So we rolled our own uniqueness validation…
def TouchyModel < ActiveRecord::Base validate :ensure_unique_case_insensitive_touchy_field def ensure_unique_case_insensitive_touchy_field if new_record? || a_touchy_field_changed? if TouchyModel.exists?(:a_touchy_field => a_touchy_field) errors.add(:a_touchy_field, "must be alone, or else it'll get whiny") end end end end
This lets us tip-toe around the call to
LOWER() and get on with our validations using the default MySQL comparison.