1-- a minimal users table, if not using a phpbb3 installation 2-- create table users 3-- ( 4-- USER_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 5-- USER_TYPE TINYINT(2) NOT NULL DEFAULT 0, 6-- USERNAME VARCHAR(255) COLLATE utf8_bin NOT NULL DEFAULT '', 7-- USER_PASSWORD VARCHAR(255) COLLATE utf8_bin NOT NULL DEFAULT '', 8-- USER_EMAIL VARCHAR(100) COLLATE utf8_bin NOT NULL DEFAULT '', 9-- PRIMARY KEY (USER_ID), 10-- KEY USER_TYPE (USER_TYPE) 11-- ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 12 13-- a minimal groups table, if not using a phpbb3 installation 14-- create table user_groups 15-- ( 16-- GROUP_ID MEDIUMINT(8) UNSIGNED NOT NULL, 17-- USER_ID MEDIUMINT(8) UNSIGNED NOT NULL, 18-- PRIMARY KEY (USER_ID, GROUP_ID) 19-- ) ENGINE=InnoDB; 20 21-- table which the forum inserts bans into, which wesnothd checks during login 22-- create table ban 23-- ( 24-- BAN_USERID INT(10) UNSIGNED NOT NULL, 25-- BAN_END INT(10) UNSIGNED NOT NULL DEFAULT 0, 26-- BAN_IP VARCHAR(100) DEFAULT NULL, 27-- BAN_EMAIL VARCHAR(100) DEFAULT NULL, 28-- BAN_EXCLUDE INT(10) UNSIGNED NOT NULL DEFAULT 0, 29-- PRIMARY KEY (BAN_USERID) 30-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 31 32------ 33 34-- extra information as necessary per user 35-- USERNAME: who this information is about 36-- USER_LASTVISIT: used by the phpbb extension displaying the last time the user logged in to the MP server 37-- USER_IS_MODERATOR: determines people who have the abilities granted to MP Moderators 38create table extra 39( 40 USERNAME VARCHAR(100) NOT NULL, 41 USER_LASTVISIT INT(10) UNSIGNED NOT NULL DEFAULT 0, 42 USER_IS_MODERATOR TINYINT(1) NOT NULL DEFAULT 0, 43 PRIMARY KEY (USERNAME) 44) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 45 46-- information about a single game 47-- INSTANCE_UUID: retrieved from the UUID() function on wesnothd start up 48-- GAME_ID: a sequential id wesnoth generates, resets on restart 49-- INSTANCE_VERSION: the version of the server 50-- GAME_NAME: the game's displayed title in the lobby 51-- START_TIME: when the players enter the game and begin playing 52-- END_TIME: when the game ends, for any particular reason 53-- MAP_NAME: the mp_scenario attribute value 54-- MAP_SOURCE_ADDON: the add-on the map comes from 55-- MAP_VERSION: the version of the add-on the map comes from 56-- ERA_NAME: the mp_era attribute value 57-- ERA_SOURCE_ADDON: the add-on the era comes from 58-- ERA_VERSION: the version of the add-on the era comes from 59-- REPLAY_NAME: the file name of the replay create when the game is ended 60-- OOS: Y/N flag of whether the game encountered an OOS error 61create table game_info 62( 63 INSTANCE_UUID CHAR(36) NOT NULL, 64 GAME_ID INT UNSIGNED NOT NULL, 65 INSTANCE_VERSION VARCHAR(255) NOT NULL, 66 GAME_NAME VARCHAR(255) NOT NULL, 67 START_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 68 END_TIME TIMESTAMP NULL DEFAULT NULL, 69 REPLAY_NAME VARCHAR(255), 70 OOS BIT(1) NOT NULL DEFAULT 0, 71 RELOAD BIT(1) NOT NULL, 72 OBSERVERS BIT(1) NOT NULL, 73 PASSWORD BIT(1) NOT NULL, 74 PUBLIC BIT(1) NOT NULL, 75 PRIMARY KEY (INSTANCE_UUID, GAME_ID) 76) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 77 78-- information about the players in a particular game present in game_info 79-- this is accurate at the start of the game, but is not currently updated if a side changes owners, someone disconnects, etc 80-- USER_ID: the ID of the player, taken from the USERS table 81-- SIDE_NUMBER: the side controlled by USER_ID 82-- IS_HOST: if USER_ID is the game's host 83-- FACTION: the faction being played by this side 84-- STATUS: the status of the side, currently only updated at game end 85-- CLIENT_VERSION: the version of the wesnoth client used to connect 86-- CLIENT_SOURCE: where the wesnoth client was downloaded from - SourceForge, Steam, etc 87create table game_player_info 88( 89 INSTANCE_UUID CHAR(36) NOT NULL, 90 GAME_ID INT UNSIGNED NOT NULL, 91 USER_ID INT NOT NULL, 92 SIDE_NUMBER SMALLINT UNSIGNED NOT NULL, 93 IS_HOST BIT(1) NOT NULL, 94 FACTION VARCHAR(255) NOT NULL, 95 CLIENT_VERSION VARCHAR(255) NOT NULL DEFAULT '', 96 CLIENT_SOURCE VARCHAR(255) NOT NULL DEFAULT '', 97 USER_NAME VARCHAR(255) NOT NULL DEFAULT '', 98 PRIMARY KEY (INSTANCE_UUID, GAME_ID, SIDE_NUMBER) 99) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 100 101-- information about the scenario/era/modifications for the game 102-- TYPE: one of era/scenario/modification 103-- ID: the id of the content 104-- SOURCE: the id of the add-on that the particular content came from 105-- VERSION: the version of the source add-on 106create table game_content_info 107( 108 INSTANCE_UUID CHAR(36) NOT NULL, 109 GAME_ID INT UNSIGNED NOT NULL, 110 TYPE VARCHAR(255) NOT NULL, 111 ID VARCHAR(255) NOT NULL, 112 SOURCE VARCHAR(255) NOT NULL, 113 VERSION VARCHAR(255) NOT NULL, 114 PRIMARY KEY (INSTANCE_UUID, GAME_ID, TYPE, ID) 115) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 116