raw SQL in migrations

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

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,