Raw SQL in migrations


#1

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


#2

Tyler MacDonald wrote:

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

Why? Since (I think) Rails 2.2, all migrations are transactional
anyway, and for older versions of Rails, you can just use the
transactional_migrations plugin. This just seems to me like a waste of
effort.

In any case, if you have large amounts of raw SQL in migrations,
something is probably wrong. What are you trying to accomplish?

Best,

Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid