[Rails] raw SQL in migrations

[Tyler MacDonald]

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com
To unsubscribe from this group, send email to 
rubyonrails-talk+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---