From fdf2fc2a01dc987d8059e279274913b989335786 Mon Sep 17 00:00:00 2001
From: Michael Becker <11881043-wandering_person@users.noreply.gitlab.com>
Date: Tue, 2 Jan 2024 14:04:39 +0700
Subject: [PATCH] Drop `milestone_id` Column From Vulnerabilities

`milestone_id` is an ignored column ([source][0])

This commit drops the column for step 2 of the
[3-MR drop column process][1]

Using the command `\d+ vulnerabilities` on [postgres.ai][2]
indicates **there ARE indexes and constraints**

As such, this migration includes adding back the index and FK
constraint in the `down` method

Related to https://gitlab.com/gitlab-org/gitlab/-/issues/268154

[0]:https://gitlab.com/gitlab-org/gitlab/-/blob/0ef7ab245a0952c68232b3a3418b673b912b5115/app/models/vulnerability.rb#L8-11
[1]:https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#the-removed-column-has-an-index-or-constraint-that-belongs-to-it
[2]:https://console.postgres.ai

Changelog: removed
---
 ...ilestone_id_column_from_vulnerabilities.rb | 21 +++++++++++++++++++
 db/schema_migrations/20240102065444           |  1 +
 db/structure.sql                              |  6 ------
 ee/spec/models/ee/vulnerability_spec.rb       |  2 +-
 4 files changed, 23 insertions(+), 7 deletions(-)
 create mode 100644 db/post_migrate/20240102065444_remove_milestone_id_column_from_vulnerabilities.rb
 create mode 100644 db/schema_migrations/20240102065444

diff --git a/db/post_migrate/20240102065444_remove_milestone_id_column_from_vulnerabilities.rb b/db/post_migrate/20240102065444_remove_milestone_id_column_from_vulnerabilities.rb
new file mode 100644
index 00000000000000..f74c15a97fb914
--- /dev/null
+++ b/db/post_migrate/20240102065444_remove_milestone_id_column_from_vulnerabilities.rb
@@ -0,0 +1,21 @@
+# frozen_string_literal: true
+
+class RemoveMilestoneIdColumnFromVulnerabilities < Gitlab::Database::Migration[2.2]
+  disable_ddl_transaction!
+
+  milestone '16.8'
+
+  def up
+    with_lock_retries do
+      remove_column :vulnerabilities, :milestone_id
+    end
+  end
+
+  def down
+    add_column :vulnerabilities, :milestone_id, :bigint unless column_exists?(:vulnerabilities, :milestone_id)
+
+    # Add back index and constraint that were dropped in `up`
+    add_concurrent_index(:vulnerabilities, :milestone_id)
+    add_concurrent_foreign_key(:vulnerabilities, :milestones, column: :milestone_id, on_delete: :nullify)
+  end
+end
diff --git a/db/schema_migrations/20240102065444 b/db/schema_migrations/20240102065444
new file mode 100644
index 00000000000000..5f5db30dc4e7c5
--- /dev/null
+++ b/db/schema_migrations/20240102065444
@@ -0,0 +1 @@
+e3fce3184c7e9c3e84e73caeaee94ab14dafc46c046e8477d5762b3d41a11a02
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 3133cfe6c6ed38..37f769f427fcf0 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -25200,7 +25200,6 @@ ALTER SEQUENCE vs_code_settings_id_seq OWNED BY vs_code_settings.id;
 
 CREATE TABLE vulnerabilities (
     id bigint NOT NULL,
-    milestone_id bigint,
     epic_id bigint,
     project_id bigint NOT NULL,
     author_id bigint NOT NULL,
@@ -35687,8 +35686,6 @@ CREATE INDEX index_vulnerabilities_on_epic_id ON vulnerabilities USING btree (ep
 
 CREATE INDEX index_vulnerabilities_on_finding_id ON vulnerabilities USING btree (finding_id);
 
-CREATE INDEX index_vulnerabilities_on_milestone_id ON vulnerabilities USING btree (milestone_id);
-
 CREATE INDEX index_vulnerabilities_on_project_id_and_id ON vulnerabilities USING btree (project_id, id);
 
 CREATE INDEX index_vulnerabilities_on_project_id_and_state_and_severity ON vulnerabilities USING btree (project_id, state, severity);
@@ -38056,9 +38053,6 @@ ALTER TABLE ONLY project_pages_metadata
 ALTER TABLE ONLY group_deletion_schedules
     ADD CONSTRAINT fk_11e3ebfcdd FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
 
-ALTER TABLE ONLY vulnerabilities
-    ADD CONSTRAINT fk_131d289c65 FOREIGN KEY (milestone_id) REFERENCES milestones(id) ON DELETE SET NULL;
-
 ALTER TABLE ONLY approval_group_rules
     ADD CONSTRAINT fk_1485c451e3 FOREIGN KEY (scan_result_policy_id) REFERENCES scan_result_policies(id) ON DELETE CASCADE;
 
diff --git a/ee/spec/models/ee/vulnerability_spec.rb b/ee/spec/models/ee/vulnerability_spec.rb
index fe19b0f9b7da0a..d49fba31689548 100644
--- a/ee/spec/models/ee/vulnerability_spec.rb
+++ b/ee/spec/models/ee/vulnerability_spec.rb
@@ -29,7 +29,7 @@
   let_it_be(:vulnerability) { create(:vulnerability, :sast, :confirmed, :low, :with_state_transition, project: project) }
   let_it_be(:finding) { create(:vulnerabilities_finding, vulnerability: vulnerability) }
 
-  it { is_expected.to have_locked_schema('c44ba975c0533ef0f7e338c8acbf8813427a2a0302180f28243a3853162bb6fa').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
+  it { is_expected.to have_locked_schema('a23fdde3fd14fefb7b51dd9bb5cdb0da62e0250d94c60f6b4e6576b223a8dffe').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
 
   it_behaves_like 'vulnerability and finding shared examples' do
     let(:transformer_method) { :itself }
-- 
GitLab