codahale.com٭blog

This is my old blog. My current writing is here: codahale.com

Rails Plugin: easier-indexes

Holy crap. I’m writing plugins.

I’m working on a data warehouse, and I was building the migration for a dimension table today. Dimension tables are these incredibly wide (50-100 columns), denormalized tables which have to be heavily indexed in order to work well. And my migration was totally out of hand. I wanted to index each column, but that lead to a stupid amount of repetition.

I had something like this1:

create_table :date_dimensions do |t|
  t.datetime :date
  t.integer :day_of_month
  t.integer :day_of_year
  t.integer :week_of_month
  # etc.
end

add_index :date_dimensions, :date
add_index :date_dimensions, :day_of_month
add_index :date_dimensions, :day_of_year
add_index :date_dimensions, :week_of_month
# etc.

I had typed :date_dimensions five times, add_index four times, and each column name twice. And I still had like forty columns to do. So I wrote a quick little plugin to compress all that into this:

create_table :date_dimensions do |t|
  t.datetime :date, :index => true
  t.integer :day_of_month, :index => true
  t.integer :day_of_year, :index => true
  t.integer :week_of_month, :index => true
  # etc.
end

Get it here: http://code.google.com/p/easier-indexes/

1 This is using the new “sexy” migration style from Edge Rails. Like most sexy things, Chris Wanstrath came up with it.

6 Responses to “Rails Plugin: easier-indexes”

  1. josh Says:

    Nice and dry. I like it.

    But… If you are doing data warehousing, shouldn’t you be using a vertical database built for that sort of thing? You’re just going to hurt yourself doing OLAP using MySQL.

  2. Coda Says:

    Who says we’re using MySQL? ;-)

  3. Chris Says:

    Bling bling. Nice.

  4. Tom Says:

    Did you check out ActiveWarehouse for your data warehousing needs?

    The ActiveWarehouse sample code has an example of how to do this in a DRY way without need of a plugin

  5. James Says:

    Sorry to be pedantic but the “sexy” migrations style came out of Hobo, Chris just brought it to the attention of the wider Rails community. I like your addition.

  6. Prese Per Arrampicata Says:

    Thanks for the code. Greetings. Prese