Skip to content

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.

Edited by Mehmet Emin INAC

Merge request reports