OMGWords Migration

[jvc56]

Purpose

The purpose of this migration is improve the database representation of an OMGWords game, so that game data are more visible to queries, queries on games are more scalable and efficient, gameplay architecture is improved, and game stats are corrected and improved.

Outcomes

GORM is replaced by PGX

GORM makes it difficult to write the efficient queries we need to be able to successfully remove the game cache and still provide a fast user experience. We are also moving away from it and towards managing our own SQL queries, which will allow us to use proper transactions.

Game cache is removed

The game cache should be removed if we ever want to scale to multiple servers.

Game requests and histories are converted from binary to a more queryable format

Right now the game request and history are stored in a binary format. This makes them difficult to query using SQL.

Queries for historical games are improved

This might involved creating a separate table with just a game id, player id, and timestamp to make queries of a users game history more performant.

Game stats are refactored and recalculated

The original game stats were written very early in the development of woogles and can be greatly improved. This includes moving away from JSON heavy formats, removing stats that aren't useful or needed yet, and recalculating certain stats that might have errors.

[domino14]

My main thought about GameHistory - it should probably not be saved in the db by default.

• We should have a game_turns table with game id / sequence number.
• Sequence number should be sent from the user to the server when making a new move.
• When someone takes a turn, it should update this table if the game id / sequence number don't already exist (set unique index)
• When we load a game on refresh, we get a list of game_turns so far instead of a GameHistory (plus any other metadata that is now in the GameHistory. It could actually even just send the same exact GameHistory, but the events list would just be empty).
• When a game ends, the GameHistory events list can be created in a transaction, and the game turns can be deleted from the table if we want. Unless we might need them for something else.

[jvc56]

• Is the games_turn table just the game id / sequence number? Does it have any other info? Would it contain the moves themselves or would the moves be references to the games table?
• If we send a list of game_turns on refresh, why would we ever delete the game turns?