403 lines
20 KiB
SQL
403 lines
20 KiB
SQL
/**
|
|
* Some general thoughts first regarding how I think we should design the database:
|
|
*
|
|
* There should be a semantic of what it means for our data to be 'consistent':
|
|
* If it is consistent, our program interacting with the database should be well-behaved, i.e. never crash or lead to weird behaviour.
|
|
* From any consistent state, our program should be able to bring the database up to the most recent state automatically, i.e.
|
|
* - Update games
|
|
* - Calculate ELO
|
|
* - Calculate statistics
|
|
* without us having to fix anything in the database.
|
|
*
|
|
* Conversely, our program should be designed in such a way that every database transaction that we do will leave the database in a consistent state.
|
|
* This ensures that (as long as there is no programming error), we can start/stop/run the program at any time, always leaving the database in a
|
|
* consistent state.
|
|
* In particular, in order to be able to have reasonably small transactions, the following all should be valid transactions:
|
|
* - Adding just the metadata (who, score, when, but not detailed actions or deck) of some game, without processing any of its statistics or elo changes
|
|
* - Adding detailed data of a game (actions, deck)
|
|
* - Calculating any subset of statistics for a game (BDR, moves, loss reasons (like: 'Discard crit', 'Bomb crit', 'Strikeout' or similar))
|
|
* - Adding any set of players to the database
|
|
* - Processing a single game for ELO change
|
|
*
|
|
* Conversely, the database should be designed in such a way that all of the above updates lead to consistent states. In particular, I'd like it to be ok if
|
|
* - Games are stored in the database whose statistics or ELO have not been processed yet
|
|
* - Game metadata is stored in the database, but their actions/deck is not
|
|
* - New players are part of the database, but we did not check for games involving them
|
|
*
|
|
* Also, I think that there should be *no* state-change in any other files except the database when running the program:
|
|
* That is, for a full replica of the server state, the source code and the current database state should be enough.
|
|
* This means no json/csv files or whatever that we both write and read to.
|
|
* (Input config files as part of the repository are ouf course fine, as well as output files like log)
|
|
*
|
|
* One thing I felt like was a bit difficult to handle during season zero is people registering after they played some games.
|
|
* At least I think this caused you quite some effort, especially since it's then not so easy to get their games into the database.
|
|
* I recall that you just stored a single game id which represented the latest one that your program already processed.
|
|
* To account for late sign-ups, I suggest the following:
|
|
* - For each registered league user, store the latest game id that we processed (=downloaded, not necessarily calculated ELO or anything) separately.
|
|
* This ensures that for new users, we will fetch all their games on the first database run, whereas for already-registered ones, we do not.
|
|
* - When checking for new games, we can now send these ids to the hanab.live site (there is an API parameter to request only games with id >= x, I think you did not use it last time)
|
|
*
|
|
* Now there's a slight problem, since calculating ELO depends on the order of games. In case we have late sign-ups, I think it's not so nice if they retroactively
|
|
* affect ELO. In particular, this could lead to ELO changes in players X that did not even play a game with new player N,
|
|
* if N and Y played some game and after that, Y and X did, meaning that Y's elo when playing with X changes retroactively, thus also affecting X.
|
|
*
|
|
* Therefore, I suggest that additionally, each game that we download from hanab.live gets a 'league id', which is just a serial number and represents
|
|
* the ordering of games in which we process them.
|
|
* So in case of late sign-ups we would just process these games a bit later. In particular, in case of no late sign-ups, league ids and game ids from hanab.live
|
|
* should have the same ordering, but this might change when we have late sign-ups.
|
|
* I'd like to store this, because in case of bugs or anything, we would then be able to re-calculate all ELO deterministically with this data.
|
|
* Also, it's nice to be transparent about this, when we do have some game logs on the website or anything, we should order by this league id, since this is the
|
|
* order that we used for calculating the ELO.
|
|
* Let me know if you think this is too complicated, but to me personally, I'd quite like that, since it just means
|
|
* (together with the above mentioned solution of storing latest download ids on a per-user basis) that late sign-ups are handled well automatically.
|
|
*
|
|
*/
|
|
|
|
|
|
/**
|
|
* To store the league participants, I suggest the following:
|
|
* - Represent each participant by internal user id
|
|
* - Store 'display name' (what shows up in the tables etc) for each participant.
|
|
* This will only be relevant for output, not any of the algorithms, so we could easily change this at any time.
|
|
* - Store the account names (from hanab.live) separately for each account, thereby allowing for multiple ones having the same user_id
|
|
* I'm not sure how much we need this, but I feel like it's not too hard to have this extra flexibility in our data structure, so just in case
|
|
* I would support this.
|
|
* To be honest, I'd also like to apply all of the code to some non-league related games where I'd need this :D.
|
|
*/
|
|
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
player_name TEXT NOT NULL UNIQUE,
|
|
/* Can be null */
|
|
discord_tag TEXT UNIQUE
|
|
);
|
|
|
|
|
|
DROP TABLE IF EXISTS user_accounts CASCADE;
|
|
CREATE TABLE user_accounts (
|
|
/* The username from hanab.live. This will be used a) for associating games to one of our user_ids and b) show up in the player profile. */
|
|
username TEXT PRIMARY KEY,
|
|
/**
|
|
* Hanab.live stores a normalized username for each account to ensure that usernames are case-insensitive and don't have weird unicodes that resemble each other.
|
|
* It uses go-unidecode to generate these from the actual usernames, and I suggest we do the same.
|
|
* The corresponding python package is 'unidecode', so we should just use that here.
|
|
* This will then ensure that no two sign-ups correspond to the same hanab.live account.
|
|
*/
|
|
normalized_username TEXT NOT NULL UNIQUE,
|
|
user_id INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
|
|
);
|
|
|
|
/*
|
|
* The latest game id that we already downloaded for a specific user. Note that this can never decrease, since on hanab.live, game ids are only incrementing.
|
|
* In my opinion, it should be a consistent state to *not* have an entry in this table for a user.
|
|
* In that case, when querying, we would of course assume nothing and fetch all the data from hanab.live (from the start of the league)
|
|
* and place an entry here after that.
|
|
* Also I suggest that the latest_game_id actually corresponds to a game that we have stored in the database,
|
|
* this helps with further sanity checks or if we want to know if statistics are up to date.
|
|
*/
|
|
DROP TABLE IF EXISTS downloads;
|
|
CREATE TABLE downloads (
|
|
/** Notice this has to be a hanab.live username, not a user_id */
|
|
normalized_username TEXT PRIMARY KEY REFERENCES user_accounts (normalized_username),
|
|
latest_game_id INTEGER NOT NULL
|
|
);
|
|
|
|
/**
|
|
* For completeness, I suggest we store the hanab.live variants here together with their names.
|
|
* So this will just be a static table that we populate once.
|
|
*/
|
|
DROP TABLE IF EXISTS variants CASCADE;
|
|
CREATE TABLE variants (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
num_suits INTEGER NOT NULL,
|
|
clue_starved BOOLEAN NOT NULL,
|
|
rating_type INTEGER NOT NULL
|
|
);
|
|
|
|
/**
|
|
* We should store data on all games locally for two reasons:
|
|
* - Only download detailed data for each game once (The actions taken and the actual deck)
|
|
* - We can evaluate statistics (also complicated ones) whenever we want, independent of the server
|
|
*/
|
|
DROP TABLE IF EXISTS games CASCADE;
|
|
CREATE TABLE games (
|
|
/** Here, we should use the same ids as the game ids from hanab.live */
|
|
id INTEGER PRIMARY KEY,
|
|
num_players SMALLINT NOT NULL,
|
|
/** This should be the same variant id as on hanab.live, even if we don't use all of them. */
|
|
variant_id SMALLINT NOT NULL,
|
|
/** Same here: seed from hanab.live */
|
|
seed TEXT NOT NULL,
|
|
score SMALLINT NOT NULL,
|
|
num_turns SMALLINT NOT NULL,
|
|
datetime_started TIMESTAMPTZ NOT NULL,
|
|
datetime_finished TIMESTAMPTZ NOT NULL,
|
|
/**
|
|
* This is the league id mentioned above that will represent the ordering of games regarding being processed by ELO.
|
|
* Note that this means when fetching new data from hanab.live, we have to fetch *all* of it and insert the games sorted
|
|
* by their ids. It's not technically needed for the ELO system to function, but it's definitely desirable to process them in order, of course.
|
|
*/
|
|
league_id SERIAL NOT NULL UNIQUE,
|
|
FOREIGN KEY (variant_id) REFERENCES variants (id)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS game_participants CASCADE;
|
|
CREATE TABLE game_participants (
|
|
game_id INTEGER NOT NULL,
|
|
/**
|
|
* This is our internal user id for league accounts. In particular, if a league account is associated to multiple hanab.live accounts,
|
|
* we will lose the distinction between them when storing participants in this way (which I think is totally ok).
|
|
* Also, this makes insertion slightly tricky: You always have to look up these ids first before being able to insert a game.
|
|
* There could be one argument that we should store the hanab.live username here: This would enable us to list games (if we were to do such a stats page)
|
|
* with the hanab.live account names instead of the 'display names' that people used for league sign-up.
|
|
* Let me know what you think.
|
|
*/
|
|
user_id INTEGER NOT NULL,
|
|
/**
|
|
* Hanab.live stores this as well, I don't think that we need it currently, but there's no harm done in storing this, since it would enable us to know
|
|
* which player is which if we were to do per-player statistics also inside an individual game.
|
|
* For example, I could imagine stuff like: 'Clues given' or 'Cards played', which might actually vary across players.
|
|
* Unfortunately, when using the 'history-full' api hook, player names are reported alphabetically, so to retrieve
|
|
* the seat order, we have to use the 'export' endpoint of the api, which requires an extra request for each game.
|
|
* This is why we allow for this entry to be null in general, so that we can easily store the games without the seat
|
|
* being known.
|
|
*/
|
|
seat SMALLINT,
|
|
FOREIGN KEY (game_id) REFERENCES games (id),
|
|
FOREIGN KEY (user_id) REFERENCES users (id),
|
|
CONSTRAINT game_participants_unique UNIQUE (game_id, user_id)
|
|
);
|
|
|
|
/**
|
|
* This is just exactly the same table as on hanab.live, since we might want to do statistics like BDR, we have to replicate this.
|
|
* Note that for now, we can just ignore this and code up the rest, but we will need this in the long run if we want to do BDR.
|
|
*/
|
|
DROP TABLE IF EXISTS game_actions CASCADE;
|
|
CREATE TABLE game_actions (
|
|
game_id INTEGER NOT NULL,
|
|
turn SMALLINT NOT NULL,
|
|
|
|
/**
|
|
* - 0 - play
|
|
* - 1 - discard
|
|
* - 2 - color clue
|
|
* - 3 - rank clue
|
|
* - 4 - game over
|
|
*/
|
|
type SMALLINT NOT NULL,
|
|
|
|
/**
|
|
* - If a play or a discard, corresponds to the order of the the card that was played/discarded.
|
|
* - If a clue, corresponds to the index of the player that received the clue.
|
|
* - If a game over, corresponds to the index of the player that caused the game to end or -1 if
|
|
* the game was terminated by the server.
|
|
*/
|
|
target SMALLINT NOT NULL,
|
|
|
|
/**
|
|
* - If a play or discard, then 0 (as NULL). It uses less database space and reduces code
|
|
* complexity to use a value of 0 for NULL than to use a SQL NULL:
|
|
* https://dev.mysql.com/doc/refman/8.0/en/data-size.html
|
|
* - If a color clue, then 0 if red, 1 if yellow, etc.
|
|
* - If a rank clue, then 1 if 1, 2 if 2, etc.
|
|
* - If a game over, then the value corresponds to the "endCondition" values in "constants.go".
|
|
*/
|
|
value SMALLINT NOT NULL,
|
|
|
|
FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE CASCADE,
|
|
PRIMARY KEY (game_id, turn)
|
|
);
|
|
|
|
|
|
/**
|
|
* Hanab.live uses the go random number generator to deterministically compute a deck shuffle out of a seed,
|
|
* where the seed is just used to seed the RNG.
|
|
* Since we can't replicate this, we need to store the seeds explicitly, but of course it's enough to do this on a per-seed basis
|
|
* rather than a per-game basis.
|
|
* Note that for now, we can just not populate this table, since this would only be relevant for statistics anyway.
|
|
* TODO: I think it should be an invariant that whenever we have stored the actions of a game, we ensure that we also have stored the
|
|
* corresponding seed here (but not vice versa).
|
|
* Unfortunately, to my knowledge, it is not possible to encode this well in PostgreSQL itself, so we will need to make sure our program
|
|
* has this invariant.
|
|
*/
|
|
DROP TABLE IF EXISTS seeds CASCADE;
|
|
CREATE TABLE seeds (
|
|
seed TEXT NOT NULL,
|
|
/** The index of the card in the deck of the seed, so 0,1,..., 49 for NoVar */
|
|
card_index SMALLINT NOT NULL,
|
|
suit_index SMALLINT NOT NULL,
|
|
rank SMALLINT NOT NULL,
|
|
PRIMARY KEY (seed, card_index)
|
|
);
|
|
|
|
|
|
/**
|
|
* I think the tables above now store everything that we need to know from hanab.live
|
|
* So the following should collect our statistics and ELO now.
|
|
*
|
|
* To store ELO, I suggest we use an append-only data structure, i.e. we just insert a new row whenever any ELO changes
|
|
* and keep the old ones. This as good for roll-back, easy to maintain, and also useful if we at some point want to model progression somwhere.
|
|
* Also, we need to separately store the initial ratings for the variants and users, in my opinion for two reasons:
|
|
* - Do not rely on some config file in the repository to be available to interpret the data here
|
|
* - Allow for separate base ratings on a per-user basis (this might be useful at some point).
|
|
*/
|
|
|
|
/* TABLES RELATED TO ELO */
|
|
|
|
/**
|
|
* Records the initial ratings of variants
|
|
*/
|
|
DROP TABLE IF EXISTS variant_base_ratings CASCADE;
|
|
CREATE TABLE variant_base_ratings (
|
|
/* Note that a variant already includes the number of suits, so we do not have to add this here. */
|
|
variant_id SMALLINT,
|
|
num_players SMALLINT NOT NULL,
|
|
rating REAL NOT NULL,
|
|
PRIMARY KEY (variant_id, num_players)
|
|
);
|
|
|
|
/**
|
|
* Store a separate row for each elo update to one of the variants
|
|
*/
|
|
DROP TABLE IF EXISTS variant_ratings CASCADE;
|
|
CREATE TABLE variant_ratings (
|
|
/** This should reference the game that triggered the elo update */
|
|
league_id INTEGER NOT NULL REFERENCES games (league_id),
|
|
variant_id SMALLINT NOT NULL REFERENCES variants (id),
|
|
num_players SMALLINT NOT NULL,
|
|
|
|
change REAL NOT NULL,
|
|
value_after REAL NOT NULL,
|
|
/**
|
|
* Since multiple variants change rating for a single game, we mark the primary variant that changed here.
|
|
* This enables us to know what rows to display on statistic pages
|
|
*/
|
|
primary_change BOOLEAN NOT NULL,
|
|
|
|
PRIMARY KEY (league_id, variant_id, num_players)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS user_base_ratings CASCADE;
|
|
CREATE TABLE user_base_ratings (
|
|
user_id INTEGER REFERENCES users (id),
|
|
/**
|
|
* Since a user has different ratings now, this should represent which of the ratings we mean.
|
|
* For now, I suggest 0 = NoVar, 1 = ClueStarved.
|
|
* I'd use an integer here to be open for more elos per player in future seasons
|
|
*/
|
|
rating_type SMALLINT NOT NULL,
|
|
|
|
rating REAL NOT NULL,
|
|
|
|
PRIMARY KEY (user_id, rating_type)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS user_ratings CASCADE;
|
|
CREATE TABLE user_ratings (
|
|
/**
|
|
* This should reference the game that triggered the elo update.
|
|
* I would use the league_id here for proper ordering.
|
|
* Also note that this can then be used to identify whether a given league game has already been processed for rating change.
|
|
*/
|
|
league_id INTEGER NOT NULL REFERENCES games (league_id),
|
|
|
|
user_id INTEGER NOT NULL REFERENCES users (id),
|
|
rating_type SMALLINT NOT NULL,
|
|
|
|
change REAL NOT NULL,
|
|
value_after REAL NOT NULL,
|
|
|
|
PRIMARY KEY (league_id, user_id)
|
|
);
|
|
|
|
|
|
/* TABLES RELATED TO STATISTICS */
|
|
|
|
/** This is a rough outline, not really happy with it right now. */
|
|
DROP TABLE IF EXISTS game_statistics CASCADE;
|
|
CREATE TABLE game_statistics (
|
|
game_id INTEGER PRIMARY KEY REFERENCES games (id),
|
|
/** I'd say all of the following can just be null in case we have not evaluated them yet. */
|
|
num_bottom_deck_risks SMALLINT,
|
|
num_crits_lost SMALLINT
|
|
);
|
|
|
|
/**
|
|
* Need a new table here, since a single game might have several outcomes:
|
|
* Think of losing a crit and striking out, for example
|
|
*/
|
|
DROP TABLE IF EXISTS game_outcomes CASCADE;
|
|
CREATE TABLE game_outcomes (
|
|
game_id INTEGER REFERENCES games (id),
|
|
/** This stores the game outcome, corresponding to the values of the statistics.GameOutcome enum */
|
|
outcome SMALLINT,
|
|
CONSTRAINT outcomes_no_duplicates UNIQUE (game_id, outcome)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS user_statistics;
|
|
CREATE TABLE user_statistics (
|
|
user_id INTEGER NOT NULL REFERENCES users (id),
|
|
/** We track stats separately for each variant type */
|
|
variant_type SMALLINT NOT NULL,
|
|
current_streak INTEGER,
|
|
maximum_streak INTEGER,
|
|
maximum_streak_last_game INTEGER REFERENCES games (league_id),
|
|
games_played INTEGER,
|
|
games_won INTEGER,
|
|
games_lost INTEGER GENERATED ALWAYS AS (games_played - games_won) STORED,
|
|
winrate REAL GENERATED ALWAYS AS (CASE WHEN games_played != 0 THEN 100 * CAST(games_won AS REAL)/ games_played ELSE NULL END) STORED,
|
|
total_bdr INTEGER,
|
|
/** Number of critical cards that were either discarded or misplayed */
|
|
total_crits_lots INTEGER,
|
|
total_game_moves INTEGER,
|
|
average_bdr REAL GENERATED ALWAYS AS (CASE WHEN games_played != 0 THEN CAST(total_bdr AS REAL) / games_played ELSE NULL END) STORED,
|
|
average_crits_lost REAL GENERATED ALWAYS AS (CASE WHEN games_played != 0 THEN CAST(total_crits_lots AS REAL) / games_played ELSE NULL END) STORED,
|
|
/** TODO: We should probably also evaluate this over normally-finished (or won?) games */
|
|
average_game_moves REAL GENERATED ALWAYS AS (CASE WHEN games_played != 0 THEN CAST(total_game_moves AS REAL) / games_played ELSE NULL END) STORED,
|
|
PRIMARY KEY (user_id, variant_type)
|
|
);
|
|
|
|
|
|
DROP TABLE IF EXISTS endgames;
|
|
CREATE TABLE endgames (
|
|
game_id INTEGER REFERENCES games (id),
|
|
turn SMALLINT,
|
|
/**
|
|
* We want to be able to store probabilities for different actions that can be taken.
|
|
* Action type can be
|
|
0 for play actions
|
|
1 for discard actions
|
|
2 for clues
|
|
*/
|
|
action_type SMALLINT CHECK (0 <= action_type AND action_type <= 2),
|
|
/**
|
|
We store cards as (suit_index, rank) here for uniqueness of representation.
|
|
If we want to refer to known trash, we will use (0,0) as representation.
|
|
*/
|
|
suit_index SMALLINT, /* 0 for clue actions */
|
|
rank SMALLINT, /* 0 for clue actions */
|
|
enumerator BIGINT NOT NULL CHECK (enumerator >= 0),
|
|
denominator BIGINT NOT NULL CHECK (denominator > 0),
|
|
chance REAL GENERATED ALWAYS AS (CAST(enumerator AS REAL) / denominator) STORED,
|
|
PRIMARY KEY (game_id, turn, action_type, suit_index, rank)
|
|
);
|
|
|
|
/**
|
|
We store separately whether we analyzed a certain game already and what the termination reason for the analysis was:
|
|
0 if evaluation completed within specified time and memory
|
|
1 if evaluation ran into timeout
|
|
2 if evaluation was empty because state is unreachable
|
|
3 if evaluation ran out of memory
|
|
This is also necessary because for some endgames, because in case 2 we will not have data,
|
|
simply because the game replay ended too early.
|
|
To avoid re-analyzing these seeds, we mark all seeds analyzed in this table.
|
|
*/
|
|
DROP TABLE IF EXISTS endgames_analyzed;
|
|
CREATE TABLE endgames_analyzed (
|
|
game_id INTEGER REFERENCES games (id),
|
|
termination_reason SMALLINT NOT NULL,
|
|
PRIMARY KEY (game_id)
|
|
); |