Migrate games to database solution

[Solaire]

Using JSON to store information about the games has worked quite well, but I don't feel like it's the best approach. Those are some of the constraints I have observed:

• In order to modify anything, the app essentially rewrites the entire JSON file with data stored in the memory. (You can imagine how many times the file might be written to)
• The app needs to read the entire JSON file and keep it in memory which means more memory usage when additional games and attributes are introduced
• There is no backwards compatibility between versions.

A lightweight database implementation will bring the following benefits:

• Reduce the memory use, the app will only load the information it currently needs,
• Backwards compatibility. The app will be able to use the same database regardless of future versions (it will need to handle DB schema changes)
• Better performance. No longer needing to parse the JSON for pre-emtively load all data and no longer needing to modify the file with every small change.

The best candidate for this feature is liteDB which apparently only requires a small DLL

The following features will need to be implemented:

• A DB core implementation (singleton design pattern?)
• Implement game reading and writing functionality
• Constant database schema. This could be a file which the program would use or a hard-coded/constant source file. With each launch the program will check the database and create it (if not found) or apply any missing tables/columns
• Migrate the data from JSON to the database on startup. I imagine this feature would be present in the next version only (so that we can remove any unused code) and after that a small, dedicated, application would be created

Refer to the 'Database migration' projects page for bugs and user stories

[Nutzzz]

One benefit of using SQLite instead of liteDB is that the itch implementation I checked in last night already uses it, so we have that .dll requirement already (to at least some extent).

[Solaire]

I did some testing with LiteDB and SQLite and I think the second one is better. As you said, itch already requires SQLite and, LiteDB is still JSON-based and it's noSQL.

[Nutzzz]

FWIW, here's a crack at a database schema, which includes some ideas for future expansion. Note the "players" table is for future support of a file-based interface for e.g., emulators or media players, where the user would select a file in a given path that matches a given filemask; this file would then be used as a parameter in a launch string for the emulator or media player.

TABLE games (
  game_id INTEGER PRIMARY KEY,
  name STRING UNIQUE,
  title STRING,
  alias STRING,
  launch STRING,
  param STRING,
  icon STRING,
  uninstall STRING,
  favourite BOOLEAN,
  hidden BOOLEAN,
  rating INTEGER,
  description STRING,
  platform INTEGER FOREIGN KEY
);

TABLE platforms (
  platform_id INTEGER PRIMARY KEY,
  platform_name STRING,
  description STRING
);

TABLE players (
  player_id INTEGER PRIMARY KEY,
  title STRING,
  launch STRING,
  param STRING,
  icon STRING,
  filepath STRING,
  filemask STRING,
  fileiconpath STRING,
  description STRING
);

TABLE tags (
  tag_id INTEGER PRIMARY KEY,
  tag_name STRING UNIQUE,
  description STRING
);

TABLE game_tags (
  game_id INTEGER PRIMARY KEY,
  tag_id INTEGER PRIMARY KEY
);

[Solaire]

Looks good, quite similar to the initial schema I have been testing SQLite with. Couple of points though: Instead of tags and game_tags tables, we could have a GameAttribute table defined as so:

TABLE "GameAttribute" (
    "GameFK"    INTEGER,
    "AttributeType" INTEGER,
    "AttributeIndex"    INTEGER,
    "AttributeValue"    varchar(255),
    FOREIGN KEY("GameFK") REFERENCES "Game"("GameID")
);

Initially the table could be used to store many tags and aliases for a game, all keyed on the PK. In the future, if we need to add further attributes, we wouldn't have to change the schema. The database will be small and I don't expect it to be filled with tons of data so performance-wise there should be no issue.

The same goes for Platform and Player tables - if they need attributes then those tables can be added (perhaps in the future)

[Nutzzz]

Hm... what's the scope of an "attribute"? Is it anything the user can change? Then couldn't most of the columns potentially be an attribute?

[Solaire]

The attribute table has three purposes:

• Store optional data that most 'objects' might not have. For example, I doubt the users will assign an alias to every game so instead of having a dedicated column, attribute could be beneficial
• Allow us to add extra properties without changing the schema
• Allow storage of multiple properties with the same name. For example, a game might have none, one or many tags. Adding multiple tags to a single column is not a big deal but when you might want to write a query by tag, then it might be complex and inefficient.

[Nutzzz]

FYI, I do create a default alias by removing articles (e.g., "the") at the beginning and any spaces or symbols (but then set it to blank if it ends up the same as the title after all, e.g., one-word titles). I'm not sure if it makes sense to have more than one alias. No question that tags fit the attribute criteria, and I like the idea that it's easily extensible for other like features.

Platform could even be an attribute. If not, perhaps there ought to be a merge attribute that assigns a relationship between two or more games that are actually the same game under more than one platform (I know I have a few of these). In this case you could have a function to find duplicates and assign one as default--for searches at least, though perhaps the default would carry over if the user selected it from the "wrong" platform.

[Solaire]

Yeah, sounds like one alias might be enough for now. I will add an alias column to the schema.

As for the games with multiple platforms, this is a tricky one. On one hand it would make sense for the game to be shown on both platforms but that would result in duplicate data. I think the best solution would be to pick one platform (the first one or where the game actually exists?) and add the other platforms as attributes. In order to avoid having to scan through each game's attributes, I can add a IsMultiPlatform flag to each game.

I doubt there is a significant number of games that would have multiple platforms (I have 1 out of 35 games and most of them will be between Steam and Ubisoft, Origin or some other company-specific client)

[Nutzzz]

Yes, the multi-platform idea doesn't matter nearly as much without an implementation of #10

[Nutzzz]

What are your thoughts about the configuration information? I used json since it was already there... though I do have a half-completed move to .settings as something I was playing with, as I'd never used those before. However it doesn't seem any easier for a user to deal with directly than .json, nor is it particularly easier to use from the programming side. If it's going to remain a file the user edits, then I think I would have made it an .ini had I done it from scratch... but I was thinking a quick in-app settings menu wouldn't be too hard for me to do. In that case, should the data move to the db too or just remain in a file?

And there's the same question for the silly json I used for command-line interaction in non-interactive mode (for fuzzy searches and re-running the last game).

[Solaire]

I think .ini format is much better than json for configuration - it has better readability and the parsing is dead-simple in comparison. When I made the first iteration of this app (that was 2-3 years ago now) as a GUI, I had an ini config file. I decided to rewrite everything as a console app and removed the configuration stuff as I didn't need it then. There is an IniParser C# source code in my public repo - nothing too complex, just some winDLL calls I think, so if we're moving the config then you can have a look at that.

Cramming the settings into the DB is also valid but the downside is that the user will not be able to edit the settings outside the program (unless they download SQLite tool). It's just a thought but I think .ini is the way to go.

As for the last bit, I think we can stuff that into the database and retrieve it on startup if necessary (probably use the system attribute table)

Ideally we should be able to completely remove the json dependency

[Nutzzz]

I agree about .ini. but note that the json dependency will remain for importing games from Epic, Indiegala, and itch (the last uses a SQLite db, but has json in the one of the fields).