Forum: Ruby on Rails raw SQL in migrations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
52cb4115a870ff1942e7e1ae96f4306f?d=identicon&s=25 Tyler MacDonald (Guest)
on 2009-04-18 05:02
(Received via mailing list)
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
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-04-18 05:49
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
marnen@marnen.org
This topic is locked and can not be replied to.