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