Implement Label based CA event query
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 aGroup
(GroupStage
), often referred asparent
. -
Stage
is used to calculate metrics:- Find all the
Issue
orMergeRequest
records matching with a date range query (start_event
andend_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))
- Find all the
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
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;
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
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
Screenshots
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
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)