Add ancestors to sbom occurrences based on

What does this MR do and why?

Add ancestors to sbom occurrences based on cyclonedx report dependsOn attribute. This change only affects project level dependency list.

EE: true

Related issue: #441118 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Database

Migrate/Rollback

$ bundle exec rails db:migrate:down:main VERSION=20240212170304
main: == [advisory_lock_connection] object_id: 117940, pg_backend_pid: 82097
main: == 20240212170304 AddAncestorsColumnToSbomOccurrences: reverting ==============
main: -- remove_column(:sbom_occurrences, :ancestors)
main:    -> 0.0017s
main: == 20240212170304 AddAncestorsColumnToSbomOccurrences: reverted (0.0045s) =====
main: == [advisory_lock_connection] object_id: 117940, pg_backend_pid: 82097

$ bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 118120, pg_backend_pid: 82565
main: == 20240212170304 AddAncestorsColumnToSbomOccurrences: migrating ==============
main: -- add_column(:sbom_occurrences, :ancestors, :jsonb, {:default=>[]})
main:    -> 0.0022s
main: == 20240212170304 AddAncestorsColumnToSbomOccurrences: migrated (0.0054s) =====

Ingestion

query plan

INSERT INTO "sbom_occurrences" ("project_id","pipeline_id","component_id","component_version_id","source_id","source_package_id","commit_sha","uuid","package_manager","input_file_path","licenses","component_name","highest_severity","vulnerability_count","ancestors","created_at","updated_at")
VALUES (53160131, 1112748954, 683081, 829581, null, 1, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '250999b2-ea07-566c-83b3-83ca8050cac7', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/alpine-baselayout', NULL, 0, '[]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 3, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '71b14492-6452-5e22-b239-2069dacd8c08', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/alpine-keys', NULL, 0, '[]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 4, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '617afbaa-346c-5bec-9820-50c196c1f664', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/apk-tools', NULL, 0, '[]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 5, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '915980e8-ce32-567b-bb01-ee044c6a2919', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/busybox', NULL, 0, '[{"name":"alpine/alpine-baselayout","version":"3.2.0-r6"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 6, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '4d7077fc-3736-5700-8d41-f8454a393c23', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/ca-certificates-bundle', NULL, 0, '[{"name":"alpine/libtls-standalone","version":"2.9.1-r1"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 7, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', 'ef35cfd8-a21f-5bbe-b113-8005cb669085', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/libc-utils', NULL, 0, '[]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 8, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '9ea9d2e5-af2a-5186-92a2-4d69a7e123b0', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/libcrypto1.1', NULL, 0, '[{"name":"alpine/apk-tools","version":"2.10.5-r1"},{"name":"alpine/libssl1.1","version":"1.1.1g-r0"},{"name":"alpine/libtls-standalone","version":"2.9.1-r1"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 8, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', 'c4ae58f6-0d03-5165-9209-f5fae54bd541', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/libssl1.1', NULL, 0, '[{"name":"alpine/apk-tools","version":"2.10.5-r1"},{"name":"alpine/libtls-standalone","version":"2.9.1-r1"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 22, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '12164bb2-f686-5879-9578-99bc6adf79a8', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/libtls-standalone', NULL, 0, '[{"name":"alpine/ssl_client","version":"1.31.1-r16"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077'), (53160131, 1112748954, 683081, 829581, null, 11, '\x66656631633634646162333032386663333162613664633338626632616235356561323831336336', '3678088e-ee02-5cfe-abdd-b2a5f5c9cb5f', 'apk', 'container-image:alpine:3.12.0', '[]', 'alpine/musl', NULL, 0, '[{"name":"alpine/alpine-baselayout","version":"3.2.0-r6"},{"name":"alpine/apk-tools","version":"2.10.5-r1"},{"name":"alpine/busybox","version":"1.31.1-r16"},{"name":"alpine/libcrypto1.1","version":"1.1.1g-r0"},{"name":"alpine/libssl1.1","version":"1.1.1g-r0"},{"name":"alpine/libtls-standalone","version":"2.9.1-r1"},{"name":"alpine/musl-utils","version":"1.1.24-r8"},{"name":"alpine/scanelf","version":"1.2.6-r0"},{"name":"alpine/ssl_client","version":"1.31.1-r16"},{"name":"alpine/zlib","version":"1.2.11-r3"}]', '2024-02-13 16:07:49.597077', '2024-02-13 16:07:49.597077')
ON CONFLICT ("uuid")
DO UPDATE SET "project_id"=excluded."project_id","pipeline_id"=excluded."pipeline_id","component_id"=excluded."component_id","component_version_id"=excluded."component_version_id","source_id"=excluded."source_id","source_package_id"=excluded."source_package_id","commit_sha"=excluded."commit_sha","package_manager"=excluded."package_manager","input_file_path"=excluded."input_file_path","licenses"=excluded."licenses","component_name"=excluded."component_name","highest_severity"=excluded."highest_severity","vulnerability_count"=excluded."vulnerability_count","ancestors"=excluded."ancestors","updated_at"=excluded."updated_at"
RETURNING id

Screenshots or screen recordings

Results based on this report: gl-sbom-trivy-report.cdx.json

Location with ancestors

Ancestors related popup