· 5 years ago · Apr 10, 2020, 06:24 PM
1--
2-- OTServBR - Global - Database schema
3--
4
5-- --------------------------------------------------------
6
7--
8-- Table structure `server_config`
9--
10
11CREATE TABLE IF NOT EXISTS `server_config` (
12 `config` varchar(50) NOT NULL,
13 `value` varchar(256) NOT NULL DEFAULT '',
14 CONSTRAINT `server_config_pk` PRIMARY KEY (`config`)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16
17INSERT INTO `server_config` (`config`, `value`) VALUES ('db_version', '0'), ('motd_hash', ''), ('motd_num', '0'), ('players_record', '0');
18
19-- --------------------------------------------------------
20
21--
22-- Table structure `accounts`
23--
24
25CREATE TABLE IF NOT EXISTS `accounts` (
26 `id` int(11) NOT NULL AUTO_INCREMENT,
27 `name` varchar(32) NOT NULL,
28 `password` char(40) NOT NULL,
29 `secret` char(16) DEFAULT NULL,
30 `type` int(11) NOT NULL DEFAULT '1',
31 `premdays` int(11) NOT NULL DEFAULT '0',
32 `lastday` int(10) UNSIGNED NOT NULL DEFAULT '0',
33 `email` varchar(255) NOT NULL DEFAULT '',
34 `creation` int(11) NOT NULL DEFAULT '0',
35 `premium_points` int(11) NOT NULL DEFAULT '0',
36 `passed` int(11) NOT NULL DEFAULT '0',
37 `block` int(11) NOT NULL DEFAULT '0',
38 `refresh` int(11) NOT NULL DEFAULT '0',
39 CONSTRAINT `accounts_pk` PRIMARY KEY (`id`),
40 CONSTRAINT `accounts_unique` UNIQUE (`name`)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42
43-- --------------------------------------------------------
44
45--
46-- Table structure `players`
47--
48
49CREATE TABLE IF NOT EXISTS `players` (
50 `id` int(11) NOT NULL AUTO_INCREMENT,
51 `name` varchar(255) NOT NULL,
52 `group_id` int(11) NOT NULL DEFAULT '1',
53 `account_id` int(11) NOT NULL DEFAULT '0',
54 `level` int(11) NOT NULL DEFAULT '1',
55 `vocation` int(11) NOT NULL DEFAULT '0',
56 `health` int(11) NOT NULL DEFAULT '150',
57 `healthmax` int(11) NOT NULL DEFAULT '150',
58 `experience` bigint(20) NOT NULL DEFAULT '0',
59 `lookbody` int(11) NOT NULL DEFAULT '0',
60 `lookfeet` int(11) NOT NULL DEFAULT '0',
61 `lookhead` int(11) NOT NULL DEFAULT '0',
62 `looklegs` int(11) NOT NULL DEFAULT '0',
63 `looktype` int(11) NOT NULL DEFAULT '136',
64 `lookaddons` int(11) NOT NULL DEFAULT '0',
65 `maglevel` int(11) NOT NULL DEFAULT '0',
66 `mana` int(11) NOT NULL DEFAULT '0',
67 `manamax` int(11) NOT NULL DEFAULT '0',
68 `manaspent` int(11) UNSIGNED NOT NULL DEFAULT '0',
69 `soul` int(10) UNSIGNED NOT NULL DEFAULT '0',
70 `town_id` int(11) NOT NULL DEFAULT '1',
71 `posx` int(11) NOT NULL DEFAULT '0',
72 `posy` int(11) NOT NULL DEFAULT '0',
73 `posz` int(11) NOT NULL DEFAULT '0',
74 `conditions` blob NOT NULL,
75 `cap` int(11) NOT NULL DEFAULT '0',
76 `sex` int(11) NOT NULL DEFAULT '0',
77 `lastlogin` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
78 `lastip` int(10) UNSIGNED NOT NULL DEFAULT '0',
79 `save` tinyint(1) NOT NULL DEFAULT '1',
80 `skull` tinyint(1) NOT NULL DEFAULT '0',
81 `skulltime` bigint(20) NOT NULL DEFAULT '0',
82 `lastlogout` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
83 `blessings` tinyint(2) NOT NULL DEFAULT '0',
84 `blessings1` tinyint(4) NOT NULL DEFAULT '0',
85 `blessings2` tinyint(4) NOT NULL DEFAULT '0',
86 `blessings3` tinyint(4) NOT NULL DEFAULT '0',
87 `blessings4` tinyint(4) NOT NULL DEFAULT '0',
88 `blessings5` tinyint(4) NOT NULL DEFAULT '0',
89 `blessings6` tinyint(4) NOT NULL DEFAULT '0',
90 `blessings7` tinyint(4) NOT NULL DEFAULT '0',
91 `blessings8` tinyint(4) NOT NULL DEFAULT '0',
92 `onlinetime` int(11) NOT NULL DEFAULT '0',
93 `deletion` bigint(15) NOT NULL DEFAULT '0',
94 `balance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
95 `offlinetraining_time` smallint(5) UNSIGNED NOT NULL DEFAULT '43200',
96 `offlinetraining_skill` int(11) NOT NULL DEFAULT '-1',
97 `stamina` smallint(5) UNSIGNED NOT NULL DEFAULT '2520',
98 `skill_fist` int(10) UNSIGNED NOT NULL DEFAULT '10',
99 `skill_fist_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
100 `skill_club` int(10) UNSIGNED NOT NULL DEFAULT '10',
101 `skill_club_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
102 `skill_sword` int(10) UNSIGNED NOT NULL DEFAULT '10',
103 `skill_sword_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
104 `skill_axe` int(10) UNSIGNED NOT NULL DEFAULT '10',
105 `skill_axe_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
106 `skill_dist` int(10) UNSIGNED NOT NULL DEFAULT '10',
107 `skill_dist_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
108 `skill_shielding` int(10) UNSIGNED NOT NULL DEFAULT '10',
109 `skill_shielding_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
110 `skill_fishing` int(10) UNSIGNED NOT NULL DEFAULT '10',
111 `skill_fishing_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
112 `skill_critical_hit_chance` int(10) UNSIGNED NOT NULL DEFAULT '0',
113 `skill_critical_hit_chance_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
114 `skill_critical_hit_damage` int(10) UNSIGNED NOT NULL DEFAULT '0',
115 `skill_critical_hit_damage_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
116 `skill_life_leech_chance` int(10) UNSIGNED NOT NULL DEFAULT '0',
117 `skill_life_leech_chance_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
118 `skill_life_leech_amount` int(10) UNSIGNED NOT NULL DEFAULT '0',
119 `skill_life_leech_amount_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
120 `skill_mana_leech_chance` int(10) UNSIGNED NOT NULL DEFAULT '0',
121 `skill_mana_leech_chance_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
122 `skill_mana_leech_amount` int(10) UNSIGNED NOT NULL DEFAULT '0',
123 `skill_mana_leech_amount_tries` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
124 `skill_criticalhit_chance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
125 `skill_criticalhit_damage` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
126 `skill_lifeleech_chance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
127 `skill_lifeleech_amount` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
128 `skill_manaleech_chance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
129 `skill_manaleech_amount` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
130 `prey_stamina_1` int(11) DEFAULT NULL,
131 `prey_stamina_2` int(11) DEFAULT NULL,
132 `prey_stamina_3` int(11) DEFAULT NULL,
133 `prey_column` smallint(6) NOT NULL DEFAULT '1',
134 `xpboost_stamina` smallint(5) DEFAULT NULL,
135 `xpboost_value` tinyint(4) DEFAULT NULL,
136 `bonus_rerolls` bigint(21) NOT NULL DEFAULT '0',
137 INDEX `account_id` (`account_id`),
138 INDEX `vocation` (`vocation`),
139 CONSTRAINT `players_pk` PRIMARY KEY (`id`),
140 CONSTRAINT `players_unique` UNIQUE (`name`),
141 CONSTRAINT `players_account_fk`
142 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
143 ON DELETE CASCADE
144) ENGINE=InnoDB DEFAULT CHARSET=utf8;
145
146-- --------------------------------------------------------
147--
148-- Table structure `account_bans`
149--
150
151CREATE TABLE IF NOT EXISTS `account_bans` (
152 `account_id` int(11) NOT NULL,
153 `reason` varchar(255) NOT NULL,
154 `banned_at` bigint(20) NOT NULL,
155 `expires_at` bigint(20) NOT NULL,
156 `banned_by` int(11) NOT NULL,
157 INDEX `banned_by` (`banned_by`),
158 CONSTRAINT `account_bans_pk` PRIMARY KEY (`account_id`),
159 CONSTRAINT `account_bans_account_fk`
160 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
161 ON DELETE CASCADE
162 ON UPDATE CASCADE,
163 CONSTRAINT `account_bans_player_fk`
164 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
165 ON DELETE CASCADE
166 ON UPDATE CASCADE
167) ENGINE=InnoDB DEFAULT CHARSET=utf8;
168
169-- --------------------------------------------------------
170
171--
172-- Table structure `account_ban_history`
173--
174
175CREATE TABLE IF NOT EXISTS `account_ban_history` (
176 `id` int(11) NOT NULL AUTO_INCREMENT,
177 `account_id` int(11) NOT NULL,
178 `reason` varchar(255) NOT NULL,
179 `banned_at` bigint(20) NOT NULL,
180 `expired_at` bigint(20) NOT NULL,
181 `banned_by` int(11) NOT NULL,
182 INDEX `account_id` (`account_id`),
183 INDEX `banned_by` (`banned_by`),
184 CONSTRAINT `account_bans_history_account_fk`
185 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
186 ON DELETE CASCADE
187 ON UPDATE CASCADE,
188 CONSTRAINT `account_bans_history_player_fk`
189 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
190 ON DELETE CASCADE
191 ON UPDATE CASCADE,
192 CONSTRAINT `account_ban_history_pk` PRIMARY KEY (`id`)
193) ENGINE=InnoDB DEFAULT CHARSET=utf8;
194
195-- --------------------------------------------------------
196
197--
198-- Table structure `account_viplist`
199--
200
201CREATE TABLE IF NOT EXISTS `account_viplist` (
202 `account_id` int(11) NOT NULL COMMENT 'id of account whose viplist entry it is',
203 `player_id` int(11) NOT NULL COMMENT 'id of target player of viplist entry',
204 `description` varchar(128) NOT NULL DEFAULT '',
205 `icon` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
206 `notify` tinyint(1) NOT NULL DEFAULT '0',
207 INDEX `account_id` (`account_id`),
208 INDEX `player_id` (`player_id`),
209 CONSTRAINT `account_viplist_unique` UNIQUE (`account_id`, `player_id`),
210 CONSTRAINT `account_viplist_account_fk`
211 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
212 ON DELETE CASCADE,
213 CONSTRAINT `account_viplist_player_fk`
214 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
215 ON DELETE CASCADE
216) ENGINE=InnoDB DEFAULT CHARSET=utf8;
217
218-- --------------------------------------------------------
219
220--
221-- Tabble Structure `daily_reward_history`
222--
223
224CREATE TABLE IF NOT EXISTS `daily_reward_history` (
225 `id` int(11) NOT NULL AUTO_INCREMENT,
226 `daystreak` smallint(2) NOT NULL DEFAULT 0,
227 `player_id` int(11) NOT NULL,
228 `timestamp` int(11) NOT NULL,
229 `description` varchar(255) DEFAULT NULL,
230 INDEX `player_id` (`player_id`),
231 CONSTRAINT `daily_reward_history_pk` PRIMARY KEY (`id`),
232 CONSTRAINT `daily_reward_history_player_fk`
233 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
234 ON DELETE CASCADE
235) ENGINE=InnoDB DEFAULT CHARSET=utf8;
236
237
238-- --------------------------------------------------------
239
240--
241-- Table structure `global_storage`
242--
243
244CREATE TABLE IF NOT EXISTS `global_storage` (
245 `key` varchar(32) NOT NULL,
246 `value` text NOT NULL,
247 CONSTRAINT `global_storage_unique` UNIQUE (`key`)
248) ENGINE=InnoDB DEFAULT CHARSET=utf8;
249
250-- --------------------------------------------------------
251
252--
253-- Table structure `guilds`
254--
255
256CREATE TABLE IF NOT EXISTS `guilds` (
257 `id` int(11) NOT NULL AUTO_INCREMENT,
258 `name` varchar(255) NOT NULL,
259 `ownerid` int(11) NOT NULL,
260 `creationdata` int(11) NOT NULL,
261 `motd` varchar(255) NOT NULL DEFAULT '',
262 `residence` int(11) NOT NULL DEFAULT '0',
263 `balance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
264 CONSTRAINT `guilds_pk` PRIMARY KEY (`id`),
265 CONSTRAINT `guilds_name_unique` UNIQUE (`name`),
266 CONSTRAINT `guilds_owner_unique` UNIQUE (`ownerid`),
267 CONSTRAINT `guilds_ownerid_fk`
268 FOREIGN KEY (`ownerid`) REFERENCES `players` (`id`)
269 ON DELETE CASCADE
270) ENGINE=InnoDB DEFAULT CHARSET=utf8;
271
272-- --------------------------------------------------------
273
274--
275-- Table structure `guild_wars`
276--
277
278CREATE TABLE IF NOT EXISTS `guild_wars` (
279 `id` int(11) NOT NULL AUTO_INCREMENT,
280 `guild1` int(11) NOT NULL DEFAULT '0',
281 `guild2` int(11) NOT NULL DEFAULT '0',
282 `name1` varchar(255) NOT NULL,
283 `name2` varchar(255) NOT NULL,
284 `status` tinyint(2) NOT NULL DEFAULT '0',
285 `started` bigint(15) NOT NULL DEFAULT '0',
286 `ended` bigint(15) NOT NULL DEFAULT '0',
287 INDEX `guild1` (`guild1`),
288 INDEX `guild2` (`guild2`),
289 CONSTRAINT `guild_wars_pk` PRIMARY KEY (`id`)
290) ENGINE=InnoDB DEFAULT CHARSET=utf8;
291
292-- --------------------------------------------------------
293
294--
295-- Table structure `guildwar_kills`
296--
297
298CREATE TABLE IF NOT EXISTS `guildwar_kills` (
299 `id` int(11) NOT NULL AUTO_INCREMENT,
300 `killer` varchar(50) NOT NULL,
301 `target` varchar(50) NOT NULL,
302 `killerguild` int(11) NOT NULL DEFAULT '0',
303 `targetguild` int(11) NOT NULL DEFAULT '0',
304 `warid` int(11) NOT NULL DEFAULT '0',
305 `time` bigint(15) NOT NULL,
306 INDEX `warid` (`warid`),
307 CONSTRAINT `guildwar_kills_pk` PRIMARY KEY (`id`),
308 CONSTRAINT `guildwar_kills_unique` UNIQUE (`warid`),
309 CONSTRAINT `guildwar_kills_warid_fk`
310 FOREIGN KEY (`warid`) REFERENCES `guild_wars` (`id`)
311 ON DELETE CASCADE
312) ENGINE=InnoDB DEFAULT CHARSET=utf8;
313
314-- --------------------------------------------------------
315
316--
317-- Table structure `guild_invites`
318--
319
320CREATE TABLE IF NOT EXISTS `guild_invites` (
321 `player_id` int(11) NOT NULL DEFAULT '0',
322 `guild_id` int(11) NOT NULL DEFAULT '0',
323 INDEX `guild_id` (`guild_id`),
324 CONSTRAINT `guild_invites_pk` PRIMARY KEY (`player_id`, `guild_id`),
325 CONSTRAINT `guild_invites_player_fk`
326 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
327 ON DELETE CASCADE,
328 CONSTRAINT `guild_invites_guild_fk`
329 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
330 ON DELETE CASCADE
331) ENGINE=InnoDB DEFAULT CHARSET=utf8;
332
333-- --------------------------------------------------------
334
335--
336-- Table structure `guild_ranks`
337--
338
339CREATE TABLE IF NOT EXISTS `guild_ranks` (
340 `id` int(11) NOT NULL AUTO_INCREMENT,
341 `guild_id` int(11) NOT NULL COMMENT 'guild',
342 `name` varchar(255) NOT NULL COMMENT 'rank name',
343 `level` int(11) NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
344 INDEX `guild_id` (`guild_id`),
345 CONSTRAINT `guild_ranks_pk` PRIMARY KEY (`id`),
346 CONSTRAINT `guild_ranks_fk`
347 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
348 ON DELETE CASCADE
349) ENGINE=InnoDB DEFAULT CHARSET=utf8;
350
351--
352-- Trigger
353--
354DELIMITER //
355CREATE TRIGGER `oncreate_guilds` AFTER INSERT ON `guilds` FOR EACH ROW BEGIN
356 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('The Leader', 3, NEW.`id`);
357 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
358 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
359END
360//
361DELIMITER ;
362
363-- --------------------------------------------------------
364
365--
366-- Table structure `guild_membership`
367--
368
369CREATE TABLE IF NOT EXISTS `guild_membership` (
370 `player_id` int(11) NOT NULL,
371 `guild_id` int(11) NOT NULL,
372 `rank_id` int(11) NOT NULL,
373 `nick` varchar(15) NOT NULL DEFAULT '',
374 INDEX `guild_id` (`guild_id`),
375 INDEX `rank_id` (`rank_id`),
376 CONSTRAINT `guild_membership_pk` PRIMARY KEY (`player_id`),
377 CONSTRAINT `guild_membership_player_fk`
378 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
379 ON DELETE CASCADE
380 ON UPDATE CASCADE,
381 CONSTRAINT `guild_membership_guild_fk`
382 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
383 ON DELETE CASCADE
384 ON UPDATE CASCADE,
385 CONSTRAINT `guild_membership_rank_fk`
386 FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`)
387 ON DELETE CASCADE
388 ON UPDATE CASCADE
389) ENGINE=InnoDB DEFAULT CHARSET=utf8;
390
391-- --------------------------------------------------------
392
393--
394-- Table structure `houses`
395--
396
397CREATE TABLE IF NOT EXISTS `houses` (
398 `id` int(11) NOT NULL AUTO_INCREMENT,
399 `owner` int(11) NOT NULL,
400 `paid` int(10) UNSIGNED NOT NULL DEFAULT '0',
401 `warnings` int(11) NOT NULL DEFAULT '0',
402 `name` varchar(255) NOT NULL,
403 `rent` int(11) NOT NULL DEFAULT '0',
404 `town_id` int(11) NOT NULL DEFAULT '0',
405 `bid` int(11) NOT NULL DEFAULT '0',
406 `bid_end` int(11) NOT NULL DEFAULT '0',
407 `last_bid` int(11) NOT NULL DEFAULT '0',
408 `highest_bidder` int(11) NOT NULL DEFAULT '0',
409 `size` int(11) NOT NULL DEFAULT '0',
410 `guildid` int(11),
411 `beds` int(11) NOT NULL DEFAULT '0',
412 INDEX `owner` (`owner`),
413 INDEX `town_id` (`town_id`),
414 CONSTRAINT `houses_pk` PRIMARY KEY (`id`)
415) ENGINE=InnoDB DEFAULT CHARSET=utf8;
416
417--
418-- trigger
419--
420DELIMITER //
421CREATE TRIGGER `ondelete_players` BEFORE DELETE ON `players`
422 FOR EACH ROW BEGIN
423 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
424END
425//
426DELIMITER ;
427
428-- --------------------------------------------------------
429
430--
431-- Table structure `house_lists`
432--
433
434CREATE TABLE IF NOT EXISTS `house_lists` (
435 `house_id` int(11) NOT NULL,
436 `listid` int(11) NOT NULL,
437 `list` text NOT NULL,
438 INDEX `house_id` (`house_id`),
439 CONSTRAINT `houses_list_house_fk`
440 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`)
441 ON DELETE CASCADE
442) ENGINE=InnoDB DEFAULT CHARSET=utf8;
443
444-- --------------------------------------------------------
445
446--
447-- Table structure `ip_bans`
448--
449
450CREATE TABLE IF NOT EXISTS `ip_bans` (
451 `ip` int(11) NOT NULL,
452 `reason` varchar(255) NOT NULL,
453 `banned_at` bigint(20) NOT NULL,
454 `expires_at` bigint(20) NOT NULL,
455 `banned_by` int(11) NOT NULL,
456 INDEX `banned_by` (`banned_by`),
457 CONSTRAINT `ip_bans_pk` PRIMARY KEY (`ip`),
458 CONSTRAINT `ip_bans_players_fk`
459 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
460 ON DELETE CASCADE
461 ON UPDATE CASCADE
462) ENGINE=InnoDB DEFAULT CHARSET=utf8;
463
464-- --------------------------------------------------------
465
466--
467-- Table structure `market_history`
468--
469
470CREATE TABLE IF NOT EXISTS `market_history` (
471 `id` int(11) NOT NULL AUTO_INCREMENT,
472 `player_id` int(11) NOT NULL,
473 `sale` tinyint(1) NOT NULL DEFAULT '0',
474 `itemtype` int(10) UNSIGNED NOT NULL,
475 `amount` smallint(5) UNSIGNED NOT NULL,
476 `price` int(10) UNSIGNED NOT NULL DEFAULT '0',
477 `expires_at` bigint(20) UNSIGNED NOT NULL,
478 `inserted` bigint(20) UNSIGNED NOT NULL,
479 `state` tinyint(1) UNSIGNED NOT NULL,
480 INDEX `player_id` (`player_id`,`sale`),
481 CONSTRAINT `market_history_pk` PRIMARY KEY (`id`),
482 CONSTRAINT `market_history_players_fk`
483 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
484 ON DELETE CASCADE
485) ENGINE=InnoDB DEFAULT CHARSET=utf8;
486
487-- --------------------------------------------------------
488
489--
490-- Table structure `market_offers`
491--
492
493CREATE TABLE IF NOT EXISTS `market_offers` (
494 `id` int(11) NOT NULL AUTO_INCREMENT,
495 `player_id` int(11) NOT NULL,
496 `sale` tinyint(1) NOT NULL DEFAULT '0',
497 `itemtype` int(10) UNSIGNED NOT NULL,
498 `amount` smallint(5) UNSIGNED NOT NULL,
499 `created` bigint(20) UNSIGNED NOT NULL,
500 `anonymous` tinyint(1) NOT NULL DEFAULT '0',
501 `price` int(10) UNSIGNED NOT NULL DEFAULT '0',
502 INDEX `sale` (`sale`,`itemtype`),
503 INDEX `created` (`created`),
504 INDEX `player_id` (`player_id`),
505 CONSTRAINT `market_offers_pk` PRIMARY KEY (`id`),
506 CONSTRAINT `market_offers_players_fk`
507 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
508 ON DELETE CASCADE
509) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510
511
512-- --------------------------------------------------------
513
514--
515-- Table structure `players_online`
516--
517
518CREATE TABLE IF NOT EXISTS `players_online` (
519 `player_id` int(11) NOT NULL,
520 CONSTRAINT `players_online_pk` PRIMARY KEY (`player_id`)
521) ENGINE=MEMORY DEFAULT CHARSET=utf8;
522
523-- --------------------------------------------------------
524
525--
526-- Table structure `player_deaths`
527--
528
529CREATE TABLE IF NOT EXISTS `player_deaths` (
530 `player_id` int(11) NOT NULL,
531 `time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
532 `level` int(11) NOT NULL DEFAULT '1',
533 `killed_by` varchar(255) NOT NULL,
534 `is_player` tinyint(1) NOT NULL DEFAULT '1',
535 `mostdamage_by` varchar(100) NOT NULL,
536 `mostdamage_is_player` tinyint(1) NOT NULL DEFAULT '0',
537 `unjustified` tinyint(1) NOT NULL DEFAULT '0',
538 `mostdamage_unjustified` tinyint(1) NOT NULL DEFAULT '0',
539 INDEX `player_id` (`player_id`),
540 INDEX `killed_by` (`killed_by`),
541 INDEX `mostdamage_by` (`mostdamage_by`),
542 CONSTRAINT `player_deaths_players_fk`
543 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
544 ON DELETE CASCADE
545) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546
547-- --------------------------------------------------------
548
549--
550-- Table structure `player_depotitems`
551--
552
553CREATE TABLE IF NOT EXISTS `player_depotitems` (
554 `player_id` int(11) NOT NULL,
555 `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',
556 `pid` int(11) NOT NULL DEFAULT '0',
557 `itemtype` int(11) NOT NULL DEFAULT '0',
558 `count` int(11) NOT NULL DEFAULT '0',
559 `attributes` blob NOT NULL,
560 CONSTRAINT `player_depotitems_unique` UNIQUE (`player_id`, `sid`),
561 CONSTRAINT `player_depotitems_players_fk`
562 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
563 ON DELETE CASCADE
564) ENGINE=InnoDB DEFAULT CHARSET=utf8;
565
566-- --------------------------------------------------------
567
568--
569-- Table structure `player_inboxitems`
570--
571
572CREATE TABLE IF NOT EXISTS `player_inboxitems` (
573 `player_id` int(11) NOT NULL,
574 `sid` int(11) NOT NULL,
575 `pid` int(11) NOT NULL DEFAULT '0',
576 `itemtype` int(11) NOT NULL DEFAULT '0',
577 `count` int(11) NOT NULL DEFAULT '0',
578 `attributes` blob NOT NULL,
579 CONSTRAINT `player_inboxitems_unique` UNIQUE (`player_id`, `sid`),
580 CONSTRAINT `player_inboxitems_players_fk`
581 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
582 ON DELETE CASCADE
583) ENGINE=InnoDB DEFAULT CHARSET=utf8;
584
585-- --------------------------------------------------------
586
587--
588-- Table structure `player_items`
589--
590
591CREATE TABLE IF NOT EXISTS `player_items` (
592 `player_id` int(11) NOT NULL DEFAULT '0',
593 `pid` int(11) NOT NULL DEFAULT '0',
594 `sid` int(11) NOT NULL DEFAULT '0',
595 `itemtype` int(11) NOT NULL DEFAULT '0',
596 `count` int(11) NOT NULL DEFAULT '0',
597 `attributes` blob NOT NULL,
598 INDEX `player_id` (`player_id`),
599 INDEX `sid` (`sid`),
600 CONSTRAINT `player_items_players_fk`
601 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
602 ON DELETE CASCADE
603) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604
605-- --------------------------------------------------------
606
607--
608-- Table structure `player_kills`
609--
610
611CREATE TABLE IF NOT EXISTS `player_kills` (
612 `player_id` int(11) NOT NULL,
613 `time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
614 `target` int(11) NOT NULL,
615 `unavenged` tinyint(1) NOT NULL DEFAULT '0'
616) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617
618-- --------------------------------------------------------
619
620--
621-- Table structure `player_misc`
622--
623
624CREATE TABLE IF NOT EXISTS `player_misc` (
625 `player_id` int(11) NOT NULL,
626 `info` blob NOT NULL
627) ENGINE=InnoDB DEFAULT CHARSET=utf8;
628
629-- --------------------------------------------------------
630
631--
632-- Table structure `player_namelocks`
633--
634
635CREATE TABLE IF NOT EXISTS `player_namelocks` (
636 `player_id` int(11) NOT NULL,
637 `reason` varchar(255) NOT NULL,
638 `namelocked_at` bigint(20) NOT NULL,
639 `namelocked_by` int(11) NOT NULL,
640 INDEX `namelocked_by` (`namelocked_by`),
641 CONSTRAINT `player_namelocks_unique` UNIQUE (`player_id`),
642 CONSTRAINT `player_namelocks_players_fk`
643 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
644 ON DELETE CASCADE
645 ON UPDATE CASCADE,
646 CONSTRAINT `player_namelocks_players2_fk`
647 FOREIGN KEY (`namelocked_by`) REFERENCES `players` (`id`)
648 ON DELETE CASCADE
649 ON UPDATE CASCADE
650) ENGINE=InnoDB DEFAULT CHARSET=utf8;
651
652-- --------------------------------------------------------
653
654--
655-- Table structure `player_prey`
656--
657
658CREATE TABLE IF NOT EXISTS `player_prey` (
659 `player_id` int(11) NOT NULL,
660 `name` varchar(50) NOT NULL,
661 `mindex` smallint(6) NOT NULL,
662 `mcolumn` int(11) NOT NULL
663) ENGINE=InnoDB DEFAULT CHARSET=utf8;
664
665-- --------------------------------------------------------
666
667--
668-- Table structure `player_preytimes`
669--
670
671CREATE TABLE IF NOT EXISTS `player_preytimes` (
672 `player_id` int(11) NOT NULL,
673 `bonus_type1` int(11) NOT NULL,
674 `bonus_value1` int(11) NOT NULL,
675 `bonus_name1` varchar(50) NOT NULL,
676 `bonus_type2` int(11) NOT NULL,
677 `bonus_value2` int(11) NOT NULL,
678 `bonus_name2` varchar(50) NOT NULL,
679 `bonus_type3` int(11) NOT NULL,
680 `bonus_value3` int(11) NOT NULL,
681 `bonus_name3` varchar(50) NOT NULL
682) ENGINE=InnoDB DEFAULT CHARSET=utf8;
683
684-- --------------------------------------------------------
685
686--
687-- Table structure `player_rewards`
688--
689
690CREATE TABLE IF NOT EXISTS `player_rewards` (
691 `player_id` int(11) NOT NULL,
692 `sid` int(11) NOT NULL,
693 `pid` int(11) NOT NULL DEFAULT '0',
694 `itemtype` int(11) NOT NULL DEFAULT '0',
695 `count` int(11) NOT NULL DEFAULT '0',
696 `attributes` blob NOT NULL,
697 CONSTRAINT `player_rewards_unique` UNIQUE (`player_id`, `sid`),
698 CONSTRAINT `player_rewards_players_fk`
699 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
700 ON DELETE CASCADE
701) ENGINE=InnoDB DEFAULT CHARSET=utf8;
702
703-- --------------------------------------------------------
704
705--
706-- Table structure `player_spells`
707--
708
709CREATE TABLE IF NOT EXISTS `player_spells` (
710 `player_id` int(11) NOT NULL,
711 `name` varchar(255) NOT NULL,
712 INDEX `player_id` (`player_id`),
713 CONSTRAINT `player_spells_players_fk`
714 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
715 ON DELETE CASCADE
716) ENGINE=InnoDB DEFAULT CHARSET=utf8;
717
718-- --------------------------------------------------------
719
720--
721-- Table structure `player_storage`
722--
723
724CREATE TABLE IF NOT EXISTS `player_storage` (
725 `player_id` int(11) NOT NULL DEFAULT '0',
726 `key` int(10) UNSIGNED NOT NULL DEFAULT '0',
727 `value` int(11) NOT NULL DEFAULT '0',
728 CONSTRAINT `player_storage_pk` PRIMARY KEY (`player_id`, `key`),
729 CONSTRAINT `player_storage_players_fk`
730 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
731 ON DELETE CASCADE
732) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733
734-- --------------------------------------------------------
735
736--
737-- Table structure `store_history`
738--
739
740CREATE TABLE IF NOT EXISTS `store_history` (
741 `id` int(11) NOT NULL AUTO_INCREMENT,
742 `account_id` int(11) NOT NULL,
743 `mode` smallint(2) NOT NULL DEFAULT '0',
744 `description` varchar(3500) NOT NULL,
745 `coin_amount` int(12) NOT NULL,
746 `time` bigint(20) UNSIGNED NOT NULL,
747 `timestamp` int(11) NOT NULL DEFAULT '0',
748 `coins` int(11) NOT NULL DEFAULT '0',
749 INDEX `account_id` (`account_id`),
750 CONSTRAINT `store_history_pk` PRIMARY KEY (`id`),
751 CONSTRAINT `store_history_account_fk`
752 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
753 ON DELETE CASCADE
754) ENGINE=InnoDB DEFAULT CHARSET=utf8;
755
756-- --------------------------------------------------------
757
758--
759-- Table structure `tile_store`
760--
761
762CREATE TABLE IF NOT EXISTS `tile_store` (
763 `house_id` int(11) NOT NULL,
764 `data` longblob NOT NULL,
765 INDEX `house_id` (`house_id`),
766 CONSTRAINT `tile_store_account_fk`
767 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`)
768 ON DELETE CASCADE
769) ENGINE=InnoDB DEFAULT CHARSET=utf8;
770
771-- --------------------------------------------------------
772
773--
774-- Table structure `prey_slots`
775--
776
777CREATE TABLE IF NOT EXISTS `prey_slots` (
778 `player_id` int(11) NOT NULL,
779 `num` smallint(2) NOT NULL,
780 `state` smallint(2) NOT NULL DEFAULT '1',
781 `unlocked` tinyint(1) NOT NULL DEFAULT '0',
782 `current` varchar(40) NOT NULL DEFAULT '',
783 `monster_list` varchar(360) NOT NULL,
784 `free_reroll_in` int(11) NOT NULL DEFAULT '0',
785 `time_left` smallint(5) NOT NULL DEFAULT '0',
786 `next_use` int(11) NOT NULL DEFAULT '0',
787 `bonus_type` smallint(3) NOT NULL,
788 `bonus_value` smallint(3) NOT NULL DEFAULT '0',
789 `bonus_grade` smallint(3) NOT NULL DEFAULT '0',
790 INDEX `player_id` (`player_id`),
791 CONSTRAINT `prey_slots_players_fk`
792 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
793 ON DELETE CASCADE
794) ENGINE=InnoDB DEFAULT CHARSET=utf8;
795
796
797-- --------------------------------------------------------
798
799--
800-- Table structure `player_charms`
801--
802
803CREATE TABLE `player_charms` (
804 `id` int(11) NOT NULL,
805 `player_id` int(11) NOT NULL,
806 `charm` VARCHAR(20),
807 `monster` VARCHAR(20)
808) ENGINE=InnoDB DEFAULT CHARSET=latin1;