From dd05e8fcf7e5d9d834c47a70aee2322065294e84 Mon Sep 17 00:00:00 2001
From: Michael Becker <11881043-wandering_person@users.noreply.gitlab.com>
Date: Thu, 30 Nov 2023 19:28:14 +0700
Subject: [PATCH] Drop `start_date_sourcing_milestone_id` Col From
 Vulnerabilities

`start_date_sourcing_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 | 24 +++++++++++++++++++
 db/schema_migrations/20231218121131           |  1 +
 db/structure.sql                              |  6 -----
 ee/spec/models/ee/vulnerability_spec.rb       |  2 +-
 4 files changed, 26 insertions(+), 7 deletions(-)
 create mode 100644 db/post_migrate/20231218121131_remove_start_date_sourcing_milestone_id_column_from_vulnerabilities.rb
 create mode 100644 db/schema_migrations/20231218121131

diff --git a/db/post_migrate/20231218121131_remove_start_date_sourcing_milestone_id_column_from_vulnerabilities.rb b/db/post_migrate/20231218121131_remove_start_date_sourcing_milestone_id_column_from_vulnerabilities.rb
new file mode 100644
index 00000000000000..1ca04e5aed1164
--- /dev/null
+++ b/db/post_migrate/20231218121131_remove_start_date_sourcing_milestone_id_column_from_vulnerabilities.rb
@@ -0,0 +1,24 @@
+# frozen_string_literal: true
+
+class RemoveStartDateSourcingMilestoneIdColumnFromVulnerabilities < Gitlab::Database::Migration[2.2]
+  disable_ddl_transaction!
+
+  milestone '16.8'
+
+  def up
+    with_lock_retries do
+      remove_column :vulnerabilities, :start_date_sourcing_milestone_id
+    end
+  end
+
+  def down
+    unless column_exists?(:vulnerabilities, :start_date_sourcing_milestone_id)
+      add_column :vulnerabilities, :start_date_sourcing_milestone_id, :bigint
+    end
+
+    # Add back index and constraint that were dropped in `up`
+    add_concurrent_index(:vulnerabilities, :start_date_sourcing_milestone_id)
+    add_concurrent_foreign_key(:vulnerabilities, :milestones, column: :start_date_sourcing_milestone_id,
+      on_delete: :nullify)
+  end
+end
diff --git a/db/schema_migrations/20231218121131 b/db/schema_migrations/20231218121131
new file mode 100644
index 00000000000000..3d26296770507a
--- /dev/null
+++ b/db/schema_migrations/20231218121131
@@ -0,0 +1 @@
+39fe9d82ebdfb8cb16d4ccb3704d0866aba63ff1bfe4087c6ce44f3c98afd0a0
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 3ed12685706725..f3373d1f2f10b8 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -24903,7 +24903,6 @@ CREATE TABLE vulnerabilities (
     title_html text,
     description text,
     description_html text,
-    start_date_sourcing_milestone_id bigint,
     state smallint DEFAULT 1 NOT NULL,
     severity smallint NOT NULL,
     severity_overridden boolean DEFAULT false,
@@ -35105,8 +35104,6 @@ CREATE INDEX index_vulnerabilities_on_project_id_and_state_and_severity ON vulne
 
 CREATE INDEX index_vulnerabilities_on_resolved_by_id ON vulnerabilities USING btree (resolved_by_id);
 
-CREATE INDEX index_vulnerabilities_on_start_date_sourcing_milestone_id ON vulnerabilities USING btree (start_date_sourcing_milestone_id);
-
 CREATE INDEX index_vulnerabilities_on_updated_by_id ON vulnerabilities USING btree (updated_by_id);
 
 CREATE INDEX index_vulnerabilities_project_id_and_id_on_default_branch ON vulnerabilities USING btree (project_id, id) WHERE (present_on_default_branch IS TRUE);
@@ -37842,9 +37839,6 @@ ALTER TABLE p_ci_builds
 ALTER TABLE ONLY approval_group_rules_users
     ADD CONSTRAINT fk_888a0df3b7 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
 
-ALTER TABLE ONLY vulnerabilities
-    ADD CONSTRAINT fk_88b4d546ef FOREIGN KEY (start_date_sourcing_milestone_id) REFERENCES milestones(id) ON DELETE SET NULL;
-
 ALTER TABLE ONLY bulk_import_entities
     ADD CONSTRAINT fk_88c725229f FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE;
 
diff --git a/ee/spec/models/ee/vulnerability_spec.rb b/ee/spec/models/ee/vulnerability_spec.rb
index 02359eb4457fe5..bc1b17d30cebe5 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('f8dc84021fa4394728e9dbb25cfa87daf143ff13d49545c082dfceb65bec65d8').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
+  it { is_expected.to have_locked_schema('669e44a7050fb809ab322ace933868498d6636bd8e11b8bbb959befc53ea082a').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
 
   it_behaves_like 'vulnerability and finding shared examples' do
     let(:transformer_method) { :itself }
-- 
GitLab