server icon indicating copy to clipboard operation
server copied to clipboard

Implement new leaderboard tables

Open Askaholic opened this issue 5 years ago • 0 comments

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 leaderboard and leaderboard_rating tables (#553)
  • Migrate old tables into new ones
    • [ ] Recompute wonGames
    • [ ] Populate leaderboard_rating_journal with rating change data from game_player_stats
  • [x] Load RatingType values 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

  1. Check the game type for an associated leaderboard entry.
  2. Check that the last known leaderboard_rating_journal entry for each player is consistent with the current ratings. 2.1. If the ratings are inconsistent, try to repair them. That is, if the current leaderboard_rating value corresponds to the last entry for mean_before and deviation_before, then write the values of mean_after and deviation_after to the leaderboard_rating table. ^ 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).
  3. Compute the new ratings.
  4. Write the rating change to the leaderboard_rating_journal.
  5. Write the new rating into the leaderboard_rating table.

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_rating from 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.

Askaholic avatar Feb 11 '20 23:02 Askaholic