I have an ActiveRecord model like this:
create_table "books" do |t|
t.string "title"
end
class Book < ActiveRecord::Base
default_scope :order => 'lower(title) DESC'
end
As you can see I want to sort by the lowercase form of the title
attribute, but this imposes a performance hit at the database level. This hit can be remedied in different ways with different databases. For example, in PostgreSQL or Oracle you create a function-based index:
CREATE INDEX lowercase_book_title_index ON book (lower(title));
SQLite3 doesn't have function-based indexes so you have to specify a collation:
CREATE INDEX lowercase_book_title_index ON book (title COLLATE NOCASE);
I haven't looked into how you do it with MySQL but I'm sure there's a way (collations? virtual columns?).
In any event I would like to do this in proper Rails fashion with a database-agnostic migration. I can of course create a simple index like this:
add_index :books, :title
But the index this generates is case-sensitive. I realize I could write a database-dependent migration, but it's not very elegant. It's also not practical--I often find myself using SQLite3 on my development workstations and PostgreSQL in production. The available options for add_index deal with index name, uniqueness, and length. Am I overlooking a way to accomplish what I'm trying to do here?
Short answer: not
Long answer: not, because not all database engines support it what Rails supports, or in a very different way. If are you sure all database engines supports it, then file a feature request for ActiveRecord developers.
Keep in your mind: in ActiveRecord migrations, you can only do what all database engine supports, database-specific stuffs needs separated initializations from migrations.
I recommend to setup this collation from a separated rake task what runs after db:migrate. In my bigger applications I use rake setup
where I can do this specific steps.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With