20170516153305_migrate_assignee_to_separate_table.rb 2.83 KB
Newer Older
1 2 3
# See http://doc.gitlab.com/ce/development/migration_style_guide.html
# for more information on how to write migrations for GitLab.

4
class MigrateAssigneeToSeparateTable < ActiveRecord::Migration[4.2]
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
  include Gitlab::Database::MigrationHelpers

  # Set this constant to true if this migration requires downtime.
  DOWNTIME = false

  # When a migration requires downtime you **must** uncomment the following
  # constant and define a short and easy to understand explanation as to why the
  # migration requires downtime.
  # DOWNTIME_REASON = ''

  # When using the methods "add_concurrent_index", "remove_concurrent_index" or
  # "add_column_with_default" you must disable the use of transactions
  # as these methods can not run in an existing transaction.
  # When using "add_concurrent_index" or "remove_concurrent_index" methods make sure
  # that either of them is the _only_ method called in the migration,
  # any other changes should go in a separate migration.
  # This ensures that upon failure _only_ the index creation or removing fails
  # and can be retried or reverted easily.
  #
  # To disable transactions uncomment the following line and remove these
  # comments:
  # disable_ddl_transaction!

  def up
    drop_table(:issue_assignees) if table_exists?(:issue_assignees)

    if Gitlab::Database.mysql?
      execute <<-EOF
        CREATE TABLE issue_assignees AS
          SELECT assignee_id AS user_id, id AS issue_id FROM issues WHERE assignee_id IS NOT NULL
      EOF
    else
      ActiveRecord::Base.transaction do
        execute('LOCK TABLE issues IN EXCLUSIVE MODE')

        execute <<-EOF
          CREATE TABLE issue_assignees AS
            SELECT assignee_id AS user_id, id AS issue_id FROM issues WHERE assignee_id IS NOT NULL
        EOF

        execute <<-EOF
            CREATE OR REPLACE FUNCTION replicate_assignee_id()
            RETURNS trigger AS
            $BODY$
            BEGIN
50
              if OLD IS NOT NULL AND OLD.assignee_id IS NOT NULL THEN
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
                  DELETE FROM issue_assignees WHERE issue_id = OLD.id;
              END IF;

              if NEW.assignee_id IS NOT NULL THEN
                  INSERT INTO issue_assignees (user_id, issue_id) VALUES (NEW.assignee_id, NEW.id);
              END IF;

              RETURN NEW;
            END;
            $BODY$
            LANGUAGE 'plpgsql'
            VOLATILE;

            CREATE TRIGGER replicate_assignee_id
            BEFORE INSERT OR UPDATE OF assignee_id
            ON issues
            FOR EACH ROW EXECUTE PROCEDURE replicate_assignee_id();
        EOF
      end
    end
  end

  def down
    drop_table(:issue_assignees) if table_exists?(:issue_assignees)

    if Gitlab::Database.postgresql?
      execute <<-EOF
        DROP TRIGGER IF EXISTS replicate_assignee_id ON issues;
        DROP FUNCTION IF EXISTS replicate_assignee_id();
      EOF
    end
  end
end