· 5 years ago · Sep 07, 2020, 02:42 PM
1CREATE TABLE IF NOT EXISTS `accounts` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `name` varchar(32) NOT NULL,
4 `password` char(40) NOT NULL,
5 `secret` char(16) DEFAULT NULL,
6 `type` int(11) NOT NULL DEFAULT '1',
7 `premdays` int(11) NOT NULL DEFAULT '0',
8 `lastday` int(10) unsigned NOT NULL DEFAULT '0',
9 `email` varchar(255) NOT NULL DEFAULT '',
10 `creation` int(11) NOT NULL DEFAULT '0',
11 PRIMARY KEY (`id`),
12 UNIQUE KEY `name` (`name`)
13) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
14
15CREATE TABLE IF NOT EXISTS `players` (
16 `id` int(11) NOT NULL AUTO_INCREMENT,
17 `name` varchar(255) NOT NULL,
18 `group_id` int(11) NOT NULL DEFAULT '1',
19 `account_id` int(11) NOT NULL DEFAULT '0',
20 `level` int(11) NOT NULL DEFAULT '1',
21 `vocation` int(11) NOT NULL DEFAULT '0',
22 `health` int(11) NOT NULL DEFAULT '150',
23 `healthmax` int(11) NOT NULL DEFAULT '150',
24 `experience` bigint(20) NOT NULL DEFAULT '0',
25 `lookbody` int(11) NOT NULL DEFAULT '0',
26 `lookfeet` int(11) NOT NULL DEFAULT '0',
27 `lookhead` int(11) NOT NULL DEFAULT '0',
28 `looklegs` int(11) NOT NULL DEFAULT '0',
29 `looktype` int(11) NOT NULL DEFAULT '136',
30 `lookaddons` int(11) NOT NULL DEFAULT '0',
31 `direction` tinyint(1) unsigned NOT NULL DEFAULT '2',
32 `maglevel` int(11) NOT NULL DEFAULT '0',
33 `mana` int(11) NOT NULL DEFAULT '0',
34 `manamax` int(11) NOT NULL DEFAULT '0',
35 `manaspent` int(11) unsigned NOT NULL DEFAULT '0',
36 `soul` int(10) unsigned NOT NULL DEFAULT '0',
37 `town_id` int(11) NOT NULL DEFAULT '1',
38 `posx` int(11) NOT NULL DEFAULT '0',
39 `posy` int(11) NOT NULL DEFAULT '0',
40 `posz` int(11) NOT NULL DEFAULT '0',
41 `conditions` blob NOT NULL,
42 `cap` int(11) NOT NULL DEFAULT '400',
43 `sex` int(11) NOT NULL DEFAULT '0',
44 `lastlogin` bigint(20) unsigned NOT NULL DEFAULT '0',
45 `lastip` int(10) unsigned NOT NULL DEFAULT '0',
46 `save` tinyint(1) NOT NULL DEFAULT '1',
47 `skull` tinyint(1) NOT NULL DEFAULT '0',
48 `skulltime` bigint(20) NOT NULL DEFAULT '0',
49 `lastlogout` bigint(20) unsigned NOT NULL DEFAULT '0',
50 `blessings` tinyint(2) NOT NULL DEFAULT '0',
51 `onlinetime` int(11) NOT NULL DEFAULT '0',
52 `deletion` bigint(15) NOT NULL DEFAULT '0',
53 `balance` bigint(20) unsigned NOT NULL DEFAULT '0',
54 `offlinetraining_time` smallint(5) unsigned NOT NULL DEFAULT '43200',
55 `offlinetraining_skill` int(11) NOT NULL DEFAULT '-1',
56 `stamina` smallint(5) unsigned NOT NULL DEFAULT '2520',
57 `skill_fist` int(10) unsigned NOT NULL DEFAULT 10,
58 `skill_fist_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
59 `skill_club` int(10) unsigned NOT NULL DEFAULT 10,
60 `skill_club_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
61 `skill_sword` int(10) unsigned NOT NULL DEFAULT 10,
62 `skill_sword_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
63 `skill_axe` int(10) unsigned NOT NULL DEFAULT 10,
64 `skill_axe_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
65 `skill_dist` int(10) unsigned NOT NULL DEFAULT 10,
66 `skill_dist_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
67 `skill_shielding` int(10) unsigned NOT NULL DEFAULT 10,
68 `skill_shielding_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
69 `skill_fishing` int(10) unsigned NOT NULL DEFAULT 10,
70 `skill_fishing_tries` bigint(20) unsigned NOT NULL DEFAULT 0,
71 PRIMARY KEY (`id`),
72 UNIQUE KEY `name` (`name`),
73 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
74 KEY `vocation` (`vocation`)
75) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
76
77CREATE TABLE IF NOT EXISTS `account_bans` (
78 `account_id` int(11) NOT NULL,
79 `reason` varchar(255) NOT NULL,
80 `banned_at` bigint(20) NOT NULL,
81 `expires_at` bigint(20) NOT NULL,
82 `banned_by` int(11) NOT NULL,
83 PRIMARY KEY (`account_id`),
84 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
85 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
86) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
87
88CREATE TABLE IF NOT EXISTS `account_ban_history` (
89 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
90 `account_id` int(11) NOT NULL,
91 `reason` varchar(255) NOT NULL,
92 `banned_at` bigint(20) NOT NULL,
93 `expired_at` bigint(20) NOT NULL,
94 `banned_by` int(11) NOT NULL,
95 PRIMARY KEY (`id`),
96 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
97 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
98) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
99
100CREATE TABLE IF NOT EXISTS `ip_bans` (
101 `ip` int(10) unsigned NOT NULL,
102 `reason` varchar(255) NOT NULL,
103 `banned_at` bigint(20) NOT NULL,
104 `expires_at` bigint(20) NOT NULL,
105 `banned_by` int(11) NOT NULL,
106 PRIMARY KEY (`ip`),
107 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
108) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
109
110CREATE TABLE IF NOT EXISTS `player_namelocks` (
111 `player_id` int(11) NOT NULL,
112 `reason` varchar(255) NOT NULL,
113 `namelocked_at` bigint(20) NOT NULL,
114 `namelocked_by` int(11) NOT NULL,
115 PRIMARY KEY (`player_id`),
116 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
117 FOREIGN KEY (`namelocked_by`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
118) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
119
120CREATE TABLE IF NOT EXISTS `account_viplist` (
121 `account_id` int(11) NOT NULL COMMENT 'id of account whose viplist entry it is',
122 `player_id` int(11) NOT NULL COMMENT 'id of target player of viplist entry',
123 `description` varchar(128) NOT NULL DEFAULT '',
124 `icon` tinyint(2) unsigned NOT NULL DEFAULT '0',
125 `notify` tinyint(1) NOT NULL DEFAULT '0',
126 UNIQUE KEY `account_player_index` (`account_id`,`player_id`),
127 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
128 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
129) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
130
131CREATE TABLE IF NOT EXISTS `guilds` (
132 `id` int(11) NOT NULL AUTO_INCREMENT,
133 `name` varchar(255) NOT NULL,
134 `ownerid` int(11) NOT NULL,
135 `creationdata` int(11) NOT NULL,
136 `motd` varchar(255) NOT NULL DEFAULT '',
137 PRIMARY KEY (`id`),
138 UNIQUE KEY (`name`),
139 UNIQUE KEY (`ownerid`),
140 FOREIGN KEY (`ownerid`) REFERENCES `players`(`id`) ON DELETE CASCADE
141) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
142
143CREATE TABLE IF NOT EXISTS `guild_invites` (
144 `player_id` int(11) NOT NULL DEFAULT '0',
145 `guild_id` int(11) NOT NULL DEFAULT '0',
146 PRIMARY KEY (`player_id`,`guild_id`),
147 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
148 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
149) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
150
151CREATE TABLE IF NOT EXISTS `guild_ranks` (
152 `id` int(11) NOT NULL AUTO_INCREMENT,
153 `guild_id` int(11) NOT NULL COMMENT 'guild',
154 `name` varchar(255) NOT NULL COMMENT 'rank name',
155 `level` int(11) NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
156 PRIMARY KEY (`id`),
157 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
158) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
159
160CREATE TABLE IF NOT EXISTS `guild_membership` (
161 `player_id` int(11) NOT NULL,
162 `guild_id` int(11) NOT NULL,
163 `rank_id` int(11) NOT NULL,
164 `nick` varchar(15) NOT NULL DEFAULT '',
165 PRIMARY KEY (`player_id`),
166 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
167 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
168 FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
169) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
170
171CREATE TABLE IF NOT EXISTS `guild_wars` (
172 `id` int(11) NOT NULL AUTO_INCREMENT,
173 `guild1` int(11) NOT NULL DEFAULT '0',
174 `guild2` int(11) NOT NULL DEFAULT '0',
175 `name1` varchar(255) NOT NULL,
176 `name2` varchar(255) NOT NULL,
177 `status` tinyint(2) NOT NULL DEFAULT '0',
178 `started` bigint(15) NOT NULL DEFAULT '0',
179 `ended` bigint(15) NOT NULL DEFAULT '0',
180 PRIMARY KEY (`id`),
181 KEY `guild1` (`guild1`),
182 KEY `guild2` (`guild2`)
183) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
184
185CREATE TABLE IF NOT EXISTS `guildwar_kills` (
186 `id` int(11) NOT NULL AUTO_INCREMENT,
187 `killer` varchar(50) NOT NULL,
188 `target` varchar(50) NOT NULL,
189 `killerguild` int(11) NOT NULL DEFAULT '0',
190 `targetguild` int(11) NOT NULL DEFAULT '0',
191 `warid` int(11) NOT NULL DEFAULT '0',
192 `time` bigint(15) NOT NULL,
193 PRIMARY KEY (`id`),
194 FOREIGN KEY (`warid`) REFERENCES `guild_wars` (`id`) ON DELETE CASCADE
195) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
196
197CREATE TABLE IF NOT EXISTS `houses` (
198 `id` int(11) NOT NULL AUTO_INCREMENT,
199 `owner` int(11) NOT NULL,
200 `paid` int(10) unsigned NOT NULL DEFAULT '0',
201 `warnings` int(11) NOT NULL DEFAULT '0',
202 `name` varchar(255) NOT NULL,
203 `rent` int(11) NOT NULL DEFAULT '0',
204 `town_id` int(11) NOT NULL DEFAULT '0',
205 `bid` int(11) NOT NULL DEFAULT '0',
206 `bid_end` int(11) NOT NULL DEFAULT '0',
207 `last_bid` int(11) NOT NULL DEFAULT '0',
208 `highest_bidder` int(11) NOT NULL DEFAULT '0',
209 `size` int(11) NOT NULL DEFAULT '0',
210 `beds` int(11) NOT NULL DEFAULT '0',
211 PRIMARY KEY (`id`),
212 KEY `owner` (`owner`),
213 KEY `town_id` (`town_id`)
214) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
215
216CREATE TABLE IF NOT EXISTS `house_lists` (
217 `house_id` int(11) NOT NULL,
218 `listid` int(11) NOT NULL,
219 `list` text NOT NULL,
220 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
221) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
222
223CREATE TABLE IF NOT EXISTS `market_history` (
224 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
225 `player_id` int(11) NOT NULL,
226 `sale` tinyint(1) NOT NULL DEFAULT '0',
227 `itemtype` int(10) unsigned NOT NULL,
228 `amount` smallint(5) unsigned NOT NULL,
229 `price` int(10) unsigned NOT NULL DEFAULT '0',
230 `expires_at` bigint(20) unsigned NOT NULL,
231 `inserted` bigint(20) unsigned NOT NULL,
232 `state` tinyint(1) unsigned NOT NULL,
233 PRIMARY KEY (`id`),
234 KEY `player_id` (`player_id`, `sale`),
235 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
236) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
237
238CREATE TABLE IF NOT EXISTS `market_offers` (
239 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
240 `player_id` int(11) NOT NULL,
241 `sale` tinyint(1) NOT NULL DEFAULT '0',
242 `itemtype` int(10) unsigned NOT NULL,
243 `amount` smallint(5) unsigned NOT NULL,
244 `created` bigint(20) unsigned NOT NULL,
245 `anonymous` tinyint(1) NOT NULL DEFAULT '0',
246 `price` int(10) unsigned NOT NULL DEFAULT '0',
247 PRIMARY KEY (`id`),
248 KEY `sale` (`sale`,`itemtype`),
249 KEY `created` (`created`),
250 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
251) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
252
253CREATE TABLE IF NOT EXISTS `players_online` (
254 `player_id` int(11) NOT NULL,
255 PRIMARY KEY (`player_id`)
256) ENGINE=MEMORY DEFAULT CHARACTER SET=utf8;
257
258CREATE TABLE IF NOT EXISTS `player_deaths` (
259 `player_id` int(11) NOT NULL,
260 `time` bigint(20) unsigned NOT NULL DEFAULT '0',
261 `level` int(11) NOT NULL DEFAULT '1',
262 `killed_by` varchar(255) NOT NULL,
263 `is_player` tinyint(1) NOT NULL DEFAULT '1',
264 `mostdamage_by` varchar(100) NOT NULL,
265 `mostdamage_is_player` tinyint(1) NOT NULL DEFAULT '0',
266 `unjustified` tinyint(1) NOT NULL DEFAULT '0',
267 `mostdamage_unjustified` tinyint(1) NOT NULL DEFAULT '0',
268 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
269 KEY `killed_by` (`killed_by`),
270 KEY `mostdamage_by` (`mostdamage_by`)
271) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
272
273CREATE TABLE IF NOT EXISTS `player_depotitems` (
274 `player_id` int(11) NOT NULL,
275 `sid` int(11) 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',
276 `pid` int(11) NOT NULL DEFAULT '0',
277 `itemtype` smallint(6) NOT NULL,
278 `count` smallint(5) NOT NULL DEFAULT '0',
279 `attributes` blob NOT NULL,
280 UNIQUE KEY `player_id_2` (`player_id`, `sid`),
281 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
282) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
283
284CREATE TABLE IF NOT EXISTS `player_inboxitems` (
285 `player_id` int(11) NOT NULL,
286 `sid` int(11) NOT NULL,
287 `pid` int(11) NOT NULL DEFAULT '0',
288 `itemtype` smallint(6) NOT NULL,
289 `count` smallint(5) NOT NULL DEFAULT '0',
290 `attributes` blob NOT NULL,
291 UNIQUE KEY `player_id_2` (`player_id`, `sid`),
292 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
293) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
294
295CREATE TABLE IF NOT EXISTS `player_items` (
296 `player_id` int(11) NOT NULL DEFAULT '0',
297 `pid` int(11) NOT NULL DEFAULT '0',
298 `sid` int(11) NOT NULL DEFAULT '0',
299 `itemtype` smallint(6) NOT NULL DEFAULT '0',
300 `count` smallint(5) NOT NULL DEFAULT '0',
301 `attributes` blob NOT NULL,
302 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
303 KEY `sid` (`sid`)
304) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
305
306CREATE TABLE IF NOT EXISTS `player_spells` (
307 `player_id` int(11) NOT NULL,
308 `name` varchar(255) NOT NULL,
309 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
310) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
311
312CREATE TABLE IF NOT EXISTS `player_storage` (
313 `player_id` int(11) NOT NULL DEFAULT '0',
314 `key` int(10) unsigned NOT NULL DEFAULT '0',
315 `value` int(11) NOT NULL DEFAULT '0',
316 PRIMARY KEY (`player_id`,`key`),
317 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
318) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
319
320CREATE TABLE IF NOT EXISTS `server_config` (
321 `config` varchar(50) NOT NULL,
322 `value` varchar(256) NOT NULL DEFAULT '',
323 PRIMARY KEY `config` (`config`)
324) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
325
326CREATE TABLE IF NOT EXISTS `tile_store` (
327 `house_id` int(11) NOT NULL,
328 `data` longblob NOT NULL,
329 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
330) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
331
332CREATE TABLE IF NOT EXISTS `towns` (
333 `id` int(11) NOT NULL AUTO_INCREMENT,
334 `name` varchar(255) NOT NULL,
335 `posx` int(11) NOT NULL DEFAULT '0',
336 `posy` int(11) NOT NULL DEFAULT '0',
337 `posz` int(11) NOT NULL DEFAULT '0',
338 PRIMARY KEY (`id`),
339 UNIQUE KEY `name` (`name`)
340) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
341
342INSERT INTO `server_config` (`config`, `value`) VALUES ('db_version', '24'), ('motd_hash', ''), ('motd_num', '0'), ('players_record', '0');
343
344DROP TRIGGER IF EXISTS `ondelete_players`;
345DROP TRIGGER IF EXISTS `oncreate_guilds`;
346
347DELIMITER //
348CREATE TRIGGER `ondelete_players` BEFORE DELETE ON `players`
349 FOR EACH ROW BEGIN
350 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
351END
352//
353CREATE TRIGGER `oncreate_guilds` AFTER INSERT ON `guilds`
354 FOR EACH ROW BEGIN
355 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('the Leader', 3, NEW.`id`);
356 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Vice-Leader', 2, NEW.`id`);
357 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Member', 1, NEW.`id`);
358END
359//
360DELIMITER ;