Case Sensitive Active Record Finds in Ruby on Rails

This was hard to find, so I thought I'd put up a little post.

I was making a site, similar to tinyurl.com, where the identifying id in the url string was a sequence of characters (like /jYi1K8c) some lowercase, some uppercase.

With MySQL, you can't just do a Item.find_by_pid('jYi1K8c') and get back the right thing, it will get back the first that matches a case-insensitive search. That is, /ab1, /AB1 and /Ab1 all match the same item.

So, to get around this, there are ways you can set up the migration to indicate the field is case sensitive.

execute %{ALTER TABLE TABLE_NAME MODIFY slug varchar(255) COLLATE utf8_bin NOT NULL}

Apparenty, you can put this right in the migration, and not use 'execute'.

But, I didn't do these. Maybe I will later, but in the short term, I just modified the find conditions in the controller to be like this:

@item = Item.first(:conditions => ['BINARY pid = ?', params[:pid]])

This is a little odd to me - usually rails makes this sort of thing easier to get at. I wouldn't be surprised if there was a better way.