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