Skip to content

Sync all MRs in a namespace with Jira Connect

Andy Schoenen requested to merge jira_connect_sync_namespace_worker into master

What does this MR do?

First iteration to sync data with Jira connect upon adding a new namespace is added. It will trigger a job for each project in the namespace to sync merge requests. Branches and commits can be added in a later iteration.

According to the Jira API documentation we can sync up to 400 MRs per requests (This information can be found in the documentation when clicking on the "Repository" type in the "BODY PARAMETERS" section). To keep it simple, we will just sync the 400 most recent MRs.

Related issues

Database

with_jira_issue_keys queue

We are adding a new scope with_jira_issue_keys to MergeRequest for filtering merge requests that have a reference to a Jira issue in title or description.

It will produce the following query:

SELECT "merge_requests".* 
FROM "merge_requests" 
WHERE (merge_requests.title ~ '[A-Z][A-Z_0-9]+-\d+' OR merge_requests.description ~ '[A-Z][A-Z_0-9]+-\d+')

The scope will be used to find the most recent 400 merge requests on a project that reference Jira issues. The query for this will be:

SELECT merge_requests.* 
FROM merge_requests 
WHERE merge_requests.target_project_id = 278964 
AND (merge_requests.title ~ '[A-Z][A-Z_0-9]+-\d+' 
OR merge_requests.description ~ '[A-Z][A-Z_0-9]+-\d+') 
ORDER BY merge_requests.id 
DESC LIMIT 400

I tried it with the gitlab-org/gitlab project https://explain.depesz.com/s/IaYr but it turned out to be very slow (1.550 min). After adding partial indexes suggested by @a_akgun it is significantly faster: https://explain.depesz.com/s/Z4InV

AddMergeRequestJiraReferenceIndexes migration

Migration output:

Up:

== 20201106082723 AddMergeRequestJiraReferenceIndexes: migrating ==============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:target_project_id, :iid], {:name=>"index_merge_requests_on_target_project_id_and_iid_jira_title", :using=>:btree, :where=>"(merge_requests.title)::text ~ '[A-Z][A-Z_0-9]+-\\d+'::text", :algorithm=>:concurrently})
   -> 0.0102s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:merge_requests, [:target_project_id, :iid], {:name=>"index_merge_requests_on_target_project_id_and_iid_jira_title", :using=>:btree, :where=>"(merge_requests.title)::text ~ '[A-Z][A-Z_0-9]+-\\d+'::text", :algorithm=>:concurrently})
   -> 0.0095s
-- execute("RESET ALL")
   -> 0.0001s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:target_project_id, :iid], {:name=>"index_merge_requests_on_target_project_id_iid_jira_description", :using=>:btree, :where=>"(merge_requests.description)::text ~ '[A-Z][A-Z_0-9]+-\\d+'::text", :algorithm=>:concurrently})
   -> 0.0070s
-- add_index(:merge_requests, [:target_project_id, :iid], {:name=>"index_merge_requests_on_target_project_id_iid_jira_description", :using=>:btree, :where=>"(merge_requests.description)::text ~ '[A-Z][A-Z_0-9]+-\\d+'::text", :algorithm=>:concurrently})
   -> 0.0022s
== 20201106082723 AddMergeRequestJiraReferenceIndexes: migrated (0.0299s) =====

Down:

== 20201106082723 AddMergeRequestJiraReferenceIndexes: reverting ==============
-- transaction_open?()
   -> 0.0000s
-- indexes(:merge_requests)
   -> 0.0105s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"index_merge_requests_on_target_project_id_and_iid_jira_title"})
   -> 0.0047s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- indexes(:merge_requests)
   -> 0.0067s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"index_merge_requests_on_target_project_id_iid_jira_description"})
   -> 0.0018s
== 20201106082723 AddMergeRequestJiraReferenceIndexes: reverted (0.0247s) =====

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Andy Schoenen

Merge request reports