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
orMergeRequest
records matching with a date range query (start_event
andend_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
andRecords
are using the base query provided by theDataCollector
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
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
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)
Edited by Adam Hegyi