Project sorting is case sensitive

[roadSurfer]

Current Behavior

The sort is case sensitive meaning that upper case names are always sorted before/after as a block.

The specific browser and version does not seem to matter. I assume the sort is simply used the ASCII code, rather than dealing with the specific letters in a case insensitve manner.

Steps to Reproduce

1. Have a number of projects with names in different cases. e.g. "Zoo_foo" and "aard_bar"
2. Sort the projects by name, ascending
3. Observe that "Zoo_foo" is listed before "aard_bar", even though alphabetically "a" comes before "z"

Expected Behavior

Ideally projects should be sorted case insensitive to make it easier to find them. This is especially important when one has many projects to manage.

Dependency-Track Frontend Version

4.7.0

Browser

Mozilla Firefox

Browser Version

Issue is browser and version independent

Operating System

Linux

Checklist

• [X] I have read and understand the contributing guidelines
• [X] I have checked the existing issues for whether this defect was already reported

[nscuro]

Transferred to dependency-track repo as the sorting is performed on the API server side.

More specifically, the sorting is done by the database server. Dependency-Track merely requests sorting to be done in ascending or descending order. Whether or not an ORDER BY is case sensitive or case insensitive depends on the collation used by the database. In case of Postgres, the default collation depends on the operating system.

We could add logic to always order by the lowercase representation of each value, but this will add a significant computation overhead on systems with large portfolios.

My recommendation for now would be to change the collation of the PROJECT table to something that is not case sensitive.

[roadSurfer]

Thanks for the tip on PostgreSQL. I can confirm is seems to be OK on SQL Server (which is running on Windows, DepTrack is deployed in K8s). Looks like updating the collation of a PostgreSQL DB is non-trivial, although it can be done.

Modifying the table using ALTER TABLE public."PROJECT" ALTER COLUMN "NAME" SET DATA TYPE character varying(255) COLLATE "en-x-icu" seems to work, although I do wonder if it would be better for the collation to be set at the time of creation?

Thanks for the advice and sorry for creating noise.

[nscuro]

No need to be sorry, now we can refer to this issue when someone is in a similar situation!

although I do wonder if it would be better for the collation to be set at the time of creation?

Yeah, probably. I'd love to offer something from the application side, but because we support multiple database systems, and the schema we use is automatically generated, there isn't much wiggle room for fine-tuning like this...