Implement group-level dependency list export API
What does this MR do and why?
This MR introduces a new API endpoint to create group-level dependency list exports.
Database review
This MR introduces a new SQL query to pass through the sbom_occurrences
table for a given group.
Lower bound SQL query
SELECT
"sbom_occurrences"."id"
FROM
"sbom_occurrences"
WHERE
"sbom_occurrences"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))))
ORDER BY
"sbom_occurrences"."id" ASC
LIMIT 1 OFFSET 0
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/19547/commands/64111
Iteration SQL query
SELECT
"sbom_occurrences"."id"
FROM
"sbom_occurrences"
WHERE
"sbom_occurrences"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))))
AND "sbom_occurrences"."id" >= 277
ORDER BY
"sbom_occurrences"."id" ASC
LIMIT 1 OFFSET 1000
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/19547/commands/64112
Related to #408838 (closed)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mehmet Emin INAC