· 7 years ago · Nov 02, 2018, 08:54 PM
1
2CREATE DATABASE IF NOT EXISTS `exile` /*!40100 DEFAULT CHARACTER SET utf8 */;
3USE `exile`;
4
5CREATE TABLE IF NOT EXISTS `account` (
6 `uid` varchar(32) NOT NULL,
7 `clan_id` int(11) unsigned DEFAULT NULL,
8 `name` varchar(64) NOT NULL,
9 `score` int(11) NOT NULL DEFAULT '0',
10 `kills` int(11) unsigned NOT NULL DEFAULT '0',
11 `deaths` int(11) unsigned NOT NULL DEFAULT '0',
12 `locker` int(11) NOT NULL DEFAULT '0',
13 `first_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
14 `last_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 `last_disconnect_at` datetime DEFAULT NULL,
16 `total_connections` int(11) unsigned NOT NULL DEFAULT '1',
17 PRIMARY KEY (`uid`),
18 KEY `clan_id` (`clan_id`),
19 CONSTRAINT `account_ibfk_1` FOREIGN KEY (`clan_id`) REFERENCES `clan` (`id`) ON DELETE SET NULL
20) ENGINE=InnoDB DEFAULT CHARSET=utf8;
21
22
23CREATE TABLE IF NOT EXISTS `clan` (
24 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
25 `name` varchar(64) NOT NULL,
26 `leader_uid` varchar(32) NOT NULL,
27 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 PRIMARY KEY (`id`),
29 KEY `leader_uid` (`leader_uid`),
30 CONSTRAINT `clan_ibfk_1` FOREIGN KEY (`leader_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE
31) ENGINE=InnoDB DEFAULT CHARSET=utf8;
32
33
34CREATE TABLE IF NOT EXISTS `clan_map_marker` (
35 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
36 `clan_id` int(11) unsigned NOT NULL,
37 `markerType` tinyint(4) NOT NULL DEFAULT '-1',
38 `positionArr` text NOT NULL,
39 `color` varchar(255) NOT NULL,
40 `icon` varchar(255) NOT NULL,
41 `iconSize` float unsigned NOT NULL,
42 `label` varchar(255) NOT NULL,
43 `labelSize` float unsigned NOT NULL,
44 PRIMARY KEY (`id`),
45 KEY `clan_id` (`clan_id`),
46 CONSTRAINT `clan_map_marker_ibfk_1` FOREIGN KEY (`clan_id`) REFERENCES `clan` (`id`) ON DELETE CASCADE
47) ENGINE=InnoDB DEFAULT CHARSET=utf8;
48
49
50CREATE TABLE IF NOT EXISTS `construction` (
51 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
52 `class` varchar(64) NOT NULL,
53 `account_uid` varchar(32) NOT NULL,
54 `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
55 `position_x` double NOT NULL DEFAULT '0',
56 `position_y` double NOT NULL DEFAULT '0',
57 `position_z` double NOT NULL DEFAULT '0',
58 `direction_x` double NOT NULL DEFAULT '0',
59 `direction_y` double NOT NULL DEFAULT '0',
60 `direction_z` double NOT NULL DEFAULT '0',
61 `up_x` double NOT NULL DEFAULT '0',
62 `up_y` double NOT NULL DEFAULT '0',
63 `up_z` double NOT NULL DEFAULT '0',
64 `is_locked` tinyint(1) NOT NULL DEFAULT '0',
65 `pin_code` varchar(6) NOT NULL DEFAULT '000000',
66 `damage` tinyint(1) unsigned NULL DEFAULT '0',
67 `territory_id` int(11) unsigned DEFAULT NULL,
68 `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
69 `deleted_at` datetime DEFAULT NULL,
70 PRIMARY KEY (`id`),
71 KEY `account_uid` (`account_uid`),
72 KEY `territory_id` (`territory_id`),
73 CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
74 CONSTRAINT `construction_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE
75) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76
77
78CREATE TABLE IF NOT EXISTS `container` (
79 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
80 `class` varchar(64) NOT NULL,
81 `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
82 `account_uid` varchar(32) DEFAULT NULL,
83 `is_locked` tinyint(1) NOT NULL DEFAULT '0',
84 `position_x` double NOT NULL DEFAULT '0',
85 `position_y` double NOT NULL DEFAULT '0',
86 `position_z` double NOT NULL DEFAULT '0',
87 `direction_x` double NOT NULL DEFAULT '0',
88 `direction_y` double NOT NULL DEFAULT '0',
89 `direction_z` double NOT NULL DEFAULT '0',
90 `up_x` double NOT NULL DEFAULT '0',
91 `up_y` double NOT NULL DEFAULT '0',
92 `up_z` double NOT NULL DEFAULT '1',
93 `cargo_items` text NOT NULL,
94 `cargo_magazines` text NOT NULL,
95 `cargo_weapons` text NOT NULL,
96 `cargo_container` text NOT NULL,
97 `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
98 `pin_code` varchar(6) NOT NULL DEFAULT '000000',
99 `territory_id` int(11) unsigned DEFAULT NULL,
100 `deleted_at` datetime DEFAULT NULL,
101 `money` int(11) unsigned NOT NULL DEFAULT '0',
102 `abandoned` datetime DEFAULT NULL,
103 PRIMARY KEY (`id`),
104 KEY `account_uid` (`account_uid`),
105 KEY `territory_id` (`territory_id`),
106 CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
107 CONSTRAINT `container_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE
108) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
109
110
111CREATE TABLE IF NOT EXISTS `player` (
112 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
113 `name` varchar(64) NOT NULL,
114 `account_uid` varchar(32) NOT NULL,
115 `money` int(11) unsigned NOT NULL DEFAULT '0',
116 `damage` double unsigned NOT NULL DEFAULT '0',
117 `hunger` double unsigned NOT NULL DEFAULT '100',
118 `thirst` double unsigned NOT NULL DEFAULT '100',
119 `alcohol` double unsigned NOT NULL DEFAULT '0',
120 `temperature` double NOT NULL DEFAULT '37',
121 `wetness` double unsigned NOT NULL DEFAULT '0',
122 `oxygen_remaining` double unsigned NOT NULL DEFAULT '1',
123 `bleeding_remaining` double unsigned NOT NULL DEFAULT '0',
124 `hitpoints` varchar(1024) NOT NULL DEFAULT '[]',
125 `direction` double NOT NULL DEFAULT '0',
126 `position_x` double NOT NULL DEFAULT '0',
127 `position_y` double NOT NULL DEFAULT '0',
128 `position_z` double NOT NULL DEFAULT '0',
129 `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
130 `assigned_items` text NOT NULL,
131 `backpack` varchar(64) NOT NULL,
132 `backpack_items` text NOT NULL,
133 `backpack_magazines` text NOT NULL,
134 `backpack_weapons` text NOT NULL,
135 `current_weapon` varchar(64) NOT NULL,
136 `goggles` varchar(64) NOT NULL,
137 `handgun_items` text NOT NULL,
138 `handgun_weapon` varchar(64) NOT NULL,
139 `headgear` varchar(64) NOT NULL,
140 `binocular` varchar(64) NOT NULL,
141 `loaded_magazines` text NOT NULL,
142 `primary_weapon` varchar(64) NOT NULL,
143 `primary_weapon_items` text NOT NULL,
144 `secondary_weapon` varchar(64) NOT NULL,
145 `secondary_weapon_items` text NOT NULL,
146 `uniform` varchar(64) NOT NULL,
147 `uniform_items` text NOT NULL,
148 `uniform_magazines` text NOT NULL,
149 `uniform_weapons` text NOT NULL,
150 `vest` varchar(64) NOT NULL,
151 `vest_items` text NOT NULL,
152 `vest_magazines` text NOT NULL,
153 `vest_weapons` text NOT NULL,
154 `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
155 PRIMARY KEY (`id`),
156 KEY `player_uid` (`account_uid`),
157 CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE
158) ENGINE=InnoDB DEFAULT CHARSET=utf8;
159
160
161CREATE TABLE IF NOT EXISTS `player_history` (
162 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
163 `account_uid` varchar(32) NOT NULL,
164 `name` varchar(64) NOT NULL,
165 `died_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
166 `position_x` double NOT NULL,
167 `position_y` double NOT NULL,
168 `position_z` double NOT NULL,
169 PRIMARY KEY (`id`)
170) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171
172
173CREATE TABLE IF NOT EXISTS `territory` (
174 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
175 `owner_uid` varchar(32) NOT NULL,
176 `name` varchar(64) NOT NULL,
177 `position_x` double NOT NULL,
178 `position_y` double NOT NULL,
179 `position_z` double NOT NULL,
180 `radius` double NOT NULL,
181 `level` int(11) NOT NULL,
182 `flag_texture` varchar(255) NOT NULL,
183 `flag_stolen` tinyint(1) NOT NULL DEFAULT '0',
184 `flag_stolen_by_uid` varchar(32) DEFAULT NULL,
185 `flag_stolen_at` datetime DEFAULT NULL,
186 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
187 `last_paid_at` datetime DEFAULT CURRENT_TIMESTAMP,
188 `xm8_protectionmoney_notified` tinyint(1) NOT NULL DEFAULT '0',
189 `build_rights` varchar(640) NOT NULL DEFAULT '0',
190 `moderators` varchar(320) NOT NULL DEFAULT '0',
191 `deleted_at` datetime DEFAULT NULL,
192 PRIMARY KEY (`id`),
193 KEY `owner_uid` (`owner_uid`),
194 KEY `flag_stolen_by_uid` (`flag_stolen_by_uid`),
195 CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
196 CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `account` (`uid`) ON DELETE SET NULL
197) ENGINE=InnoDB DEFAULT CHARSET=utf8;
198
199
200CREATE TABLE IF NOT EXISTS `vehicle` (
201 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
202 `class` varchar(64) NOT NULL,
203 `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
204 `account_uid` varchar(32) DEFAULT NULL,
205 `is_locked` tinyint(1) NOT NULL DEFAULT '0',
206 `fuel` double unsigned NOT NULL DEFAULT '0',
207 `damage` double unsigned NOT NULL DEFAULT '0',
208 `hitpoints` text NOT NULL,
209 `position_x` double NOT NULL DEFAULT '0',
210 `position_y` double NOT NULL DEFAULT '0',
211 `position_z` double NOT NULL DEFAULT '0',
212 `direction_x` double NOT NULL DEFAULT '0',
213 `direction_y` double NOT NULL DEFAULT '0',
214 `direction_z` double NOT NULL DEFAULT '0',
215 `up_x` double NOT NULL DEFAULT '0',
216 `up_y` double NOT NULL DEFAULT '0',
217 `up_z` double NOT NULL DEFAULT '1',
218 `cargo_items` text NOT NULL,
219 `cargo_magazines` text NOT NULL,
220 `cargo_weapons` text NOT NULL,
221 `cargo_container` text NOT NULL,
222 `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
223 `pin_code` varchar(6) NOT NULL DEFAULT '000000',
224 `deleted_at` datetime DEFAULT NULL,
225 `money` int(11) unsigned NOT NULL DEFAULT '0',
226 `vehicle_texture` text NOT NULL,
227 `territory_id` int(11) unsigned DEFAULT NULL,
228 `nickname` varchar(64) NOT NULL DEFAULT '',
229 PRIMARY KEY (`id`),
230 KEY `account_uid` (`account_uid`),
231 KEY `vehicle_ibfk_2_idx` (`territory_id`),
232 CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
233 CONSTRAINT `vehicle_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE
234) ENGINE=InnoDB DEFAULT CHARSET=utf8;
235
236;