One of the things that quickly tripped me up in using SQLite in Rails was that there was no way to make a column case insensitive.
Most human textual comparisons are case insensitive, we think that “dog” and “doG” and “DOG” all denote the same thing. If we have a uniqueness constraint on a column, then we want it to protect us from having three separate entries for those three strings. Also, when we’re searching for “dOg” we want it to match any one of the three strings.
Every database has its own way of doing these sorts of things - SQLite uses one of the more sensible methods: it allows you to specify that any column you like should be case insensitive (and then any index on that column will automatically be case insensitive too).
So, you can write:
CREATE TABLE foo ( bar varchar(255) COLLATE NOCASE );
And you’ll get a case insensitive column ‘bar’. This is so often what you want, more often than not, so I wrote a quick gem that does this by default for all your migrations.
This is a kludgy kludge!
What would be nice would be to add a new option to the migration methods so that any given column could be marked with something like: :case => false Unfortunately, the Rails code for all that is not very DRY and stuffed with metaprogramming - so it’s not easy to retrofit a gem to make that happen. So my gem just makes it happen for every string or text field.
Mostly this will be what you want, but every so often case sensitivity is something that is desirable - so beware :)
