Ignore sbom_occurrences.vulnerabilities and add other columns

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.

• I have evaluated the MR acceptance checklist for this MR.