Add `primary_license_xid` to `sbom_occurrences`

What does this MR do and why?

Add primary_license_xid to sbom_occurrences

This is part of the group-level dependency list page performance epic

We eventually want to add a new query for sorting by license, mirroring the work done in MR 154054, which added the improved query for sorting based on highest_severity

Since licenses are currently stored in a JSON array, we first need to add a primary_license_xid¹ column in order to facilitate the new sort query

We set this new column to set this to the spdx_identifier of the first license

Follow-up work

After the column is added, we can add the new sort as described in the implementation plan

Database

bundle exec rails db:migrate

main: == [advisory_lock_connection] object_id: 126120, pg_backend_pid: 831594
main: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: migrating =============
main: -- add_column(:sbom_occurrences, :primary_license_xid, :text)
main:    -> 0.0009s
main: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: migrated (0.0040s) ====

main: == [advisory_lock_connection] object_id: 126120, pg_backend_pid: 831594
ci: == [advisory_lock_connection] object_id: 126420, pg_backend_pid: 831596
ci: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: migrating =============
ci: -- add_column(:sbom_occurrences, :primary_license_xid, :text)
ci:    -> 0.0007s
ci: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: migrated (0.0087s) ====

ci: == [advisory_lock_connection] object_id: 126420, pg_backend_pid: 831596
main: == [advisory_lock_connection] object_id: 126620, pg_backend_pid: 831599
main: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrating =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT check_659c1f7b68\nCHECK ( char_length(primary_license_xid) <= 255 )\nNOT VALID;\n")
main:    -> 0.0008s
main: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrated (0.0291s) 

main: == [advisory_lock_connection] object_id: 126620, pg_backend_pid: 831599
ci: == [advisory_lock_connection] object_id: 126760, pg_backend_pid: 831601
ci: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrating =
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE sbom_occurrences\nADD CONSTRAINT check_659c1f7b68\nCHECK ( char_length(primary_license_xid) <= 255 )\nNOT VALID;\n")
ci:    -> 0.0008s
ci: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrated (0.0143s) 

ci: == [advisory_lock_connection] object_id: 126760, pg_backend_pid: 831601
main: == [advisory_lock_connection] object_id: 127180, pg_backend_pid: 831604
main: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrating 
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT check_659c1f7b68;")
main:    -> 0.0004s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrated (0.0054s) 

main: == [advisory_lock_connection] object_id: 127180, pg_backend_pid: 831604
ci: == [advisory_lock_connection] object_id: 127300, pg_backend_pid: 831607
ci: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrating 
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE sbom_occurrences VALIDATE CONSTRAINT check_659c1f7b68;")
ci:    -> 0.0005s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: migrated (0.0096s) 

ci: == [advisory_lock_connection] object_id: 127300, pg_backend_pid: 831607

AddPrimaryLicenseIdToSbomOccurrences

bundle exec rails db:migrate:down:main VERSION=20240606033233

bundle exec rails db:migrate:down:main VERSION=20240606033233

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 839654
main: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: reverting =============
main: -- remove_column(:sbom_occurrences, :primary_license_xid, :text)
main:    -> 0.0010s
main: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: reverted (0.0047s) ====

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 839654

bundle exec rails db:migrate:down:ci VERSION=20240606033233

bundle exec rails db:migrate:down:ci VERSION=20240606033233

ci: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 839215
ci: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: reverting =============
ci: -- remove_column(:sbom_occurrences, :primary_license_xid, :text)
ci:    -> 0.0011s
ci: == 20240606033233 AddPrimaryLicenseIdToSbomOccurrences: reverted (0.0095s) ====

ci: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 839215

AddTextLimitOnPrimaryLicenseIdForSbomOccurrences

bundle exec rails db:migrate:down:main VERSION=20240606033234

bundle exec rails db:migrate:down:main VERSION=20240606033234

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 838712
main: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverting =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE sbom_occurrences\n            DROP CONSTRAINT IF EXISTS check_659c1f7b68\n")
main:    -> 0.0009s
main: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverted (0.0099s) 

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 838712

bundle exec rails db:migrate:down:ci VERSION=20240606033234

bundle exec rails db:migrate:down:ci VERSION=20240606033234

ci: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 838153
ci: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverting =
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE sbom_occurrences\n            DROP CONSTRAINT IF EXISTS check_659c1f7b68\n")
ci:    -> 0.0008s
ci: == 20240606033234 AddTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverted (0.0142s) 

ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences

bundle exec rails db:migrate:down:main VERSION=20240605094830

bundle exec rails db:migrate:down:main VERSION=20240605094830

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 837706
main: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverting 
main: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverted (0.0027s) 

main: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 837706

bundle exec rails db:migrate:down:ci VERSION=20240605094830

bundle exec rails db:migrate:down:ci VERSION=20240605094830

ci: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 837210
ci: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverting 
ci: == 20240605094830 ValidateTextLimitOnPrimaryLicenseIdForSbomOccurrences: reverted (0.0091s) 

ci: == [advisory_lock_connection] object_id: 125660, pg_backend_pid: 837210

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.

---

related to: #450922 (closed)

Changelog: added

---

1. *_xid nomenclature indicates this is an external id and not a foreign key. Docs here