othire.sql

main 432 lines · 15.1 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- OTHire database schema.
2-- Source: https://github.com/Ezzz-dev/OTHire/blob/master/source/schema.mysql
3-- License: GPL-2.0. Covers ZnoteX's OTHIRE engine choice.
4
5CREATE TABLE `groups` (
6 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
7 `name` VARCHAR(255) NOT NULL COMMENT 'group name',
8
9 `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
10 `access` INT NOT NULL DEFAULT 0,
11 `violation` INT NOT NULL DEFAULT 0,
12 `maxdepotitems` INT NOT NULL,
13 `maxviplist` INT NOT NULL,
14
15 PRIMARY KEY (`id`)
16) ENGINE = InnoDB;
17INSERT INTO `groups` VALUES (1, 'Player', 0, 0, 0, 2000, 100),(2, 'Tutor', 16777216, 0, 0, 2000, 100),(3, 'Sennior Tutor', 274894684160, 0, 0, 2000, 100),(4, 'Community Manager', 69681547968463, 2, 0, 1000, 100),(5, 'Game Master', 69681547968463, 2, 0, 1000, 100),(6, 'GOD/OWNER', 57171953819640, 3, 0, 2000, 100);
18
19CREATE TABLE `accounts` (
20 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
21 `name` VARCHAR(32) NOT NULL,
22
23 `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
24 `email` VARCHAR(255) NOT NULL DEFAULT '',
25 `premend` INT UNSIGNED NOT NULL DEFAULT 0,
26 `blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
27 `warnings` INT NOT NULL DEFAULT 0,
28
29 PRIMARY KEY (`id`),
30 UNIQUE (`name`)
31) ENGINE = InnoDB;
32INSERT INTO `accounts` VALUES (1, 'tibia', 'tibia', '', 0, 0, 0);
33
34CREATE TABLE `players` (
35 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
36 `name` VARCHAR(255) NOT NULL,
37 `account_id` INT UNSIGNED NOT NULL,
38 `group_id` INT UNSIGNED NOT NULL COMMENT 'users group',
39
40 `sex` INT UNSIGNED NOT NULL DEFAULT 0,
41 `vocation` INT UNSIGNED NOT NULL DEFAULT 0,
42 `experience` BIGINT UNSIGNED NOT NULL DEFAULT 0,
43 `level` INT UNSIGNED NOT NULL DEFAULT 1,
44 `maglevel` INT UNSIGNED NOT NULL DEFAULT 0,
45 `health` INT NOT NULL DEFAULT 100,
46 `healthmax` INT NOT NULL DEFAULT 100,
47 `mana` INT NOT NULL DEFAULT 100,
48 `manamax` INT NOT NULL DEFAULT 100,
49 `manaspent` INT UNSIGNED NOT NULL DEFAULT 0,
50 `soul` INT UNSIGNED NOT NULL DEFAULT 0,
51 `direction` INT UNSIGNED NOT NULL DEFAULT 0,
52 `lookbody` INT UNSIGNED NOT NULL DEFAULT 10,
53 `lookfeet` INT UNSIGNED NOT NULL DEFAULT 10,
54 `lookhead` INT UNSIGNED NOT NULL DEFAULT 10,
55 `looklegs` INT UNSIGNED NOT NULL DEFAULT 10,
56 `looktype` INT UNSIGNED NOT NULL DEFAULT 136,
57 `lookaddons` INT UNSIGNED NOT NULL DEFAULT 0,
58 `posx` INT NOT NULL DEFAULT 0,
59 `posy` INT NOT NULL DEFAULT 0,
60 `posz` INT NOT NULL DEFAULT 0,
61 `cap` INT NOT NULL DEFAULT 0,
62 `lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
63 `lastlogout` INT UNSIGNED NOT NULL DEFAULT 0,
64 `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
65 `save` TINYINT(1) NOT NULL DEFAULT TRUE,
66 `conditions` BLOB NOT NULL COMMENT 'drunk, poisoned etc',
67 `skull_type` INT NOT NULL DEFAULT 0,
68 `skull_time` INT UNSIGNED NOT NULL DEFAULT 0,
69 `loss_experience` INT NOT NULL DEFAULT 100,
70 `loss_mana` INT NOT NULL DEFAULT 100,
71 `loss_skills` INT NOT NULL DEFAULT 100,
72 `loss_items` INT NOT NULL DEFAULT 10,
73 `loss_containers` INT NOT NULL DEFAULT 100,
74 `town_id` INT NOT NULL COMMENT 'old masterpos, temple spawn point position',
75 `balance` INT NOT NULL DEFAULT 0 COMMENT 'money balance of the player for houses paying',
76 `stamina` INT NOT NULL DEFAULT 151200000 COMMENT 'player stamina in milliseconds',
77 `online` TINYINT(1) NOT NULL DEFAULT 0,
78 `rank_id` INT NOT NULL COMMENT 'only if you use __OLD_GUILD_SYSTEM__',
79 `guildnick` VARCHAR(255) NOT NULL COMMENT 'only if you use __OLD_GUILD_SYSTEM__',
80
81 PRIMARY KEY (`id`),
82 UNIQUE (`name`),
83 KEY (`online`),
84 FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
85 FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
86) ENGINE = InnoDB;
87
88CREATE TABLE `guilds` (
89 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
90
91 `name` VARCHAR(255) NOT NULL,
92 `owner_id` INT UNSIGNED NOT NULL,
93 `creationdate` INT NOT NULL,
94
95 PRIMARY KEY (`id`),
96 UNIQUE (`name`),
97 FOREIGN KEY (`owner_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
98) ENGINE = InnoDB;
99
100CREATE TABLE `guild_ranks` (
101 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
102 `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',
103
104 `name` VARCHAR(255) NOT NULL COMMENT 'rank name',
105 `level` INT NOT NULL COMMENT 'rank level - leader, vice leader, member, maybe something else',
106
107 PRIMARY KEY (`id`),
108 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
109) ENGINE = InnoDB;
110
111CREATE TABLE `guild_members` (
112 `player_id` INT UNSIGNED NOT NULL COMMENT 'if you doesnt use new guild system you are free to delete this table',
113 `rank_id` INT UNSIGNED NOT NULL COMMENT 'a rank which belongs to certain guild',
114
115 `nick` VARCHAR(255) NOT NULL DEFAULT '',
116
117 UNIQUE (`player_id`),
118 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
119 FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`) ON DELETE CASCADE
120) ENGINE = InnoDB;
121
122CREATE TABLE `guild_invites` (
123 `player_id` INT UNSIGNED NOT NULL,
124 `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',
125
126 UNIQUE (`player_id`),
127 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
128 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
129) ENGINE = InnoDB;
130
131CREATE TABLE `guild_wars` (
132 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
133 `guild_id` INT UNSIGNED NOT NULL COMMENT 'the guild which declared the war',
134 `opponent_id` INT UNSIGNED NOT NULL COMMENT 'the enemy guild at war',
135
136 `frag_limit` INT UNSIGNED NOT NULL DEFAULT 10 COMMENT 'kills needed to win the war',
137 `declaration_date` INT UNSIGNED NOT NULL,
138 `end_date` INT UNSIGNED NOT NULL,
139 `guild_fee` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'amount of money the guild has to pay if loses the war',
140 `opponent_fee` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'amount of money the enemy guild has to pay if loses the war',
141 `guild_frags` INT UNSIGNED NOT NULL DEFAULT 0,
142 `opponent_frags` INT UNSIGNED NOT NULL DEFAULT 0,
143 `comment` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'the guild leader can leave a message for the other guild',
144 `status` INT NOT NULL DEFAULT 0 COMMENT '-1 -> will be ignored (finished or unaccepted) 0 -> to be started 1 -> started/not finished',
145
146 PRIMARY KEY (`id`),
147 FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE,
148 FOREIGN KEY (`opponent_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
149) ENGINE = InnoDB;
150
151CREATE TABLE `player_viplist` (
152 `player_id` INT UNSIGNED NOT NULL COMMENT 'id of player whose viplist entry it is',
153 `vip_id` INT UNSIGNED NOT NULL COMMENT 'id of target player of viplist entry',
154
155 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
156 FOREIGN KEY (`vip_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
157) ENGINE = InnoDB;
158
159CREATE TABLE `player_spells` (
160 `player_id` INT UNSIGNED NOT NULL,
161 `name` VARCHAR(255) NOT NULL,
162
163 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
164) ENGINE = InnoDB;
165
166CREATE TABLE `player_storage` (
167 `player_id` INT UNSIGNED NOT NULL,
168 `key` INT NOT NULL,
169 `value` INT NOT NULL,
170
171 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
172) ENGINE = InnoDB;
173
174CREATE TABLE `player_skills` (
175 `player_id` INT UNSIGNED NOT NULL,
176
177 `skillid` INT UNSIGNED NOT NULL,
178 `value` INT UNSIGNED NOT NULL DEFAULT 0,
179 `count` INT UNSIGNED NOT NULL DEFAULT 0,
180
181 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
182) ENGINE = InnoDB;
183
184CREATE TABLE `player_items` (
185 `player_id` INT UNSIGNED NOT NULL,
186 `sid` INT NOT NULL,
187 `pid` INT NOT NULL DEFAULT 0,
188 `itemtype` INT NOT NULL,
189 `count` INT NOT NULL DEFAULT 0,
190 `attributes` BLOB COMMENT 'replaces unique_id, action_id, text, special_desc',
191
192 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
193 UNIQUE (`player_id`, `sid`)
194) ENGINE = InnoDB;
195
196CREATE TABLE `houses` (
197 `id` INT UNSIGNED NOT NULL,
198 `townid` INT UNSIGNED NOT NULL DEFAULT 0,
199
200 `name` VARCHAR(100) NOT NULL,
201 `rent` INT UNSIGNED NOT NULL DEFAULT 0,
202 `guildhall` TINYINT(1) NOT NULL DEFAULT 0,
203 `tiles` INT UNSIGNED NOT NULL DEFAULT 0,
204 `doors` INT UNSIGNED NOT NULL DEFAULT 0,
205 `beds` INT UNSIGNED NOT NULL DEFAULT 0,
206 `owner` INT NOT NULL DEFAULT 0,
207 `paid` INT UNSIGNED NOT NULL DEFAULT 0,
208 `clear` TINYINT(1) NOT NULL DEFAULT 0,
209 `warnings` INT NOT NULL DEFAULT 0,
210 `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
211
212 PRIMARY KEY (`id`)
213) ENGINE = InnoDB;
214
215CREATE TABLE `house_auctions` (
216 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
217 `house_id` INT UNSIGNED NOT NULL,
218 `player_id` INT UNSIGNED NOT NULL,
219
220 `bid` INT UNSIGNED NOT NULL DEFAULT 0,
221 `limit` INT UNSIGNED NOT NULL DEFAULT 0,
222 `endtime` INT UNSIGNED NOT NULL DEFAULT 0,
223
224 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE,
225 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
226 PRIMARY KEY (`id`)
227) ENGINE = InnoDB;
228
229CREATE TABLE `house_lists` (
230 `house_id` INT UNSIGNED NOT NULL,
231 `listid` INT NOT NULL,
232 `list` TEXT NOT NULL,
233
234 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
235) ENGINE = InnoDB;
236
237CREATE TABLE `bans` (
238 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
239 `type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
240 `value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number',
241 `param` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'mask',
242 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
243 `expires` INT NOT NULL,
244 `added` INT UNSIGNED NOT NULL,
245 `admin_id` INT UNSIGNED,
246 `comment` VARCHAR(1024) NOT NULL DEFAULT '',
247 `reason` INT UNSIGNED NOT NULL DEFAULT 0,
248 `action` INT UNSIGNED NOT NULL DEFAULT 0,
249 `statement` VARCHAR(255) NOT NULL DEFAULT '',
250
251 PRIMARY KEY (`id`),
252 KEY (`type`, `value`),
253 KEY (`expires`),
254 FOREIGN KEY (`admin_id`) REFERENCES `players` (`id`) ON DELETE SET NULL
255) ENGINE = InnoDB;
256
257CREATE TABLE `tiles` (
258 `id` INT UNSIGNED NOT NULL,
259 `house_id` INT UNSIGNED NOT NULL DEFAULT 0,
260 `x` INT(5) UNSIGNED NOT NULL,
261 `y` INT(5) UNSIGNED NOT NULL,
262 `z` INT(2) UNSIGNED NOT NULL,
263
264 PRIMARY KEY(`id`),
265 KEY(`x`, `y`, `z`),
266 FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE NO ACTION
267) ENGINE = InnoDB;
268
269CREATE TABLE `tile_items` (
270 `tile_id` INT UNSIGNED NOT NULL,
271 `sid` INT NOT NULL,
272 `pid` INT NOT NULL DEFAULT 0,
273 `itemtype` INT NOT NULL,
274 `count` INT NOT NULL DEFAULT 0,
275 `attributes` BLOB NOT NULL,
276
277 INDEX (`sid`),
278 FOREIGN KEY (`tile_id`) REFERENCES `tiles` (`id`) ON DELETE CASCADE
279) ENGINE = InnoDB;
280
281CREATE TABLE `map_store` (
282 `house_id` INT UNSIGNED NOT NULL,
283 `data` LONGBLOB NOT NULL,
284
285 KEY(`house_id`)
286) ENGINE = InnoDB;
287
288CREATE TABLE `player_deaths` (
289 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
290 `player_id` INT UNSIGNED NOT NULL,
291 `date` INT UNSIGNED NOT NULL,
292 `level` INT NOT NULL,
293
294 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
295 PRIMARY KEY(`id`),
296 INDEX(`date`)
297) ENGINE = InnoDB;
298
299CREATE TABLE `killers` (
300 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
301 `death_id` INT UNSIGNED NOT NULL,
302 `final_hit` TINYINT(1) NOT NULL DEFAULT 1,
303
304 PRIMARY KEY(`id`),
305 FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`) ON DELETE CASCADE
306) ENGINE = InnoDB;
307
308CREATE TABLE `environment_killers` (
309 `kill_id` INT UNSIGNED NOT NULL,
310 `name` VARCHAR(255) NOT NULL,
311
312 PRIMARY KEY (`kill_id`, `name`),
313 FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE
314) ENGINE = InnoDB;
315
316CREATE TABLE `player_killers` (
317 `kill_id` INT UNSIGNED NOT NULL,
318 `player_id` INT UNSIGNED NOT NULL,
319 `unjustified` TINYINT(1) NOT NULL DEFAULT 0,
320
321 PRIMARY KEY (`kill_id`, `player_id`),
322 FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE,
323 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
324) ENGINE = InnoDB;
325
326CREATE TABLE `player_depotitems` (
327 `player_id` INT UNSIGNED NOT NULL,
328 `sid` INT 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',
329 `pid` INT NOT NULL DEFAULT 0,
330 `itemtype` INT NOT NULL,
331 `count` INT NOT NULL DEFAULT 0,
332 `attributes` BLOB NOT NULL,
333
334 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
335 UNIQUE (`player_id`, `sid`)
336) ENGINE = InnoDB;
337
338CREATE TABLE `market_offers` (
339 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
340 `player_id` INT UNSIGNED NOT NULL,
341
342 `sale` TINYINT(1) NOT NULL DEFAULT 0,
343 `itemtype` INT NOT NULL,
344 `amount` INT NOT NULL,
345
346 `created` INT NOT NULL,
347 `anonymous` TINYINT(1) DEFAULT 0,
348 `price` INT NOT NULL DEFAULT 0,
349
350 PRIMARY KEY (`id`),
351 KEY (`sale`, `itemtype`),
352 KEY (`created`),
353 FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
354) ENGINE = InnoDB;
355
356CREATE TABLE `market_statistics` (
357 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
358
359 `sale` TINYINT(1) NOT NULL,
360 `itemtype` INT NOT NULL,
361 `price` INT NOT NULL,
362 `amount` INT NOT NULL,
363 `when` INT NOT NULL,
364
365 PRIMARY KEY(`id`),
366 KEY(`sale`, `itemtype`, `when`)
367) ENGINE = InnoDB;
368
369CREATE TABLE `global_storage` (
370 `key` INT UNSIGNED NOT NULL,
371 `value` INT NOT NULL,
372
373 PRIMARY KEY(`key`)
374) ENGINE = InnoDB;
375
376CREATE TABLE `schema_info` (
377 `name` VARCHAR(255) NOT NULL,
378 `value` VARCHAR(255) NOT NULL,
379
380 PRIMARY KEY (`name`)
381) ENGINE = InnoDB;
382
383INSERT INTO `schema_info` (`name`, `value`) VALUES ('version', 24);
384
385DELIMITER |
386
387CREATE TRIGGER `ondelete_accounts`
388BEFORE DELETE
389ON `accounts`
390FOR EACH ROW
391BEGIN
392 DELETE FROM `bans` WHERE `type` = 3 AND `value` = OLD.`id`;
393END|
394
395CREATE TRIGGER `ondelete_players`
396BEFORE DELETE
397ON `players`
398FOR EACH ROW
399BEGIN
400 DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`;
401 UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
402END|
403
404CREATE TRIGGER `oncreate_guilds`
405AFTER INSERT
406ON `guilds`
407FOR EACH ROW
408BEGIN
409 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
410 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
411 INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
412END|
413
414CREATE TRIGGER `oncreate_players`
415AFTER INSERT
416ON `players`
417FOR EACH ROW
418BEGIN
419 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
420 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
421 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
422 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
423 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
424 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
425 INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
426END|
427
428DELIMITER ;
429INSERT INTO `players` VALUES (1, 'Administrator', 1, 6, 2, 1, 0, 1, 0, 185, 185, 35, 35, 0, 100, 2, 10, 10, 10, 10, 75, 0, 200, 200, 6, 435, 0, 0, 1, 1, '', 0, 0, 100, 100, 100, 10, 100, 1, 0, 151200000, 0, 0, '');
430INSERT INTO `players` VALUES (2, 'Player', 1, 1, 1, 1, 0, 1, 0, 185, 185, 35, 35, 0, 100, 2, 10, 10, 10, 10, 75, 0, 200, 200, 6, 435, 0, 0, 1, 1, '', 0, 0, 100, 100, 100, 10, 100, 1, 0, 151200000, 0, 0, '');
431
432# to add your own privileges for players/gms please use this flag generator http://hem.bredband.net/johannesrosen/playerflags.html
Top