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?

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