GB2 Database Format

This document explains the SQLite database format for GB2. It uses the existing GB1.3 database format as an example of how it might be represented in GB2.

In the current GameBase database format, both game data (game name, publisher, genre, etc.) and personal data (highscore, difficulty, personal rating, favourites, etc.) are stored in the same database. This makes updates to the main game data more work, as an “import” of personal data from a previous version of a database needs to be done in order to preserve it.

This is resolved in GB2 by splitting all game data and user data into 2 separate database files: master and user.

• master - This stores all the game data and updates are accomplished by simply replacing the old database file with the new one.
• user - This stores all the user generated data. It never gets overwritten when a new master database in installed. It links to items (games) in the master by ID and can store any data the user wishes against any game in the master (even files/screenshots etc).

When the frontend loads a “database” - it is actually loading both database files into one database connection. There is obviously a stipulation that for this to work the meaning of the items (e.g. games) in the master database cannot change once added/distributed (e.g. id 12345 is *always* Impossible Mission). This is generally the case in existing GameBase databases but should be enforced as much as possible in GB2.

A user database can only be shared with a single master database. The user database stores a GUID of its master and will only work with that.

GB2 databases do not have a fixed data format. They don't have to store “Games”… they could just as well store “Apps”, “Demos”, “Books”, “Movies”, or really whatever Collection is desired. They do this by containing information about the data they store in meta tables. This includes the columns, data types and other meta data necessary to generate, read from and write to data tables. No longer is a GameBase database required to host a “publisher” field, or a “musician” field. The creator of the master db can decide what information about their collection they want to store.

• Collection
  • A Collection is a top-level “list” of items that a GB2 database can store.
  • There can be more than 1 collection per database. For example, in the GB64 database, there would be 2 collections: a “Games” collection and a “Music” collection.
  • Each Collection in a database has its own meta table and corresponding data table.
  • A full Collection is only present in a master database. In a user database, an item in a Collection table simply points to the ID of the item in the master Collection table.
  • It's possible to view data from multiple Collections in one list or “View”.
• List
  • A List is a table that is linked to from a Collection (or a Link Table - see below).
  • There can be many Lists in a database and they are shared across all Collections. For example, in the GB64 database, the “Musicians” table would be a List (it is linked to from the “Games” and “Music” collections).
  • Each List in a database has its own meta table and corresponding data table.
  • A List is not shared between a master and a user database. That is, a List present in a master database can only be linked to from a Collection in the master db (and vice versa).
• Link Table
  • When linking multiple rows from a List into a Collection (e.g. 3 “programmers” are linked to a “game”), a Link Table is used. Generally this stores the ID of both the collection and list item (e.g. collection_item_id, list_item_id).

A slug is a key or system name that identifies certain items in a database. For example, each Collection and List has a slug to identify it. Slugs consist of lower-case letters and numbers only and by design can be used as OS folder or filenames. They adhere to the following regex: ^[a-z][a-z0-9]*$

Data describing an item in a collection or list can in most cases be defined by a native SQLite data type. For example, if you wanted to add a List of Programmers, you could use the SQLite data type “TEXT” for the programmer's name.

Some data items however have special meanings and as such cannot be described by an SQLite data type alone. For example, the “name” of an item in a Collection is shown a specific way in the frontend (maybe in large bold text).

For these special data items, Meta Column Types are used. This basically gives the frontend a heads-up that it has to do something special with a data item.

The following special-case fields are those that have a specific behaviour and cannot be defined by an SQLite datatype alone.

The types shown in the Required, Validation Min, Validation Max, Validation Regex and Precision columns below mean the following…

• meta - The value is determined in the host meta table (e.g. meta_collection_game).
• linked-to meta - The value is determined in the linked-to meta table (in the case of ONE and MANY column types - e.g. meta_list_programmer).
• n/a - Not applicable.

Locations: [master, user]

This table stores core database information that should not be changed manually. Values in this table are auto-generated and read-only.

Examples

Locations: [master]

The meta_master table contains core information on the master database such as its name, version, terms, license, etc.

Example

Locations: [master]

This table stores contacts associated with the database project. E.g those that made the database, or are copyright holders etc.

Example

Locations: [master]

This table stores URLs pertaining to the database. For example a Help URL, a Website URL, a “Submissions” URL, etc.

Example

Collections are what in GameBase are currently the “Games” and “Music” tables. They may be unioned together and shown in one list in the frontend (e.g. the “all music” view in GameBase shows all games-with-music from the “Games” table, along with all entries from the “Music” table). Unioned Views are not Collections; they are custom views based on multiple Collections that share common columns.

Locations: master

Example

Locations: master, user

This is a meta definition of the “data” table for a Collection. This is created by the database creator. Columns in this table will be editable in the GBToolbox, but not in the Frontend by default, unless explicitly unlocked by the user, or unless the database creator has set a password on the db for extended edit functionality.

Examples: meta_collection_game, meta_collection_music

Locations: master, user

This table contains a list of “media sets” for any given Collection. A media set is a set of related “media files” linked to an item in a collection. For example, a list of game files, a list of screenshots, music files, …/

Example: meta_collection_game_mediaset

* The “handler” column determines how the file is handled by the frontend…

• script - uses a script (javascript) to run the media (replacement for GEMUS)
• image - uses GB2's inbuilt image viewer if media file's mimetype supported, else uses shell option
• video - uses GB2's inbuilt video viewer if media file's mimetype supported, else uses shell option
• doc - uses GB2's inbuilt document viewer if media file's mimetype supported, else uses shell option
• shell - lets the OS run the media file in whatever the registered application is

This is to group columns in a Collection into datasets (e.g. could be tabs in the frontend).

Locations: master, user

Example: meta_collection_game_dataset

Example

Locations: master, user

This is a meta definition of the “data” table for a List.

Examples: meta_list_publisher, meta_list_musician

Most of these tables are generated from data defined in the corresponding meta table, as linked by the tables' [slug].

This table is generated using data in the meta table meta_collection_[slug].

Location: master

Location: user

Location: master, user

This table only exists if the meta_collection_[slug]_mediaset table has entries. It stores media files linked to the collection specified in the first [slug]'s mediaset (specified in the second [slug]).

Location: user

This table contains statistical/user information pertaining to records in the corresponding data_collection_[slug]_mediaset_[slug] table.

This table is generated using data in the meta table meta_list_[slug].

Location: master, user

This table links data in the Collection identified by the first [slug] and the List identified by the second [slug], and defines a one-to-many relationship between the tables. Used when meta column types MANY or MANYITEM (or derivatives of) are specified in the Collection's meta table. E.g. data_link_game_publisher.

This table resides in the same database as that which hosts the List (Lists - and thus data_link tables - are not shared across master and user databases).

Location: master, user

Location: master

Location: user

Location: master

Location: [master]

Location: [master]

Location: master

Location: master

Location: user

Location: master

Location: master

Location: user