Friday, March 02, 2007

Data Loaders in Migrations

Two cases of data usage at RHG (as outlined in the DB Migrations With a Twist post) - migration consolidations and test data creation via migrations, led us to having migrations that just load and execute SQL statements from a file. Another use case is mass-loading of referential data. To help with those tasks we use a simple module that provides a method for data loading:

module DataLoader

def load_data(name = 'data', ext = 'sql')
file_name = data_file(name, ext) || fail("Cannot find the data file for #{ name }")
execute_sql_from_file(file_name)
end

def execute_sql_from_file(file_name)
say_with_time("Executing SQL from #{ file_name }") do
IO.readlines(file_name).join.gsub("\r\n", "\n").split(";\n").each do |s|
execute(s) unless s == "\n"
end
end
end

private

def data_file(name, ext)
mode = ENV['RAILS_ENV'] || 'development'
[
data_file_name(mode + '_', name, ext),
data_file_name('', name, ext)
].detect { |file_name| File.exists?(file_name) }
end

def data_file_name(prefix, name, ext)
"#{ RAILS_ROOT }/db/migrate/data/#{ prefix }#{ name }.#{ ext }"
end

end

A sample migration using the method for loading the consolidated data to all DBs looks like this:

class ConsolidatedMigrations < ActiveRecord::Migration

extend DataLoader

def self.up
all_dbs.each_key do |name|
run_on_db(name) { load_data "consolidated_for_#{ name }" }
end
end

end

It loads Rail-environment specific files from the db/migrate/data/ directory:

development_consolidated_for_main.sql
development_consolidated_for_portal_referential.sql
...
production_consolidated_for_main.sql
production_consolidated_for_portal_referential.sql
...
test_consolidated_for_main.sql
test_consolidated_for_portal_referential.sql