gistfile1.rb

class AddSetPositionsStoredProc < ActiveRecord::Migration

  STORED_PROC_NAME = 'set_positions'

  CREATE_STORED_PROC_SQL = %Q{
    CREATE PROCEDURE #{STORED_PROC_NAME}()
    BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE pid INT;
      DECLARE curs CURSOR FOR SELECT DISTINCT parent_id FROM units;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      OPEN curs;

      REPEAT
        FETCH curs INTO pid;
        IF NOT done THEN
          SET @rownum := 0;
          UPDATE units SET position = @rownum := @rownum + 1
          WHERE parent_id = pid
          ORDER BY CASE WHEN position IS NULL THEN 1 ELSE 0 END, position, short_name;
        END IF;
      UNTIL done END REPEAT;

      CLOSE curs;

      SET @rownum := 0;
      UPDATE units SET position = @rownum := @rownum + 1
      WHERE parent_id IS NULL
      ORDER BY CASE WHEN position IS NULL THEN 1 ELSE 0 END, position, short_name;
    END
  }

  def self.up
    ActiveRecord::Base.connection.execute(CREATE_STORED_PROC_SQL)
  end

  def self.down
    sql = "DROP PROCEDURE #{STORED_PROC_NAME}"
    ActiveRecord::Base.connection.execute(sql)
  end

end