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_expressioncolumn (text, max 1024 chars, unique index) to hold multi-token SPDX expressions likeMIT OR Apache-2.0 spdx_identifierrelaxed to nullable so expression-only rows have no identifierspdx_expressionlength validation onLicense(thewith_spdx_expressionsscope moved to !250674 (merged))- presence validation: exactly one of
spdx_identifierorspdx_expressionmust be non-null per row (DB constraintnum_nonnulls(spdx_identifier, spdx_expression) = 1; model validatorexactly_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)