Normalize pm_affected_packages.package_name

Zamir Martinsrequested to merge
normalize_pm_affected_packages_package_name into master

What does this MR do and why?

There is an ongoing issue comparing pm_affected_package.package_name with sbom_components.name whenever the purl_type is pypi.

Therefore this MR normalizes pm_affected_packages.package_name when purl_type is pypi.

This MR has to be merged after Use normalized package_name during `pm_affected... (!183995 - merged) • Zamir Martins • 17.10 when addressing Component name mismatch between existing vs new... (#515324 - closed) • Zamir Martins • 17.11 • Needs attention

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

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.

DB query

Execution plan (BBM)

UPDATE "pm_affected_packages"
SET package_name = REGEXP_REPLACE(LOWER(package_name), '[-_.]+', '-')
WHERE "pm_affected_packages"."id" BETWEEN 51 AND 151
AND "pm_affected_packages"."purl_type" = 8
AND "pm_affected_packages"."id" >= 51
AND NOT (package_name = REGEXP_REPLACE(LOWER(package_name), '[-_.]+', '-'))
AND (
  NOT EXISTS (
    SELECT 1 FROM pm_affected_packages AS target
    WHERE target.pm_advisory_id = pm_affected_packages.pm_advisory_id
    AND target.purl_type = pm_affected_packages.purl_type
    AND target.package_name = REGEXP_REPLACE(LOWER(pm_affected_packages.package_name), '[-_.]+', '-')
    AND target.distro_version = pm_affected_packages.distro_version
  )
)

Execution plan (single query)

update pm_affected_packages
set package_name = REGEXP_REPLACE(LOWER(package_name), '[-_.]+', '-')
where purl_type = 8
and package_name != REGEXP_REPLACE(LOWER(package_name), '[-_.]+', '-')
and not exists (
  select 1 from pm_affected_packages as target
  where target.purl_type = pm_affected_packages.purl_type
  and target.package_name = REGEXP_REPLACE(LOWER(pm_affected_packages.package_name), '[-_.]+', '-')
  and target.pm_advisory_id = pm_affected_packages.pm_advisory_id
  and target.distro_version = pm_affected_packages.distro_version
);

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.

Edited by Zamir Martins

Merge request reports