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