Find a way to make COMPONENT and REPOSITORY_META_COMPONENT tables JOIN-able

[nscuro]

Current Behavior

It is not possible to join the COMPONENT and REPOSITORY_META_COMPONENT tables:

• COMPONENT has PURL and PURL_COORDINATES columns
• REPOSITORY_META_COMPONENT has REPOSITORY_TYPE, NAMESPACE and NAME columns
• REPOSITORY_TYPE is not necessarily the same a PURL type
• Namespace and name segments of full PURLs might be URL-encoded (e.g. pkg:npm/%40foo/[email protected])
• NAMESPACE and NAME columns of REPOSITORY_META_COMPONENT contain PURL namespace and name in URL-decoded form

Thus:

• There is no single column we can join on
• Constructing or deconstructing PURLs we can join on in the query is not reliable

This causes a few bad limitations:

• When fetching data for the /api/v1/finding/project/{uuid} endpoint, we run into the N+1 problem because we can't join the data in the original findings SQL query: https://github.com/DependencyTrack/hyades-apiserver/blob/main/src/main/java/org/dependencytrack/persistence/FindingsQueryManager.java#L380-L408
• It is impossible to delete REPOSITORY_META_COMPONENT records that refer to components that no longer exist in the portfolio. The table can thus only grow over time.

Proposed Behavior

Find a way to make COMPONENT and REPOSITORY_META_COMPONENT tables JOIN-able.

Creating new columns, new SQL helper functions etc. are all possibilities we can investigate.

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this enhancement was already requested