Rails - Apr 2009 - 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


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---