blob: b3023a1f91594872e7261fa8116ebe7210efa747 (
plain)
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
|
# frozen_string_literal: true
class AddHasIssuesOnVulnerabilityReadsTrigger < Gitlab::Database::Migration[1.0]
include Gitlab::Database::SchemaHelpers
TRIGGER_ON_INSERT = 'trigger_update_has_issues_on_vulnerability_issue_links_update'
INSERT_FUNCTION_NAME = 'set_has_issues_on_vulnerability_reads'
TRIGGER_ON_DELETE = 'trigger_update_has_issues_on_vulnerability_issue_links_delete'
DELETE_FUNCTION_NAME = 'unset_has_issues_on_vulnerability_reads'
def up
create_trigger_function(INSERT_FUNCTION_NAME, replace: true) do
<<~SQL
UPDATE
vulnerability_reads
SET
has_issues = true
WHERE
vulnerability_id = NEW.vulnerability_id AND has_issues IS FALSE;
RETURN NULL;
SQL
end
execute(<<~SQL)
CREATE OR REPLACE FUNCTION #{DELETE_FUNCTION_NAME}()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
has_issue_links integer;
BEGIN
PERFORM 1
FROM
vulnerability_reads
WHERE
vulnerability_id = OLD.vulnerability_id
FOR UPDATE;
SELECT 1 INTO has_issue_links FROM vulnerability_issue_links WHERE vulnerability_id = OLD.vulnerability_id LIMIT 1;
IF (has_issue_links = 1) THEN
RETURN NULL;
END IF;
UPDATE
vulnerability_reads
SET
has_issues = false
WHERE
vulnerability_id = OLD.vulnerability_id;
RETURN NULL;
END
$$;
SQL
execute(<<~SQL)
CREATE TRIGGER #{TRIGGER_ON_INSERT}
AFTER INSERT ON vulnerability_issue_links
FOR EACH ROW
EXECUTE FUNCTION #{INSERT_FUNCTION_NAME}();
SQL
execute(<<~SQL)
CREATE TRIGGER #{TRIGGER_ON_DELETE}
AFTER DELETE ON vulnerability_issue_links
FOR EACH ROW
EXECUTE FUNCTION #{DELETE_FUNCTION_NAME}();
SQL
end
def down
drop_trigger(:vulnerability_issue_links, TRIGGER_ON_INSERT)
drop_function(INSERT_FUNCTION_NAME)
drop_trigger(:vulnerability_issue_links, TRIGGER_ON_DELETE)
drop_function(DELETE_FUNCTION_NAME)
end
end
|
