Game database model: dbmodel.sql - Revision history

Thoun: /* CREATE TABLES */

2026-03-03T13:33:38Z

CREATE TABLES

← Older revision		Revision as of 13:33, 3 March 2026
Line 98:		Line 98:
	Pay attention to the following:		Pay attention to the following:
	* Always add IF NOT EXISTS		* Always add IF NOT EXISTS
	* Always set ENGINE=InnoDB DEFAULT CHARSET=~~utf8~~		* Always set ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
	* Define primary keys		* 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		* If you create NOT NULL fields after the game has gone into production, make sure you took care of the database migration

Thoun: /* PHP */

2026-03-03T13:33:23Z

PHP

← Older revision		Revision as of 13:33, 3 March 2026
Line 115:		Line 115:

	== PHP ==		== PHP ==
	Database initialization should be in the function setupNewGame() in '~~gamename.game~~.php'.		Database initialization (filling the table created by this file) should be in the function setupNewGame() in 'Game.php'.

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

Thoun: /* Default tables */

2026-03-03T13:15:25Z

Default tables

← Older revision		Revision as of 13:15, 3 March 2026
Line 69:		Line 69:
	By default, BGA creates four tables for your game: '''global''', '''stats''', '''gamelog''', and '''player'''.		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).		You '''MUST NOT MODIFY''' the schemas of the '''global''', '''stats''', or '''gamelog''' tables, and any '''bga_''' prefixed table (and you must not access them directly with SQL queries in your PHP code).

	=== The '''player''' table ===		=== The '''player''' table ===

Victoria La: added size paragraph

2026-01-24T14:19:46Z

added size paragraph

← Older revision		Revision as of 14:19, 24 January 2026
Line 134:		Line 134:
	While it does generally produce consecutive values, it does not guarantee contiguous generation (i.e. you can end up with "skipped" numbers). Technically you can even end up with later-number rows being inserted earlier (you'd likely only notice or care about that if you also had a "creation_timestamp" column, though, and wanted it to be "in sync" with the auto_incremented column).		While it does generally produce consecutive values, it does not guarantee contiguous generation (i.e. you can end up with "skipped" numbers). Technically you can even end up with later-number rows being inserted earlier (you'd likely only notice or care about that if you also had a "creation_timestamp" column, though, and wanted it to be "in sync" with the auto_incremented column).
	More details can be found [https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable here]		More details can be found [https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable here]


			== Beware of the size ==

			Please note the database size for each game is not unlimited, if size of all table (including system tables) exceeds 64Mb it will stop working.
			The biggest contributor to this will be gamelog and reply related tables, therefore please consider the size when designing data send for each state (args) and player's notifications.

	[[Category:Studio]]		[[Category:Studio]]

Victoria La: /* Create your schema */

2026-01-24T14:14:22Z

Create your schema

← Older revision		Revision as of 14:14, 24 January 2026
Line 42:		Line 42:
	* Do not overcomplicate, you are dealing with games with 50-500 pieces!		* 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		** 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~~		* The database should only be storing dynamic data, all static data should be stored in material file (php).
	** 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.		** 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.		* Columns should be permanent, independent of the game data, i.e. location, type, position, row, etc.

Thoun at 13:13, 1 October 2025

2025-10-01T13:13:04Z

