Skip to content

Ignore sbom_occurrences.vulnerabilities and add other columns

Zamir Martins requested to merge update_sbom_occurrences_vulnerabilities into master

What does this MR do and why?

Ignore sbom_occurrences.vulnerabilities and add sbom_occurrences.highest_severity and sbom_occurrences.vulnerability_count.

It revert the changes added as part of Add vulnerabilities column to sbom_occurrences (!132981 - merged) as a change of direction was proposed as part of this thread.

TLDR; It is a recently added column which hasn't been used yet. The proposal is to change from a jsonb array of hashes into an async load of vulnerability based on the new table sbom_occurrences_vulnerabilities

EE: true Changelog: changed

Related issue: #426122 (closed)

This PoC MR illustrates how this change will fit into the bigger picture.

Migrations

20231031114050

$ bundle exec rails db:migrate:redo:main VERSION=20231031114050
main: == [advisory_lock_connection] object_id: 183680, pg_backend_pid: 46227
main: == 20231031114050 AddHighestSeverityAndVulnerabilityCountIntoSbomOccurrences: reverting
main: -- remove_column(:sbom_occurrences, :vulnerability_count, :integer, {:null=>false, :default=>0})
main:    -> 0.0027s
main: -- remove_column(:sbom_occurrences, :highest_severity, :smallint, {:null=>true})
main:    -> 0.0009s
main: == 20231031114050 AddHighestSeverityAndVulnerabilityCountIntoSbomOccurrences: reverted (0.0081s)

main: == [advisory_lock_connection] object_id: 183680, pg_backend_pid: 46227
main: == [advisory_lock_connection] object_id: 184560, pg_backend_pid: 46499
main: == 20231031114050 AddHighestSeverityAndVulnerabilityCountIntoSbomOccurrences: migrating
main: -- add_column(:sbom_occurrences, :highest_severity, :smallint, {:null=>true})
main:    -> 0.0019s
main: -- add_column(:sbom_occurrences, :vulnerability_count, :integer, {:null=>false, :default=>0})
main:    -> 0.0018s
main: == 20231031114050 AddHighestSeverityAndVulnerabilityCountIntoSbomOccurrences: migrated (0.0094s)

main: == [advisory_lock_connection] object_id: 184560, pg_backend_pid: 46499

20231031164724

bundle exec rails db:migrate:redo:main VERSION=20231031164724
main: == [advisory_lock_connection] object_id: 183680, pg_backend_pid: 46979
main: == 20231031164724 CreateSbomOccurrencesVulnerabilities: reverting =============
main: -- drop_table(:sbom_occurrences_vulnerabilities)
main:    -> 0.0025s
main: == 20231031164724 CreateSbomOccurrencesVulnerabilities: reverted (0.0074s) ====

main: == [advisory_lock_connection] object_id: 183680, pg_backend_pid: 46979
main: == [advisory_lock_connection] object_id: 184480, pg_backend_pid: 47253
main: == 20231031164724 CreateSbomOccurrencesVulnerabilities: migrating =============
main: -- create_table(:sbom_occurrences_vulnerabilities)
main:    -> 0.0115s
main: == 20231031164724 CreateSbomOccurrencesVulnerabilities: migrated (0.1487s) ====

main: == [advisory_lock_connection] object_id: 184480, pg_backend_pid: 47253

20231121152706

$ bundle exec rails db:migrate:redo:main VERSION=20231121152706
main: == [advisory_lock_connection] object_id: 184460, pg_backend_pid: 37729
main: == 20231121152706 AddForeignKeyOnSbomOccurrencesVulnerabilitiesVulnerabilityId: reverting
main: -- remove_foreign_key(:sbom_occurrences_vulnerabilities, {:to_table=>:vulnerabilities, :column=>:vulnerability_id, :on_delete=>:cascade})
main:    -> 0.0772s
main: -- remove_foreign_key(:sbom_occurrences_vulnerabilities, {:to_table=>:sbom_occurrences, :column=>:sbom_occurrence_id, :on_delete=>:cascade})
main:    -> 0.0020s
main: == 20231121152706 AddForeignKeyOnSbomOccurrencesVulnerabilitiesVulnerabilityId: reverted (0.0824s)

main: == [advisory_lock_connection] object_id: 184460, pg_backend_pid: 37729
main: == [advisory_lock_connection] object_id: 190520, pg_backend_pid: 38001
main: == 20231121152706 AddForeignKeyOnSbomOccurrencesVulnerabilitiesVulnerabilityId: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities ADD CONSTRAINT fk_07b81e3a81 FOREIGN KEY (vulnerability_id) REFERENCES vulnerabilities (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0010s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities VALIDATE CONSTRAINT fk_07b81e3a81;")
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities ADD CONSTRAINT fk_c677cb859e FOREIGN KEY (sbom_occurrence_id) REFERENCES sbom_occurrences (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0006s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities VALIDATE CONSTRAINT fk_c677cb859e;")
main:    -> 0.0010s
main: == 20231121152706 AddForeignKeyOnSbomOccurrencesVulnerabilitiesVulnerabilityId: migrated (0.0457s)

main: == [advisory_lock_connection] object_id: 190520, pg_backend_pid: 38001

20231123125750

$ bundle exec rails db:migrate:redo:main VERSION=20231123125750
main: == [advisory_lock_connection] object_id: 184440, pg_backend_pid: 45305
main: == 20231123125750 AddForeignKeyOnSbomOccurrencesVulnerabilitiesOccurrenceId: reverting
main: -- remove_foreign_key(:sbom_occurrences_vulnerabilities, {:to_table=>:sbom_occurrences, :column=>:sbom_occurrence_id, :on_delete=>:cascade})
main:    -> 0.0794s
main: == 20231123125750 AddForeignKeyOnSbomOccurrencesVulnerabilitiesOccurrenceId: reverted (0.0825s)

main: == [advisory_lock_connection] object_id: 184440, pg_backend_pid: 45305
main: == [advisory_lock_connection] object_id: 187780, pg_backend_pid: 45577
main: == 20231123125750 AddForeignKeyOnSbomOccurrencesVulnerabilitiesOccurrenceId: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities ADD CONSTRAINT fk_c677cb859e FOREIGN KEY (sbom_occurrence_id) REFERENCES sbom_occurrences (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0015s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE sbom_occurrences_vulnerabilities VALIDATE CONSTRAINT fk_c677cb859e;")
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20231123125750 AddForeignKeyOnSbomOccurrencesVulnerabilitiesOccurrenceId: migrated (0.0395s)

main: == [advisory_lock_connection] object_id: 187780, pg_backend_pid: 45577

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 Zamir Martins

Merge request reports

Loading