Draft: Update sbom_occurrences_vulnerabilities for CVS
What does this MR do and why?
Update sbom_occurrences_vulnerabilities for CVS.
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.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
| Before | After |
|---|---|
Database query plan
Fetching of `sbom_occurrences.id` (link to query plan)
WITH cte (uuid, package_manager, component_name, version, project_id) AS
( VALUES
('0a60da45-912f-5776-a563-11daad1b182a', 'npm', 'resp-modifier' , '6.0.2' , 27107499),
('268866ec-1791-5b0a-a15a-c9821038972f', 'npm', 'exit' , '0.1.2' , 27107499),
('76ee53fc-cd45-5ef5-b138-fc1970a9be42', 'npm', 'easy-extender' , '2.3.4' , 27107499),
('db8fe329-ee19-5429-80f1-bcff4b97157f', 'npm', 'eazy-logger' , '4.0.1' , 27107499),
('3bc5b6f3-22f3-5741-b84a-9ef8fe80474d', 'npm', 'hamljs' , '0.6.2' , 27107499),
('697b5f36-b69f-5432-bb90-84f0390625e1', 'npm', '@babel/helper-string-parser' , '7.24.7' , 27107499),
('73fd7560-9824-5826-bfdf-2c448bc14c33', 'npm', 'brace-expansion' , '1.1.11' , 27107499),
('521747ae-ab5f-594f-a989-5d79f849663e', 'npm', 'is-data-descriptor' , '1.0.1' , 27107499),
('f4660c2b-5da9-58c8-9331-b8b3335593df', 'npm', 'regexpp' , '3.2.0' , 27107499),
('5efa5b72-3232-5841-87ac-9fa9899d2249', 'npm', 'wrap-ansi' , '7.0.0' , 27107499),
('76a08202-6ee2-554a-a50b-ce069ad355fd', 'npm', 'slugify' , '1.6.6' , 27107499),
('781653e0-c2f5-5498-bf9d-21b7c9d249dd', 'npm', 'yallist' , '4.0.0' , 27107499),
('c7d63a07-81d1-5669-9133-3014f4c897bc', 'npm', 'yargs-parser' , '21.1.1' , 27107499),
('a8b6cdb7-77ce-5d77-a2f9-83238c286710', 'npm', 'atob' , '2.1.2' , 27107499),
('90d522b5-bd57-5fc2-b4e5-deb09ce3a534', 'npm', 'type-fest' , '0.20.2' , 27107499),
('2bd4542b-8e05-5751-a1d3-6f74517764f8', 'npm', 'type-fest' , '0.21.3' , 27107499),
('918debaf-d3b3-5005-acaa-bf9a84b62734', 'npm', '@openapitools/openapi-generator-cli' , '2.13.4' , 27107499),
('36bd73db-5707-5112-8092-eb3115fd0b31', 'npm', 'is-arrayish' , '0.2.1' , 27107499),
('7971a745-4ab0-5221-9ab4-dbec413c22e1', 'npm', '@types/yargs' , '15.0.19' , 27107499),
('bf141de8-a653-5ed6-baa0-66c3923d504f', 'npm', 'array.prototype.flatmap' , '1.3.2' , 27107499),
('94448f20-8cc0-579c-964d-6b7566803720', 'npm', 'mimic-fn' , '2.1.0' , 27107499),
('9ab5a07d-33ce-5083-bf38-c572fe688b96', 'npm', 'object.values' , '1.2.0' , 27107499),
('94e6bff2-3cc2-552b-9fc9-4590f9793186', 'npm', 'os-tmpdir' , '1.0.2' , 27107499),
('3ba8ea91-13bf-546b-b29b-ffac501aa07e', 'npm', 'p-limit' , '1.3.0' , 27107499),
('fbd1dcd7-dc2b-5028-aa65-1b20601735b2', 'npm', 'p-locate' , '2.0.0' , 27107499),
('e0e9106f-9a0a-5595-af58-ba164914c2d0', 'npm', 'safe-regex' , '1.1.0' , 27107499),
('f3d6c1d6-b8eb-54e7-bcc3-2bc40393902e', 'npm', '@types/graceful-fs' , '4.1.9' , 27107499),
('c5e4cf3a-75a7-57b2-bb42-5e8e7ac1147f', 'npm', '@types/istanbul-lib-coverage' , '2.0.6' , 27107499),
('8b98a286-dc86-5050-8a9c-ab6c9e6c445c', 'npm', 'cssom' , '0.4.4' , 27107499),
('69a494df-47e3-593d-8eb5-400cf99218ef', 'npm', 'typed-array-length' , '1.0.6' , 27107499),
('bf635f94-f7ec-540a-8524-85b1cdd2685c', 'npm', 'universalify' , '2.0.1' , 27107499),
('858ddaae-6f35-5b15-874c-b776544013fb', 'npm', 'update-browserslist-db' , '1.0.16' , 27107499),
('270b4a3a-ee62-55cb-b246-4aa9136c5b85', 'npm', 'pify' , '2.3.0' , 27107499),
('ec050bc1-6c43-56f2-bd40-4479f511d565', 'npm', 'portscanner' , '2.2.0' , 27107499),
('1c861bc9-78f8-55cb-a92e-655562fb4e78', 'npm', 'posthtml-parser' , '0.11.0' , 27107499),
('86d6e050-e84d-5c14-a0f8-3646da879154', 'npm', 'cache-base' , '1.0.1' , 27107499),
('cc319a3a-5440-5e5a-81c0-135ab49a4f4a', 'npm', 'parse5' , '6.0.1' , 27107499),
('10f41b79-d5de-5471-a153-244279820d63', 'npm', 'collect-v8-coverage' , '1.0.2' , 27107499),
('04554026-654e-5c94-bbb0-fe26dbdd754a', 'npm', 'globals' , '13.24.0' , 27107499),
('356361a8-a6b6-511c-9381-5697812db98b', 'npm', 'hosted-git-info' , '2.8.9' , 27107499),
('090db4ef-f12c-55fe-9007-403c7a6e35ca', 'npm', 'html-escaper' , '2.0.2' , 27107499),
('e07132f5-7ea8-5a6a-8aaf-445217447fc5', 'npm', 'dev-ip' , '1.0.1' , 27107499),
('1ea5a0b8-62d8-5e9f-9b8a-e623b130ea35', 'npm', 'is-data-view' , '1.0.1' , 27107499),
('0bd0f817-c076-5878-a4d3-384a39238bdb', 'npm', 'validate-npm-package-license' , '3.0.4' , 27107499),
('8bc99120-e6d0-51fd-850b-ddc0cfbe6836', 'npm', 'walker' , '1.0.8' , 27107499),
('7bd50c8c-ecf7-5c99-b47f-d45374728c1c', 'npm', 'to-regex-range' , '5.0.1' , 27107499),
('5d1780a1-c4af-5f1e-b283-87cc45af9d58', 'npm', 'argparse' , '1.0.10' , 27107499),
('c3c6e153-affe-58d0-af12-1093cd2f607b', 'npm', 'brace-expansion' , '2.0.1' , 27107499),
('77b570ae-9ebb-5969-b360-2208659a70ff', 'npm', 'chokidar' , '3.6.0' , 27107499),
('94c9b7bc-954a-5d3b-9802-bbea784377eb', 'npm', 'cliui' , '8.0.1' , 27107499),
('fb465f70-c9a4-5bd6-a32c-f762b51d7e85', 'npm', 'commondir' , '1.0.1' , 27107499),
('cdcbbfa9-2dc2-55a6-89c6-38c3a5a7a101', 'npm', 'inherits' , '2.0.4' , 27107499),
('5752fc5b-e1ec-5b7b-bd89-d5901fe178cd', 'npm', 'opn' , '5.3.0' , 27107499),
('7cc2f874-0197-52e4-9e07-a7a650804e66', 'npm', 'parseurl' , '1.3.3' , 27107499),
('6856abe9-47b0-5d06-a3d7-5c425faa8e9e', 'npm', 'capture-exit' , '2.0.0' , 27107499),
('86c1d869-97a4-5720-b449-cc17dc749db7', 'npm', 'fill-range' , '4.0.0' , 27107499),
('0ad69162-d34d-513d-85c4-61f266681dcc', 'npm', 'find-up' , '2.1.0' , 27107499),
('1fe29a82-2ca4-5fc4-8132-73ea5d89e67f', 'npm', 'flat-cache' , '3.2.0' , 27107499),
('b4d7a825-e3a2-53ca-8038-de84cd3ba51f', 'npm', 'flatted' , '3.3.1' , 27107499),
('74234193-5d3a-5667-a5de-6cf1749b3f6c', 'npm', 'pretty-format' , '26.6.2' , 27107499),
('4760f2e3-b0a5-553f-8675-2c47220bc9a1', 'npm', '@istanbuljs/schema' , '0.1.3' , 27107499),
('3c4a5b9c-8a03-517b-b11b-e7f11e1852c7', 'npm', '@nestjs/axios' , '3.0.2' , 27107499),
('9ffce4a3-70b4-5ce8-943c-06e8e3ec613e', 'npm', '@nestjs/common' , '10.3.0' , 27107499),
('cd16065f-b64d-572e-9ac3-2905421dc619', 'npm', '@tootallnate/once' , '1.1.2' , 27107499),
('049f889f-dd8f-5cbd-80ee-37ae765f58e4', 'npm', 'snapdragon' , '0.8.2' , 27107499),
('3afbc870-3dbb-5655-8197-702e54ae0557', 'npm', 'tsconfig-paths' , '3.15.0' , 27107499),
('7cf2499b-c72c-5442-b241-4630d452b7e4', 'npm', 'tslib' , '2.6.2' , 27107499),
('e713d908-6f6e-5869-ab0b-005bd39b6622', 'npm', 'type-detect' , '4.0.8' , 27107499),
('c857e198-17eb-59b7-8bdc-0a6ab0582727', 'npm', 'istanbul-lib-coverage' , '3.2.2' , 27107499),
('abcc7f1f-4afa-590f-bb73-0ce90be2365e', 'npm', 'semver' , '5.7.2' , 27107499),
('2a5d6cb1-95b1-55e4-b10d-3e9de89d7eea', 'npm', 'connect' , '3.6.6' , 27107499),
('e5a0b56a-f01e-5bec-98ce-389d522c357c', 'npm', 'function-bind' , '1.1.2' , 27107499),
('0b27e8d2-6ef6-51ae-9884-b96a37ca7088', 'npm', 'wrappy' , '1.0.2' , 27107499),
('abc65a1d-c04f-5bf7-9ad8-f7cf4c886693', 'npm', 'iconv-lite' , '0.4.24' , 27107499),
('a03b2d9f-034a-5105-afac-9c3e14abf0ef', 'npm', 'immutable' , '3.8.2' , 27107499),
('cb21692f-0d43-52b7-a711-580af805b9a1', 'npm', 'inherits' , '2.0.3' , 27107499),
('61ff8e52-707e-5f38-bff9-7e7ff5fb7dad', 'npm', '@babel/helper-split-export-declaration' , '7.24.7' , 27107499),
('829a5e4c-10f0-58f6-ab83-43bda1294dfa', 'npm', 'minimatch' , '3.1.2' , 27107499),
('71d5c8da-d2fb-5d95-87fc-ed5084351583', 'npm', 'array-includes' , '3.1.8' , 27107499),
('130a62e5-455b-5e89-b1a7-3f15059418b5', 'npm', 'assign-symbols' , '1.0.0' , 27107499),
('d1fc7d93-f7f8-57de-9891-7929e472abfd', 'npm', 'astral-regex' , '2.0.0' , 27107499),
('27c9efdc-9e91-5ad2-9c4d-5d58d37e65ef', 'npm', 'asynckit' , '0.4.0' , 27107499),
('831458c6-c3e0-5dac-84f8-746fcce7d30a', 'npm', 'base64-js' , '1.5.1' , 27107499),
('b1acd57b-d192-5f2c-9984-8296736b08be', 'npm', 'bl' , '4.1.0' , 27107499),
('ff77b285-b0b7-54b4-bfcd-6dab8b9fe8e8', 'npm', 'bser' , '2.1.1' , 27107499),
('5c8e70e2-def9-5824-ae88-763a6faebae8', 'npm', 'buffer' , '5.7.1' , 27107499),
('149e8967-4a1c-54ce-a545-0fb16bdc9308', 'npm', '@apidevtools/json-schema-ref-parser' , '9.0.6' , 27107499),
('f3541618-720f-5382-8d04-b0bb34f49c1f', 'npm', '@apidevtools/swagger-cli' , '4.0.4' , 27107499),
('b639282d-9c6d-5c20-b47b-1b3e34a02f8a', 'npm', '@apidevtools/swagger-methods' , '3.0.2' , 27107499),
('ba503481-65eb-53d8-8794-2b102bc0e500', 'npm', '@babel/code-frame' , '7.12.11' , 27107499),
('121404f3-e348-5301-b2f6-44283e6eb041', 'npm', 'jest-cli' , '26.6.3' , 27107499),
('79736a0f-991d-5024-ad26-8fd915b99f57', 'npm', 'jest-environment-jsdom' , '26.6.2' , 27107499),
('30415b06-e35f-54c1-b661-65dec151408b', 'npm', 'ansi-regex' , '6.0.1' , 27107499),
('77b6285e-8730-5e77-a75e-5f1c80d0c849', 'npm', 'ansi-styles' , '4.3.0' , 27107499),
('2563f862-01c2-5c48-9423-ee6b07df2b40', 'npm', 'ansi-styles' , '6.2.1' , 27107499),
('73176479-dde5-592b-9ce8-98928cd49943', 'npm', 'any-promise' , '0.1.0' , 27107499),
('593192c3-c9fc-58ae-b0e3-d83c6acafc7e', 'npm', 'anymatch' , '3.1.3' , 27107499),
('c5be615d-4827-5a29-a7e3-f3bfe28d0f77', 'npm', 'argparse' , '2.0.1' , 27107499),
('06352e46-78c7-5999-ab45-09e3428c126d', 'npm', 'array-differ' , '1.0.0' , 27107499),
('6bbb95f8-0a39-597c-af2f-278f81fe6877', 'npm', 'array-differ' , '3.0.0' , 27107499)
)
SELECT "cte"."uuid", "cte"."project_id", "sbom_occurrences"."id"
FROM "sbom_occurrences"
INNER JOIN cte
ON cte.package_manager = sbom_occurrences.package_manager
AND cte.component_name = sbom_occurrences.component_name
AND cte.project_id = sbom_occurrences.project_id
INNER JOIN sbom_component_versions
ON sbom_component_versions.id = sbom_occurrences.component_version_id
AND cte.version = sbom_component_versions.version;How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Edited by Zamir Martins