tfs_03.sql

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