canary.sql

main 889 lines · 37.7 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- Canary database schema.
2-- Source: https://github.com/opentibiabr/canary/blob/main/schema.sql
3-- License: GPL-2.0. Covers ZnoteX's CANARY engine choice ("Canary / OTServBR-Global").
4
5-- Canary - Database (Schema)
6
7-- Table structure `server_config`
8CREATE TABLE IF NOT EXISTS `server_config` (
9 `config` varchar(50) NOT NULL,
10 `value` varchar(256) NOT NULL DEFAULT '',
11 CONSTRAINT `server_config_pk` PRIMARY KEY (`config`)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13
14INSERT INTO `server_config` (`config`, `value`) VALUES ('db_version', '59'), ('motd_hash', ''), ('motd_num', '0'), ('players_record', '0');
15
16-- Table structure `accounts`
17CREATE TABLE IF NOT EXISTS `accounts` (
18 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
19 `name` varchar(32) NOT NULL,
20 `password` VARCHAR(255) NOT NULL,
21 `email` varchar(255) NOT NULL DEFAULT '',
22 `premdays` int(11) NOT NULL DEFAULT '0',
23 `premdays_purchased` int(11) NOT NULL DEFAULT '0',
24 `lastday` int(10) UNSIGNED NOT NULL DEFAULT '0',
25 `type` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
26 `coins` int(12) UNSIGNED NOT NULL DEFAULT '0',
27 `coins_transferable` int(12) UNSIGNED NOT NULL DEFAULT '0',
28 `tournament_coins` int(12) UNSIGNED NOT NULL DEFAULT '0',
29 `creation` int(11) UNSIGNED NOT NULL DEFAULT '0',
30 `recruiter` INT(6) DEFAULT 0,
31 `house_bid_id` int(11) NOT NULL DEFAULT '0',
32 CONSTRAINT `accounts_pk` PRIMARY KEY (`id`),
33 CONSTRAINT `accounts_unique` UNIQUE (`name`),
34 INDEX `accounts_email` (`email`),
35 INDEX `accounts_password` (`password`)
36) ENGINE=InnoDB DEFAULT CHARSET=utf8;
37
38-- Table structure `coins_transactions`
39CREATE TABLE IF NOT EXISTS `coins_transactions` (
40 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
41 `account_id` int(11) UNSIGNED NOT NULL,
42 `type` tinyint(1) UNSIGNED NOT NULL,
43 `coin_type` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
44 `amount` int(12) UNSIGNED NOT NULL,
45 `description` varchar(3500) NOT NULL,
46 `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
47 INDEX `account_id` (`account_id`),
48 CONSTRAINT `coins_transactions_pk` PRIMARY KEY (`id`),
49 CONSTRAINT `coins_transactions_account_fk`
50 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
51 ON DELETE CASCADE
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53
54-- Table structure `players`
55CREATE TABLE IF NOT EXISTS `players` (
56 `id` int(11) NOT NULL AUTO_INCREMENT,
57 `name` varchar(255) NOT NULL,
58 `group_id` int(11) NOT NULL DEFAULT '1',
59 `account_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
60 `level` int(11) NOT NULL DEFAULT '1',
61 `vocation` int(11) NOT NULL DEFAULT '0',
62 `health` int(11) NOT NULL DEFAULT '150',
63 `healthmax` int(11) NOT NULL DEFAULT '150',
64 `experience` bigint(20) NOT NULL DEFAULT '0',
65 `lookbody` int(11) NOT NULL DEFAULT '0',
66 `lookfeet` int(11) NOT NULL DEFAULT '0',
67 `lookhead` int(11) NOT NULL DEFAULT '0',
68 `looklegs` int(11) NOT NULL DEFAULT '0',
69 `looktype` int(11) NOT NULL DEFAULT '136',
70 `lookaddons` int(11) NOT NULL DEFAULT '0',
71 `maglevel` int(11) NOT NULL DEFAULT '0',
72 `mana` int(11) NOT NULL DEFAULT '0',
73 `manamax` int(11) NOT NULL DEFAULT '0',
74 `manaspent` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
75 `soul` int(10) UNSIGNED NOT NULL DEFAULT '0',
76 `town_id` int(11) NOT NULL DEFAULT '1',
77 `posx` int(11) NOT NULL DEFAULT '0',
78 `posy` int(11) NOT NULL DEFAULT '0',
79 `posz` int(11) NOT NULL DEFAULT '0',
80 `conditions` mediumblob NOT NULL,
81 `cap` int(11) NOT NULL DEFAULT '0',
82 `sex` int(11) NOT NULL DEFAULT '0',
83 `pronoun` int(11) NOT NULL DEFAULT '0',
84 `lastlogin` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
85 `lastip` int(10) UNSIGNED NOT NULL DEFAULT '0',
86 `save` tinyint(1) NOT NULL DEFAULT '1',
87 `skull` tinyint(1) NOT NULL DEFAULT '0',
88 `skulltime` bigint(20) NOT NULL DEFAULT '0',
89 `expert_pvp_mode` tinyint(1) UNSIGNED 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` tinyint(2) 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 `manashield` INT UNSIGNED NOT NULL DEFAULT '0',
139 `max_manashield` INT UNSIGNED NOT NULL DEFAULT '0',
140 `xpboost_stamina` smallint(5) UNSIGNED DEFAULT NULL,
141 `xpboost_value` tinyint(4) UNSIGNED DEFAULT NULL,
142 `marriage_status` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
143 `marriage_spouse` int(11) NOT NULL DEFAULT '-1',
144 `bonus_rerolls` bigint(21) NOT NULL DEFAULT '0',
145 `prey_wildcard` bigint(21) NOT NULL DEFAULT '0',
146 `task_points` bigint(21) NOT NULL DEFAULT '0',
147 `quickloot_fallback` tinyint(1) DEFAULT '0',
148 `lookmountbody` tinyint(3) unsigned NOT NULL DEFAULT '0',
149 `lookmountfeet` tinyint(3) unsigned NOT NULL DEFAULT '0',
150 `lookmounthead` tinyint(3) unsigned NOT NULL DEFAULT '0',
151 `lookmountlegs` tinyint(3) unsigned NOT NULL DEFAULT '0',
152 `lookfamiliarstype` int(11) unsigned NOT NULL DEFAULT '0',
153 `isreward` tinyint(1) NOT NULL DEFAULT '1',
154 `istutorial` tinyint(1) NOT NULL DEFAULT '0',
155 `forge_dusts` bigint(21) NOT NULL DEFAULT '0',
156 `forge_dust_level` bigint(21) NOT NULL DEFAULT '100',
157 `randomize_mount` tinyint(1) NOT NULL DEFAULT '0',
158 `boss_points` int NOT NULL DEFAULT '0',
159 `comment` varchar(255) NOT NULL DEFAULT '',
160 `animus_mastery` mediumblob DEFAULT NULL,
161 INDEX `account_id` (`account_id`),
162 INDEX `vocation` (`vocation`),
163 CONSTRAINT `players_pk` PRIMARY KEY (`id`),
164 CONSTRAINT `players_unique` UNIQUE (`name`),
165 CONSTRAINT `players_account_fk`
166 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
167 ON DELETE CASCADE
168) ENGINE=InnoDB DEFAULT CHARSET=utf8;
169
170-- Table structure `account_bans`
171CREATE TABLE IF NOT EXISTS `account_bans` (
172 `account_id` int(11) UNSIGNED NOT NULL,
173 `reason` varchar(255) NOT NULL,
174 `banned_at` bigint(20) NOT NULL,
175 `expires_at` bigint(20) NOT NULL,
176 `banned_by` int(11) NOT NULL,
177 INDEX `banned_by` (`banned_by`),
178 CONSTRAINT `account_bans_pk` PRIMARY KEY (`account_id`),
179 CONSTRAINT `account_bans_account_fk`
180 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
181 ON DELETE CASCADE
182 ON UPDATE CASCADE,
183 CONSTRAINT `account_bans_player_fk`
184 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
185 ON DELETE CASCADE
186 ON UPDATE CASCADE
187) ENGINE=InnoDB DEFAULT CHARSET=utf8;
188
189-- Table structure `account_ban_history`
190CREATE TABLE IF NOT EXISTS `account_ban_history` (
191 `id` int(11) NOT NULL AUTO_INCREMENT,
192 `account_id` int(11) UNSIGNED NOT NULL,
193 `reason` varchar(255) NOT NULL,
194 `banned_at` bigint(20) NOT NULL,
195 `expired_at` bigint(20) NOT NULL,
196 `banned_by` int(11) NOT NULL,
197 INDEX `account_id` (`account_id`),
198 INDEX `banned_by` (`banned_by`),
199 CONSTRAINT `account_bans_history_account_fk`
200 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
201 ON DELETE CASCADE
202 ON UPDATE CASCADE,
203 CONSTRAINT `account_bans_history_player_fk`
204 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
205 ON DELETE CASCADE
206 ON UPDATE CASCADE,
207 CONSTRAINT `account_ban_history_pk` PRIMARY KEY (`id`)
208) ENGINE=InnoDB DEFAULT CHARSET=utf8;
209
210-- Table structure `account_viplist`
211CREATE TABLE IF NOT EXISTS `account_viplist` (
212 `account_id` int(11) UNSIGNED 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-- Table structure `account_vipgroup`
229CREATE TABLE IF NOT EXISTS `account_vipgroups` (
230 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
231 `account_id` int(11) UNSIGNED NOT NULL COMMENT 'id of account whose vip group entry it is',
232 `name` varchar(128) NOT NULL,
233 `customizable` BOOLEAN NOT NULL DEFAULT '1',
234 CONSTRAINT `account_vipgroups_pk` PRIMARY KEY (`id`, `account_id`),
235 CONSTRAINT `account_vipgroups_accounts_fk`
236 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
237 ON DELETE CASCADE
238) ENGINE=InnoDB DEFAULT CHARSET=utf8;
239
240--
241-- Trigger
242--
243DELIMITER //
244CREATE TRIGGER `oncreate_accounts` AFTER INSERT ON `accounts` FOR EACH ROW BEGIN
245 INSERT INTO `account_vipgroups` (`account_id`, `name`, `customizable`) VALUES (NEW.`id`, 'Enemies', 0);
246 INSERT INTO `account_vipgroups` (`account_id`, `name`, `customizable`) VALUES (NEW.`id`, 'Friends', 0);
247 INSERT INTO `account_vipgroups` (`account_id`, `name`, `customizable`) VALUES (NEW.`id`, 'Trading Partner', 0);
248END
249//
250DELIMITER ;
251
252-- Table structure `account_vipgrouplist`
253CREATE TABLE IF NOT EXISTS `account_vipgrouplist` (
254 `account_id` int(11) UNSIGNED NOT NULL COMMENT 'id of account whose viplist entry it is',
255 `player_id` int(11) NOT NULL COMMENT 'id of target player of viplist entry',
256 `vipgroup_id` int(11) UNSIGNED NOT NULL COMMENT 'id of vip group that player belongs',
257 INDEX `account_id` (`account_id`),
258 INDEX `player_id` (`player_id`),
259 INDEX `vipgroup_id` (`vipgroup_id`),
260 CONSTRAINT `account_vipgrouplist_unique` UNIQUE (`account_id`, `player_id`, `vipgroup_id`),
261 CONSTRAINT `account_vipgrouplist_player_fk`
262 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
263 ON DELETE CASCADE,
264 CONSTRAINT `account_vipgrouplist_vipgroup_fk`
265 FOREIGN KEY (`vipgroup_id`, `account_id`) REFERENCES `account_vipgroups` (`id`, `account_id`)
266 ON DELETE CASCADE
267) ENGINE=InnoDB DEFAULT CHARSET=utf8;
268
269-- Table structure `boosted_boss`
270CREATE TABLE IF NOT EXISTS `boosted_boss` (
271 `boostname` TEXT,
272 `date` varchar(250) NOT NULL DEFAULT '',
273 `raceid` varchar(250) NOT NULL DEFAULT '',
274 `looktypeEx` int(11) NOT NULL DEFAULT 0,
275 `looktype` int(11) NOT NULL DEFAULT 136,
276 `lookfeet` int(11) NOT NULL DEFAULT 0,
277 `looklegs` int(11) NOT NULL DEFAULT 0,
278 `lookhead` int(11) NOT NULL DEFAULT 0,
279 `lookbody` int(11) NOT NULL DEFAULT 0,
280 `lookaddons` int(11) NOT NULL DEFAULT 0,
281 `lookmount` int(11) DEFAULT 0,
282 PRIMARY KEY (`date`)
283) ENGINE=InnoDB DEFAULT CHARSET=utf8;
284
285INSERT INTO `boosted_boss` (`boostname`, `date`, `raceid`) VALUES ('default', 0, 0);
286
287-- Table structure `boosted_creature`
288CREATE TABLE IF NOT EXISTS `boosted_creature` (
289 `boostname` TEXT,
290 `date` varchar(250) NOT NULL DEFAULT '',
291 `raceid` varchar(250) NOT NULL DEFAULT '',
292 `looktype` int(11) NOT NULL DEFAULT 136,
293 `lookfeet` int(11) NOT NULL DEFAULT 0,
294 `looklegs` int(11) NOT NULL DEFAULT 0,
295 `lookhead` int(11) NOT NULL DEFAULT 0,
296 `lookbody` int(11) NOT NULL DEFAULT 0,
297 `lookaddons` int(11) NOT NULL DEFAULT 0,
298 `lookmount` int(11) DEFAULT 0,
299 PRIMARY KEY (`date`)
300) ENGINE=InnoDB DEFAULT CHARSET=utf8;
301
302INSERT INTO `boosted_creature` (`boostname`, `date`, `raceid`) VALUES ('default', 0, 0);
303
304-- Tabble Structure `daily_reward_history`
305CREATE TABLE IF NOT EXISTS `daily_reward_history` (
306 `id` int(11) NOT NULL AUTO_INCREMENT,
307 `daystreak` smallint(2) NOT NULL DEFAULT 0,
308 `player_id` int(11) NOT NULL,
309 `timestamp` int(11) NOT NULL,
310 `description` varchar(255) DEFAULT NULL,
311 INDEX `player_id` (`player_id`),
312 CONSTRAINT `daily_reward_history_pk` PRIMARY KEY (`id`),
313 CONSTRAINT `daily_reward_history_player_fk`
314 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
315 ON DELETE CASCADE
316) ENGINE=InnoDB DEFAULT CHARSET=utf8;
317
318-- Tabble Structure `forge_history`
319CREATE TABLE IF NOT EXISTS `forge_history` (
320 `id` int NOT NULL AUTO_INCREMENT,
321 `player_id` int NOT NULL,
322 `action_type` int NOT NULL DEFAULT '0',
323 `description` text NOT NULL,
324 `is_success` tinyint NOT NULL DEFAULT '0',
325 `bonus` tinyint NOT NULL DEFAULT '0',
326 `done_at` bigint NOT NULL,
327 `done_at_date` datetime DEFAULT NOW(),
328 `cost` bigint UNSIGNED NOT NULL DEFAULT '0',
329 `gained` bigint UNSIGNED NOT NULL DEFAULT '0',
330 CONSTRAINT `forge_history_pk` PRIMARY KEY (`id`),
331 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
332 UNIQUE KEY `unique_player_done_at` (`player_id`, `done_at`)
333) ENGINE=InnoDB DEFAULT CHARSET=utf8;
334
335-- Table structure `global_storage`
336CREATE TABLE IF NOT EXISTS `global_storage` (
337 `key` varchar(32) NOT NULL,
338 `value` text NOT NULL,
339 CONSTRAINT `global_storage_unique` UNIQUE (`key`)
340) ENGINE=InnoDB DEFAULT CHARSET=utf8;
341
342-- Table structure `guilds`
343CREATE TABLE IF NOT EXISTS `guilds` (
344 `id` int(11) NOT NULL AUTO_INCREMENT,
345 `level` int(11) NOT NULL DEFAULT '1',
346 `name` varchar(255) NOT NULL,
347 `ownerid` int(11) NOT NULL,
348 `creationdata` int(11) NOT NULL,
349 `motd` varchar(255) NOT NULL DEFAULT '',
350 `residence` int(11) NOT NULL DEFAULT '0',
351 `balance` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
352 `points` int(11) NOT NULL DEFAULT '0',
353 CONSTRAINT `guilds_pk` PRIMARY KEY (`id`),
354 CONSTRAINT `guilds_name_unique` UNIQUE (`name`),
355 CONSTRAINT `guilds_owner_unique` UNIQUE (`ownerid`),
356 CONSTRAINT `guilds_ownerid_fk`
357 FOREIGN KEY (`ownerid`) REFERENCES `players` (`id`)
358 ON DELETE CASCADE
359) ENGINE=InnoDB DEFAULT CHARSET=utf8;
360
361-- Table structure `guild_wars`
362CREATE TABLE IF NOT EXISTS `guild_wars` (
363 `id` int(11) NOT NULL AUTO_INCREMENT,
364 `guild1` int(11) NOT NULL DEFAULT '0',
365 `guild2` int(11) NOT NULL DEFAULT '0',
366 `name1` varchar(255) NOT NULL,
367 `name2` varchar(255) NOT NULL,
368 `status` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
369 `started` bigint(15) NOT NULL DEFAULT '0',
370 `ended` bigint(15) NOT NULL DEFAULT '0',
371 `frags_limit` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
372 `payment` bigint(13) UNSIGNED NOT NULL DEFAULT '0',
373 `duration_days` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
374 INDEX `guild1` (`guild1`),
375 INDEX `guild2` (`guild2`),
376 CONSTRAINT `guild_wars_pk` PRIMARY KEY (`id`)
377) ENGINE=InnoDB DEFAULT CHARSET=utf8;
378
379-- Table structure `guildwar_kills`
380CREATE TABLE IF NOT EXISTS `guildwar_kills` (
381 `id` int(11) NOT NULL AUTO_INCREMENT,
382 `killer` varchar(50) NOT NULL,
383 `target` varchar(50) NOT NULL,
384 `killerguild` int(11) NOT NULL DEFAULT '0',
385 `targetguild` int(11) NOT NULL DEFAULT '0',
386 `warid` int(11) NOT NULL DEFAULT '0',
387 `time` bigint(15) NOT NULL,
388 INDEX `warid` (`warid`),
389 CONSTRAINT `guildwar_kills_pk` PRIMARY KEY (`id`),
390 CONSTRAINT `guildwar_kills_warid_fk`
391 FOREIGN KEY (`warid`) REFERENCES `guild_wars` (`id`)
392 ON DELETE CASCADE
393) ENGINE=InnoDB DEFAULT CHARSET=utf8;
394
395-- Table structure `guild_invites`
396CREATE TABLE IF NOT EXISTS `guild_invites` (
397 `player_id` int(11) NOT NULL DEFAULT '0',
398 `guild_id` int(11) NOT NULL DEFAULT '0',
399 `date` int(11) NOT NULL,
400 INDEX `guild_id` (`guild_id`),
401 CONSTRAINT `guild_invites_pk` PRIMARY KEY (`player_id`, `guild_id`),
402 CONSTRAINT `guild_invites_player_fk`
403 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
404 ON DELETE CASCADE,
405 CONSTRAINT `guild_invites_guild_fk`
406 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
407 ON DELETE CASCADE
408) ENGINE=InnoDB DEFAULT CHARSET=utf8;
409
410-- Table structure `guild_ranks`
411CREATE TABLE IF NOT EXISTS `guild_ranks` (
412 `id` int(11) NOT NULL AUTO_INCREMENT,
413 `guild_id` int(11) NOT NULL COMMENT 'guild',
414 `name` varchar(255) NOT NULL COMMENT 'rank name',
415 `level` int(11) NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
416 INDEX `guild_id` (`guild_id`),
417 CONSTRAINT `guild_ranks_pk` PRIMARY KEY (`id`),
418 CONSTRAINT `guild_ranks_fk`
419 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
420 ON DELETE CASCADE
421) ENGINE=InnoDB DEFAULT CHARSET=utf8;
422
423--
424-- Trigger
425--
426DELIMITER //
427CREATE TRIGGER `oncreate_guilds` AFTER INSERT ON `guilds` FOR EACH ROW BEGIN
428 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('The Leader', 3, NEW.`id`);
429 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
430 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
431END
432//
433DELIMITER ;
434
435-- Table structure `guild_membership`
436CREATE TABLE IF NOT EXISTS `guild_membership` (
437 `player_id` int(11) NOT NULL,
438 `guild_id` int(11) NOT NULL,
439 `rank_id` int(11) NOT NULL,
440 `nick` varchar(15) NOT NULL DEFAULT '',
441 INDEX `guild_id` (`guild_id`),
442 INDEX `rank_id` (`rank_id`),
443 CONSTRAINT `guild_membership_pk` PRIMARY KEY (`player_id`),
444 CONSTRAINT `guild_membership_player_fk`
445 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
446 ON DELETE CASCADE
447 ON UPDATE CASCADE,
448 CONSTRAINT `guild_membership_guild_fk`
449 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`)
450 ON DELETE CASCADE
451 ON UPDATE CASCADE,
452 CONSTRAINT `guild_membership_rank_fk`
453 FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`)
454 ON DELETE CASCADE
455 ON UPDATE CASCADE
456) ENGINE=InnoDB DEFAULT CHARSET=utf8;
457
458-- Table structure `houses`
459CREATE TABLE IF NOT EXISTS `houses` (
460 `id` int(11) NOT NULL AUTO_INCREMENT,
461 `owner` int(11) NOT NULL,
462 `new_owner` int(11) NOT NULL DEFAULT '-1',
463 `paid` int(10) UNSIGNED NOT NULL DEFAULT '0',
464 `warnings` int(11) NOT NULL DEFAULT '0',
465 `name` varchar(255) NOT NULL,
466 `rent` int(11) NOT NULL DEFAULT '0',
467 `town_id` int(11) NOT NULL DEFAULT '0',
468 `size` int(11) NOT NULL DEFAULT '0',
469 `guildid` int(11),
470 `beds` int(11) NOT NULL DEFAULT '0',
471 `bidder` int(11) NOT NULL DEFAULT '0',
472 `bidder_name` varchar(255) NOT NULL DEFAULT '',
473 `highest_bid` int(11) NOT NULL DEFAULT '0',
474 `internal_bid` int(11) NOT NULL DEFAULT '0',
475 `bid_end_date` int(11) NOT NULL DEFAULT '0',
476 `state` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
477 `transfer_status` tinyint(1) DEFAULT '0',
478 INDEX `owner` (`owner`),
479 INDEX `town_id` (`town_id`),
480 CONSTRAINT `houses_pk` PRIMARY KEY (`id`)
481) ENGINE=InnoDB DEFAULT CHARSET=utf8;
482
483--
484-- trigger
485--
486DELIMITER //
487CREATE TRIGGER `ondelete_players` BEFORE DELETE ON `players` FOR EACH ROW BEGIN
488 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
489END
490//
491DELIMITER ;
492
493-- Table structure `house_lists`
494CREATE TABLE IF NOT EXISTS `house_lists` (
495 `house_id` int NOT NULL,
496 `listid` int NOT NULL,
497 `version` bigint NOT NULL DEFAULT '0',
498 `list` text NOT NULL,
499 PRIMARY KEY (`house_id`, `listid`),
500 KEY `house_id_index` (`house_id`),
501 KEY `version` (`version`),
502 CONSTRAINT `houses_list_house_fk` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
503) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
504
505-- Table structure `ip_bans`
506CREATE TABLE IF NOT EXISTS `ip_bans` (
507 `ip` int(11) NOT NULL,
508 `reason` varchar(255) NOT NULL,
509 `banned_at` bigint(20) NOT NULL,
510 `expires_at` bigint(20) NOT NULL,
511 `banned_by` int(11) NOT NULL,
512 INDEX `banned_by` (`banned_by`),
513 CONSTRAINT `ip_bans_pk` PRIMARY KEY (`ip`),
514 CONSTRAINT `ip_bans_players_fk`
515 FOREIGN KEY (`banned_by`) REFERENCES `players` (`id`)
516 ON DELETE CASCADE
517 ON UPDATE CASCADE
518) ENGINE=InnoDB DEFAULT CHARSET=utf8;
519
520-- Table structure `market_history`
521CREATE TABLE IF NOT EXISTS `market_history` (
522 `id` int(11) NOT NULL AUTO_INCREMENT,
523 `player_id` int(11) NOT NULL,
524 `sale` tinyint(1) NOT NULL DEFAULT '0',
525 `itemtype` int(10) UNSIGNED NOT NULL,
526 `amount` smallint(5) UNSIGNED NOT NULL,
527 `price` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
528 `expires_at` bigint(20) UNSIGNED NOT NULL,
529 `inserted` bigint(20) UNSIGNED NOT NULL,
530 `state` tinyint(1) UNSIGNED NOT NULL,
531 `tier` tinyint UNSIGNED NOT NULL DEFAULT '0',
532 INDEX `player_id` (`player_id`,`sale`),
533 CONSTRAINT `market_history_pk` PRIMARY KEY (`id`),
534 CONSTRAINT `market_history_players_fk`
535 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
536 ON DELETE CASCADE
537) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538
539-- Table structure `market_offers`
540CREATE TABLE IF NOT EXISTS `market_offers` (
541 `id` int(11) NOT NULL AUTO_INCREMENT,
542 `player_id` int(11) NOT NULL,
543 `sale` tinyint(1) NOT NULL DEFAULT '0',
544 `itemtype` int(10) UNSIGNED NOT NULL,
545 `amount` smallint(5) UNSIGNED NOT NULL,
546 `created` bigint(20) UNSIGNED NOT NULL,
547 `anonymous` tinyint(1) NOT NULL DEFAULT '0',
548 `price` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
549 `tier` tinyint UNSIGNED NOT NULL DEFAULT '0',
550 INDEX `sale` (`sale`,`itemtype`),
551 INDEX `created` (`created`),
552 INDEX `player_id` (`player_id`),
553 CONSTRAINT `market_offers_pk` PRIMARY KEY (`id`),
554 CONSTRAINT `market_offers_players_fk`
555 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
556 ON DELETE CASCADE
557) ENGINE=InnoDB DEFAULT CHARSET=utf8;
558
559-- Table structure `players_online`
560CREATE TABLE IF NOT EXISTS `players_online` (
561 `player_id` int(11) NOT NULL,
562 CONSTRAINT `players_online_pk` PRIMARY KEY (`player_id`),
563 CONSTRAINT `players_online_players_fk`
564 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
565 ON DELETE CASCADE
566) ENGINE=MEMORY DEFAULT CHARSET=utf8;
567
568-- Table structure `active_livestream_casters`
569CREATE TABLE IF NOT EXISTS `active_livestream_casters` (
570 `caster_id` int(11) NOT NULL,
571 `livestream_status` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
572 `livestream_viewers` int(11) UNSIGNED NOT NULL DEFAULT '0',
573 CONSTRAINT `active_livestream_casters_pk` PRIMARY KEY (`caster_id`),
574 CONSTRAINT `active_livestream_casters_players_fk`
575 FOREIGN KEY (`caster_id`) REFERENCES `players` (`id`)
576 ON DELETE CASCADE
577) ENGINE=InnoDB DEFAULT CHARSET=utf8;
578
579-- Table structure `player_charm`
580CREATE TABLE IF NOT EXISTS `player_charms` (
581 `player_id` int(11) NOT NULL,
582 `charm_points` SMALLINT NOT NULL DEFAULT '0',
583 `minor_charm_echoes` SMALLINT NOT NULL DEFAULT '0',
584 `max_charm_points` SMALLINT NOT NULL DEFAULT '0',
585 `max_minor_charm_echoes` SMALLINT NOT NULL DEFAULT '0',
586 `charm_expansion` BOOLEAN NOT NULL DEFAULT FALSE,
587 `UsedRunesBit` INT NOT NULL DEFAULT '0',
588 `UnlockedRunesBit` INT NOT NULL DEFAULT '0',
589 `charms` BLOB NULL,
590 `tracker list` BLOB NULL,
591 CONSTRAINT `player_charms_players_fk`
592 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
593 ON DELETE CASCADE
594) ENGINE = InnoDB DEFAULT CHARSET=utf8;
595
596-- Table structure `player_deaths`
597CREATE TABLE IF NOT EXISTS `player_deaths` (
598 `player_id` int(11) NOT NULL,
599 `time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
600 `level` int(11) NOT NULL DEFAULT '1',
601 `killed_by` varchar(255) NOT NULL,
602 `is_player` tinyint(1) NOT NULL DEFAULT '1',
603 `mostdamage_by` varchar(100) NOT NULL,
604 `mostdamage_is_player` tinyint(1) NOT NULL DEFAULT '0',
605 `unjustified` tinyint(1) NOT NULL DEFAULT '0',
606 `mostdamage_unjustified` tinyint(1) NOT NULL DEFAULT '0',
607 `participants` TEXT NOT NULL,
608 INDEX `player_id` (`player_id`),
609 INDEX `killed_by` (`killed_by`),
610 INDEX `mostdamage_by` (`mostdamage_by`),
611 CONSTRAINT `player_deaths_players_fk`
612 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
613 ON DELETE CASCADE
614) ENGINE=InnoDB DEFAULT CHARSET=utf8;
615
616-- Table structure `player_depotitems`
617CREATE TABLE IF NOT EXISTS `player_depotitems` (
618 `player_id` int(11) NOT NULL,
619 `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',
620 `pid` int(11) NOT NULL DEFAULT '0',
621 `itemtype` int(11) NOT NULL DEFAULT '0',
622 `count` int(11) NOT NULL DEFAULT '0',
623 `attributes` blob NOT NULL,
624 CONSTRAINT `player_depotitems_unique` UNIQUE (`player_id`, `sid`),
625 CONSTRAINT `player_depotitems_players_fk`
626 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
627 ON DELETE CASCADE
628) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629
630-- Table structure `player_hirelings`
631CREATE TABLE IF NOT EXISTS `player_hirelings` (
632 `id` INT NOT NULL PRIMARY KEY auto_increment,
633 `player_id` INT NOT NULL,
634 `name` varchar(255),
635 `active` tinyint unsigned NOT NULL DEFAULT '0',
636 `sex` tinyint unsigned NOT NULL DEFAULT '0',
637 `posx` int(11) NOT NULL DEFAULT '0',
638 `posy` int(11) NOT NULL DEFAULT '0',
639 `posz` int(11) NOT NULL DEFAULT '0',
640 `lookbody` int(11) NOT NULL DEFAULT '0',
641 `lookfeet` int(11) NOT NULL DEFAULT '0',
642 `lookhead` int(11) NOT NULL DEFAULT '0',
643 `looklegs` int(11) NOT NULL DEFAULT '0',
644 `looktype` int(11) NOT NULL DEFAULT '136',
645 FOREIGN KEY(`player_id`) REFERENCES `players`(`id`)
646 ON DELETE CASCADE
647) ENGINE=InnoDB DEFAULT CHARSET=utf8;
648
649-- Table structure `player_inboxitems`
650CREATE TABLE IF NOT EXISTS `player_inboxitems` (
651 `player_id` int(11) NOT NULL,
652 `sid` int(11) NOT NULL,
653 `pid` int(11) NOT NULL DEFAULT '0',
654 `itemtype` int(11) NOT NULL DEFAULT '0',
655 `count` int(11) NOT NULL DEFAULT '0',
656 `attributes` blob NOT NULL,
657 CONSTRAINT `player_inboxitems_unique` UNIQUE (`player_id`, `sid`),
658 CONSTRAINT `player_inboxitems_players_fk`
659 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
660 ON DELETE CASCADE
661) ENGINE=InnoDB DEFAULT CHARSET=utf8;
662
663-- Table structure `player_items`
664CREATE TABLE IF NOT EXISTS `player_items` (
665 `player_id` int(11) NOT NULL DEFAULT '0',
666 `pid` int(11) NOT NULL DEFAULT '0',
667 `sid` int(11) NOT NULL DEFAULT '0',
668 `itemtype` int(11) NOT NULL DEFAULT '0',
669 `count` int(11) NOT NULL DEFAULT '0',
670 `attributes` blob NOT NULL,
671 INDEX `player_id` (`player_id`),
672 INDEX `sid` (`sid`),
673 CONSTRAINT `player_items_players_fk`
674 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
675 ON DELETE CASCADE,
676 CONSTRAINT `player_items_pk`
677 PRIMARY KEY (`player_id`, `pid`, `sid`)
678) ENGINE=InnoDB DEFAULT CHARSET=utf8;
679
680-- Table structure `player_wheeldata`
681CREATE TABLE IF NOT EXISTS `player_wheeldata` (
682 `player_id` int(11) NOT NULL,
683 `slot` blob NOT NULL,
684 INDEX `player_id` (`player_id`),
685 CONSTRAINT `player_wheeldata_players_fk`
686 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
687 ON DELETE CASCADE,
688 CONSTRAINT `player_wheeldata_pk`
689 PRIMARY KEY (`player_id`)
690) ENGINE=InnoDB DEFAULT CHARSET=utf8;
691
692-- Table structure `player_kills`
693CREATE TABLE IF NOT EXISTS `player_kills` (
694 `player_id` int(11) NOT NULL,
695 `time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
696 `target` int(11) NOT NULL,
697 `unavenged` tinyint(1) NOT NULL DEFAULT '0',
698 CONSTRAINT `player_kills_players_fk`
699 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
700 ON DELETE CASCADE
701) ENGINE=InnoDB DEFAULT CHARSET=utf8;
702
703-- Table structure `player_namelocks`
704CREATE TABLE IF NOT EXISTS `player_namelocks` (
705 `player_id` int(11) NOT NULL,
706 `reason` varchar(255) NOT NULL,
707 `namelocked_at` bigint(20) NOT NULL,
708 `namelocked_by` int(11) NOT NULL,
709 INDEX `namelocked_by` (`namelocked_by`),
710 CONSTRAINT `player_namelocks_unique` UNIQUE (`player_id`),
711 CONSTRAINT `player_namelocks_players_fk`
712 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
713 ON DELETE CASCADE
714 ON UPDATE CASCADE,
715 CONSTRAINT `player_namelocks_players2_fk`
716 FOREIGN KEY (`namelocked_by`) REFERENCES `players` (`id`)
717 ON DELETE CASCADE
718 ON UPDATE CASCADE
719) ENGINE=InnoDB DEFAULT CHARSET=utf8;
720
721-- Table structure `player_prey`
722CREATE TABLE IF NOT EXISTS `player_prey` (
723 `player_id` int(11) NOT NULL,
724 `slot` tinyint(1) NOT NULL,
725 `state` tinyint(1) NOT NULL,
726 `raceid` varchar(250) NOT NULL,
727 `option` tinyint(1) NOT NULL,
728 `bonus_type` tinyint(1) NOT NULL,
729 `bonus_rarity` tinyint(1) NOT NULL,
730 `bonus_percentage` varchar(250) NOT NULL,
731 `bonus_time` varchar(250) NOT NULL,
732 `free_reroll` bigint(20) NOT NULL,
733 `monster_list` BLOB NULL,
734 CONSTRAINT `player_prey_pk` PRIMARY KEY (`player_id`, `slot`),
735 CONSTRAINT `player_prey_players_fk`
736 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
737 ON DELETE CASCADE
738) ENGINE=InnoDB DEFAULT CHARSET=utf8;
739
740-- Table structure `player_taskhunt`
741CREATE TABLE IF NOT EXISTS `player_taskhunt` (
742 `player_id` int(11) NOT NULL,
743 `slot` tinyint(1) NOT NULL,
744 `state` tinyint(1) NOT NULL,
745 `raceid` varchar(250) NOT NULL,
746 `upgrade` tinyint(1) NOT NULL,
747 `rarity` tinyint(1) NOT NULL,
748 `kills` varchar(250) NOT NULL,
749 `disabled_time` bigint(20) NOT NULL,
750 `free_reroll` bigint(20) NOT NULL,
751 `monster_list` BLOB NULL,
752 CONSTRAINT `player_taskhunt_pk` PRIMARY KEY (`player_id`, `slot`),
753 CONSTRAINT `player_taskhunt_players_fk`
754 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
755 ON DELETE CASCADE
756) ENGINE=InnoDB DEFAULT CHARSET=utf8;
757
758-- Table structure `player_bosstiary`
759CREATE TABLE IF NOT EXISTS `player_bosstiary` (
760 `player_id` int NOT NULL,
761 `bossIdSlotOne` int NOT NULL DEFAULT 0,
762 `bossIdSlotTwo` int NOT NULL DEFAULT 0,
763 `removeTimes` int NOT NULL DEFAULT 1,
764 `tracker` blob NOT NULL,
765 CONSTRAINT `player_bosstiary_players_fk`
766 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
767 ON DELETE CASCADE
768) ENGINE=InnoDB DEFAULT CHARSET=utf8;
769
770-- Table structure `player_rewards`
771CREATE TABLE IF NOT EXISTS `player_rewards` (
772 `player_id` int(11) NOT NULL,
773 `sid` int(11) NOT NULL,
774 `pid` int(11) NOT NULL DEFAULT '0',
775 `itemtype` int(11) NOT NULL DEFAULT '0',
776 `count` int(11) NOT NULL DEFAULT '0',
777 `attributes` blob NOT NULL,
778 CONSTRAINT `player_rewards_unique` UNIQUE (`player_id`, `sid`),
779 CONSTRAINT `player_rewards_players_fk`
780 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
781 ON DELETE CASCADE
782) ENGINE=InnoDB DEFAULT CHARSET=utf8;
783
784-- Table structure `player_spells`
785CREATE TABLE IF NOT EXISTS `player_spells` (
786 `player_id` int(11) NOT NULL,
787 `name` varchar(255) NOT NULL,
788 INDEX `player_id` (`player_id`),
789 CONSTRAINT `player_spells_players_fk`
790 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
791 ON DELETE CASCADE,
792 CONSTRAINT `player_spells_pk` PRIMARY KEY (`player_id`, `name`)
793) ENGINE=InnoDB DEFAULT CHARSET=utf8;
794
795-- Table structure `player_stash`
796CREATE TABLE IF NOT EXISTS `player_stash` (
797 `player_id` INT(16) NOT NULL,
798 `item_id` INT(16) NOT NULL,
799 `item_count` INT(32) NOT NULL,
800 CONSTRAINT `player_stash_pk` PRIMARY KEY (`player_id`, `item_id`),
801 CONSTRAINT `player_stash_players_fk`
802 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
803 ON DELETE CASCADE
804) ENGINE=InnoDB DEFAULT CHARSET=utf8;
805
806-- Table structure `player_storage`
807CREATE TABLE IF NOT EXISTS `player_storage` (
808 `player_id` int(11) NOT NULL DEFAULT '0',
809 `key` int(10) UNSIGNED NOT NULL DEFAULT '0',
810 `value` int(11) NOT NULL DEFAULT '0',
811 CONSTRAINT `player_storage_pk` PRIMARY KEY (`player_id`, `key`),
812 CONSTRAINT `player_storage_players_fk`
813 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
814 ON DELETE CASCADE
815) ENGINE=InnoDB DEFAULT CHARSET=utf8;
816
817-- Table structure `store_history`
818CREATE TABLE IF NOT EXISTS `store_history` (
819 `id` int(11) NOT NULL AUTO_INCREMENT,
820 `account_id` int(11) UNSIGNED NOT NULL,
821 `mode` smallint(2) NOT NULL DEFAULT '0',
822 `description` varchar(3500) NOT NULL,
823 `coin_type` tinyint(1) NOT NULL DEFAULT '0',
824 `coin_amount` int(12) NOT NULL,
825 `time` bigint(20) UNSIGNED NOT NULL,
826 `timestamp` int(11) NOT NULL DEFAULT '0',
827 `coins` int(11) NOT NULL DEFAULT '0',
828 INDEX `account_id` (`account_id`),
829 CONSTRAINT `store_history_pk` PRIMARY KEY (`id`),
830 CONSTRAINT `store_history_account_fk`
831 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
832 ON DELETE CASCADE
833) ENGINE=InnoDB DEFAULT CHARSET=utf8;
834
835-- Table structure `tile_store`
836CREATE TABLE IF NOT EXISTS `tile_store` (
837 `house_id` int(11) NOT NULL,
838 `data` longblob NOT NULL,
839 INDEX `house_id` (`house_id`),
840 CONSTRAINT `tile_store_account_fk`
841 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`)
842 ON DELETE CASCADE
843) ENGINE=InnoDB DEFAULT CHARSET=utf8;
844
845-- Table structure `towns`
846CREATE TABLE IF NOT EXISTS `towns` (
847 `id` int NOT NULL AUTO_INCREMENT,
848 `name` varchar(255) NOT NULL,
849 `posx` int NOT NULL DEFAULT '0',
850 `posy` int NOT NULL DEFAULT '0',
851 `posz` int NOT NULL DEFAULT '0',
852 PRIMARY KEY (`id`),
853 UNIQUE KEY `name` (`name`)
854) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
855
856-- Table structure `account_sessions`
857CREATE TABLE IF NOT EXISTS `account_sessions` (
858 `id` VARCHAR(191) NOT NULL,
859 `account_id` INTEGER UNSIGNED NOT NULL,
860 `expires` BIGINT UNSIGNED NOT NULL,
861
862 PRIMARY KEY (`id`)
863) ENGINE=InnoDB DEFAULT CHARSET=utf8;
864
865-- Table structure `kv_store`
866CREATE TABLE IF NOT EXISTS `kv_store` (
867 `key_name` varchar(191) NOT NULL,
868 `timestamp` bigint NOT NULL,
869 `value` longblob NOT NULL,
870 PRIMARY KEY (`key_name`)
871) ENGINE=InnoDB DEFAULT CHARSET=utf8;
872
873-- Create Account god/god
874INSERT INTO `accounts`
875(`id`, `name`, `email`, `password`, `type`) VALUES
876(1, 'god', '@god', '21298df8a3277357ee55b01df9530b535cf08ec1', 6);
877
878-- Create player on GOD account
879-- Create sample characters
880INSERT INTO `players`
881(`id`, `name`, `group_id`, `account_id`, `level`, `vocation`, `health`, `healthmax`, `experience`, `lookbody`, `lookfeet`, `lookhead`, `looklegs`, `looktype`, `maglevel`, `mana`, `manamax`, `manaspent`, `town_id`, `conditions`, `cap`, `sex`, `skill_club`, `skill_club_tries`, `skill_sword`, `skill_sword_tries`, `skill_axe`, `skill_axe_tries`, `skill_dist`, `skill_dist_tries`) VALUES
882(1, 'Rook Sample', 1, 1, 2, 0, 155, 155, 100, 113, 115, 95, 39, 129, 2, 60, 60, 5936, 1, '', 410, 1, 12, 155, 12, 155, 12, 155, 12, 93),
883(2, 'Sorcerer Sample', 1, 1, 8, 1, 185, 185, 4200, 113, 115, 95, 39, 129, 0, 90, 90, 0, 8, '', 470, 1, 10, 0, 10, 0, 10, 0, 10, 0),
884(3, 'Druid Sample', 1, 1, 8, 2, 185, 185, 4200, 113, 115, 95, 39, 129, 0, 90, 90, 0, 8, '', 470, 1, 10, 0, 10, 0, 10, 0, 10, 0),
885(4, 'Paladin Sample', 1, 1, 8, 3, 185, 185, 4200, 113, 115, 95, 39, 129, 0, 90, 90, 0, 8, '', 470, 1, 10, 0, 10, 0, 10, 0, 10, 0),
886(5, 'Knight Sample', 1, 1, 8, 4, 185, 185, 4200, 113, 115, 95, 39, 129, 0, 90, 90, 0, 8, '', 470, 1, 10, 0, 10, 0, 10, 0, 10, 0),
887(6, 'Monk Sample', 1, 1, 8, 9, 185, 185, 4200, 113, 115, 95, 39, 129, 0, 90, 90, 0, 8, '', 470, 1, 10, 0, 10, 0, 10, 0, 10, 0),
888(7, 'GOD', 6, 1, 2, 0, 155, 155, 100, 113, 115, 95, 39, 75, 0, 60, 60, 0, 8, '', 410, 1, 10, 0, 10, 0, 10, 0, 10, 0);
889
Top