· 8 years ago · May 14, 2018, 04:14 PM
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 `map_store`;
12DROP TABLE IF EXISTS `bans`;
13DROP TABLE IF EXISTS `house_lists`;
14DROP TABLE IF EXISTS `houses`;
15DROP TABLE IF EXISTS `player_items`;
16DROP TABLE IF EXISTS `player_skills`;
17DROP TABLE IF EXISTS `player_storage`;
18DROP TABLE IF EXISTS `player_viplist`;
19DROP TABLE IF EXISTS `player_spells`;
20DROP TABLE IF EXISTS `player_deaths`;
21DROP TABLE IF EXISTS `guild_ranks`;
22DROP TABLE IF EXISTS `guilds`;
23DROP TABLE IF EXISTS `guild_invites`;
24DROP TABLE IF EXISTS `global_storage`;
25DROP TABLE IF EXISTS `players`;
26DROP TABLE IF EXISTS `accounts`;
27DROP TABLE IF EXISTS `groups`;
28
29CREATE TABLE `groups`
30(
31 `id` INT NOT NULL AUTO_INCREMENT,
32 `name` VARCHAR(255) NOT NULL COMMENT 'group name',
33 `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
34 `access` INT NOT NULL,
35 `maxdepotitems` INT NOT NULL,
36 `maxviplist` INT NOT NULL,
37 PRIMARY KEY (`id`)
38) ENGINE = InnoDB;
39
40INSERT INTO `groups` VALUES (3, 'a god', 134788128760, 1, 0, 0);
41INSERT INTO `groups` VALUES (2, 'a gamemaster', 137438953471, 1, 0, 0);
42INSERT INTO `groups` VALUES (1, 'player', 0, 0, 0, 0);
43
44CREATE TABLE `accounts`
45(
46 `id` INT NOT NULL AUTO_INCREMENT,
47 `password` VARCHAR(255) /* VARCHAR(32) for MD5*/ NOT NULL DEFAULT '',
48 `type` INT NOT NULL DEFAULT 1,
49 `premdays` INT NOT NULL DEFAULT 0,
50 `lastday` INT UNSIGNED NOT NULL DEFAULT 0,
51 `key` VARCHAR(20) NOT NULL DEFAULT '0',
52 `email` VARCHAR(255) NOT NULL DEFAULT '',
53 `blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
54 `warnings` INT NOT NULL DEFAULT 0,
55 `group_id` INT NOT NULL DEFAULT 1,
56 PRIMARY KEY (`id`),
57 FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
58) ENGINE = InnoDB;
59
60INSERT INTO `accounts` VALUES (1, '1', 1, 65535, 0, '0', '', 0, 0, 1);
61
62CREATE TABLE `players`
63(
64 `id` INT NOT NULL AUTO_INCREMENT,
65 `name` VARCHAR(255) NOT NULL,
66 `group_id` INT NOT NULL DEFAULT 1,
67 `account_id` INT NOT NULL DEFAULT 0,
68 `level` INT NOT NULL DEFAULT 1,
69 `vocation` INT NOT NULL DEFAULT 0,
70 `health` INT NOT NULL DEFAULT 150,
71 `healthmax` INT NOT NULL DEFAULT 150,
72 `experience` BIGINT NOT NULL DEFAULT 0,
73 `lookbody` INT NOT NULL DEFAULT 0,
74 `lookfeet` INT NOT NULL DEFAULT 0,
75 `lookhead` INT NOT NULL DEFAULT 0,
76 `looklegs` INT NOT NULL DEFAULT 0,
77 `looktype` INT NOT NULL DEFAULT 136,
78 `lookaddons` INT NOT NULL DEFAULT 0,
79 `maglevel` INT NOT NULL DEFAULT 0,
80 `mana` INT NOT NULL DEFAULT 0,
81 `manamax` INT NOT NULL DEFAULT 0,
82 `manaspent` INT NOT NULL DEFAULT 0,
83 `soul` INT UNSIGNED NOT NULL DEFAULT 0,
84 `town_id` INT NOT NULL DEFAULT 0,
85 `posx` INT NOT NULL DEFAULT 0,
86 `posy` INT NOT NULL DEFAULT 0,
87 `posz` INT NOT NULL DEFAULT 0,
88 `conditions` BLOB NOT NULL,
89 `cap` INT NOT NULL DEFAULT 0,
90 `sex` INT NOT NULL DEFAULT 0,
91 `lastlogin` BIGINT UNSIGNED NOT NULL DEFAULT 0,
92 `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
93 `save` TINYINT(1) NOT NULL DEFAULT 1,
94 `redskull` TINYINT(1) NOT NULL DEFAULT 0,
95 `redskulltime` INT NOT NULL DEFAULT 0,
96 `rank_id` INT NOT NULL DEFAULT 0,
97 `guildnick` VARCHAR(255) NOT NULL DEFAULT '',
98 `lastlogout` BIGINT UNSIGNED NOT NULL DEFAULT 0,
99 `blessings` TINYINT(2) NOT NULL DEFAULT 0,
100 `direction` INT NOT NULL DEFAULT 0 COMMENT 'NOT IN USE BY THE SERVER',
101 `loss_experience` INT NOT NULL DEFAULT '10' COMMENT 'NOT IN USE BY THE SERVER',
102 `loss_mana` INT NOT NULL DEFAULT '10' COMMENT 'NOT IN USE BY THE SERVER',
103 `loss_skills` INT NOT NULL DEFAULT '10' COMMENT 'NOT IN USE BY THE SERVER',
104 `premend` INT NOT NULL DEFAULT 0 COMMENT 'NOT IN USE BY THE SERVER',
105 `online` TINYINT NOT NULL DEFAULT 0,
106 PRIMARY KEY (`id`),
107 KEY (`name`),
108 FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
109 FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)
110) ENGINE = InnoDB;
111
112INSERT INTO `players` VALUES (1, 'Account Manager', 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 10, 10, 10, 0, 0);
113
114CREATE TABLE `bans`
115(
116 `type` INT NOT NULL COMMENT 'this field defines if its ip, accountban or namelock',
117 `ip` INT UNSIGNED NOT NULL DEFAULT 0,
118 `mask` INT UNSIGNED NOT NULL DEFAULT 4294967295,
119 `player` INT UNSIGNED NOT NULL DEFAULT 0,
120 `account` INT UNSIGNED NOT NULL DEFAULT 0,
121 `time` INT UNSIGNED NOT NULL DEFAULT 0,
122 `reason_id` INT NOT NULL DEFAULT 0,
123 `action_id` INT NOT NULL DEFAULT 0,
124 `comment` VARCHAR(60) NOT NULL DEFAULT '',
125 `banned_by` INT UNSIGNED NOT NULL DEFAULT 0
126) ENGINE = InnoDB;
127
128CREATE TABLE `global_storage`
129(
130 `key` INT UNSIGNED NOT NULL,
131 `value` INT NOT NULL,
132 PRIMARY KEY (`key`)
133) ENGINE = InnoDB;
134
135CREATE TABLE `guilds`
136(
137 `id` INT NOT NULL AUTO_INCREMENT,
138 `name` VARCHAR(255) NOT NULL COMMENT 'guild name - nothing else needed here',
139 `ownerid` INT NOT NULL,
140 `creationdata` INT NOT NULL,
141 `motd` VARCHAR(255) NOT NULL,
142 PRIMARY KEY (`id`)
143) ENGINE = InnoDB;
144
145CREATE TABLE `guild_invites`
146(
147 `player_id` INT UNSIGNED NOT NULL DEFAULT 0,
148 `guild_id` INT UNSIGNED NOT NULL DEFAULT 0
149) ENGINE = InnoDB;
150
151CREATE TABLE `guild_ranks`
152(
153 `id` INT NOT NULL AUTO_INCREMENT,
154 `guild_id` INT NOT NULL COMMENT 'guild',
155 `name` VARCHAR(255) NOT NULL COMMENT 'rank name',
156 `level` INT NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
157 PRIMARY KEY (`id`),
158 FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
159) ENGINE = InnoDB;
160
161CREATE TABLE `house_lists`
162(
163 `house_id` INT NOT NULL,
164 `listid` INT NOT NULL,
165 `list` TEXT NOT NULL
166) ENGINE = InnoDB;
167
168CREATE TABLE `houses`
169(
170 `id` INT NOT NULL AUTO_INCREMENT,
171 `owner` INT NOT NULL,
172 `paid` INT UNSIGNED NOT NULL DEFAULT 0,
173 `warnings` TEXT NOT NULL,
174 PRIMARY KEY (`id`)
175) ENGINE = InnoDB;
176
177CREATE TABLE `player_deaths`
178(
179 `player_id` INT NOT NULL,
180 `time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
181 `level` INT NOT NULL DEFAULT 1,
182 `killed_by` VARCHAR(255) NOT NULL,
183 `is_player` TINYINT(1) NOT NULL DEFAULT 1,
184 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
185) ENGINE = InnoDB;
186
187CREATE TABLE `player_depotitems`
188(
189 `player_id` INT NOT NULL,
190 `depot_id` INT NOT NULL DEFAULT 0,
191 `sid` INT NOT NULL COMMENT 'any given range eg 0-100 will be reserved for depot lockers and all > 100 will be then normal items inside depots',
192 `pid` INT NOT NULL DEFAULT 0,
193 `itemtype` INT NOT NULL,
194 `count` INT NOT NULL DEFAULT 0,
195 `attributes` BLOB NOT NULL,
196 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
197 KEY (`player_id`, `depot_id`),
198 UNIQUE KEY (`player_id`, `sid`)
199) ENGINE = InnoDB;
200
201CREATE TABLE `player_items`
202(
203 `player_id` INT NOT NULL DEFAULT 0,
204 `pid` INT NOT NULL DEFAULT 0,
205 `sid` INT NOT NULL DEFAULT 0,
206 `itemtype` INT NOT NULL DEFAULT 0,
207 `count` INT NOT NULL DEFAULT 0,
208 `attributes` BLOB NOT NULL,
209 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
210) ENGINE = InnoDB;
211
212CREATE TABLE `player_skills`
213(
214 `player_id` INT NOT NULL DEFAULT 0,
215 `skillid` tinyint(4) NOT NULL DEFAULT 0,
216 `value` INT UNSIGNED NOT NULL DEFAULT 0,
217 `count` INT UNSIGNED NOT NULL DEFAULT 0,
218 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
219) ENGINE = InnoDB;
220
221CREATE TABLE `player_spells`
222(
223 `player_id` INT NOT NULL,
224 `name` VARCHAR(255) NOT NULL,
225 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
226) ENGINE = InnoDB;
227
228CREATE TABLE `player_storage`
229(
230 `player_id` INT NOT NULL DEFAULT 0,
231 `key` INT UNSIGNED NOT NULL DEFAULT 0,
232 `value` INT NOT NULL DEFAULT 0,
233 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
234) ENGINE = InnoDB;
235
236CREATE TABLE `player_viplist`
237(
238 `player_id` INT NOT NULL COMMENT 'id of player whose viplist entry it is',
239 `vip_id` INT NOT NULL COMMENT 'id of target player of viplist entry',
240 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
241 FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
242) ENGINE = InnoDB;
243
244CREATE TABLE `map_store`
245(
246 `house_id` int(10) UNSIGNED NOT NULL,
247 `data` LONGBLOB NOT NULL,
248 KEY `house_id` (`house_id`)
249) ENGINE = InnoDB;
250
251CREATE TABLE `tiles`
252(
253 `id` INT NOT NULL AUTO_INCREMENT,
254 `x` INT NOT NULL,
255 `y` INT NOT NULL,
256 `z` INT NOT NULL,
257 PRIMARY KEY(`id`)
258) ENGINE = InnoDB;
259
260CREATE TABLE `tile_items`
261(
262 `tile_id` INT NOT NULL,
263 `sid` INT NOT NULL,
264 `pid` INT NOT NULL DEFAULT 0,
265 `itemtype` INT NOT NULL,
266 `count` INT NOT NULL DEFAULT 0,
267 `attributes` BLOB NOT NULL,
268 FOREIGN KEY (`tile_id`) REFERENCES `tiles` (`id`) ON DELETE CASCADE,
269 INDEX (`sid`)
270) ENGINE = InnoDB;
271
272CREATE TABLE `tile_store`
273(
274 `house_id` INT(11) NOT NULL,
275 `data` LONGBLOB NOT NULL,
276 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
277) ENGINE = InnoDB;
278
279DELIMITER |
280
281CREATE TRIGGER `ondelete_accounts`
282BEFORE DELETE
283ON `accounts`
284FOR EACH ROW
285BEGIN
286 DELETE FROM `bans` WHERE `account` = OLD.`id`;
287END|
288
289CREATE TRIGGER `ondelete_guilds`
290BEFORE DELETE
291ON `guilds`
292FOR EACH ROW
293BEGIN
294 UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
295END|
296
297CREATE TRIGGER `ondelete_players`
298BEFORE DELETE
299ON `players`
300FOR EACH ROW
301BEGIN
302 DELETE FROM `bans` WHERE `type` = 2 AND `player` = OLD.`id`;
303 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
304END|
305
306CREATE TRIGGER `oncreate_guilds`
307AFTER INSERT
308ON `guilds`
309FOR EACH ROW
310BEGIN
311 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('the Leader', 3, NEW.`id`);
312 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Vice-Leader', 2, NEW.`id`);
313 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Member', 1, NEW.`id`);
314END|
315
316CREATE TRIGGER `oncreate_players`
317AFTER INSERT
318ON `players`
319FOR EACH ROW
320BEGIN
321 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
322 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
323 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
324 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
325 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
326 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
327 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
328END|
329
330DELIMITER ;