blob: 845c3c01a2ac399fe3c0d2248121e7a62757d5d7 (
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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
# frozen_string_literal: true
class RenameServicesToIntegrations < Gitlab::Database::Migration[1.0]
include Gitlab::Database::SchemaHelpers
enable_lock_retries!
# Function and trigger names match those migrated in:
# - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/49916
# - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/51852
WIKI_FUNCTION_NAME = 'set_has_external_wiki'
TRACKER_FUNCTION_NAME = 'set_has_external_issue_tracker'
WIKI_TRIGGER_ON_INSERT_NAME = 'trigger_has_external_wiki_on_insert'
WIKI_TRIGGER_ON_UPDATE_NAME = 'trigger_has_external_wiki_on_update'
WIKI_TRIGGER_ON_DELETE_NAME = 'trigger_has_external_wiki_on_delete'
TRACKER_TRIGGER_ON_INSERT_NAME = 'trigger_has_external_issue_tracker_on_insert'
TRACKER_TRIGGER_ON_UPDATE_NAME = 'trigger_has_external_issue_tracker_on_update'
TRACKER_TRIGGER_ON_DELETE_NAME = 'trigger_has_external_issue_tracker_on_delete'
ALL_TRIGGERS = [
WIKI_TRIGGER_ON_INSERT_NAME,
WIKI_TRIGGER_ON_UPDATE_NAME,
WIKI_TRIGGER_ON_DELETE_NAME,
TRACKER_TRIGGER_ON_INSERT_NAME,
TRACKER_TRIGGER_ON_UPDATE_NAME,
TRACKER_TRIGGER_ON_DELETE_NAME
].freeze
def up
execute('LOCK services IN ACCESS EXCLUSIVE MODE')
drop_all_triggers(:services)
rename_table_safely(:services, :integrations)
recreate_all_triggers(:integrations)
end
def down
execute('LOCK integrations IN ACCESS EXCLUSIVE MODE')
drop_all_triggers(:integrations)
undo_rename_table_safely(:services, :integrations)
recreate_all_triggers(:services)
end
private
def drop_all_triggers(table_name)
ALL_TRIGGERS.each do |trigger_name|
drop_trigger(table_name, trigger_name)
end
end
def recreate_all_triggers(table_name)
wiki_create_insert_trigger(table_name)
wiki_create_update_trigger(table_name)
wiki_create_delete_trigger(table_name)
tracker_replace_trigger_function(table_name)
tracker_create_insert_trigger(table_name)
tracker_create_update_trigger(table_name)
tracker_create_delete_trigger(table_name)
end
def wiki_create_insert_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{WIKI_TRIGGER_ON_INSERT_NAME}
AFTER INSERT ON #{table_name}
FOR EACH ROW
WHEN (NEW.active = TRUE AND NEW.type = 'ExternalWikiService' AND NEW.project_id IS NOT NULL)
EXECUTE FUNCTION #{WIKI_FUNCTION_NAME}();
SQL
end
def wiki_create_update_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{WIKI_TRIGGER_ON_UPDATE_NAME}
AFTER UPDATE ON #{table_name}
FOR EACH ROW
WHEN (NEW.type = 'ExternalWikiService' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)
EXECUTE FUNCTION #{WIKI_FUNCTION_NAME}();
SQL
end
def wiki_create_delete_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{WIKI_TRIGGER_ON_DELETE_NAME}
AFTER DELETE ON #{table_name}
FOR EACH ROW
WHEN (OLD.type = 'ExternalWikiService' AND OLD.project_id IS NOT NULL)
EXECUTE FUNCTION #{WIKI_FUNCTION_NAME}();
SQL
end
# Using `replace: true` to rewrite the existing function
def tracker_replace_trigger_function(table_name)
create_trigger_function(TRACKER_FUNCTION_NAME, replace: true) do
<<~SQL
UPDATE projects SET has_external_issue_tracker = (
EXISTS
(
SELECT 1
FROM #{table_name}
WHERE project_id = COALESCE(NEW.project_id, OLD.project_id)
AND active = TRUE
AND category = 'issue_tracker'
)
)
WHERE projects.id = COALESCE(NEW.project_id, OLD.project_id);
RETURN NULL;
SQL
end
end
def tracker_create_insert_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{TRACKER_TRIGGER_ON_INSERT_NAME}
AFTER INSERT ON #{table_name}
FOR EACH ROW
WHEN (NEW.category = 'issue_tracker' AND NEW.active = TRUE AND NEW.project_id IS NOT NULL)
EXECUTE FUNCTION #{TRACKER_FUNCTION_NAME}();
SQL
end
def tracker_create_update_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{TRACKER_TRIGGER_ON_UPDATE_NAME}
AFTER UPDATE ON #{table_name}
FOR EACH ROW
WHEN (NEW.category = 'issue_tracker' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)
EXECUTE FUNCTION #{TRACKER_FUNCTION_NAME}();
SQL
end
def tracker_create_delete_trigger(table_name)
execute(<<~SQL)
CREATE TRIGGER #{TRACKER_TRIGGER_ON_DELETE_NAME}
AFTER DELETE ON #{table_name}
FOR EACH ROW
WHEN (OLD.category = 'issue_tracker' AND OLD.active = TRUE AND OLD.project_id IS NOT NULL)
EXECUTE FUNCTION #{TRACKER_FUNCTION_NAME}();
SQL
end
end
|