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