| Previous revision |
| — | gb2 [2023/10/04 14:15] (current) – external edit 127.0.0.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 | |
| | |
| | \\ |
