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
|
# frozen_string_literal: true
module Gitlab
module BackgroundMigration
# Backfills resource_link_events from system_note_metadata and notes records
class BackfillResourceLinkEvents < BatchedMigrationJob
operation_name :backfill_resource_link_events
feature_category :team_planning
# AR model for resource_link_events inlined
class ResourceLinkEvent < ApplicationRecord
self.table_name = 'resource_link_events'
enum action: {
add: 1,
remove: 2
}
end
scope_to ->(relation) { relation.where("action='relate_to_parent' OR action='unrelate_from_parent'") }
def perform
each_sub_batch do |sub_batch|
values_subquery = resource_link_event_values_query(sub_batch.select(:id).to_sql)
connection.execute(<<~SQL)
INSERT INTO resource_link_events (action, issue_id, child_work_item_id, user_id, created_at, system_note_metadata_id)
#{values_subquery}
ON CONFLICT (system_note_metadata_id) DO NOTHING;
SQL
end
end
def resource_link_event_values_query(ids_subquery)
<<~SQL
SELECT
CASE WHEN system_note_metadata.action='relate_to_parent' THEN #{ResourceLinkEvent.actions[:add]}
ELSE #{ResourceLinkEvent.actions[:remove]}
END AS action,
parent_issues.id AS issue_id,
notes.noteable_id AS child_work_item_id,
notes.author_id AS user_id,
system_note_metadata.created_at AS created_at,
system_note_metadata.id AS system_note_metadata_id
FROM system_note_metadata
INNER JOIN notes ON system_note_metadata.note_id = notes.id
INNER JOIN issues as work_items ON work_items.id = notes.noteable_id,
LATERAL (
-- This lateral join searches for the id of the parent issue.
--
-- When a child work item is added to its parent,
-- "relate_to_parent" is recorded as `system_note_metadata.action`
-- and a note records to which parent the child work item is added e.g, "added #1 (iid) as parent".
--
-- Based on the iid of the parent extracted from the note and using the child work item's project id,
-- we can find out the id of the parent issue.
SELECT issues.id
FROM issues
WHERE
issues.project_id = work_items.project_id
AND issues.iid = CASE WHEN system_note_metadata.action='relate_to_parent' THEN substring(notes.note from 'added #(\\d+) as parent')::bigint
ELSE substring(notes.note from 'removed parent \\S+ #(\\d+)')::bigint
END
) parent_issues
WHERE
system_note_metadata.id IN (#{ids_subquery})
SQL
end
end
end
end
|
