Game database model: dbmodel.sql

Game File Reference

Overview

• dbmodel.sql - database model
• gameinfos.inc.php - meta-information
• gameoptions.json - game options & user preferences
• img/ - game art
• Game Metadata Manager - tags and metadata media
• material.inc.php - static data
• misc/ - studio-only storage
• modules/ - additional game code
• states.inc.php - state machine
• stats.json - statistics
• X.action.php - player actions
• X.css - interface stylesheet
• Game.php - main logic
• X.js - interface logic
• X.view.php - dynamic game layout
• X_X.tpl - static game layout

---

Useful Components

Official

• Deck: a PHP component to manage cards (deck, hands, picking cards, moving cards, shuffle deck, ...).
• Draggable: a JS component to manage drag'n'drop actions.
• Counter: a JS component to manage a counter that can increase/decrease (ex: player's score).
• ExpandableSection: a JS component to manage a rectangular block of HTML than can be displayed/hidden.
• Scrollmap: a JS component to manage a scrollable game area (useful when the game area can be infinite. Examples: Saboteur or Takenoko games).
• Stock: a JS component to manage and display a set of game elements displayed at a position.
• Zone: a JS component to manage a zone of the board where several game elements can come and leave, but should be well displayed together (See for example: token's places at Can't Stop).

Undocumented component (if somebody knows please help with docs)

• Wrapper: a JS component to wrap a <div> element around its child, even if these elements are absolute positioned.

Unofficial

• BGA Code Sharing - Shared resources, projects on git hub, common code, other links
• BGA Studio Cookbook - Tips and instructions on using API's, libraries and frameworks
• Common board game elements image resources

---

Game Development Process

• First steps with BGA Studio
• Create a game in BGA Studio: Complete Walkthrough
• Tutorial reversi
• Tutorial gomoku
• Tutorial hearts
• BGA Studio Guidelines
• BGA game Lifecycle
• Pre-release checklist
• Post-release phase
• Player Resources - add player help/rules to your game page

---

Guides for Common Topics

• Translations - make your game translatable
• Game Replay
• Mobile Users
• 3D
• Compatibility

---

Miscellaneous Resources

• Studio FAQ
• Tools and tips of BGA Studio - Tips and instructions on setting up development environment
• Studio logs - Instructions for log access
• Practical debugging - Tips focused on debugging
• Troubleshooting - Most common "I am really stuck" situations
• Studio Bugs - Reports against Studio itself (not BGA!)

In this file, you specify the database schema of your game.

This file contains SQL queries that will be executed during the creation of your game table.

Note: you can't change the database schema during the game.

Create your schema

To build this file, we recommend you build the tables you need with the PhpMyAdmin tool (see the BGA user guide), and then export them and copy/paste the content to this file.

Note: the name of a column must not be the same as the name of the table, as the framework replay function relies on regexp substitution to save/restore the previous state in a clone table with another name.

Example: Deck component, see Deck

CREATE TABLE IF NOT EXISTS `card` (
  `card_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card_type` varchar(16) NOT NULL,
  `card_type_arg` int(11) NOT NULL,
  `card_location` varchar(16) NOT NULL,
  `card_location_arg` int(11) NOT NULL,
  PRIMARY KEY (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Example: Euro Game. See details on database design for euro game at BGA_Studio_Cookbook#Database_for_The_euro_game

CREATE TABLE IF NOT EXISTS `token` (
  `token_key` varchar(32) NOT NULL,
  `token_location` varchar(32) NOT NULL,
  `token_state` int(10),
  PRIMARY KEY (`token_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Rules you should follow:

• Do not overcomplicate, you are dealing with games with 50-500 pieces!
  • If you have 5 tables for a card game with 30 cards - it is overkill
• The database should only be storing dynamic data, all static data should be stored in material.php.inc
  • Example: if you have cards that have power, color, and special abilities. In the database, you only need to store the card type, all the other properties should not be there. The only exception is if it changes during the game.
• Columns should be permanent, independent of the game data, i.e. location, type, position, row, etc.
  • Example: do not create columns such as Country1, Country2, Country3, etc.
• Do not store translatable strings in the database, use an integer number of "ident" to access properties
  • i.e. card type should be "22" or "bob_cat", instead of "Bob's cat"
• Create a separate module in PHP to handle all database queries, do a lot of type checking to prevent SQL injections

Example of method to handle a database query:

    // Set token state
    function setTokenState($token_key, $state) {
        self::checkState($state); // ensure state is number
        self::checkKey($token_key); // ensure key is alphanum
        $sql = "UPDATE " . $this->table;
        $sql .= " SET token_state='$state'";
        $sql .= " WHERE token_key='$token_key'"; // don't need to escape anymore since we checked key before
        self::DbQuery($sql);
        return $state;
    }

Default tables

By default, BGA creates four tables for your game: global, stats, gamelog, and player.

You MUST NOT MODIFY the schemas of the global, stats, or gamelog tables (and you must not access them directly with SQL queries in your PHP code).

The player table

You may add columns to the player table. It is very practical to add simple values associated with players. NB: you must not alter existing columns created by the framework.

Example:

ALTER TABLE `player` ADD `player_reserve_size` SMALLINT UNSIGNED NOT NULL DEFAULT '7';

The commonly used columns of the default "player" table are:

• player_no: the index of a player in natural playing order (starting with 1)
• player_id (int)
• player_name: (note: it is better to access this data with the getActivePlayerName() or loadPlayersBasicInfos() methods)
• player_score: the current score of a player (displayed in the player panel). You must update this field to update a player's score.
• player_score_aux: the secondary score, used as a tie breaker. You must update this field according to tie breaking rules of the game (see also: Manage_player_scores_and_Tie_breaker)

• player_table_order: gives an indication of the rank of the player by order of arrival in the lobby (starting with 1). It is not the same as player_no (which is the player order within the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).
  Note: player_table_order only exists during game initialization (in the setupNewGame function). It is not added as a column in the players Db table.

CAUTION: player_table_order is not guaranteed to be equal to the rank of the player in the table. For example, in a four-player game, if the table was full but the third player leaves before the game starts, the fourth player becomes the third on this table but their player_table_no is still equal to four! If another player then joins, their player_table_no will then be 5.
Thus, it is essential to normalize these values first in the game setup if you wish to use them to prevent bugs at game launch. For example, if the set of player_table_order are <player A>: 3, <player B>: 2, <player C>: 5, <player D>: 7, you see that you can't read those values as ranks directly, but you can still deduce that <player B> was first on the table, then <player A>, then <player C>, and then <player D> 😉

See Assigning Player Order in the BGA Studio Cookbook for an example.

CREATE TABLES

You can create tables, using engine InnoDB. See examples above on how to create tables. Pay attention to the following:

• Always add IF NOT EXISTS
• Always set ENGINE=InnoDB DEFAULT CHARSET=utf8
• Define primary keys
• If you create NOT NULL fields after the game has gone into production, make sure you took care of the database migration

Note: if you use comments, then you must not do it in the same line as the code.

Example:

`activated` BOOL NOT NULL DEFAULT 1, --  activated or not

will also comment out the whole column `activated` BOOL, and that code will not be executed.

PHP

Database initialization should be in the function setupNewGame() in 'gamename.game.php'.

Database schema migration should be in the function upgradeTableDb(), see below.

Warning: all CREATE/ALTER tables and views should be in dbmodel.sql. Do not call these queries from PHP to avoid implicit commits https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html which will cause issues. The only time you can do such queries from PHP is in the upgradeTableDb() method.

Errors Log

To trace Database creation, check the logs that you can access in /admin/studio.

Post-release database modification

If you want to modify your database schema after the first release of your game to production, then you should implement the upgradeTableDb() method, see Updating the database schema after release