Query

GOG Galaxy 2.0 uses an SQLite DB to store game information. This is located at:

C:\ProgramData\GOG.com\Galaxy\storage\galaxy-2.0.db

If you want to browse that database, you'll have to install the corresponding SQLite binaries for your operating system and you can find its guide here. To open the database, use the .open command:

sqlite3> .open "C:\\ProgramData\\GOG.com\\Galaxy\\storage\\galaxy-2.0.db"

Note: Remember to escape the backslashes.

To list the available tables, use the .tables command like this:

sqlite3> .tables

This will give you a list of available tables. Of these, three are our primary concern. They are

• GameLinks: Contains a list of all games we have using releaseKey which is the only relevant column.
• GamePieces: Contains a list of all the data used by the launcher and not just what you own. This includes the rows releaseKey, gamePieceTypeId and value. (The other columns are irrelevant.)
• GamePieceTypes: Contains a region specific list of which gamePieceTypeId maps to what type of value.

You can check the columns using pragma table_info(table_name):

sqlite3> pragma table_info(GamePieces)

Here's an explanation of each relevant column for the first two tables:

Column Name	Data Type	Use
releaseKey	STRING	This uniquely represents which game you have and which platform you own it on. It is typically of the type platformname_gameid, for example gog_123134. You can even have multiple releases on the same platform. For example, during some giveaways, Humble Bundle gave away a key as well as a DRM-free build of the game. This counts as two separate releaseKeys.
gamePieceTypeId	INT	This value identifies the type of info the corresponding releaseKey entry holds.
value	STRING	This simply holds the relevant text in the form of a JSON string. For example, "{"title":"Alan Wake"}".

Example output for a single releaseKey:

steam_108710|170|{"criticsScore":83,"developers":["Remedy Entertainment"],"genres":["Adventure","Shooter"],"publishers":["Microsoft Game Studios","Nordic Games Publishing","E-Frontier","Remedy Entertainment","Namco Bandai Partners","Legacy Interactive"],"releaseDate":1329350400,"themes":["Action","Horror","Thriller"]}   
steam_108710|171|{"title":"Alan Wake"}
steam_108710|239|{"criticsScore":83,"developers":["Remedy Entertainment"],"genres":["Adventure","Shooter"],"publishers":["Microsoft Game Studios","Nordic Games Publishing","E-Frontier","Remedy Entertainment","Namco Bandai Partners","Legacy Interactive"],"releaseDate":1329350400,"themes":["Action","Horror","Thriller"]}   
steam_108710|244|{"title":"Alan Wake"}

Here's an explanation for the columns of the GamePieceTypes database:

Column Name	Data Type	Use
id	INT	This value is the one you'll have to use to denote the required gamePieceTypeId.
type	STRING	This value tells you the type of value correponding to the gamePieceTypeId specified in id. For example, in my region, an id of 171 corresponds to originalTitle. Here are the following fields that are relevant: - originalTitle, title: The corresponding entry for value is the game's title. - originalMeta, meta: The corresponding entry for value is the game's metadata. Note that these integers are to be used in conjunction with the GamePieces table.

Example output:

171|originalTitle

We need to obtain all the values pertaining to our owned games from the GameLinks table. We can accomplish this using a JOIN. Here's the corresponding query:

sqlite3> CREATE TEMP VIEW MasterList AS SELECT GamePieces.releaseKey, GamePieces.gamePieceTypeId, GamePieces.value FROM GameLinks JOIN GamePieces ON GameLinks.releaseKey = GamePieces.releaseKey;

We create a temporary view so that it doesn't persist in the database and use a SELECT clause such that we select only those games' values which are present in the GameLinks database. This actually contains a lot of duplicates as many games with different release key may actually be the same title. We could select unique titles, but then naming variations would cause issues. For example: Batman: Arkham Knight vs Batman - Arkham Knight. A simpler method would be to select by metadata as these remain unique to the game regardless of its title. So our next query is:

sqlite3> CREATE TEMP VIEW UniqueMasterList AS SELECT DISTINCT(MasterList.value) AS metadata, MasterCopy.value AS title FROM MasterList, MasterList AS MasterCopy WHERE ((MasterList.gamePieceTypeId = 239) OR (MasterList.GamePieceTypeId = 170)) AND ((MasterCopy.gamePieceTypeId = 171) OR (MasterCopy.gamePieceTypeId = 244)) AND (MasterCopy.releaseKey = MasterList.releaseKey);

Here, one may ask why a self join is necessary. This is because we want to move away from using gamePieceTypeId and instead make the title and metadata part of a single entry or row. We take a unique "metadata"/"details" value and match it with the second copy of MasterList. From there, we select the corresponding releaseKey and then select the title's matching the selected releaseKey from the MasterList copy. Also, my values of 239 and 170 are for metadata and 171 and 244 are for game titles. Note again that these gamePieceTypeIds are region specific and that you'll have to use values for your own region. Now all we need to do is add the releaseKey from each title and group them all together to display all the platforms under a single title. Again we use metadata as the unique attribute:

sqlite3> SELECT UniqueMasterList.title, GROUP_CONCAT(DISTINCT MasterList.releaseKey), UniqueMasterList.metadata FROM UniqueMasterList, MasterList WHERE UniqueMasterList.metadata = MasterList.value GROUP BY UniqueMasterList.metadata ORDER BY UniqueMasterList.title;

We perform another join operation, this time on MasterList to select all entries that match a particular title based on the metadata attribute and add them together by concatenating those strings. To group them under a title, we use GROUP BY while the ORDER BY clause simply sorts the results alphabetically.

All queries can undoubtedly be streamlined and I am therefore open to suggestions to optimize them. Perhaps one improvement would be to perform a join of GamePieceTypes on GamePieces to weed out the irrelevant data.