9ScSn3TD

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