Skip to content

Filter dependencies by license

mo khan requested to merge mokhax/422087/query-by-license into master

What does this MR do and why?

This change adds support for a &licenses[]= query string parameter that can be used to filter dependencies by 1 or more SPDX identifiers. This change is gated by the group_level_filtering feature flag and only available to group hierarchies that are below a certain threshold.

#422087 (closed)

WITH "our_occurrences" AS MATERIALIZED (
  SELECT DISTINCT ON (sbom_occurrences.component_version_id) sbom_occurrences.*,
    COUNT(sbom_occurrences.id) OVER (PARTITION BY sbom_occurrences.component_version_id) AS occurrence_count,
    COUNT(sbom_occurrences.project_id) OVER (PARTITION BY sbom_occurrences.component_version_id) AS project_count
  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 "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
  )
  AND "sbom_occurrences"."component_version_id" IS NOT NULL
  ORDER BY 
    "sbom_occurrences"."component_version_id" DESC,
    "sbom_occurrences"."id" DESC
)
SELECT "sbom_occurrences".*
FROM "our_occurrences" AS "sbom_occurrences" 
WHERE (licenses#>'{0,spdx_identifier}' ?| array['Apache-2.0'] OR licenses#>'{1,spdx_identifier}' ?| array['Apache-2.0'])
ORDER BY sbom_occurrences.id asc 
LIMIT 20 OFFSET 0;
Time: 511.738 ms
  - planning: 15.217 ms
  - execution: 496.521 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 58773 (~459.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22835/commands/73659

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 mo khan

Merge request reports

Loading