1 files changed, 84 insertions, 0 deletions
diff --git a/db/post_migrate/20210210221006_cleanup_projects_with_bad_has_external_issue_tracker_data.rb b/db/post_migrate/20210210221006_cleanup_projects_with_bad_has_external_issue_tracker_data.rb
new file mode 100644
index 00000000000..4b8bf014066
--- /dev/null
+++ b/db/post_migrate/20210210221006_cleanup_projects_with_bad_has_external_issue_tracker_data.rb
@@ -0,0 +1,84 @@
+# frozen_string_literal: true
+
+class CleanupProjectsWithBadHasExternalIssueTrackerData < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+  TMP_INDEX_NAME = 'tmp_idx_projects_on_id_where_has_external_issue_tracker_is_true'.freeze
+  BATCH_SIZE = 100
+
+  disable_ddl_transaction!
+
+  class Service < ActiveRecord::Base
+    include EachBatch
+    belongs_to :project
+
+    self.table_name = 'services'
+    self.inheritance_column = :_type_disabled
+  end
+
+  class Project < ActiveRecord::Base
+    include EachBatch
+
+    self.table_name = 'projects'
+  end
+
+  def up
+    update_projects_with_active_external_issue_trackers
+    update_projects_without_active_external_issue_trackers
+  end
+
+  def down
+    # no-op : can't go back to incorrect data
+  end
+
+  private
+
+  def update_projects_with_active_external_issue_trackers
+    scope = Service.where(active: true, category: 'issue_tracker').where.not(project_id: nil).distinct(:project_id)
+
+    scope.each_batch(of: BATCH_SIZE) do |relation|
+      scope_with_projects = relation
+        .joins(:project)
+        .select('project_id')
+        .merge(Project.where(has_external_issue_tracker: false).where(pending_delete: false))
+
+      execute(<<~SQL)
+      WITH project_ids_to_update (id) AS (
+        #{scope_with_projects.to_sql}
+      )
+      UPDATE projects SET has_external_issue_tracker = true WHERE id IN (SELECT id FROM project_ids_to_update)
+      SQL
+    end
+  end
+
+  def update_projects_without_active_external_issue_trackers
+    # Add a temporary index to speed up the scoping of projects.
+    index_where = <<~SQL
+      "projects"."has_external_issue_tracker" = TRUE
+      AND "projects"."pending_delete" = FALSE
+    SQL
+
+    add_concurrent_index(:projects, :id, where: index_where, name: TMP_INDEX_NAME)
+
+    services_sub_query = Service
+      .select('1')
+      .where('services.project_id = projects.id')
+      .where(category: 'issue_tracker')
+      .where(active: true)
+
+    # 322 projects are scoped in this query on GitLab.com.
+    Project.where(index_where).each_batch(of: BATCH_SIZE) do |relation|
+      relation_with_exists_query = relation.where('NOT EXISTS (?)', services_sub_query)
+      execute(<<~SQL)
+      WITH project_ids_to_update (id) AS (
+        #{relation_with_exists_query.select(:id).to_sql}
+      )
+      UPDATE projects SET has_external_issue_tracker = false WHERE id IN (SELECT id FROM project_ids_to_update)
+      SQL
+    end
+
+    # Drop the temporary index.
+    remove_concurrent_index_by_name(:projects, TMP_INDEX_NAME)
+  end
+end