1 files changed, 185 insertions, 0 deletions
diff --git a/db/migrate/20230216142836_update_vulnerability_reads_trigger_to_set_has_issue.rb b/db/migrate/20230216142836_update_vulnerability_reads_trigger_to_set_has_issue.rb
new file mode 100644
index 00000000000..73afa176b97
--- /dev/null
+++ b/db/migrate/20230216142836_update_vulnerability_reads_trigger_to_set_has_issue.rb
@@ -0,0 +1,185 @@
+# frozen_string_literal: true
+
+class UpdateVulnerabilityReadsTriggerToSetHasIssue < Gitlab::Database::Migration[2.1]
+  enable_lock_retries!
+
+  def up
+    execute(<<~SQL)
+    CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads()
+      RETURNS trigger
+      LANGUAGE plpgsql
+    AS $$
+    DECLARE
+      severity smallint;
+      state smallint;
+      report_type smallint;
+      resolved_on_default_branch boolean;
+      present_on_default_branch boolean;
+      namespace_id bigint;
+      has_issues boolean;
+    BEGIN
+      IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
+        RETURN NULL;
+      END IF;
+
+      IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
+        RETURN NULL;
+      END IF;
+
+      SELECT
+        vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
+      INTO
+        severity, state, report_type, resolved_on_default_branch, present_on_default_branch
+      FROM
+        vulnerabilities
+      WHERE
+        vulnerabilities.id = NEW.vulnerability_id;
+
+      IF present_on_default_branch IS NOT true THEN
+        RETURN NULL;
+      END IF;
+
+      SELECT
+        projects.namespace_id
+      INTO
+        namespace_id
+      FROM
+        projects
+      WHERE
+        projects.id = NEW.project_id;
+
+      SELECT
+        EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.vulnerability_id)
+      INTO
+        has_issues;
+
+      INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
+        VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint), has_issues)
+        ON CONFLICT(vulnerability_id) DO NOTHING;
+      RETURN NULL;
+    END
+    $$
+    SQL
+
+    execute(<<~SQL)
+    CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability()
+      RETURNS trigger
+      LANGUAGE plpgsql
+    AS $$
+    DECLARE
+      scanner_id bigint;
+      uuid uuid;
+      location_image text;
+      cluster_agent_id text;
+      casted_cluster_agent_id bigint;
+      namespace_id bigint;
+      has_issues boolean;
+    BEGIN
+      SELECT
+        v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id
+      INTO
+        scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id
+      FROM
+        vulnerability_occurrences v_o
+      INNER JOIN projects ON projects.id = v_o.project_id
+      WHERE
+        v_o.vulnerability_id = NEW.id
+      LIMIT 1;
+
+      SELECT
+        EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.id)
+      INTO
+        has_issues;
+
+      INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
+        VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
+        ON CONFLICT(vulnerability_id) DO NOTHING;
+      RETURN NULL;
+    END
+    $$
+    SQL
+  end
+
+  def down
+    execute(<<~SQL)
+      CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
+          LANGUAGE plpgsql
+          AS $$
+      DECLARE
+        severity smallint;
+        state smallint;
+        report_type smallint;
+        resolved_on_default_branch boolean;
+        present_on_default_branch boolean;
+        namespace_id bigint;
+      BEGIN
+        IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
+          RETURN NULL;
+        END IF;
+
+        IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
+          RETURN NULL;
+        END IF;
+
+        SELECT
+          vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
+        INTO
+          severity, state, report_type, resolved_on_default_branch, present_on_default_branch
+        FROM
+           vulnerabilities
+        WHERE
+          vulnerabilities.id = NEW.vulnerability_id;
+
+        IF present_on_default_branch IS NOT true THEN
+          RETURN NULL;
+        END IF;
+
+        SELECT
+          projects.namespace_id
+        INTO
+          namespace_id
+        FROM
+          projects
+        WHERE
+          projects.id = NEW.project_id;
+
+        INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
+          VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint))
+          ON CONFLICT(vulnerability_id) DO NOTHING;
+        RETURN NULL;
+      END
+      $$;
+    SQL
+
+    execute(<<~SQL)
+      CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability() RETURNS trigger
+          LANGUAGE plpgsql
+          AS $$
+      DECLARE
+        scanner_id bigint;
+        uuid uuid;
+        location_image text;
+        cluster_agent_id text;
+        casted_cluster_agent_id bigint;
+        namespace_id bigint;
+      BEGIN
+        SELECT
+          v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id
+        INTO
+          scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id
+        FROM
+          vulnerability_occurrences v_o
+        INNER JOIN projects ON projects.id = v_o.project_id
+        WHERE
+          v_o.vulnerability_id = NEW.id
+        LIMIT 1;
+
+        INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
+          VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
+          ON CONFLICT(vulnerability_id) DO NOTHING;
+        RETURN NULL;
+      END
+      $$;
+    SQL
+  end
+end