search

leaderboardsgg / leaderboard-backend

The backend for Leaderboards.gg. Also holds public-facing APIs.
https://info.leaderboards.gg
GNU General Public License v3.0
12 stars 16 forks source link

Bug: Cat Slugs Are Globally Unique #222

Open TheTedder opened 3 months ago

TheTedder commented 3 months ago

Cat slugs are globally unique instead of unique to LBs.

Step(s) to Reproduce

Create a category with a slug that has been used for a different game but not the current game.

Expected Result

The category is created successfully.

Actual Outcome

The category fails to be created because its slug is "already used" by a different leaderboard.

How to Fix This

Change the uniqueness constraint so that it includes the leaderboard_id field too.

TheTedder commented 2 months ago

Note: Make sure the Leaderboard ID is specified as the first column of the index for performance reasons:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned.