server
server copied to clipboard
Implement new leaderboard tables
We added some new tables for storing people's ratings in a more sensible manner. These need to be implemented on the server (and also API, but the server will be the only application writing to the tables).
Checklist
- [x] Use new
leaderboardandleaderboard_ratingtables (#553) - Migrate old tables into new ones
- [ ] Recompute
wonGames - [ ] Populate
leaderboard_rating_journalwith rating change data fromgame_player_stats
- [ ] Recompute
- [x] Load
RatingTypevalues from database instead of having a hardcoded enum (#428)
How it works
A leaderboard table defines a certain type of rating i.e. global, ladder1v1 or ladder2v2. A leaderboard_rating table stores players current ratings for each leaderboard. This includes the total number of games played for that leaderboard. After a game finishes, the server computes players new ratings and writes the proposed change into a leaderboard_rating_journal table. This can also include verifying that current player rating data is consistent with the last recorded journal entry. Once the journal entry is written, the leaderboard_rating table is updated to the new computed rating.
List of steps for writing new ratings
- Check the game type for an associated
leaderboardentry. - Check that the last known
leaderboard_rating_journalentry for each player is consistent with the current ratings. 2.1. If the ratings are inconsistent, try to repair them. That is, if the currentleaderboard_ratingvalue corresponds to the last entry formean_beforeanddeviation_before, then write the values ofmean_afteranddeviation_afterto theleaderboard_ratingtable. ^ 2.2 If the ratings are inconsistent, but do no match up with the last journal entry, then add a journal entry and signal that something bad happened to the database (it would be good to monitor when this happens in grafana). - Compute the new ratings.
- Write the rating change to the
leaderboard_rating_journal. - Write the new rating into the
leaderboard_ratingtable.
See the migrations here: https://github.com/FAForever/db/blob/develop/migrations/V73__leaderboards_and_map_pools.sql
Other thoughts
- ^ Need to be able to tell from the database if a player won their game in order to correctly repair
leaderboard_ratingfrom the journal. - We need think carefully about what happens when a player is in two games which are rated at the same time. This is actually possible because of flawed logic in the game end calculations. That means we should think about how to set up our database transactions so that computing a new rating and writing it to the rating tables is more or less atomic.