Add spdx_expression column to pm_licenses for v3 license ingestion

Adds the spdx_expression column to pm_licenses and relaxes spdx_identifier to nullable, enabling v3 PMDB license rows to be stored alongside existing v2 rows.

What does this MR do and why?

  • new spdx_expression column (text, max 1024 chars, unique index) to hold multi-token SPDX expressions like MIT OR Apache-2.0
  • spdx_identifier relaxed to nullable so expression-only rows have no identifier
  • spdx_expression length validation on License (the with_spdx_expressions scope moved to !250674 (merged))
  • presence validation: exactly one of spdx_identifier or spdx_expression must be non-null per row (DB constraint num_nonnulls(spdx_identifier, spdx_expression) = 1; model validator exactly_one_of_identifier_or_expression)

Both columns are permanent. A package can legitimately have both identifier licenses and expression licenses simultaneously; the two license types coexist per package and are surfaced separately to the user. The spdx_identifier nullable relaxation is safe for existing rows: all current rows have a non-null identifier and the constraint is relaxed, not tightened.

The write path (routing, data object, upsert) is in !250674 (merged). The read path is tracked in #606525 (closed).

Closes #606521 (closed)

Database queries

The with_spdx_identifiers scope performs an IN lookup against the uniquely indexed spdx_identifier column. The db:gitlabcom-database-testing job passed against the current migration set.

Plans are from postgres.ai (production clone). pm_licenses is currently empty on production and is expected to remain a small table, so seq scan is the correct plan. The planner will not use the index for a table this size.

with_spdx_identifiers(values):

SELECT "pm_licenses".* FROM "pm_licenses" WHERE "pm_licenses"."spdx_identifier" IN ('MIT', 'Apache-2.0')
 Seq Scan on public.pm_licenses  (cost=0.00..0.00 rows=1 width=88) (actual time=0.007..0.007 rows=0 loops=1)
   Filter: (pm_licenses.spdx_identifier = ANY ('{MIT,Apache-2.0}'::text[]))
   I/O Timings: read=0.000 write=0.000
 Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'

This MR adds the unique index on the new spdx_expression column; the with_spdx_expressions scope that uses it lands in !250674 (merged).

MR acceptance checklist

  • I have evaluated the MR acceptance checklist for this MR
  • This MR has been reviewed by a domain expert (required for database, security, and performance changes)
Edited by Igor Frenkel

Merge request reports

Loading
Loading