1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
# frozen_string_literal: true
class UpdateInsertOrUpdateVulnerabilityReadsFunction < Gitlab::Database::Migration[2.0]
FUNCTION_NAME = 'insert_or_update_vulnerability_reads'
enable_lock_retries!
def up
execute(<<~SQL)
CREATE OR REPLACE FUNCTION #{FUNCTION_NAME}() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
severity smallint;
state smallint;
report_type smallint;
resolved_on_default_branch boolean;
present_on_default_branch 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;
INSERT INTO vulnerability_reads (vulnerability_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, 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
end
def down
execute(<<~SQL)
CREATE OR REPLACE FUNCTION #{FUNCTION_NAME}() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
severity smallint;
state smallint;
report_type smallint;
resolved_on_default_branch 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
INTO
severity, state, report_type, resolved_on_default_branch
FROM
vulnerabilities
WHERE
vulnerabilities.id = NEW.vulnerability_id;
INSERT INTO vulnerability_reads (vulnerability_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, 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
end
end
|
