Question about case-insensitive columns in Rails

My question.txt

Hola Rails people. I have a column in my database that I need to treat
as case-insensitive. The standard solution to this is to downcase the
value before inserting it into the database, and when doing
searches. My problems with this are:

1. I have to make calls to String#downcase in several places where I
*really* mean "this is case-insensitive"

2. I can only provide automatic-downcasing when going through the API
I provide. Anyone calling MyClass.where(:name => foo) has to know to
pass in a downcased version of foo.

I can think a few possible solution areas off the top of my head:

1. Do everything in the database. How exactly? Maybe I can create a
virtual column or view, and have a trigger on the db. DBMS is
Postgres, whatever version Heroku runs :)

2. A gem or plugin that treats entire columns as case-insentive by
automatically downcasing stuff as well as hooking into any arbitrary
SQL queries and dowcasing them.

3. Something much simpler that I haven't thought of :)

What do you guys know?

There is a citext module for postgres which provides a case-insensitve text type citext, and citext is baked into Postgres 9. But Heroku runs PostgreSQL 8.3.11 (at least for my app), and I don't think they have the citext module installed. So I don't think that's an option.

Great question, Pat. I was looking into converting a rails app form MySQL to Postgres a couple years ago, and needed to treat some columns in a case-insensitive fashion. My half-finished solution is here.

Obviously, that isn't rails-3 compatible, but there might be some ideas to steal there.

Looks like a good start. Thanks!

If the query is of the form where email = '[email protected]', the solution is to do where lower(email) = lower([email protected]). And by the way, postgres allows expression indexes, so you can index lower(email).

If the query is of the form where foo LIKE 'something%', just use where foo ILIKE 'something%'

Cheers!

@hgimenez yeah...but the point is that I don't want to have to include calls to lower() in SQL or String#downcase in Ruby. Too easy to miss one, and then what?

If your tests pass why do you worry about other places where people might possibly call where(:foo => 'bar') or whatever? ;)

Anyways, I'd do a trigger. Building a view works fine until you have to insert or update values on it, in which case you'll have to write postgres rules to overwrite the query and insert/update the underlying table instead.

One strategy is just to override where in the appropriate model:

def self.where(*args)
  args.first.tap{|a| a[:first_name].try(:upcase!) if a.is_a?(Hash) }
  super(*args)
end

And do the same for other methods you need to call. There's probably some way to hook into rails at a deeper level; find whatever routine rails 3 uses for sanitization and wrap it with some method that does basically what I described above. That would be even more "clever," but far, far more dangerous in terms of introducing bugs down the road.

I have to say, though, this kind of shit is precisely what will make maintenance suck down the road. There's nothing worse than spending a day debugging some code only to realize that calling .where(..) isn't ACTUALLY calling .where, but some some "convenience code" plus where(...).

The right answer is to write integration tests and let those ensure that you're using a named scope or downcase or whatever in the right place. It's nice to know how to do this stuff, but I'd never let the above code past a code review.

That being said, I'm happy to provide the gun as long as you're providing the foot.

I agree with @agraves. This is exactly the kind of thing that will cause someone a headache. Yes, you have to write a couple extra words here and there. But down the road, those words are perfectly documented. One of the biggest problems in rails is how obscure some code can be because someone didn't want to keep writing the same 2 or 3 words in a few places and decided that "magically DRY" is better than documented, so it created a plugin/gem/whatever to "make it DRYer" :)

What I would do is something like:

def self.insensitive_name(attrs)
  name = attrs.delete(:name).to_s
  attrs.merge("lower(name)" => name.downcase)
end

where(insensitive_name(name: "foo"))

Then you have something that's documented, and is a bit more explicit than just doing the lower() and downcase dance everywhere.

alright, so here's what I want to do:

class User < ActiveRecord::Base
  case_insensitive :email
end

BOOM. The rest happens like magic.

If this is a Rails 3 app why not use Arel's match method? It forces a case-insensitive query (like for MySQL, ilike for Postgres)

relation = arel_table[:column].match(value)
where(relation)

@patmaddox now I have to find that line between validations, callbacks, and all that. If a query at some point starts behaving weird because of a bug in the "like magic" code, I'll spend a while debugging and then hate you for committing that code. I totally get that you want to avoid typing the same thing time and time again. But I've been bitten by that kind of thing way too many times to consider anything that isn't explicit a good solution. We'll probably have to agree to disagree though :)

@bcardarella maybe I'm missing something but is there a way to have that applied all the time? as I've mentioned, the point is to specify case-insensitivity once and only once.

@godfoca it's a good thing that when you work with me, we pair, so this won't be some shocking surprise. Also, my "solution (not yet implemented) is absolutely explicit. The rule is, "This is case-insensitive all the time." That's what my code says. By sprinkling calls to scopes everywhere, I've made the code verbose, but the rule is implicit.

@patmaddox yeah, I got that after some tweets between you and @foca

I will say this: I understand you have a use case for it but this smells to me. Doing something like this feels about as dirty as setting a default_scope on your model.

You people are all crazy. I just want you to know that :)

I'll accept that as praise