I have a turn based strategy game for desktop and mobile, the game app uses a sqlite database to play local games. There's nothing special about the database design, I have tables to keep track of units in the game, player commands, etc.
I want to implement multiplayer now on a dedicated game server, and am thinking of using the same setup as the client apps. I'd create a sqlite db on the server file system for each game my users create. I'm expecting around 1,500 simultaneous games (based on other games I've run).
Here are the pros I imagined:
- I can reuse all the game code as-is - I just point the game wrapper server-side to a sqlite db on the server machine.
- Cheap to start and delete games (happens quite often with turn based games).
- Hitting disk a lot, since sqlite is a wrapper around a file basically (my game server has SSD drives, tho).
- Portability - I'd have a mess of sqlite files on the file system. If I have to move servers, could be a little gross copying them to a new machine. Also have to check the # of files in a directory limit for my OS, or shard them.
- Front end synchronization - I'd still have to have a centralized mysql db or similar to keep track of all the individual games on disk, their general states (started, waiting, ended etc) for general user queries on their games list.
I'd be interested if anyone has tried a setup like this. I can always use a single mysql instance and one set of tables shared between all games, just need to add a gameId column to separate all the data. It would be interesting to see the Units table though with millions of rows shared between all the games.