New created projects could inherit services from groups
What does this MR do?
We can now save integrations that belong to a group !39959 (merged).
When creating/importing a new project, the precedence to create new integrations is: closest integration from ancestors of the project, instance-level integration or template service.
Group-level integrations are under a feature flag group_level_integrations
.
SQL queries
The query is based on two ideas: @lulalala
's alternative of the previous code !32775 (comment 351633123) and the code to find the closest group integration https://gitlab.com/gitlab-org/gitlab/-/blob/23a90108/app/models/service.rb#L244-251, and also using UNION
https://docs.gitlab.com/ee/development/sql.html#use-unions.
SELECT "services".*
FROM (
(SELECT "services".*
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."instance" = TRUE)
UNION
(SELECT "services".*
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."template" = TRUE)
UNION
(SELECT "services".*
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IN
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 3)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id"
FROM "base_and_ancestors" AS "namespaces"))) services
ORDER BY TYPE ASC, array_position(array
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 3)
UNION
(SELECT "namespaces".*
FROM "namespaces", "base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id"
FROM "base_and_ancestors" AS "namespaces")::bigint[], services.group_id),
INSTANCE DESC;
Time: 32.406 ms
- planning: 2.399 ms
- execution: 30.007 ms
- I/O read: 26.199 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 12 (~96.00 KiB) from the buffer pool
- reads: 21 (~168.00 KiB) from the OS file cache, including disk I/O
- dirtied: 14 (~112.00 KiB)
- writes: 0
Query plan: https://explain.depesz.com/s/HsDN
Related to the first checkbox of #242012 (closed) and #244320 (closed)