tfs_10.sql

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