I've taken to using this when I just want a migration to be a big wad of sql;
runsplit.rb in my lib/ directory:
class Runsplit < ActiveRecord::Migration def self.runsplit(sql) transaction do sql.split(';').each do |stmt| execute(stmt) if (stmt.strip! && stmt.length>0) end end end end
And then whenever I have a migration where want to do the SQL statements by myself, I make the migration inherit off of 'Runsplit', like this;
class AlterUserStates < Runsplit def self.up runsplit %q{ ALTER TABLE user_states ALTER COLUMN user_id DROP NOT NULL; ALTER TABLE user_states ADD session_id TEXT NULL; } end
def self.down runsplit %q{ DELETE FROM user_states WHERE user_id IS NULL; ALTER TABLE user_states ALTER COLUMN user_id SET NOT NULL; ALTER TABLE user_states DROP session_id; } end end
This works really well, except when the actual SQL statement has semicolons in it. For that, I'm still using execute();
class GenerateSalt < ActiveRecord::Migration def self.up execute %q{ CREATE FUNCTION generate_salt () RETURNS CHAR(10) LANGUAGE PLPGSQL VOLATILE AS $$ DECLARE rv CHAR(10); i INT; BEGIN i := 0; rv := ''; WHILE i < 10 LOOP rv := rv || chr((random() * 25)::int + 65); i := i + 1; END LOOP; RETURN rv; END $$ } end
def self.down execute %q{DROP FUNCTION generate_salt()} end end
