Update software license policies table

What does this MR do and why?

This MR updates the software_license_policies table to support the custom software license. The changes on this MR will be used by MR !153461 (closed).

Related to #441078 (closed)

Migrations

20240501193623_add_custom_software_licenses_id_to_software_license_policies.rb

up

main: == 20240501193623 AddCustomSoftwareLicensesIdToSoftwareLicensePolicies: migrating 
main: -- add_column(:software_license_policies, :custom_software_license_id, :bigint, {:null=>true, :if_not_exists=>true})
main:    -> 0.0366s
main: == 20240501193623 AddCustomSoftwareLicensesIdToSoftwareLicensePolicies: migrated (0.0458s) 

down

main: == [advisory_lock_connection] object_id: 124260, pg_backend_pid: 88365
main: == 20240501193623 AddCustomSoftwareLicensesIdToSoftwareLicensePolicies: reverting 
main: -- remove_column(:software_license_policies, :custom_software_license_id, {:if_exists=>true})
main:    -> 0.0206s
main: == 20240501193623 AddCustomSoftwareLicensesIdToSoftwareLicensePolicies: reverted (0.0344s) 

db/post_migrate/20240501191302_remove_not_null_from_software_license_policies_software_licence_id_column.rb

up

main: == 20240501191302 RemoveNotNullFromSoftwareLicensePoliciesSoftwareLicenceIdColumn: migrating 
main: -- change_column_null(:software_license_policies, :software_license_id, true)
main:    -> 0.0011s
main: == 20240501191302 RemoveNotNullFromSoftwareLicensePoliciesSoftwareLicenceIdColumn: migrated (0.0047s) 

down

main: == 20240501191302 RemoveNotNullFromSoftwareLicensePoliciesSoftwareLicenceIdColumn: reverting 
main: -- change_column_null(:software_license_policies, :software_license_id, false)
main:    -> 0.0046s
main: == 20240501191302 RemoveNotNullFromSoftwareLicensePoliciesSoftwareLicenceIdColumn: reverted (0.0127s) 

db/post_migrate/20240501202252_add_software_license_existence_constraint_to_software_license_policies.rb

up

main: == 20240501202252 AddSoftwareLicenseExistenceConstraintToSoftwareLicensePolicies: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE software_license_policies\nADD CONSTRAINT check_9ba23ae4c3\nCHECK ( num_nonnulls(custom_software_license_id, software_license_id) = 1 )\nNOT VALID;\n")
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE software_license_policies VALIDATE CONSTRAINT check_9ba23ae4c3;")
main:    -> 0.0027s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240501202252 AddSoftwareLicenseExistenceConstraintToSoftwareLicensePolicies: migrated (0.0694s) 

down

main: == 20240501202252 AddSoftwareLicenseExistenceConstraintToSoftwareLicensePolicies: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE software_license_policies\n            DROP CONSTRAINT IF EXISTS check_9ba23ae4c3\n")
main:    -> 0.0008s
main: == 20240501202252 AddSoftwareLicenseExistenceConstraintToSoftwareLicensePolicies: reverted (0.0523s)

db/post_migrate/20240506152601_add_custom_software_license_fk_to_software_license_policies.rb

up

main: == 20240506152601 AddCustomSoftwareLicenseFkToSoftwareLicensePolicies: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0010s
main: -- index_exists?(:software_license_policies, :custom_software_license_id, {:name=>"i_software_license_policies_on_custom_software_license_id", :algorithm=>:concurrently})
main:    -> 0.0052s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:software_license_policies, :custom_software_license_id, {:name=>"i_software_license_policies_on_custom_software_license_id", :algorithm=>:concurrently})
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0008s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE software_license_policies ADD CONSTRAINT fk_74f6d8328a FOREIGN KEY (custom_software_license_id) REFERENCES custom_software_licenses (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0021s
main: -- execute("ALTER TABLE software_license_policies VALIDATE CONSTRAINT fk_74f6d8328a;")
main:    -> 0.0056s
main: == 20240506152601 AddCustomSoftwareLicenseFkToSoftwareLicensePolicies: migrated (0.0806s) 

down

main: -- remove_foreign_key(:software_license_policies, {:column=>:custom_software_license_id})
main:    -> 0.0032s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- indexes(:software_license_policies)
main:    -> 0.0025s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:software_license_policies, {:algorithm=>:concurrently, :name=>"i_software_license_policies_on_custom_software_license_id"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240506152601 AddCustomSoftwareLicenseFkToSoftwareLicensePolicies: reverted (0.0521s) 

db/post_migrate/20240506211629_add_unique_index_to_software_license_policies.rb

up

main: == 20240506211629 AddUniqueIndexToSoftwareLicensePolicies: migrating ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:software_license_policies, [:project_id, :custom_software_license_id, :scan_result_policy_id], {:name=>"idx_software_license_policies_unique_on_custom_license_project", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0054s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- add_index(:software_license_policies, [:project_id, :custom_software_license_id, :scan_result_policy_id], {:name=>"idx_software_license_policies_unique_on_custom_license_project", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20240506211629 AddUniqueIndexToSoftwareLicensePolicies: migrated (0.0291s) =

down

main: == 20240506211629 AddUniqueIndexToSoftwareLicensePolicies: reverting ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0152s
main: -- indexes(:software_license_policies)
main:    -> 0.0059s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:software_license_policies, {:algorithm=>:concurrently, :name=>"idx_software_license_policies_unique_on_custom_license_project"})
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240506211629 AddUniqueIndexToSoftwareLicensePolicies: reverted (0.0421s) =

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.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.