Group Based Cycle Analytics Backend

What does this MR do?

• Extends the CA query backend to handle Group as a parameter. (EE only)
  • Group level Cycle Analytics
• Note 1: this change is not user facing
• Note 2: this MR is part of a bigger feature, previous MR: https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/31713
• Feature docs: https://about.gitlab.com/product/cycle-analytics/
• Related issue: https://gitlab.com/gitlab-org/gitlab-ee/issues/12196

What is Cycle Analytics

• Find all the Issue or MergeRequest records matching with a date range query (start_event and end_event) = Stage.
• Calculate the duration (end_event_time - start_event_time)
• Extract the median duration.
• Extract the list of records relevant to the date range.

High Level Overview

• DataCollector is the high level interface for the feature.
• BaseQueryBuilder is responsible for providing the base query, joining the absolutely necessary tables and do high level filtering.
• An Event (start, end) could alter the query (join additional tables when needed, apply_query_customization). It defines a timestamp expression that will be used for the duration calculation.
• Median and Records are using the base query provided by the DataCollector and do additional query manipulation.

Most of the code is already merged, this MR only extends the implementation a bit.

  Defined for a Group (EE) or for a Project (CE)

  +---------------------+
  | Stages              |
  |   +-------------+   |
  |   |   Stage A   |   |                                 +--------+
  |   |             |   |      +---------------+   +----> | Median |
  |   +-------------+   |      |               |   |      +--------+
  |   | Start Event | +------> | DataCollector | +-+
  |   +-------------+   |      |               |   |      +---------+
  |   | End Event   |   |      +---------------+   +----> | Records |
  |   +-------------+   |                                 +---------+
  |                     |
  |   +-------------+   |
  |   |   Stage B   |   |
  |   |             |   |
  |   +-------------+   |
  |   | Start Event |   |
  |   +-------------+   |
  |   | End Event   |   |
  |   +-------------+   |
  |                     |
  |   ...               |
  +---------------------+

Queries

There are not much changes compared to the project based queries. Only difference is that we need to do a recursive lookup to get the group and its subgroups.

Issue query:

SELECT     "issues"."title",
           "issues"."iid",
           "issues"."id",
           "issues"."created_at",
           "issues"."author_id",
           "issues"."project_id",
           Extract(epoch FROM "issue_metrics"."first_mentioned_in_commit_at" - "issues"."created_at") AS total_time
FROM       "issues"
INNER JOIN "projects"
ON         "projects"."id" = "issues"."project_id"
INNER JOIN "issue_metrics"
ON         "issue_metrics"."issue_id" = "issues"."id"
LEFT JOIN  project_features
ON         projects.id = project_features.project_id
INNER JOIN (WITH recursive "base_and_descendants" AS
           (
                  SELECT "namespaces".*
                  FROM   "namespaces"
                  WHERE  "namespaces"."type" IN ('Group')
                  AND    "namespaces"."id" = 9970
                  UNION
                  SELECT "namespaces".*
                  FROM   "namespaces",
                         "base_and_descendants"
                  WHERE  "namespaces"."type" IN ('Group')
                  AND    "namespaces"."parent_id" = "base_and_descendants"."id")SELECT   "id"
    FROM     "base_and_descendants" AS "namespaces") namespaces
  ON       namespaces.id=projects.namespace_id
  WHERE    (
                    issues.confidential IS NOT true
           OR       (
                             issues.confidential = true
                    AND      (
                                      issues.author_id = 4156052
                             OR       EXISTS
                                      (
                                             SELECT true
                                             FROM   issue_assignees
                                             WHERE  user_id = 4156052
                                             AND    issue_id = issues.id)
                             OR       EXISTS
                                      (
                                             SELECT 1
                                             FROM   "project_authorizations"
                                             WHERE  "project_authorizations"."user_id" = 4156052
                                             AND    (
                                                           project_authorizations.project_id = issues.project_id)
                                             AND    (
                                                           project_authorizations.access_level >= 20)))))
  AND      "projects"."namespace_id" = 9970
  AND      (
                    EXISTS
                    (
                           SELECT 1
                           FROM   "project_authorizations"
                           WHERE  "project_authorizations"."user_id" = 4156052
                           AND    (
                                         project_authorizations.project_id = projects.id)
                           AND    (
                                         project_authorizations.access_level >= 10))
           OR       projects.visibility_level IN (10,20))
  AND      (
                    "project_features"."issues_access_level" IS NULL
           OR       "project_features"."issues_access_level" IN (20,30)
           OR       (
                             "project_features"."issues_access_level" = 10
                    AND      EXISTS
                             (
                                    SELECT 1
                                    FROM   "project_authorizations"
                                    WHERE  "project_authorizations"."user_id" = 4156052
                                    AND    (
                                                  project_authorizations.project_id = projects.id)
                                    AND    (
                                                  project_authorizations.access_level >= 10))))
  AND      "issues"."created_at" >= '2019-07-17 05:30:50.81329970'
  AND      "issue_metrics"."first_mentioned_in_commit_at" >= "issues"."created_at"
  ORDER BY "issue_metrics"."first_mentioned_in_commit_at" DESC limit 20

Plan from PRD

MR query:

 SELECT     "merge_requests"."title",
           "merge_requests"."iid",
           "merge_requests"."id",
           "merge_requests"."created_at",
           "merge_requests"."author_id",
           "merge_requests"."state",
           "merge_requests"."target_project_id",
           Extract(epoch FROM "merge_request_metrics"."merged_at" - "merge_requests"."created_at") AS total_time
FROM       "merge_requests"
INNER JOIN "projects"
ON         "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "merge_request_metrics"
ON         "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
LEFT JOIN  project_features
ON         projects.id = project_features.project_id
INNER JOIN (WITH recursive "base_and_descendants" AS
           (
                  SELECT "namespaces".*
                  FROM   "namespaces"
                  WHERE  "namespaces"."type" IN ('Group')
                  AND    "namespaces"."id" = 9970
                  UNION
                  SELECT "namespaces".*
                  FROM   "namespaces",
                         "base_and_descendants"
                  WHERE  "namespaces"."type" IN ('Group')
                  AND    "namespaces"."parent_id" = "base_and_descendants"."id")SELECT   "id"
    FROM     "base_and_descendants" AS "namespaces") namespaces
  ON       namespaces.id=projects.namespace_id
  WHERE    "projects"."namespace_id" = 9970
  AND      (
                    EXISTS
                    (
                           SELECT 1
                           FROM   "project_authorizations"
                           WHERE  "project_authorizations"."user_id" = 4156052
                           AND    (
                                         project_authorizations.project_id = projects.id)
                           AND    (
                                         project_authorizations.access_level >= 20))
           OR       projects.visibility_level IN (10,20))
  AND      (
                    "project_features"."merge_requests_access_level" IS NULL
           OR       "project_features"."merge_requests_access_level" IN (20,30)
           OR       (
                             "project_features"."merge_requests_access_level" = 10
                    AND      EXISTS
                             (
                                    SELECT 1
                                    FROM   "project_authorizations"
                                    WHERE  "project_authorizations"."user_id" = 4156052
                                    AND    (
                                                  project_authorizations.project_id = projects.id)
                                    AND    (
                                                  project_authorizations.access_level >= 20))))
  AND      "merge_requests"."created_at" >= '2019-07-17 05:31:38.29970802'
  AND      "merge_request_metrics"."merged_at" >= "merge_requests"."created_at"
  ORDER BY "merge_request_metrics"."merged_at" DESC limit 20 

Plan from PRD

Screenshots

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation created/updated or follow-up review issue created
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Performance and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers

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

Closes #12196 (closed)