codahale.com٭blog

Coda Hale lives in Berkeley, CA, where he writes about Ruby on Rails, usability, web design and development, and the occasional bit about bicycles.

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.

This entry was posted on Wednesday, July 18th, 2007 at 10:28pm and is filed under Programming, Ruby and Rails. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

18th Jul 2007 10:28pm Programming Ruby and Rails
 

6 Responses to “Rails Plugin: easier-indexes”

  1. josh Says:
    July 19th, 2007 at 6:49am

    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:
    July 19th, 2007 at 7:19am

    Who says we’re using MySQL? ;-)

  3. Chris Says:
    July 19th, 2007 at 12:35pm

    Bling bling. Nice.

  4. Tom Says:
    July 19th, 2007 at 10:16pm

    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:
    September 23rd, 2007 at 8:15am

    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:
    November 4th, 2007 at 4:26pm

    Thanks for the code. Greetings. Prese