← Older revision		Revision as of 13:13, 1 October 2025
Line 84:		Line 84:
	* <strong>player_id</strong> (int)		* <strong>player_id</strong> (int)
	* <strong>player_name</strong>: (note: it is better to access this data with the getActivePlayerName() or loadPlayersBasicInfos() methods)		* <strong>player_name</strong>: (note: it is better to access this data with the getActivePlayerName() or loadPlayersBasicInfos() methods)
	* <strong>player_score</strong>: the current score of a player (displayed in the player panel). ~~You must update~~ this field to update a player's score.		* <strong>player_score</strong>: the current score of a player (displayed in the player panel). Update this field with $this->playerScore, to update a player's score.
	* <strong>player_score_aux</strong>: the secondary score, used as a tie breaker. ~~You must update~~ this field according to tie breaking rules of the game (see also: [[Main_game_logic:_yourgamename.game.php#Manage_player_scores_and_Tie_breaker\|Manage_player_scores_and_Tie_breaker]])		* <strong>player_score_aux</strong>: the secondary score, used as a tie breaker. Update this field with $this->playerScoreAux, according to tie breaking rules of the game (see also: [[Main_game_logic:_yourgamename.game.php#Manage_player_scores_and_Tie_breaker\|Manage_player_scores_and_Tie_breaker]])

	* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.		* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.

Vagabond: Added a section on AUTO_INCREMENT behavior.

2025-09-27T13:04:38Z

Added a section on AUTO_INCREMENT behavior.

← Older revision		Revision as of 13:04, 27 September 2025
Line 128:		Line 128:
	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 [[Post-release phase#Updating the database schema\|Updating the database schema after release]]		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 [[Post-release phase#Updating the database schema\|Updating the database schema after release]]

			== Uses and misuses of AUTO_INCREMENT ==

			AUTO_INCREMENT is not particularly well-named; it's best to think of it as simply guaranteeing uniqueness of a column value. Try to design your schema and model so you don't require strictly-increasing-by-one identifiers; do not assume that the next row inserted will have a value one larger than the current maximum.

			While it does generally produce consecutive values, it does not guarantee contiguous generation (i.e. you can end up with "skipped" numbers). Technically you can even end up with later-number rows being inserted earlier (you'd likely only notice or care about that if you also had a "creation_timestamp" column, though, and wanted it to be "in sync" with the auto_incremented column).
			More details can be found [https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable here]

	[[Category:Studio]]		[[Category:Studio]]

Thoun at 07:40, 28 May 2025

2025-05-28T07:40:33Z

← Older revision		Revision as of 07:40, 28 May 2025
Line 57:		Line 57:
	self::checkState($state); // ensure state is number		self::checkState($state); // ensure state is number
	self::checkKey($token_key); // ensure key is alphanum		self::checkKey($token_key); // ensure key is alphanum
	$sql = "UPDATE ~~" .~~ $this->table;		$sql = "UPDATE `{$this->table}`";
	$sql .= " SET token_state='$state'";		$sql .= " SET `token_state` = '$state'";
	$sql .= " WHERE token_key='$token_key'"; // don't need to escape anymore since we checked key before		$sql .= " WHERE `token_key` = '$token_key'"; // don't need to escape anymore since we checked key before
	self::DbQuery($sql);		self::DbQuery($sql);
	return $state;		return $state;

Polinow: Corrected lots of minor English errors. Did not change any code, did not change any filenames, did not change any function names, etc.

2023-07-05T05:29:28Z

Corrected lots of minor English errors. Did not change any code, did not change any filenames, did not change any function names, etc.

← Older revision		Revision as of 05:29, 5 July 2023
Line 1:		Line 1:
	{{Studio_Framework_Navigation}}		{{Studio_Framework_Navigation}}

	In this file you specify the database schema of your game.		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.		This file contains SQL queries that will be executed during the creation of your game table.
Line 11:		Line 11:
	== Create your schema ==		== Create your schema ==

	To build this file, we recommend you to build the tables you need with the PhpMyAdmin tool (see BGA user guide), and then to export them and to copy/paste the content ~~inside~~ this file.		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:''' ~~you~~ must not ~~use for a column~~ the same name ~~as for~~ the table, as the framework replay function relies on regexp substitution to save/restore a previous state in a clone table with another name.		'''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]]		Example: Deck component, see [[Deck]]
Line 42:		Line 42:
	* Do not overcomplicate, you are dealing with games with 50-500 pieces!		* 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		** If you have 5 tables for a card game with 30 cards - it is overkill
	* ~~Database~~ should only be storing dynamic data, all static data should be stored in material.php.inc		* 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. ~~Only~~ exception ~~being~~ if it changes during the game.		** 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 game data, i.e. location, type, position, row, etc		* Columns should be permanent, independent of the game data, i.e. location, type, position, row, etc.
	** Example: do not create columns ~~like~~ Country1, Country2, Country3, ...		** Example: do not create columns such as Country1, Country2, Country3, etc.
	* Do not store translatable ~~string~~ in the database, use integer number of "ident" to access properties		* 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"		** i.e. card type should be "22" or "bob_cat", instead of "Bob's cat"
	* Create separate module in ~~php~~ to handle all database queries, do a lot of type checking to prevent SQL injections		* Create a separate module in PHP to handle all database queries, do a lot of type checking to prevent SQL injections

	Example of method ~~handling~~ database query:		Example of method to handle a database query:

	<pre>		<pre>
Line 69:		Line 69:
	By default, BGA creates four tables for your game: '''global''', '''stats''', '''gamelog''', and '''player'''.		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).		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 ===		=== The '''player''' table ===

	You may add columns to the '''player''' table. ~~This~~ is very practical to add simple values associated with players. '''NB:''' you must not alter existing columns created by the framework.		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:		Example:
Line 80:		Line 80:
	</pre>		</pre>

	The commonly used columns of default "player" table are:		The commonly used columns of the default "player" table are:
	* <strong>player_no</strong>: the index of player in natural playing order (starting with 1)		* <strong>player_no</strong>: the index of a player in natural playing order (starting with 1)
	* <strong>player_id</strong> (int)		* <strong>player_id</strong> (int)
	* <strong>player_name</strong>: (note: it is better to access this data with getActivePlayerName() or loadPlayersBasicInfos() methods)		* <strong>player_name</strong>: (note: it is better to access this data with the getActivePlayerName() or loadPlayersBasicInfos() methods)
	* <strong>player_score</strong>: the current score of ~~the~~ player (displayed in the player panel). You must update this field to update player's ~~scores~~.		* <strong>player_score</strong>: the current score of a player (displayed in the player panel). You must update this field to update a player's score.
	* <strong>player_score_aux</strong>: the secondary score, used as a tie breaker. You must update this field according to tie breaking rules of the game (see also: [[Main_game_logic:_yourgamename.game.php#Manage_player_scores_and_Tie_breaker\|Manage_player_scores_and_Tie_breaker]])		* <strong>player_score_aux</strong>: the secondary score, used as a tie breaker. You must update this field according to tie breaking rules of the game (see also: [[Main_game_logic:_yourgamename.game.php#Manage_player_scores_and_Tie_breaker\|Manage_player_scores_and_Tie_breaker]])

	* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.		* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.

	<strong><u>CAUTION:</u></strong> '''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 <u>but</u> their player_table_no is still equal to four! If another player then joins, their player_table_no will then be 5...<br />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 ~~that~~ values as ranks directly, but you can still deduce that <player B> was first on the table, then <player A> then <player C> then <player D> 😉		<strong><u>CAUTION:</u></strong> '''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 <u>but</u> their player_table_no is still equal to four! If another player then joins, their player_table_no will then be 5.<br />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 [https://en.doc.boardgamearena.com/BGA_Studio_Cookbook#Assigning_Player_Order Assigning Player Order] in the '''BGA Studio Cookbook''' for an example.		See [https://en.doc.boardgamearena.com/BGA_Studio_Cookbook#Assigning_Player_Order Assigning Player Order] in the '''BGA Studio Cookbook''' for an example.
Line 100:		Line 100:
	* Always set ENGINE=InnoDB DEFAULT CHARSET=utf8		* Always set ENGINE=InnoDB DEFAULT CHARSET=utf8
	* Define primary keys		* Define primary keys
	* If you create NOT NULL fields after game in production, make sure you took care of database migration		* 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 ~~put~~ comments, you ~~cannot~~ do it in the same line as code.
			'''Note''': if you use comments, then you must not do it in the same line as the code.

	Example:		Example:
Line 111:		Line 112:
	</pre>		</pre>

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

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

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

	Warning: all CREATE/ALTER tables and ~~view~~ 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		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.		which will cause issues. The only time you can do such queries from PHP is in the upgradeTableDb() method.

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

	== Post-release database modification ==		== Post-release database modification ==
	If you want to modify your database schema after the first release of your game in production, you should implement upgradeTableDb method, see [[Post-release phase#Updating the database schema\|Updating the database schema after release]]		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 [[Post-release phase#Updating the database schema\|Updating the database schema after release]]


	[[Category:Studio]]		[[Category:Studio]]

Mike Crosley: /* Default tables */

2022-09-11T09:45:13Z

Default tables

← Older revision		Revision as of 09:45, 11 September 2022
Line 67:		Line 67:
	== Default tables ==		== Default tables ==

	By default, BGA creates 4 tables for your game: '''global''', '''stats''', '''gamelog''', and '''player'''.		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).		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).
Line 89:		Line 89:
	* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.		* <strong>player_table_order</strong>: 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 <u>within</u> the game). player_table_order is useful for setting custom teams if desired in a game option (for instance, 1st-2nd vs 3rd-4th).<br/><strong>Note:</strong> player_table_order ''only exists during game initialization'' (in the '''setupNewGame''' function). It is not added as a column in the '''players''' Db table.

	<strong><u>CAUTION:</u></strong> '''player_table_order''' is not guaranteed to be equal to the rank of the player in the table. For example, in a 4-player game, if the table was full but the ~~3rd~~ player leaves before the game starts, the ~~4th~~ player becomes ~~3rd~~ on this table <u>but</u> their player_table_no is still equal to 4! If another player then joins, their player_table_no will then be 5...<br />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 that values as ranks directly, but you can still deduce that <player B> was ~~1st~~ on the table, then <player A> then <player C> then <player D> 😉		<strong><u>CAUTION:</u></strong> '''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 <u>but</u> their player_table_no is still equal to four! If another player then joins, their player_table_no will then be 5...<br />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 that values as ranks directly, but you can still deduce that <player B> was first on the table, then <player A> then <player C> then <player D> 😉

	See [https://en.doc.boardgamearena.com/BGA_Studio_Cookbook#Assigning_Player_Order Assigning Player Order] in the '''BGA Studio Cookbook''' for an example.		See [https://en.doc.boardgamearena.com/BGA_Studio_Cookbook#Assigning_Player_Order Assigning Player Order] in the '''BGA Studio Cookbook''' for an example.