· 8 years ago · Jun 25, 2018, 03:26 PM
1 DROP TRIGGER IF EXISTS `oncreate_players`;
2 DROP TRIGGER IF EXISTS `oncreate_guilds`;
3 DROP TRIGGER IF EXISTS `ondelete_players`;
4 DROP TRIGGER IF EXISTS `ondelete_guilds`;
5 DROP TRIGGER IF EXISTS `ondelete_accounts`;
6
7 DROP TABLE IF EXISTS `player_depotitems`;
8 DROP TABLE IF EXISTS `tile_items`;
9 DROP TABLE IF EXISTS `tile_store`;
10 DROP TABLE IF EXISTS `tiles`;
11 DROP TABLE IF EXISTS `bans`;
12 DROP TABLE IF EXISTS `house_lists`;
13 DROP TABLE IF EXISTS `houses`;
14 DROP TABLE IF EXISTS `player_items`;
15 DROP TABLE IF EXISTS `player_namelocks`;
16 DROP TABLE IF EXISTS `player_statements`;
17 DROP TABLE IF EXISTS `player_skills`;
18 DROP TABLE IF EXISTS `player_storage`;
19 DROP TABLE IF EXISTS `player_viplist`;
20 DROP TABLE IF EXISTS `player_spells`;
21 DROP TABLE IF EXISTS `player_deaths`;
22 DROP TABLE IF EXISTS `killers`;
23 DROP TABLE IF EXISTS `environment_killers`;
24 DROP TABLE IF EXISTS `player_killers`;
25 DROP TABLE IF EXISTS `guild_ranks`;
26 DROP TABLE IF EXISTS `guilds`;
27 DROP TABLE IF EXISTS `guild_invites`;
28 DROP TABLE IF EXISTS `global_storage`;
29 DROP TABLE IF EXISTS `players`;
30 DROP TABLE IF EXISTS `accounts`;
31 DROP TABLE IF EXISTS `server_record`;
32 DROP TABLE IF EXISTS `server_motd`;
33 DROP TABLE IF EXISTS `server_reports`;
34 DROP TABLE IF EXISTS `server_config`;
35 DROP TABLE IF EXISTS `account_viplist`;
36
37 CREATE TABLE `accounts`
38 (
39 `id` INT NOT NULL AUTO_INCREMENT,
40 `name` VARCHAR(32) NOT NULL DEFAULT '',
41 `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
42 `salt` VARCHAR(40) NOT NULL DEFAULT '',
43 `premdays` INT NOT NULL DEFAULT 0,
44 `lastday` INT UNSIGNED NOT NULL DEFAULT 0,
45 `email` VARCHAR(255) NOT NULL DEFAULT '',
46 `key` VARCHAR(32) NOT NULL DEFAULT '0',
47 `blocked` TINYINT(1) NOT NULL DEFAULT FALSE COMMENT 'internal usage',
48 `warnings` INT NOT NULL DEFAULT 0,
49 `group_id` INT NOT NULL DEFAULT 1,
50 PRIMARY KEY (`id`), UNIQUE (`name`)
51 ) ENGINE = InnoDB;
52
53 INSERT INTO `accounts` VALUES (1, '1', '1', '', 65535, 0, '', '0', 0, 0, 1);
54
55 CREATE TABLE `players`
56 (
57 `id` INT NOT NULL AUTO_INCREMENT,
58 `name` VARCHAR(255) NOT NULL,
59 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
60 `group_id` INT NOT NULL DEFAULT 1,
61 `account_id` INT NOT NULL DEFAULT 0,
62 `level` INT NOT NULL DEFAULT 1,
63 `vocation` INT NOT NULL DEFAULT 0,
64 `health` INT NOT NULL DEFAULT 150,
65 `healthmax` INT NOT NULL DEFAULT 150,
66 `experience` BIGINT UNSIGNED NOT NULL DEFAULT 0,
67 `lookbody` INT NOT NULL DEFAULT 0,
68 `lookfeet` INT NOT NULL DEFAULT 0,
69 `lookhead` INT NOT NULL DEFAULT 0,
70 `looklegs` INT NOT NULL DEFAULT 0,
71 `looktype` INT NOT NULL DEFAULT 136,
72 `lookaddons` INT NOT NULL DEFAULT 0,
73 `lookmount` INT NOT NULL DEFAULT 0,
74 `maglevel` INT NOT NULL DEFAULT 0,
75 `mana` INT NOT NULL DEFAULT 0,
76 `manamax` INT NOT NULL DEFAULT 0,
77 `manaspent` BIGINT UNSIGNED NOT NULL DEFAULT 0,
78 `soul` INT UNSIGNED NOT NULL DEFAULT 0,
79 `town_id` INT NOT NULL DEFAULT 0,
80 `posx` INT NOT NULL DEFAULT 0,
81 `posy` INT NOT NULL DEFAULT 0,
82 `posz` INT NOT NULL DEFAULT 0,
83 `conditions` BLOB NOT NULL,
84 `cap` INT NOT NULL DEFAULT 0,
85 `sex` INT NOT NULL DEFAULT 0,
86 `lastlogin` BIGINT UNSIGNED NOT NULL DEFAULT 0,
87 `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
88 `save` TINYINT(1) NOT NULL DEFAULT 1,
89 `skull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
90 `skulltime` INT NOT NULL DEFAULT 0,
91 `rank_id` INT NOT NULL DEFAULT 0,
92 `guildnick` VARCHAR(255) NOT NULL DEFAULT '',
93 `lastlogout` BIGINT UNSIGNED NOT NULL DEFAULT 0,
94 `blessings` TINYINT(2) NOT NULL DEFAULT 0,
95 `pvp_blessing` TINYINT(1) NOT NULL DEFAULT 0,
96 `balance` BIGINT UNSIGNED NOT NULL DEFAULT 0,
97 `stamina` BIGINT UNSIGNED NOT NULL DEFAULT 151200000 COMMENT 'stored in miliseconds',
98 `direction` INT NOT NULL DEFAULT 2,
99 `loss_experience` INT NOT NULL DEFAULT 100,
100 `loss_mana` INT NOT NULL DEFAULT 100,
101 `loss_skills` INT NOT NULL DEFAULT 100,
102 `loss_containers` INT NOT NULL DEFAULT 100,
103 `loss_items` INT NOT NULL DEFAULT 100,
104 `premend` INT NOT NULL DEFAULT 0 COMMENT 'NOT IN USE BY THE SERVER',
105 `online` TINYINT(1) NOT NULL DEFAULT 0,
106 `marriage` INT UNSIGNED NOT NULL DEFAULT 0,
107 `promotion` INT NOT NULL DEFAULT 0,
108 `deleted` INT NOT NULL DEFAULT 0,
109 `description` VARCHAR(255) NOT NULL DEFAULT '',
110 PRIMARY KEY (`id`), UNIQUE (`name`, `deleted`),
111 KEY (`account_id`), KEY (`group_id`),
112 KEY (`online`), KEY (`deleted`),
113 FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
114 ) ENGINE = InnoDB;
115
116 INSERT INTO `players` VALUES (1, 'Account Manager', 0, 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 0, 853, 921, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '');
117
118 CREATE TABLE `account_viplist`
119 (
120 `account_id` INT NOT NULL,
121 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
122 `player_id` INT NOT NULL,
123 KEY (`account_id`), KEY (`player_id`), KEY (`world_id`), UNIQUE (`account_id`, `player_id`),
124 FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
125 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
126 ) ENGINE = InnoDB;
127
128 CREATE TABLE `player_deaths`
129 (
130 `id` INT NOT NULL AUTO_INCREMENT,
131 `player_id` INT NOT NULL,
132 `date` BIGINT UNSIGNED NOT NULL,
133 `level` INT UNSIGNED NOT NULL,
134 PRIMARY KEY (`id`), INDEX (`date`),
135 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
136 ) ENGINE = InnoDB;
137
138 CREATE TABLE `player_depotitems`
139 (
140 `player_id` INT NOT NULL,
141 `sid` INT NOT NULL COMMENT 'any given range, eg. 0-100 is reserved for depot lockers and all above 100 will be normal items inside depots',
142 `pid` INT NOT NULL DEFAULT 0,
143 `itemtype` INT NOT NULL,
144 `count` INT NOT NULL DEFAULT 0,
145 `attributes` BLOB NOT NULL,
146 KEY (`player_id`), UNIQUE (`player_id`, `sid`),
147 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
148 ) ENGINE = InnoDB;
149
150 CREATE TABLE `player_items`
151 (
152 `player_id` INT NOT NULL,
153 `pid` INT NOT NULL DEFAULT 0,
154 `sid` INT NOT NULL DEFAULT 0,
155 `itemtype` INT NOT NULL DEFAULT 0,
156 `count` INT NOT NULL DEFAULT 0,
157 `attributes` BLOB NOT NULL,
158 KEY (`player_id`), UNIQUE (`player_id`, `sid`),
159 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
160 ) ENGINE = InnoDB;
161
162 CREATE TABLE `player_namelocks`
163 (
164 `player_id` INT NOT NULL,
165 `name` VARCHAR(255) NOT NULL,
166 `new_name` VARCHAR(255) NOT NULL,
167 `date` BIGINT NOT NULL DEFAULT 0,
168 KEY (`player_id`),
169 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
170 ) ENGINE = InnoDB;
171
172 CREATE TABLE `player_statements`
173 (
174 `id` INT NOT NULL AUTO_INCREMENT,
175 `player_id` INT NOT NULL,
176 `channel_id` INT NOT NULL DEFAULT 0,
177 `text` VARCHAR (255) NOT NULL,
178 `date` BIGINT NOT NULL DEFAULT 0,
179 PRIMARY KEY (`id`), KEY (`player_id`), KEY (`channel_id`),
180 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
181 ) ENGINE = InnoDB;
182
183 CREATE TABLE `player_skills`
184 (
185 `player_id` INT NOT NULL,
186 `skillid` TINYINT(2) NOT NULL DEFAULT 0,
187 `value` INT UNSIGNED NOT NULL DEFAULT 0,
188 `count` INT UNSIGNED NOT NULL DEFAULT 0,
189 KEY (`player_id`), UNIQUE (`player_id`, `skillid`),
190 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
191 ) ENGINE = InnoDB;
192
193 CREATE TABLE `player_spells`
194 (
195 `player_id` INT NOT NULL,
196 `name` VARCHAR(255) NOT NULL,
197 KEY (`player_id`), UNIQUE (`player_id`, `name`),
198 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
199 ) ENGINE = InnoDB;
200
201 CREATE TABLE `player_storage`
202 (
203 `player_id` INT NOT NULL,
204 `key` VARCHAR(32) NOT NULL DEFAULT '0',
205 `value` TEXT NOT NULL,
206 KEY (`player_id`), UNIQUE (`player_id`, `key`),
207 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
208 ) ENGINE = InnoDB;
209
210 CREATE TABLE `player_viplist`
211 (
212 `player_id` INT NOT NULL,
213 `vip_id` INT NOT NULL,
214 KEY (`player_id`), KEY (`vip_id`), UNIQUE (`player_id`, `vip_id`),
215 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
216 FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
217 ) ENGINE = InnoDB;
218
219 CREATE TABLE `killers`
220 (
221 `id` INT NOT NULL AUTO_INCREMENT,
222 `death_id` INT NOT NULL,
223 `final_hit` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
224 `unjustified` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
225 `war` INT NOT NULL DEFAULT 0,
226 PRIMARY KEY (`id`),
227 FOREIGN KEY (`death_id`) REFERENCES `player_deaths`(`id`) ON DELETE CASCADE
228 ) ENGINE = InnoDB;
229
230 CREATE TABLE `player_killers`
231 (
232 `kill_id` INT NOT NULL,
233 `player_id` INT NOT NULL,
234 FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE,
235 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
236 ) ENGINE = InnoDB;
237
238 CREATE TABLE `environment_killers`
239 (
240 `kill_id` INT NOT NULL,
241 `name` VARCHAR(255) NOT NULL,
242 FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE
243 ) ENGINE = InnoDB;
244
245 CREATE TABLE `houses`
246 (
247 `id` INT UNSIGNED NOT NULL,
248 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
249 `owner` INT NOT NULL,
250 `paid` INT UNSIGNED NOT NULL DEFAULT 0,
251 `warnings` INT NOT NULL DEFAULT 0,
252 `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
253 `name` VARCHAR(255) NOT NULL,
254 `town` INT UNSIGNED NOT NULL DEFAULT 0,
255 `size` INT UNSIGNED NOT NULL DEFAULT 0,
256 `price` INT UNSIGNED NOT NULL DEFAULT 0,
257 `rent` INT UNSIGNED NOT NULL DEFAULT 0,
258 `doors` INT UNSIGNED NOT NULL DEFAULT 0,
259 `beds` INT UNSIGNED NOT NULL DEFAULT 0,
260 `tiles` INT UNSIGNED NOT NULL DEFAULT 0,
261 `guild` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
262 `clear` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
263 UNIQUE (`id`, `world_id`)
264 ) ENGINE = InnoDB;
265
266 CREATE TABLE `tile_store`
267 (
268 `house_id` INT UNSIGNED NOT NULL,
269 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
270 `data` LONGBLOB NOT NULL,
271 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
272 ) ENGINE = InnoDB;
273
274 CREATE TABLE `house_auctions`
275 (
276 `house_id` INT UNSIGNED NOT NULL,
277 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
278 `player_id` INT NOT NULL,
279 `bid` INT UNSIGNED NOT NULL DEFAULT 0,
280 `limit` INT UNSIGNED NOT NULL DEFAULT 0,
281 `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,
282 UNIQUE (`house_id`, `world_id`),
283 FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE,
284 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
285 ) ENGINE = InnoDB;
286
287 CREATE TABLE `house_lists`
288 (
289 `house_id` INT UNSIGNED NOT NULL,
290 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
291 `listid` INT NOT NULL,
292 `list` TEXT NOT NULL,
293 UNIQUE (`house_id`, `world_id`, `listid`),
294 FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
295 ) ENGINE = InnoDB;
296
297 CREATE TABLE `house_data`
298 (
299 `house_id` INT UNSIGNED NOT NULL,
300 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
301 `data` LONGBLOB NOT NULL,
302 UNIQUE (`house_id`, `world_id`),
303 FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
304 ) ENGINE = InnoDB;
305
306 CREATE TABLE `tiles`
307 (
308 `id` INT UNSIGNED NOT NULL,
309 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
310 `house_id` INT UNSIGNED NOT NULL,
311 `x` INT(5) UNSIGNED NOT NULL,
312 `y` INT(5) UNSIGNED NOT NULL,
313 `z` TINYINT(2) UNSIGNED NOT NULL,
314 UNIQUE (`id`, `world_id`),
315 KEY (`x`, `y`, `z`),
316 FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
317 ) ENGINE = InnoDB;
318
319 CREATE TABLE `tile_items`
320 (
321 `tile_id` INT UNSIGNED NOT NULL,
322 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
323 `sid` INT NOT NULL,
324 `pid` INT NOT NULL DEFAULT 0,
325 `itemtype` INT NOT NULL,
326 `count` INT NOT NULL DEFAULT 0,
327 `attributes` BLOB NOT NULL,
328 UNIQUE (`tile_id`, `world_id`, `sid`), KEY (`sid`),
329 FOREIGN KEY (`tile_id`) REFERENCES `tiles`(`id`) ON DELETE CASCADE
330 ) ENGINE = InnoDB;
331
332 CREATE TABLE `guilds`
333 (
334 `id` INT NOT NULL AUTO_INCREMENT,
335 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
336 `name` VARCHAR(255) NOT NULL,
337 `ownerid` INT NOT NULL,
338 `creationdata` INT NOT NULL,
339 `checkdata` INT NOT NULL,
340 `motd` VARCHAR(255) NOT NULL,
341 `balance` BIGINT UNSIGNED NOT NULL DEFAULT 0,
342 PRIMARY KEY (`id`),
343 UNIQUE (`name`, `world_id`)
344 ) ENGINE = InnoDB;
345
346 CREATE TABLE `guild_invites`
347 (
348 `player_id` INT NOT NULL DEFAULT 0,
349 `guild_id` INT NOT NULL DEFAULT 0,
350 UNIQUE (`player_id`, `guild_id`),
351 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
352 FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
353 ) ENGINE = InnoDB;
354
355 CREATE TABLE `guild_ranks`
356 (
357 `id` INT NOT NULL AUTO_INCREMENT,
358 `guild_id` INT NOT NULL,
359 `name` VARCHAR(255) NOT NULL,
360 `level` INT NOT NULL COMMENT '1 - leader, 2 - vice leader, 3 - member',
361 PRIMARY KEY (`id`),
362 FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
363 ) ENGINE = InnoDB;
364
365 CREATE TABLE `guild_wars`
366 (
367 `id` INT NOT NULL AUTO_INCREMENT,
368 `guild_id` INT NOT NULL,
369 `enemy_id` INT NOT NULL,
370 `begin` BIGINT NOT NULL DEFAULT 0,
371 `end` BIGINT NOT NULL DEFAULT 0,
372 `frags` INT UNSIGNED NOT NULL DEFAULT 0,
373 `payment` BIGINT UNSIGNED NOT NULL DEFAULT 0,
374 `guild_kills` INT UNSIGNED NOT NULL DEFAULT 0,
375 `enemy_kills` INT UNSIGNED NOT NULL DEFAULT 0,
376 `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
377 PRIMARY KEY (`id`), KEY `status` (`status`),
378 KEY `guild_id` (`guild_id`), KEY `enemy_id` (`enemy_id`),
379 FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,
380 FOREIGN KEY (`enemy_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
381 ) ENGINE=InnoDB;
382
383 CREATE TABLE `guild_kills`
384 (
385 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
386 `guild_id` INT NOT NULL,
387 `war_id` INT NOT NULL,
388 `death_id` INT NOT NULL,
389 FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,
390 FOREIGN KEY (`war_id`) REFERENCES `guild_wars`(`id`) ON DELETE CASCADE,
391 FOREIGN KEY (`death_id`) REFERENCES `player_deaths`(`id`) ON DELETE CASCADE
392 ) ENGINE = InnoDB;
393
394 CREATE TABLE `bans`
395 (
396 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
397 `type` TINYINT(1) NOT NULL COMMENT '1 - ip, 2 - player, 3 - account, 4 - notation',
398 `value` INT UNSIGNED NOT NULL COMMENT 'ip - ip address, player - player_id, account - account_id, notation - account_id',
399 `param` INT UNSIGNED NOT NULL COMMENT 'ip - mask, player - type (1 - report, 2 - lock, 3 - ban), account - player, notation - player',
400 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
401 `expires` INT NOT NULL DEFAULT -1,
402 `added` INT UNSIGNED NOT NULL,
403 `admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
404 `comment` TEXT NOT NULL,
405 PRIMARY KEY (`id`),
406 KEY `type` (`type`, `value`),
407 KEY `active` (`active`)
408 ) ENGINE = InnoDB;
409
410 CREATE TABLE `global_storage`
411 (
412 `key` VARCHAR(32) NOT NULL,
413 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
414 `value` TEXT NOT NULL,
415 UNIQUE (`key`, `world_id`)
416 ) ENGINE = InnoDB;
417
418 CREATE TABLE `server_config`
419 (
420 `config` VARCHAR(35) NOT NULL DEFAULT '',
421 `value` VARCHAR(255) NOT NULL DEFAULT '',
422 UNIQUE (`config`)
423 ) ENGINE = InnoDB;
424
425 INSERT INTO `server_config` VALUES ('db_version', 31);
426
427 CREATE TABLE `server_motd`
428 (
429 `id` INT UNSIGNED NOT NULL,
430 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
431 `text` TEXT NOT NULL,
432 UNIQUE (`id`, `world_id`)
433 ) ENGINE = InnoDB;
434
435 INSERT INTO `server_motd` VALUES (1, 0, 'Welcome to The Forgotten Server!');
436
437 CREATE TABLE `server_record`
438 (
439 `record` INT NOT NULL,
440 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
441 `timestamp` BIGINT NOT NULL,
442 UNIQUE (`record`, `world_id`, `timestamp`)
443 ) ENGINE = InnoDB;
444
445 INSERT INTO `server_record` VALUES (0, 0, 0);
446
447 CREATE TABLE `server_reports`
448 (
449 `id` INT NOT NULL AUTO_INCREMENT,
450 `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
451 `player_id` INT NOT NULL DEFAULT 1,
452 `posx` INT NOT NULL DEFAULT 0,
453 `posy` INT NOT NULL DEFAULT 0,
454 `posz` INT NOT NULL DEFAULT 0,
455 `timestamp` BIGINT NOT NULL DEFAULT 0,
456 `report` TEXT NOT NULL,
457 `reads` INT NOT NULL DEFAULT 0,
458 PRIMARY KEY (`id`),
459 KEY (`world_id`), KEY (`reads`),
460 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
461 ) ENGINE = InnoDB;
462
463 DELIMITER |
464
465 CREATE TRIGGER `ondelete_accounts`
466 BEFORE DELETE
467 ON `accounts`
468 FOR EACH ROW
469 BEGIN
470 DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;
471 END|
472
473 CREATE TRIGGER `oncreate_guilds`
474 AFTER INSERT
475 ON `guilds`
476 FOR EACH ROW
477 BEGIN
478 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
479 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
480 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
481 END|
482
483 CREATE TRIGGER `ondelete_guilds`
484 BEFORE DELETE
485 ON `guilds`
486 FOR EACH ROW
487 BEGIN
488 UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
489 END|
490
491 CREATE TRIGGER `oncreate_players`
492 AFTER INSERT
493 ON `players`
494 FOR EACH ROW
495 BEGIN
496 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
497 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
498 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
499 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
500 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
501 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
502 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
503 END|
504
505 CREATE TRIGGER `ondelete_players`
506 BEFORE DELETE
507 ON `players`
508 FOR EACH ROW
509 BEGIN
510 DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;
511 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
512 END|
513
514 DELIMITER ;