Skip to content

Draft: Add models for SBoM ingestion

Brian Williams requested to merge bwill/add-sbom-models into master

What does this MR do and why?

This MR adds initial tables for storing Software Bill of Materials (SBoM) components in GitLab.

We're using the following initial schema which was determined from a research spike.

SBOM_Advisories_Projects__5_

Database

Migrations up

main: == 20220616182001 CreateSbomComponents: migrating =============================
main: -- create_table(:sbom_components, {})
main: -- quote_column_name(:name)
main:    -> 0.0000s
main:    -> 0.0041s
main: == 20220616182001 CreateSbomComponents: migrated (0.0048s) ====================

main: == 20220616182015 CreateSbomComponentVersions: migrating ======================
main: -- create_table(:sbom_component_versions, {})
main: -- quote_column_name(:version)
main:    -> 0.0000s
main:    -> 0.0043s
main: == 20220616182015 CreateSbomComponentVersions: migrated (0.0044s) =============

main: == 20220616182016 CreateSbomSources: migrating ================================
main: -- create_table(:sbom_sources, {})
main:    -> 0.0035s
main: == 20220616182016 CreateSbomSources: migrated (0.0036s) =======================

main: == 20220616182038 CreateSbomOccurrences: migrating ============================
main: -- create_table(:sbom_occurrences, {})
main:    -> 0.0021s
main: == 20220616182038 CreateSbomOccurrences: migrated (0.0023s) ===================

main: == 20220616183240 AddSbomComponentVersionsForeignKeyToSbomOccurrences: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:sbom_occurrences)
main:    -> 0.0034s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT fk_4b88e5b255\nFOREIGN KEY (component_version_id)\nREFERENCES sbom_component_versions (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0014s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT fk_4b88e5b255;")
main:    -> 0.0016s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220616183240 AddSbomComponentVersionsForeignKeyToSbomOccurrences: migrated (0.0185s) 

main: == 20220616183309 AddSbomSourceForeignKeyToSbomOccurrences: migrating =========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:sbom_occurrences)
main:    -> 0.0020s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT fk_c2a5562923\nFOREIGN KEY (source_id)\nREFERENCES sbom_sources (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0015s
main: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT fk_c2a5562923;")
main:    -> 0.0009s
main: == 20220616183309 AddSbomSourceForeignKeyToSbomOccurrences: migrated (0.0077s) 

main: == 20220616183310 AddProjectForeignKeyToSbomOccurrences: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:sbom_occurrences)
main:    -> 0.0020s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT fk_157506c0e2\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0026s
main: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT fk_157506c0e2;")
main:    -> 0.0030s
main: == 20220616183310 AddProjectForeignKeyToSbomOccurrences: migrated (0.0117s) ===

main: == 20220616183327 AddPipelineForeignKeyToSbomOccurrences: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:sbom_occurrences)
main:    -> 0.0025s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT fk_6d35129173\nFOREIGN KEY (pipeline_id)\nREFERENCES ci_pipelines (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0008s
main: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT fk_6d35129173;")
main:    -> 0.0014s
main: == 20220616183327 AddPipelineForeignKeyToSbomOccurrences: migrated (0.0075s) ==

Migrations down

main: == 20220616183327 AddPipelineForeignKeyToSbomOccurrences: reverting ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:sbom_occurrences, {:column=>:pipeline_id})
main:    -> 0.0043s
main: == 20220616183327 AddPipelineForeignKeyToSbomOccurrences: reverted (0.0110s) ==

main: == 20220616183310 AddProjectForeignKeyToSbomOccurrences: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:sbom_occurrences, {:column=>:project_id})
main:    -> 0.0024s
main: == 20220616183310 AddProjectForeignKeyToSbomOccurrences: reverted (0.0036s) ===

main: == 20220616183309 AddSbomSourceForeignKeyToSbomOccurrences: reverting =========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:sbom_occurrences, {:column=>:source_id})
main:    -> 0.0025s
main: == 20220616183309 AddSbomSourceForeignKeyToSbomOccurrences: reverted (0.0046s) 

main: == 20220616183240 AddSbomComponentVersionsForeignKeyToSbomOccurrences: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:sbom_occurrences, {:column=>:component_version_id})
main:    -> 0.0022s
main: == 20220616183240 AddSbomComponentVersionsForeignKeyToSbomOccurrences: reverted (0.0034s) 

main: == 20220616182038 CreateSbomOccurrences: reverting ============================
main: -- drop_table(:sbom_occurrences, {})
main:    -> 0.0015s
main: == 20220616182038 CreateSbomOccurrences: reverted (0.0026s) ===================

main: == 20220616182016 CreateSbomSources: reverting ================================
main: -- drop_table(:sbom_sources, {})
main:    -> 0.0010s
main: == 20220616182016 CreateSbomSources: reverted (0.0011s) =======================

main: == 20220616182015 CreateSbomComponentVersions: reverting ======================
main: -- drop_table(:sbom_component_versions, {})
main:    -> 0.0014s
main: == 20220616182015 CreateSbomComponentVersions: reverted (0.0015s) =============

main: == 20220616182001 CreateSbomComponents: reverting =============================
main: -- drop_table(:sbom_components, {})
main:    -> 0.0077s
main: == 20220616182001 CreateSbomComponents: reverted (0.0079s) ====================

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 Brian Williams

Merge request reports