BgqRZmFd

1DROP TRIGGER IF EXISTS `oncreate_players`;
2DROP TRIGGER IF EXISTS `oncreate_guilds`;
3DROP TRIGGER IF EXISTS `ondelete_players`;
4DROP TRIGGER IF EXISTS `ondelete_guilds`;
5DROP TRIGGER IF EXISTS `ondelete_accounts`;
6
7DROP TABLE IF EXISTS `player_depotitems`;
8DROP TABLE IF EXISTS `tile_items`;
9DROP TABLE IF EXISTS `tile_store`;
10DROP TABLE IF EXISTS `tiles`;
11DROP TABLE IF EXISTS `bans`;
12DROP TABLE IF EXISTS `house_lists`;
13DROP TABLE IF EXISTS `houses`;
14DROP TABLE IF EXISTS `player_items`;
15DROP TABLE IF EXISTS `player_namelocks`;
16DROP TABLE IF EXISTS `player_statements`;
17DROP TABLE IF EXISTS `player_skills`;
18DROP TABLE IF EXISTS `player_storage`;
19DROP TABLE IF EXISTS `player_viplist`;
20DROP TABLE IF EXISTS `player_spells`;
21DROP TABLE IF EXISTS `player_deaths`;
22DROP TABLE IF EXISTS `killers`;
23DROP TABLE IF EXISTS `environment_killers`;
24DROP TABLE IF EXISTS `player_killers`;
25DROP TABLE IF EXISTS `guild_ranks`;
26DROP TABLE IF EXISTS `guilds`;
27DROP TABLE IF EXISTS `guild_invites`;
28DROP TABLE IF EXISTS `global_storage`;
29DROP TABLE IF EXISTS `players`;
30DROP TABLE IF EXISTS `accounts`;
31DROP TABLE IF EXISTS `server_record`;
32DROP TABLE IF EXISTS `server_motd`;
33DROP TABLE IF EXISTS `server_reports`;
34DROP TABLE IF EXISTS `server_config`;
35DROP TABLE IF EXISTS `account_viplist`;
36
37CREATE 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
53INSERT INTO `accounts` VALUES (1, '1', '1', '', 65535, 0, '', '0', 0, 0, 1);
54
55CREATE 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
116INSERT 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
118CREATE 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
128CREATE 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
138CREATE 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
150CREATE 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
162CREATE 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
172CREATE 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
183CREATE 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
193CREATE 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
201CREATE TABLE `player_storage`
202(
203        `player_id` INT NOT NULL,
204        `key` VARCHAR(32) NOT NULL DEFAULT '0',
205        `value` VARCHAR(255) NOT NULL DEFAULT '0',
206        KEY (`player_id`), UNIQUE (`player_id`, `key`),
207        FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
208) ENGINE = InnoDB;
209
210CREATE 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
219CREATE 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
230CREATE 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
238CREATE 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
245CREATE 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
266CREATE 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
274CREATE 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
287CREATE 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
297CREATE 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
306CREATE 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
319CREATE 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
332CREATE 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
346CREATE 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
355CREATE 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
365CREATE 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
383CREATE 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
394CREATE 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
410CREATE TABLE `global_storage`
411(
412        `key` VARCHAR(32) NOT NULL,
413        `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
414        `value` VARCHAR(255) NOT NULL DEFAULT '0',
415        UNIQUE  (`key`, `world_id`)
416) ENGINE = InnoDB;
417
418CREATE 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
425INSERT INTO `server_config` VALUES ('db_version', 31);
426
427CREATE 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
435INSERT INTO `server_motd` VALUES (1, 0, 'Welcome to The Forgotten Server!');
436
437CREATE 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
445INSERT INTO `server_record` VALUES (0, 0, 0);
446
447CREATE 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
463DELIMITER |
464
465CREATE TRIGGER `ondelete_accounts`
466BEFORE DELETE
467ON `accounts`
468FOR EACH ROW
469BEGIN
470        DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;
471END|
472
473CREATE TRIGGER `oncreate_guilds`
474AFTER INSERT
475ON `guilds`
476FOR EACH ROW
477BEGIN
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`);
481END|
482
483CREATE TRIGGER `ondelete_guilds`
484BEFORE DELETE
485ON `guilds`
486FOR EACH ROW
487BEGIN
488        UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
489END|
490
491CREATE TRIGGER `oncreate_players`
492AFTER INSERT
493ON `players`
494FOR EACH ROW
495BEGIN
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);
503END|
504
505CREATE TRIGGER `ondelete_players`
506BEFORE DELETE
507ON `players`
508FOR EACH ROW
509BEGIN
510        DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;
511        UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
512END|
513
514DELIMITER ;