Add cleanup migration for orphan Software Licenses

What does this MR do and why?

This MR adds a cleanup migration for orphan Software Licenses as proposed in issue #338801 (closed).

This migration deletes software licenses without a spdx_identifier that does not belong to any project.

The expected number of licenses to be deleted can be found here.

Query

DELETE FROM "software_licenses"
WHERE "software_licenses"."spdx_identifier" IS NULL
    AND "software_licenses"."id" NOT IN (
        SELECT
            "software_license_policies"."software_license_id"
        FROM
            "software_license_policies")

Query Plan

Migration

up

main: == 20230313185145 CleanupOrphanSoftwareLicenses: migrating ====================
main: == 20230313185145 CleanupOrphanSoftwareLicenses: migrated (0.0281s) ===========

down

main: == 20230313185145 CleanupOrphanSoftwareLicenses: reverting ====================
main: == 20230313185145 CleanupOrphanSoftwareLicenses: reverted (0.0965s) ===========

How to set up and validate locally

1. Open the rails console

rails c

2. Make sure you don’t have any orphan licenses to make the test easier

SoftwareLicense.where(spdx_identifier: nil).count

3. Create a orphan license that should be deleted

SoftwareLicense.create!(name: 'orphan license', spdx_identifier: nil)

4. Create a license without a spdx that belongs to a project and shouldn’t be deleted

SoftwareLicense.create!(name: 'nil spdx license', spdx_identifier: nil)
SoftwareLicensePolicy.create!(project_id: Project.last.id, software_license_id: SoftwareLicense.last.id)

5. Check the number of licenses without spdx_identifier in the database. It should be 2.

SoftwareLicense.where(spdx_identifier: nil).count

6. Run the migration in another terminal

bin/rails db:migrate:up:main VERSION=20230313185145

7. Check the number of licenses without spdx_identifier in the database. It should be 1.

SoftwareLicense.where(spdx_identifier: nil).count

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.