User Tools

Site Tools


gb2

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

gb2 [2022/06/09 18:09] (current)
Jimbo created
Line 1: Line 1:
 +====== 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.
 +
 +===== General Concepts =====
 +
 +==== Master and User databases ====
 +
 +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.
 +
 +==== Meta and Data tables ====
 +
 +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.
 +
 +==== Collections,​ Lists and Link Tables ====
 +
 +  * **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).
 +
 +==== Slugs ====
 +
 +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]*$
 +
 +\\
 +
 +===== Meta Column Types =====
 +
 +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.
 +\\
 +^ Meta Column Type ^ SQLite DataType ^ Example Values ^ Description ^ Required ^ Validation Min ^ Validation Max ^ Validation Regex ^ Precision ^
 +| BOOLEAN | INTEGER | 0 or 1 | Represents a YES/NO, TRUE/FALSE, ON/OFF value. Treated/​displayed a certain way in the frontend. | meta | 0 | 1 | none | n/a |
 +| CLONEOF |  //-a collection item-// ​ || A version of ONEITEM that is treated/​displayed a specific way in the frontend. Restricted to item's in the same [[#​Collection]]. | meta | n/a | n/a | n/a | n/a |
 +| COMMENT | TEXT | "Cheat code: 23455" | COMMENT fields are displayed a certain way in the frontend. | meta | meta | meta | meta | n/a |
 +| CURRENCY | REAL | 19.99 | Represents a monetary amount. Treated/​displayed a certain way in the frontend. | meta | meta | meta | n/a | meta |
 +| DATE | TEXT | "​2012-04-15"​ | Represents a date in ISO 8601 format. Treated/​displayed a certain way in the frontend. | meta | n/a | n/a | n/a | n/a |
 +| DATETIME | TEXT | "​2012-04-15 23:​34:​13"​ | Represents a date/time in ISO 8601 format. Treated/​displayed a certain way in the frontend. | meta | n/a | n/a | n/a | n/a |
 +| MANY |  //-any column from linked-to table-// ​ || Can only be used in a [[#​meta_collection_[slug]]] table (**user** and **master**). Uses a link table to provide a one-to-many relationship to records in a **List**. MANY records can be linked to (e.g. a game links to 5 programmers). | meta | linked-to meta | linked-to meta | linked-to meta | n/a |
 +| MANYITEM |  //-a collection item-// ​ || Can only be used in a [[#​meta_collection_[slug]]] table (**user** and **master**). Uses a link table to provide a one-to-many relationship to items in another (or the same) [[#​Collection]]. MANY items can be linked to (e.g. a game links to 5 other "​related"​ games). | meta | meta | meta | n/a | n/a |
 +| MULTILINE | TEXT | "I am a Multi-line\nstring of text\non lots of lines\nwith line breaks."​ | Represents a multi-line text string. Treated/​displayed a certain way in the frontend. | meta | meta | meta | meta | n/a |
 +| NAME | TEXT | "Great Giana Sisters, The" | Can only be used in a **master** [[#​meta_collection_[slug]]] table. The textual identifier (name/​title/​etc) of a Collection'​s item. There must be one and only one NAME column per [[#​meta_collection_[slug]]] table. Treated in the frontend as a special case for displaying, formatting, editing, etc. | yes | meta | meta | meta | n/a |
 +| ONE |  //-any column from linked-to table-// ​ || Can only be used in a [[#​meta_collection_[slug]]] table (**user** and **master**). Links to a record in a **List** by its INTEGER PRIMARY KEY. Only ONE record can be linked to (e.g. a game links to one publisher). | meta | linked-to meta | linked-to meta | linked-to meta | n/a |
 +| ONEITEM |  //-a collection item-// ​ || Can only be used in a [[#​meta_collection_[slug]]] table (**user** and **master**). Links directly to an item in another (or the same) [[#​Collection]]. ONE item can be linked to (e.g. a game is a "clone of" one other game). | meta | n/a | n/a | n/a | n/a |
 +| PERCENTAGE | INTEGER | 50, 0, 100 | Represents a percentage from 0% to 100%. Treated and displayed a certain way in the frontend. | meta | 0 | 100 | none | n/a |
 +| PRECURSOR |  -a collection item-  || Links to the item's PRECURSOR. Treated/​displayed a certain way in the frontend. Low level this column stores the precursor-item'​s ID (from the same [[#​Collection]]). Can only be used in a **master** [[#​meta_collection_[slug]]] table and there cannot be more than one.  | meta | n/a | n/a | n/a | n/a |
 +| RANGEFROMINTEGER | INTEGER | 1 | The "​from"​ part of a "​range"​ of numbers. ​ Should always have a corresponding RANGETOINTEGER field with the same column_name (see the example in [[#​meta_collection_game]]). At the db level, this takes the column name and suffixes "​_range_from"​ to it. | meta | meta | meta | meta | n/a |
 +| RANGETOINTEGER | INTEGER | 10 | The "​to"​ part of a "​range"​ of numbers. ​ Should always have a corresponding RANGEFROMINTEGER field with the same column_name (see the example in [[#​meta_collection_game]]). At the db level, this takes the column name and suffixes "​_range_to"​ to it.  | meta | meta | meta | meta | n/a |
 +| RANGEFROMREAL | REAL | 3.99 | The "​from"​ part of a "​range"​ of numbers. ​ Should always have a corresponding RANGETOREAL field with the same column_name. At the db level, this takes the column name and suffixes "​_range_from"​ to it. | meta | meta | meta | meta | meta |
 +| RANGETOREAL | REAL | 9.99 | The "​to"​ part of a "​range"​ of numbers. ​ Should always have a corresponding RANGEFROMREAL field with the same column_name. At the db level, this takes the column name and suffixes "​_range_to"​ to it.  | meta | meta | meta | meta | meta |
 +| SEEALSO |  //-a collection item-// ​ || A version of MANYITEM that is treated/​displayed a specific way in the frontend. Restricted to item's in the same [[#​Collection]] Can only be used in a **master** [[#​meta_collection_[slug]]] table and there cannot be more than one. | meta | meta | meta | n/a | n/a |
 +| SEQUEL |  -a collection item-  || Links to the item's SEQUEL. Treated/​displayed a certain way in the frontend. Low level this column stores the sequel-item'​s ID (from the same [[#​Collection]]). Can only be used in a **master** [[#​meta_collection_[slug]]] table and there cannot be more than one.  | meta | n/a | n/a | n/a | n/a |
 +| TAGLINE | TEXT | "The brothers are history"​ | Can only be used in a **master** [[#​meta_collection_[slug]]] table. The textual identifier of an item's tag line. There must be one and only one TAGLINE column per [[#​meta_collection_[slug]]] table. Treated in the frontend as a special case for displaying, formatting, editing, etc. | meta | meta | meta | meta | n/a |
 +| URL | TEXT | http://​foo.com/​ | Any field column called "​url"​ or ending in "​_url"​ will automatically be treated as a URL by the frontend. ​ An additional column called "​anchor"​ or (same name) ending in "​_anchor"​ can be specified too, and if exists is used as the text/anchor for the URL when shown in the frontend. | meta | n/a | n/a | meta | n/a |
 +
 +\\
 +
 +===== List of Tables =====
 +
 +^ Table Name ^ Location ^ Description ​ ^
 +| [[#​_meta]] ​ | master, user | Read only table containing core information on database format, version and identification. |
 +| [[#​meta_master]] ​ | master | Core information on the master database. |
 +| [[#​meta_master_contact]] ​ | master | Top level contacts (people) relating to the master database. |
 +| [[#​meta_master_url]] ​ | master | Top level web links relating to the master database. |
 +| [[#​meta_collection]] ​ | master | Defines which collections are in the database. |
 +| [[#​meta_collection_[slug]]] ​ | master, user | Defines a specific **Collection**. E.g. meta_collection_game. |
 +| [[#​meta_collection_[slug]_mediaset]] ​ | master, user | Defines how specific media sets are used for a specific collection. |
 +| [[#​meta_collection_[slug]_dataset]] ​ | master, user | Defines how certain data is grouped in a specific Collection. |
 +| [[#​meta_list]] ​ | master, user | Defines which lists are in the database. |
 +| [[#​meta_list_[slug]]] ​ | master, user | Defines a specific **List**. E.g. meta_list_publisher. |
 +| [[#​data_collection_[slug]]] ​ | master, user | Contains the data defined in [[#​meta_collection_[slug]]]. E.g. data_collection_game. |
 +| [[#​data_collection_[slug]_mediaset_[slug]]] ​ | master, user | Contains the media data for a specific Collection'​s mediaset. |
 +| [[#​data_collection_[slug]_mediaset_[slug]_stat]] ​ | user | Contains stats/​userdata for the media data of a specific Collection'​s mediaset. |
 +| [[#​data_list_[slug]]] ​ | master, user | Contains the data defined in [[#​meta_list_[slug]]]. E.g. data_list_publisher. |
 +| [[#​data_link_[slug]_[slug]]] ​ | master, user | A one-to-many link table from a Collection to a List. E.g. data_link_game_publisher. |
 +
 +\\
 +
 +===== Meta Tables =====
 +
 +==== _meta ====
 +
 +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.
 +
 +^  _meta ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| _version_major ​ | INTEGER | Major version number of database format. |
 +| _version_minor ​ | INTEGER | Minor version number of database format. |
 +| _guid  | TEXT | Globally unique indentifier for the database - auto-generated on database creation. |
 +| _creation_datetime_utc ​ | TEXT | UTC timestamp in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +| _master_guid ​ | TEXT | For user databases, this will be the __guid of the master database. Not present in master databases. |
 +
 +[[#​_meta_table|Examples]]
 +
 +\\
 +
 +==== meta_master ====
 +
 +Locations: **[master]**
 +
 +The meta_master table contains core information on the master database such as its name, version, terms, license, etc.
 +
 +^  meta_master ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| long_name ​ | TEXT | Full name of the database, e.g. "The Gamebase64 Collection V8 (c) 2010 The GB64 Team". |
 +| short_name ​ | TEXT | Short or abbreviated name of the database, e.g. "GB64 v8". |
 +| version_major ​ | INTEGER | Major version of database. |
 +| version_minor ​ | INTEGER | Minor version of database. |
 +| version_release_date ​ | TEXT | Release date for this version, in ISO8601 format ("​YYYY-MM-DD"​). |
 +| intro_message ​ | TEXT | Message shown to users the first time they load the database. Can be NULL. |
 +| terms  | TEXT | Terms/​Conditions of use. Shown before loading the database for the first time. Users will be required to "​accept"​ the terms before continuing. Can be NULL. |
 +| license_name ​ | TEXT | Name of the license the database is released under, e.g. "​Creative Commons 2.0". Can be NULL for no license. |
 +| license_url ​ | TEXT | URL pointing to web page of the specified license_name. Can be NULL. |
 +| license_text ​ | TEXT | Full text version of the license. Can be NULL. |
 +| acknowledgements ​ | TEXT | Text containing acknowledgments. Can be NULL. |
 +| copyright_year ​ | TEXT | Copyright year of the database (in YYYY format). |
 +| copyright_holder ​ | TEXT | Name of the copyright holders (e.g. "The GB64 Team"​). | 
 +
 +[[#​meta_master_table|Example]]
 +
 +\\
 +
 +==== meta_master_contact ====
 +
 +Locations: **[master]**
 +
 +This table stores contacts associated with the database project. E.g those that made the database, or are copyright holders etc.
 +
 +^  meta_master_contact ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id  | INTEGER PRIMARY KEY ||
 +| name  | TEXT | Contact'​s name. e.g. "Bobby Brown" |
 +| email_address ​ | TEXT | Contact'​s email address, e.g. bobbybrown@goesdown.com. Shown in the frontend if supplied. |
 +| url  | TEXT | Contact'​s URL, e.g. "​http://​www.facebook.com/​bobbybrown/​. ​ Shown in the frontend if supplied. |
 +| anchor ​ | TEXT | Name/anchor text for the contact'​s URL (can be NULL). ​ e.g. "​Facebook"​. ​ Shown in the frontend if supplied. |
 +| roles  | TEXT | What the contact did in the project. ​ E.g. "​Database creator and maintainer."​. Shown in the frontend if supplied. |
 +| is_copyright_holder ​ | INTEGER | 0 (FALSE) or 1 (TRUE). Whether the contact is a copyright holder to the database. Shown in the frontend'​s "​copyright holder'​s"​ list if set. |
 +| ordinal ​ | INTEGER | Determines what order the contact is shown in the frontend. |
 +
 +[[#​meta_master_contact_table|Example]]
 +
 +\\
 +
 +==== meta_master_url ====
 +
 +Locations: **[master]**
 +
 +This table stores URLs pertaining to the database. For example a Help URL, a Website URL, a "​Submissions"​ URL, etc.
 +
 +^  meta_master_url ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id  | INTEGER PRIMARY KEY ||
 +| url  | TEXT | The URL, e.g. "​http://​www.gb64.com/​submissions"​. |
 +| anchor ​ | TEXT | The name/anchor text for the URL, e.g. "​Submissions"​. Can be NULL. |
 +| description ​ | TEXT | More in-depth description text of what the URL is for.  Can be NULL. |
 +| ordinal ​ | INTEGER | Determines what order the URL is shown in the frontend. |
 +
 +[[#​meta_master_url_table|Example]]
 +
 +\\
 +
 +==== meta_collection ====
 +
 +**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**
 +
 +^  meta_collection ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id |  INTEGER PRIMARY KEY  |  |
 +| slug |  TEXT  | Related meta/data tables use this as a prefix/​identifier. |
 +| name |  TEXT  | What the collection is displayed as in the frontend. |
 +| item_name |  TEXT  | What a single item is refered to in this collection. |
 +| item_name_plural |  TEXT  | Plural version of item_name. |
 +| ordinal |  INTEGER ​ | Sort order position shown in the frontend (e.g. in a "view selector"​). |
 +
 +[[#​meta_collection_table|Example]]
 +
 +\\
 +
 +==== meta_collection_[slug] ====
 +
 +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.
 +
 +^  meta_collection_[slug] ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| column_name |  TEXT  | The name used as the column name in the corresponding data table. |
 +| column_type |  TEXT  | The name of the column type. This maps to either a native SQLite data type, or a "​meta"​ data type for columns that have more specific behaviour. |
 +| name |  TEXT  | How the column is named to a human |
 +| name_plural |  TEXT  | As above but when its refered to in its plural form. |
 +| master_name |  TEXT  | How the column is named to a human for fields with shared ordinals. |
 +| is_required |  INTEGER ​ | Whether data for the column is required. Data in columns are stored as NULL if not required. NULL columns can correspond to "​Unknown"​ values in the frontend (e.g. the publisher for a game is unknown). |
 +| validation_min |  TEXT  | Min length of string if underlying SQLite datatype is TEXT, min value if datatype is INTEGER or REAL, min number of permissable linked records if meta column type is MANY/​MANYITEM (or a derivation of), NULL if there is no restriction or it is not applicable. |
 +| validation_max |  TEXT  | Max length of string if underlying SQLite datatype is TEXT, max value if datatype is INTEGER or REAL, max number of permissable linked records if meta column type is MANY/​MANYITEM (or a derivation of), NULL if there is no restriction or it is not applicable. |
 +| validation_regex |  TEXT  | Regular expression for validation of TEXT datatypes. Not applicable for ONE/​ONEITEM/​MANY/​MANYITEM columns (or derivations of). NULL if no restriction or if it is not applicable. |
 +| dataset_id |  INTEGER ​ | Links to [[#​meta_collection_[slug]_dataset|meta_collection_[slug]_dataset::​id]] to determine how/where it is shown in the frontend. This column is ignored for some column_type'​s (NAME, TAGNAME, etc) that do not sit in a dataset. |
 +| ordinal |  REAL  | The sort position in the assigned dataset. Lower numbers are displayed before higher numbers. ​ Columns with an ordinal containing the same integer segment (e.g. 1, 1.2, 1.5) are treated as a single unit when displaying in the frontend, with the column having the lowest ordinal being the "​master"​. ​ E.g.  If you have columns/​ordinals:​ year (1), publisher (1.5), then in the frontend it might be shown as "​Published:​ 1986 Mastertronic"​ (on the same line). In these cases, the "​master"​ //​master_name//​ field is used for a display label. |
 +
 +Examples: [[#​meta_collection_game]],​ [[#​meta_collection_music]]
 +
 +\\
 +
 +==== meta_collection_[slug]_mediaset ====
 +
 +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, .../
 +
 +^  meta_collection_[slug]_mediaset ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id | INTEGER PRIMARY KEY | |
 +| slug |  TEXT  | Internal key for the mediaset, used for icons, etc. in the frontend. |
 +| name |  TEXT  | Nice name shown to users. |
 +| handler * |  TEXT  | One of [script,​image,​video,​doc,​shell] (determines how handled by the frontend). |
 +| action_text |  TEXT  | For the frontend, e.g "Play Game". |
 +| ordinal |  INTEGER ​ | Sort order of the mediaset when showing in the frontend. |
 +| hashtype | TEXT | Determines the hash/​checksum function to use for all files in the mediaset. One of "​crc32"​ or "​md5"​. Can be NULL if not required. | 
 +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
 +
 +\\
 +
 +==== meta_collection_[slug]_dataset ====
 +
 +This is to group columns in a [[#​Collection]] into datasets (e.g. could be tabs in the frontend).
 +
 +Locations: **master, user**
 +
 +^  meta_collection_[slug]_dataset ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id |  INTEGER PRIMARY KEY  | |
 +| slug |  TEXT  | Internal key for the dataset (e.g. used for icon filenames). |
 +| name |  TEXT  | Nice name of the dataset for display in frontend. |
 +| ordinal |  INTEGER ​ | Sort order position of dataset shown in the frontend (e.g. order of tabs). |
 +
 +Example: [[#​meta_collection_game_dataset]]
 +
 +\\
 +
 +==== meta_list ====
 +
 +^  meta_list ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id |  INTEGER PRIMARY KEY  |  |
 +| slug |  TEXT  | Related meta/data tables use this as a prefix/​identifier. |
 +| name |  TEXT  | What the List is displayed as in the frontend. |
 +| item_name |  TEXT  | What a single item is refered to in this collection. |
 +| item_name_plural |  TEXT  | Plural version of item_name. |
 +| ordinal |  INTEGER ​ | Sort order position shown in the frontend (e.g. in a "List selector"​). |
 +| default_ordinal_column |  TEXT  | Specifies the column in the List's data table to use for default sort ordering. |
 +
 +[[#​meta_list_table|Example]]
 +
 +\\
 +
 +==== meta_list_[slug] ====
 +
 +Locations: **master**, **user**
 +
 +This is a meta definition of the "​data"​ table for a List.
 +
 +^  meta_list_[slug] ​ ^^^
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| column_name |  TEXT  | The name used as the column name in the corresponding data table. |
 +| column_type |  TEXT  | The name of the column type. This maps to either a native SQLite data type, or a "​meta"​ data type for columns that have more specific behaviour. ONE/​ONEITEM/​MANY/​MANYITEM meta column types are not allowed in Lists. |
 +| name |  TEXT  | How the column is named to a human |
 +| name_plural |  TEXT  | As above but when its refered to in its plural form. |
 +| is_required |  INTEGER ​ | Whether data for the column is required. Data in columns are stored as NULL if not required. NULL columns can correspond to "​Unknown"​ values in the frontend (e.g. the publisher for a game is unknown). |
 +| validation_min |  TEXT  | Min length of string if underlying SQLite datatype is TEXT, min value if datatype is INTEGER or REAL, NULL if there is no restriction or it is not applicable. |
 +| validation_max |  TEXT  | Max length of string if underlying SQLite datatype is TEXT, max value if datatype is INTEGER or REAL, NULL if there is no restriction or it is not applicable. |
 +| validation_regex |  TEXT  | Regular expression for validation of TEXT datatypes. NULL if no restriction or if it is not applicable. |
 +| ordinal |  REAL  | The sort position of the field when displaying the list record for editing. |
 +
 +Examples: [[#​meta_list_publisher]],​ [[#​meta_list_musician]]
 +
 +\\
 +
 +===== Data Tables =====
 +
 +Most of these tables are generated from data defined in the corresponding meta table, as linked by the tables'​ [slug].
 +
 +==== data_collection_[slug] ====
 +
 +This table is generated using data in the meta table [[#​meta_collection_[slug]]].
 +
 +Location: **master**
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id  | INTEGER PRIMARY KEY | |
 +| created_datetime_utc | TEXT | Date the record was created, in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +| updated_datetime_utc | TEXT | Date the record was last updated, in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +|  //other columns determined by master meta_collection_[slug]// ​ |||
 +
 +Location: **user **
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id  | INTEGER PRIMARY KEY | Links to the record in the **master**. |
 +| is_favourite ​ | INTEGER | Either 0 or 1. Determines if the item is flagged by the user as a "​Favourite"​. |
 +|  //other columns determined by user meta_collection_[slug]// ​ |||
 +
 +\\
 +
 +==== data_collection_[slug]_mediaset_[slug] ====
 +
 +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]).
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id | INTEGER PRIMARY KEY ||
 +| collection_item_id | INTEGER | Links to the collection item in [[#​data_collection_[slug]]]. |
 +| created_datetime_utc | TEXT | Date the record was created, in ISO8601 format (“YYYY-MM-DD HH:​MM:​SS”). |
 +| updated_datetime_utc | TEXT | Date the record was created, in ISO8601 format (“YYYY-MM-DD HH:​MM:​SS”). Determined by md5 checksum on media file. |
 +| name | TEXT | Nice name for the media file (e.g. "​Original Disk"​). |
 +| notes | TEXT (multi-line) | Extra textual data to store with this file (e.g. "Check out the main sprite!"​). Could be shown as mouse-over text, footnote, etc. |
 +| path | TEXT | Path/file within user defined paths. |
 +| runnable_file | TEXT | Mimic of gamebase'​s FileToRun field - stores the file to run within an archive. |
 +| script_vars | TEXT (multi-line) | Defined script vars (mimics GEMUS key=value pairs) assigned to this file. |
 +| script_vars_parent_id | INTEGER | If not NULL, points to another id in the same table where the script vars are inherited from (any inherited script vars can be overridden by those in the current record). |
 +| ordinal | INTEGER | The order these media files are shown in the frontend (grouped per single collection item). |
 +| version_major | INTEGER ​ | Major version of the media file. Defaults to NULL. If set, can be used as a "view filter"​. |
 +| version_minor | INTEGER ​ | Minor version of the media file. Defaults to NULL. If set, can be used as a "view filter"​. |
 +| hash | TEXT | Hash/​checksum of the media file. Format of hash decided by [[#​meta_collection_[slug]_mediaset|meta_collection_[slug]_mediaset::​hashtype]] |
 +| url | TEXT | Remote download url. |
 +| plugin_vars | TEXT (multi-line) | Vars saved from a media-file plugin (e.g. GB's existing FilenameIndex field, C64Filename etc. |
 +
 +\\
 +
 +==== data_collection_[slug]_mediaset_[slug]_stat ====
 +
 +Location: **user**
 +
 +This table contains statistical/​user information pertaining to records in the corresponding [[#​data_collection_[slug]_mediaset_[slug]]] table.
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id | INTEGER PRIMARY KEY | Points to corresponding [[#​data_collection_[slug]_mediaset_[slug]|data_collection_[slug]_mediaset_[slug]::​id]]. |
 +| last_executed_datetime_utc | TEXT | When the file was last run/​executed,​ in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +| num_times_executed | INTEGER | The number of times the file has been run. |
 +| does_exist_in_paths | INTEGER | Cached var for speed determining if the file is found/​exists in user defined paths. |
 +
 +\\
 +
 +==== data_list_[slug] ====
 +
 +This table is generated using data in the meta table [[#​meta_list_[slug]]].
 +
 +Location: **master**, **user**
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| id  | INTEGER PRIMARY KEY | |
 +| created_datetime_utc | TEXT | Date the record was created, in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +| updated_datetime_utc | TEXT | Date the record was last updated, in ISO8601 format ("​YYYY-MM-DD HH:​MM:​SS"​). |
 +|  //other columns determined by meta_list_[slug]// ​ |||
 +
 +\\
 +
 +==== data_link_[slug]_[slug] ====
 +
 +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**
 +
 +^ Column Name  ^ SQLite DataType ^ Description ^
 +| collection_item_id ​ | INTEGER | Points to a Collection item ID. |
 +| list_item_id ​ | INTEGER | Points to a List item ID. |
 +
 +\\
 +
 +===== An Example =====
 +
 +==== _meta table ====
 +
 +Location: **master**
 +
 +^ _meta ^^^^^
 +^ _version_major ^ _version_minor ^ _guid ^ _creation_datetime_utc ^
 +| 1 | 0 | "​{21EC2020-3AEA-1069-A2DD-08002B30309D}"​ | "​2012-03-23 22:​14:​23"​ |
 +
 +Location: **user**
 +
 +^ _meta ^^^^^
 +^ _version_major ^ _version_minor ^ _guid ^ _creation_datetime_utc ^ _master_guid ^
 +| 1 | 0 | "​{3F2504E0-4F89-11D3-9A0C-0305E82C3301}"​ | "​2012-04-20 07:​23:​54"​ | "​{21EC2020-3AEA-1069-A2DD-08002B30309D}"​ |
 +
 +\\
 +
 +==== meta_master table ====
 +
 +Location: **master**
 +
 +^  meta_master ​ ^^
 +^ Column ​ ^ Data  ^
 +| long_name ​ | "​Gamebase64 v8.0" ​ |
 +| short_name ​ | "GB64 v8" ​ |
 +| version_major ​ | 8  |
 +| version_minor ​ | 0  |
 +| version_release_date ​ | "​2012-01-14" ​ |
 +| intro_message ​ | "​Thanks for downloading GB64 ..." ​ |
 +| terms  | "You must accept these terms: ..." ​ |
 +| license_name ​ | "GB64 License" ​ |
 +| license_url ​ | "​http://​www.gb64.com/​license" ​ |
 +| license_text ​ | "This is the text of the GB64 License..." ​ |
 +| acknowledgements ​ | "Many thanks go to ..." ​ |
 +| copyright_year ​ | "​2007" ​ |
 +| copyright_holder ​ | "The GB64 Team" ​ |
 +
 +\\
 +
 +==== meta_master_contact table ====
 +
 +Location: **[master]**
 +
 +^  meta_master_contact ​ ^^^
 +^ Column ​ ^ Data  ^ Data  ^
 +| id  | 1  | 2  |
 +| name  | Jimbo  | Borg  |
 +| email_address ​ | jimbo@example.com ​ | NULL  |
 +| url  | NULL  | http://​borg.example.com/ ​ |
 +| anchor ​ | NULL  | Blog  |
 +| roles  | Coder  | DB Creator and Maintainer ​ |
 +| is_copyright_holder ​ | 1  | 1  |
 +| ordinal ​ | 2  | 1  |
 +
 +\\
 +
 +==== meta_master_url table ====
 +
 +Location: **[master]**
 +
 +^  meta_master_url ​ ^^^
 +^ Column ​ ^ Data  ^ Data  ^
 +| id  | 1  | 2  |
 +| url  | http://​www.gb64.com/ ​ | http://​www.gb64.com/​submissions ​ |
 +| anchor ​ | Website ​ | Submissions ​ |
 +| description ​ | Our home page.  | Please do not email us before checking our existing list.  |
 +| ordinal ​ | 1  | 2  |
 +
 +\\
 +
 +==== meta_collection table ====
 +
 +Location: **master**
 +
 +^  meta_collection ​ ^^^^^^
 +^ id ^ slug ^ name ^ item_name ^ item_name_plural ^ ordinal ^
 +| 1 | "​game"​ | "​GB64"​ | "​Game"​ | "​Games"​ | 1 |
 +| 2 | "​music"​ | "​HVSC"​ | "​SID"​ | "​SIDs"​ | 2 |
 +
 +\\
 +
 +==== meta_collection_game_dataset ====
 +
 +Location: **master**
 +
 +^  meta_collection_game_dataset ​ ^^^^
 +^ id ^ slug ^ name ^ ordinal ^
 +| 1 | "​info"​ | "Game Info" | 1 |
 +| 2 | "​version"​ | "​Version Info" | 2 |
 +| 4 | "​notes"​ | "​Notes"​ | 3 |
 +
 +Location: **user**
 +
 +^  meta_collection_game_dataset ​ ^^^^
 +^ id ^ slug ^ name ^ ordinal ^
 +| 1 | "​info"​ | "​Personal Info" | 1 |
 +
 +\\
 +
 +==== meta_collection_game_mediaset ====
 +
 +Location: **master**
 +
 +^  meta_collection_game_mediaset ​ ^^^^^^^
 +^ id ^ slug ^ name ^ handler ^ action_text ^ ordinal ^
 +| 1 | "​game"​ | "​Games"​ | "​script"​ | "Play Game" |  1 |
 +| 2 | "​music"​ | "​Music"​ | "​script"​ | "Play Music" |  2 |
 +| 3 | "​video"​ | "​Videos"​ | "​video"​ | "Play Video" |  3 |
 +| 4 | "​screenshot"​ | "​Screenshots"​ | "​image"​ | "View Screenshots"​ |  4 |
 +| 5 | "​doc"​ | "​Docs"​ | "​doc"​ | "View Docs" |  5 |
 +| 6 | "​extra"​ | "​Extras"​ | "​shell"​ | "View Extras"​ |  6 |
 +
 +\\
 +
 +==== meta_collection_game ====
 +
 +Location: **master**
 +
 +^  meta_collection_game ​ ^^^^^^^^^^^
 +^ column_name ​ ^ column_type ^ name ^ name_plural ^ master_name ^ is_required ^ validation_min ^ validation_max ^ validation_regex ^ dataset_id ^ ordinal ^
 +| "​name"​ | "​NAME"​ | "​Name"​ | NULL | NULL | 1 | 1 | 255 | NULL | NULL | NULL |
 +| "​tagline"​ | "​TAGLINE"​ | "​Tagline"​ | NULL | NULL | 0 | NULL | 255 | NULL | NULL | NULL |
 +| "​year::​name"​ | "​ONE"​ | "Year Published"​ | "Years Published"​ | "​Published"​ | 0 | NULL | NULL | NULL | 1 | 1 |
 +| "​publisher::​name"​ | "​MANY"​ | "​Publisher"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 1.5 |
 +| "​developer::​name"​ | "​MANY"​ | "​Developer"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 3 |
 +| "​programmer::​name"​ | "​MANY"​ | "​Programmer"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 4 |
 +| "​musician::​name"​ | "​MANY"​ | "​Musician"​ | NULL | "​Musician"​ | 0 | NULL | NULL | NULL | 1 | 5 |
 +| "​musician::​grp"​ | "​MANY"​ | "​Group"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 5.1 |
 +| "​musician::​nick"​ | "​MANY"​ | "​Nick"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 5.2 |
 +| "​artist::​name"​ | "​MANY"​ | "​Artist"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 6 |
 +| "​language::​name"​ | "​MANY"​ | "​Language"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 7 |
 +| "​genre::​name"​ | "​MANY"​ | "​Genre"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 8 |
 +| "​control::​name"​ | "​MANY"​ | "​Control Method"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 9 |
 +| "​tag::​name"​ | "​MANY"​ | "​Tag"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 10 |
 +| "​reviewrating::​name/​rating/​url"​ | "​MANY"​ | "​Review Rating"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 11 |
 +| "​weblink::​url"​ | "​MANY"​ | "Web Link" | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 12 |
 +| "​licence::​name"​ | "​ONE"​ | "​License"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 13 |
 +| "​rarity::​name"​ | "​ONE"​ | "​Rarity"​ | "​Rarities"​ | NULL | 0 | NULL | NULL | NULL | 1 | 14 |
 +| "​num_players"​ | "​RANGEFROMINTEGER"​ | "​Number of Players"​ | "​Number of Players"​ | NULL | 0 | 0 | 99 | NULL | 1 | 15 |
 +| "​num_players"​ | "​RANGETOINTEGER"​ | NULL | NULL | NULL | 0 | 0 | 99 | NULL | 1 | 16 |
 +| "​is_simultaneous_play"​ | "​BOOLEAN"​ | "​Simultaneous Play" | "​Simultaneous Play" | NULL | 0 | NULL | NULL | NULL | 1 | 17 |
 +| "​comment"​ | "​COMMENT"​ | "​Comment"​ | NULL | NULL | 0 | 0 | 255 | NULL | 1 | 18 |
 +| "​cracker::​name"​ | "​MANY"​ | "​Cracker"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 1 |
 +| "​num_trainers"​ | "​INTEGER"​ | "No. Trainers"​ | NULL | NULL | 0 | 0 | 999 | NULL | 2 | 2 |
 +| "​has_loading_screen"​ | "​BOOLEAN"​ | "​Loading Screen"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 3 |
 +| "​has_title_screen"​ | "​BOOLEAN"​ | "Title Screen"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 4 |
 +| "​has_high_score_saver"​ | "​BOOLEAN"​ | "High Score Saver" | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 5 |
 +| "​has_included_docs"​ | "​BOOLEAN"​ | "​Included Docs" | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 6 |
 +| "​does_require_true_drive_emulation"​ | "​BOOLEAN"​ | "True Drive Emulation"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 7 |
 +| "​is_playable"​ | "​BOOLEAN"​ | "​Playable"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 8 |
 +| "​is_an_original"​ | "​BOOLEAN"​ | "​Original"​ | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 9 |
 +| "​game_version_number"​ | "​INTEGER"​ | "Game Version No." | "Game Version Nos." | NULL | 0 | 1 | 999 | NULL | 2 | 10 |
 +| "​version_weblink::​url"​ | "​MANY"​ | "Web Link" | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 11 |
 +| "​length"​ | "​INTEGER"​ | "​Length"​ | NULL | "​Length"​ | 0 | 0 | NULL | NULL | 2 | 12 |
 +| "​length::​name"​ | "​ONE"​ | "​Length Type" | NULL | NULL | 0 | NULL | NULL | NULL | 2 | 12.5 |
 +| "​palntsc::​name"​ | "​ONE"​ | "​Pal/​NTSC"​ | "​Pal/​NTSC"​ | NULL | 0 | NULL | NULL | NULL | 2 | 13 |
 +| "​version_comment"​ | "​COMMENT"​ | "​Comment"​ | NULL | NULL | 0 | 0 | 255 | NULL | 2 | 14 |
 +| "​notes"​ | "​MULTILINE"​ | "​Notes"​ | NULL | NULL | 0 | 0 | 64000 | NULL | 3 | 1 |
 +| NULL | "​PRECURSOR"​ | "​Precursor"​ | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
 +| NULL | "​SEQUEL"​ | "​Sequel"​ | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
 +| NULL | "​CLONEOF"​ | "Clone of" | "​Clones of" | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
 +| NULL | "​SEEALSO"​ | "See Also" | "See Also" | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
 +
 +Location: **user**
 +
 +^ column_name ​ ^ column_type ^ name ^ name_plural ^ master_name ^ is_required ^ validation_min ^ validation_max ^ validation_regex ^ dataset_id ^ ordinal ^
 +| "​difficulty::​name"​ | "​ONE"​ | "​Difficulty"​ | "​Difficulties"​ | NULL | 0 | NULL | NULL | NULL | 1 | 1 |
 +| "​high_score"​ | "​TEXT"​ | "High Score" | NULL | NULL | 0 | NULL | 255 | NULL | 1 | 2 |
 +| "​rating"​ | "​RATING"​ | "​Rating"​ | NULL | NULL | 0 | NULL | NULL | NULL | 1 | 3 |
 +
 +\\
gb2.txt · Last modified: 2022/06/09 18:09 by Jimbo