Implement Label based CA event query (!19871) · Merge requests · GitLab.org / GitLab

Adam Hegyi requested to merge 12196-label-based-ca-query into master Nov 11, 2019

What does this MR do?

This MR enables exposes label event based cycle analytics stages via API and implements the query backend:

Implement query to join resource_label_events table.
Extend the event serializer to show if an event is label based.
Expose the label in a stage if the event is label based.
Add missing event pairs for Issue events.

Example: Show the median duration between issue creation time (event) and workflowproduction label added. Also list the relevant Issue records.

To get the label events (add, remove), we join the resource_label_events table. More info and explanation for the query is here: ee/lib/gitlab/analytics/cycle_analytics/stage_events/label_based_stage_event.rb.

What is Cycle Analytics

Related issue: https://gitlab.com/gitlab-org/gitlab-ee/issues/12196
Stages can be defined for a Project (ProjectStage) or a Group (GroupStage), often referred as parent.
Stage is used to calculate metrics:
- 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) (already implemented and merged)
- Extract the median duration. (already implemented and merged)
- Extract the list of records relevant to the date range. (already implemented and merged)
- Persist label based stages. (already implemented and merged: !16118 (merged))

Try it out

Before start, make sure you execute Feature.enable(:cycle_analytics) and make sure that you're logged in on your local dev env.

There is a new seed file that creates a few labels and records for customizable cycle analytics feature:

SEED_CUSTOMIZABLE_CYCLE_ANALYTICS=true bundle exec rake db:seed_fu FILTER=customizable_cycle_analytics

Open the following URL in your browser, it will show the configured custom stages for a group:

/-/analytics/cycle_analytics/stages?group_id=$YOUR_GROUP_FULL_PATH

Find one of the stages (stages key) with label (example: IssueCreated-IssueInDevLabelAdded), find its id attribute, and visit:

Median:

/-/analytics/cycle_analytics/stages/$STAGE_ID/median.json?group_id=$YOUR_GROUP_FULL_PATH&created_after=2019-05-01&created_before=2020-12-01

Records:

/-/analytics/cycle_analytics/stages/$STAGE_ID/records.json?group_id=$YOUR_GROUP_FULL_PATH&created_after=2019-05-01&created_before=2020-12-01

Queries

Issue query

Querying Issues
Start Event Label: ~"workflow::In dev"
End Event Label: workflowproduction

Median:

SELECT EXTRACT(EPOCH
               FROM percentile_cont(0.5) WITHIN GROUP(
                                                      ORDER BY issue_label_added_9b68a4358d.created_at - issue_label_added_e12654b5e9.created_at)) AS median
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."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 "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."issue_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."issue_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 2526320) issue_label_added_e12654b5e9 ON issue_label_added_e12654b5e9.model_id = issues.id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."issue_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."issue_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11838120) issue_label_added_9b68a4358d ON issue_label_added_9b68a4358d.model_id = issues.id
WHERE "issues"."created_at" >= '2019-07-01'
  AND "issues"."created_at" <= '2020-11-01'
  AND (issue_label_added_e12654b5e9.row_id = 1)
  AND (issue_label_added_9b68a4358d.row_id = 1)
  AND issue_label_added_9b68a4358d.created_at >= issue_label_added_e12654b5e9.created_at

Plan

Records:

SELECT "issues"."title",
       "issues"."iid",
       "issues"."id",
       "issues"."created_at",
       "issues"."author_id",
       "issues"."project_id",
       EXTRACT(EPOCH
               FROM issue_label_added_698d59573d.created_at - issue_label_added_f2fdb17ed8.created_at) AS total_time
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_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 "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."issue_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."issue_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 2526320) issue_label_added_f2fdb17ed8 ON issue_label_added_f2fdb17ed8.model_id = issues.id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."issue_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."issue_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11838120) issue_label_added_698d59573d ON issue_label_added_698d59573d.model_id = issues.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))
       OR projects.visibility_level IN (0,
                                        10,
                                        20))
  AND ("project_features"."issues_access_level" > 0
       OR "project_features"."issues_access_level" IS NULL)
  AND "issues"."created_at" >= '2019-07-01'
  AND "issues"."created_at" <= '2020-11-01'
  AND (issue_label_added_f2fdb17ed8.row_id = 1)
  AND (issue_label_added_698d59573d.row_id = 1)
  AND issue_label_added_698d59573d.created_at >= issue_label_added_f2fdb17ed8.created_at
ORDER BY issue_label_added_698d59573d.created_at DESC
LIMIT 20;

Plan

Merge Request Query

Querying Merge requests
Start Event Label: databasereview pending
End Event Label: databaseapproved

Median:

SELECT EXTRACT(EPOCH
               FROM percentile_cont(0.5) WITHIN GROUP(
                                                      ORDER BY merge_request_label_added_31de57a575.created_at - merge_request_label_added_a8678d12b5.created_at)) AS median
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_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 "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."merge_request_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."merge_request_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11108304) merge_request_label_added_a8678d12b5 ON merge_request_label_added_a8678d12b5.model_id = merge_requests.id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."merge_request_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."merge_request_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11108306) merge_request_label_added_31de57a575 ON merge_request_label_added_31de57a575.model_id = merge_requests.id
WHERE "merge_requests"."created_at" >= '2019-07-01'
  AND "merge_requests"."created_at" <= '2020-12-01'
  AND (merge_request_label_added_a8678d12b5.row_id = 1)
  AND (merge_request_label_added_31de57a575.row_id = 1)
  AND merge_request_label_added_31de57a575.created_at >= merge_request_label_added_a8678d12b5.created_at

Plan

Records:

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_label_added_81fb42115b.created_at - merge_request_label_added_3feb028fab.created_at) AS total_time
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_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 "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."merge_request_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."merge_request_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11108304) merge_request_label_added_3feb028fab ON merge_request_label_added_3feb028fab.model_id = merge_requests.id
INNER JOIN
  (SELECT "resource_label_events"."created_at",
          "resource_label_events"."merge_request_id" AS model_id,
          row_number() OVER (PARTITION BY "resource_label_events"."merge_request_id"
                             ORDER BY "resource_label_events"."created_at" ASC) AS row_id
   FROM "resource_label_events"
   WHERE "resource_label_events"."action" = 1
     AND "resource_label_events"."label_id" = 11108306) merge_request_label_added_81fb42115b ON merge_request_label_added_81fb42115b.model_id = merge_requests.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))
       OR projects.visibility_level IN (0,
                                        10,
                                        20))
  AND ("project_features"."merge_requests_access_level" > 0
       OR "project_features"."merge_requests_access_level" IS NULL)
  AND "merge_requests"."created_at" >= '2019-07-01'
  AND "merge_requests"."created_at" <= '2020-12-01'
  AND (merge_request_label_added_3feb028fab.row_id = 1)
  AND (merge_request_label_added_81fb42115b.row_id = 1)
  AND merge_request_label_added_81fb42115b.created_at >= merge_request_label_added_3feb028fab.created_at
ORDER BY merge_request_label_added_81fb42115b.created_at DESC
LIMIT 20

Plan

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability 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 #13157 (closed), which is the BE-specific implementation for #12196 (closed)

Edited Nov 12, 2019 by Dan Jensen

Implement Label based CA event query

What does this MR do?

What is Cycle Analytics

Try it out

Queries

Issue query

Merge Request Query

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports