Creating common fields in your Rails model database migrations

Sometimes you want a set of common set of fields in a bunch of your tables. DRY it up a little so you can write this in your migrations:

include MigrationHelpers

class CreateUserTales < ActiveRecord::Migration
  def self.up
    create_table :user_tales do |t|
      t.common_fields
      t.integer :tale_id, :null => false
    end
    add_common_field_indexes :user_tales
    add_index :user_tales, :user_id
    foreign_key :user_tales, :tale_id, :tales
  end
end

You need a /lib/migration_helpers.rb containing this:

module MigrationHelpers

  # Common fields for which we want to create an index by default.
  # Rather than create indexes for every field we create them by default only for those fields which do not change every time the record is updated.
  COMMON_INDEX_FIELDS = %w(owner_id created_by created_at)

  class ActiveRecord::ConnectionAdapters::TableDefinition
    def common_fields
      self.integer :owner_id, :created_by, :updated_by
      self.integer :version, :default => 0, :null => false
      self.boolean :is_deleted, :default => false, :null => false
      self.timestamps                   # adds created_at and updated_at as datetimes
    end
  end

  # Create indexes for the Common Fields
  def add_common_field_indexes(table_name, columns_to_index=COMMON_INDEX_FIELDS)
    columns_to_index.each do |column_name|
      index_name = "index_#{table_name}_on_#{column_name}"
      execute "CREATE INDEX #{index_name} ON #{quote_table_name(table_name)} (#{column_name})";
    end
  end

  # Hide mysql's foreign key syntax behind a simple method
  def foreign_key(from_table, from_column, to_table)
    constraint_name = "fk_#{from_table}_#{from_column}"
    execute %{ALTER TABLE #{from_table} ADD CONSTRAINT #{constraint_name} FOREIGN KEY (#{from_column}) REFERENCES #{to_table}(id)}
  end
end