Skip to content

Migrations for adding issue_id to versions table (CE)

Luke Duncalfe requested to merge 11090-export-design-management-1-issue-migration into master

What does this MR do?

These migrations do the following:

1. Adds a new issue_id column to versions

This fixes an n+1 problem when loading versions for an issue in GraphQL as AR can now load from cache.

2. Changes the unique index on versions.sha to include issue_id

This allows us to export and import Version data as part of the project importer/exporter (https://gitlab.com/gitlab-org/gitlab-ee/issues/11090). In order to import version data, we need to allow duplicate sha values for Versions.

Note, this new scope could have used project_id instead of issue_id, however there is a need to migrate issue_id to the table (see 1.) and not a need to migrate project_id. It may also be handy in future if we ever want to add the ability to copy design data from one issue to another within the same project.

3. Updates all Versions with an issue_id

This backfills the data for older records. As mentioned in https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/30765#note_192089192 there is very little (or no) existing data.

Migration output

➜  gitlab git:(11090-export-design-management-1-issue-migration) be rake db:migrate
== 20190715042813 AddIssueIdToVersions: migrating =============================
-- add_reference(:design_management_versions, :issue, {:index=>true, :foreign_key=>{:on_delete=>:cascade}})
   -> 0.0326s
== 20190715042813 AddIssueIdToVersions: migrated (0.0326s) ====================

== 20190715043944 RemoveShaIndexFromVersions: migrating =======================
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0007s
-- index_exists?(:design_management_versions, :sha, {:algorithm=>:concurrently})
   -> 0.0022s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:design_management_versions, {:algorithm=>:concurrently, :column=>:sha})
   -> 0.0054s
-- execute("RESET ALL")
   -> 0.0003s
== 20190715043944 RemoveShaIndexFromVersions: migrated (0.0091s) ==============

== 20190715043954 SetIssueIdForAllVersions: migrating =========================
-- execute("UPDATE design_management_versions as versions SET issue_id = (\n      SELECT design_management_designs.issue_id\n        FROM design_management_designs\n        INNER JOIN design_management_designs_versions ON design_management_designs.id = design_management_designs_versions.design_id\n        WHERE design_management_designs_versions.version_id = versions.id\n        LIMIT 1\n    )")
   -> 0.0028s
== 20190715043954 SetIssueIdForAllVersions: migrated (0.0028s) ================

== 20190715044501 AddUniqueIssueIdShaIndexToVersions: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:design_management_versions, [:sha, :issue_id], {:unique=>true, :using=>:btree, :algorithm=>:concurrently})
   -> 0.0016s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:design_management_versions, [:sha, :issue_id], {:unique=>true, :using=>:btree, :algorithm=>:concurrently})
   -> 0.0026s
-- execute("RESET ALL")
   -> 0.0004s
== 20190715044501 AddUniqueIssueIdShaIndexToVersions: migrated (0.0052s) ======

Database checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)

When adding or modifying queries to improve performance:

  • [-] Included data that shows the performance improvement, preferably in the form of a benchmark
  • [-] Included the output of EXPLAIN (ANALYZE, BUFFERS) of the relevant queries

When adding foreign keys to existing tables:

  • [-] Included a migration to remove orphaned rows in the source table before adding the foreign key
  • [-] Removed any instances of dependent: ... that may no longer be necessary

When adding tables:

  • [-] Ordered columns based on the Ordering Table Columns guidelines
  • [-] Added foreign keys to any columns pointing to data in other tables
  • [-] Added indexes for fields that are used in statements such as WHERE, ORDER BY, GROUP BY, and JOINs

When removing columns, tables, indexes or other structures:

  • Removed these in a post-deployment migration
  • Made sure the application no longer uses (or ignores) these structures

General checklist

Edited by 🤖 GitLab Bot 🤖

Merge request reports