[8.21/8.22]Biosoft 0.6.6.0

[Nooben nr1]

@ Limannen

^_^ database bugged xD

use this

CREATE TABLE `groups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'group name',
    `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `access` INT NOT NULL,
    `maxdepotitems` INT NOT NULL,
    `maxviplist` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
    `premDays` INT NOT NULL,
    `premEnd` INT NOT NULL,	
    `email` VARCHAR(255) NOT NULL DEFAULT '',
    `blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
    `deleted` TINYINT(1) NOT NULL DEFAULT FALSE,
    `warned` TINYINT(1) NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `deathlist` (
    `player` INT NOT NULL default 0,
    `killer` VARCHAR(30) collate latin1_general_ci NOT NULL DEFAULT  '',
    `level` INT NOT NULL default 0,
    `date` INT NOT NULL default 0
) ENGINE = InnoDB;

CREATE TABLE `player_addons` (
    `player_id` INT UNSIGNED NOT NULL DEFAULT  0,
    `outfit` INT UNSIGNED NOT NULL DEFAULT  0,
    `type` INT UNSIGNED NOT NULL DEFAULT  0
) ENGINE = InnoDB;

CREATE TABLE `player_blessings` (
    `player_id` INT NOT NULL,
    `blessing` INT NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `players` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `group_id` INT UNSIGNED NOT NULL COMMENT 'users group',
    `sex` INT UNSIGNED NOT NULL DEFAULT 0,
    `married` INT NOT NULL DEFAULT 0,
    `lover` INT NOT NULL DEFAULT 0,
    `testify` INT NOT NULL DEFAULT 0,
    `engaged` INT NOT NULL DEFAULT 0,	    
	`vocation` INT UNSIGNED NOT NULL DEFAULT 0,
    `experience` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `level` INT UNSIGNED NOT NULL DEFAULT 1,
    `maglevel` INT UNSIGNED NOT NULL DEFAULT 0,
    `health` INT UNSIGNED NOT NULL DEFAULT 100,
    `healthmax` INT UNSIGNED NOT NULL DEFAULT 100,
    `mana` INT UNSIGNED NOT NULL DEFAULT 100,
    `manamax` INT UNSIGNED NOT NULL DEFAULT 100,
    `manaspent` INT UNSIGNED NOT NULL DEFAULT 0,
    `soul` INT UNSIGNED NOT NULL DEFAULT 0,
    `direction` INT UNSIGNED NOT NULL DEFAULT 0,
    `lookbody` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookfeet` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookhead` INT UNSIGNED NOT NULL DEFAULT 10,
    `looklegs` INT UNSIGNED NOT NULL DEFAULT 10,
    `looktype` INT UNSIGNED NOT NULL DEFAULT 136,
    `lookaddons` INT UNSIGNED NOT NULL DEFAULT 0,
    `posx` INT NOT NULL DEFAULT 0,
    `posy` INT NOT NULL DEFAULT 0,
    `posz` INT NOT NULL DEFAULT 0,
    `cap` INT NOT NULL DEFAULT 0,
    `lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
    `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
    `save` TINYINT(1) NOT NULL DEFAULT TRUE,
    `conditions` BLOB NOT NULL COMMENT 'drunk, poisoned etc (maybe also food and redskull)',
    `redskulltime` INT UNSIGNED NOT NULL DEFAULT 0,
    `redskull` TINYINT(1) NOT NULL DEFAULT FALSE,
    `guildnick` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'additional nick in guild - mostly for web interfaces i think',
    `loss_experience` INT NOT NULL DEFAULT 10,
    `loss_mana` INT NOT NULL DEFAULT 10,
    `loss_skills` INT NOT NULL DEFAULT 10,
    `loss_items` INT NOT NULL DEFAULT 10,
    `rank_id` INT NOT NULL COMMENT 'by this field everything with guilds is done - player has a rank which belongs to certain guild',
    `town_id` INT NOT NULL COMMENT 'old masterpos, temple spawn point position',
    `needtobeteleported` INT NOT NULL,
    `ispromoted` INT NOT NULL,
    `havepromotion` INT NOT NULL,	
    `balance` INT NOT NULL DEFAULT 0 COMMENT 'money balance of the player for houses paying',
    PRIMARY KEY (`id`),
    UNIQUE (`name`),
    FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guilds` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'guild name - nothing else needed here',
    `ownerid` INT NOT NULL,
    `creationdata` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guild_ranks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',
    `name` VARCHAR(255) NOT NULL COMMENT 'rank name',
    `level` INT NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_viplist` (
    `player_id` INT UNSIGNED NOT NULL COMMENT 'id of player whose viplist entry it is',
    `vip_id` INT UNSIGNED NOT NULL COMMENT 'id of target player of viplist entry',
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`vip_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_spells` (
    `player_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_storage` (
    `player_id` INT UNSIGNED NOT NULL,
    `key` INT NOT NULL,
    `value` INT NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_skills` (
    `player_id` INT UNSIGNED NOT NULL,
    `skillid` INT UNSIGNED NOT NULL,
    `value` INT UNSIGNED NOT NULL DEFAULT 0,
    `count` INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_items` (
    `player_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB COMMENT 'replaces unique_id, action_id, text, special_desc',
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `houses` (
    `id` INT UNSIGNED NOT NULL,
    `owner` INT NOT NULL,
    `paid` INT UNSIGNED NOT NULL DEFAULT 0,
    `warnings` INT NOT NULL DEFAULT 0,
    `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `house_lists` (
    `house_id` INT UNSIGNED NOT NULL,
    `listid` INT NOT NULL,
    `list` TEXT NOT NULL,
    FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `bans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
    `value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number',
    `param` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'mask',
    `active` TINYINT(1) NOT NULL DEFAULT TRUE,
    `expires` INT UNSIGNED NOT NULL,
    `added` INT UNSIGNED NOT NULL,
    `admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
    `comment` VARCHAR(255) NOT NULL DEFAULT '',
    `reason` INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY  (`id`),
    KEY (`type`, `value`),
    KEY (`expires`)
) ENGINE = InnoDB;

CREATE TABLE `tiles` (
    `id` INT UNSIGNED NOT NULL,
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    `z` INT NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = InnoDB;

CREATE TABLE `tile_items` (
    `tile_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,
    FOREIGN KEY (`tile_id`) REFERENCES `tiles` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_depotitems` (
    `player_id` INT UNSIGNED NOT NULL,
    `depot_id` INT NOT NULL DEFAULT 0,
    `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',
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    INDEX (`player_id`, `depot_id`),
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `global_storage` (
    `key` INT UNSIGNED NOT NULL,
    `value` INT NOT NULL,
    PRIMARY KEY(`key`)
) ENGINE = InnoDB;

DELIMITER |

CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 3 AND `value` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_guilds`
BEFORE DELETE
ON `guilds`
FOR EACH ROW
BEGIN
    UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
    DELETE FROM `guild_ranks` WHERE `guild_id` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`;
    UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|

CREATE TRIGGER `oncreate_guilds`
AFTER INSERT
ON `guilds`
FOR EACH ROW
BEGIN
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
END|

CREATE TRIGGER `oncreate_players`
AFTER INSERT
ON `players`
FOR EACH ROW
BEGIN
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
END|

DELIMITER ;

When I use that I get this:

MySQL sa:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

 

[gm neo colombia]

CREATE TABLE `groups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'group name',
    `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `access` INT NOT NULL,
    `maxdepotitems` INT NOT NULL,
    `maxviplist` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
    `premDays` INT NOT NULL,
    `premEnd` INT NOT NULL,	
    `email` VARCHAR(255) NOT NULL DEFAULT '',
    `blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
    `deleted` TINYINT(1) NOT NULL DEFAULT FALSE,
    `warned` TINYINT(1) NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `deathlist` (
    `player` INT NOT NULL default 0,
    `killer` VARCHAR(30) collate latin1_general_ci NOT NULL DEFAULT  '',
    `level` INT NOT NULL default 0,
    `date` INT NOT NULL default 0
) ENGINE = InnoDB;

CREATE TABLE `player_addons` (
    `player_id` INT UNSIGNED NOT NULL DEFAULT  0,
    `outfit` INT UNSIGNED NOT NULL DEFAULT  0,
    `type` INT UNSIGNED NOT NULL DEFAULT  0
) ENGINE = InnoDB;

CREATE TABLE `player_blessings` (
    `player_id` INT NOT NULL,
    `blessing` INT NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `players` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `group_id` INT UNSIGNED NOT NULL COMMENT 'users group',
    `sex` INT UNSIGNED NOT NULL DEFAULT 0,
    `married` INT NOT NULL DEFAULT 0,
    `lover` INT NOT NULL DEFAULT 0,
    `testify` INT NOT NULL DEFAULT 0,
    `engaged` INT NOT NULL DEFAULT 0,	    
	`vocation` INT UNSIGNED NOT NULL DEFAULT 0,
    `experience` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `level` INT UNSIGNED NOT NULL DEFAULT 1,
    `maglevel` INT UNSIGNED NOT NULL DEFAULT 0,
    `health` INT UNSIGNED NOT NULL DEFAULT 100,
    `healthmax` INT UNSIGNED NOT NULL DEFAULT 100,
    `mana` INT UNSIGNED NOT NULL DEFAULT 100,
    `manamax` INT UNSIGNED NOT NULL DEFAULT 100,
    `manaspent` INT UNSIGNED NOT NULL DEFAULT 0,
    `soul` INT UNSIGNED NOT NULL DEFAULT 0,
    `direction` INT UNSIGNED NOT NULL DEFAULT 0,
    `lookbody` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookfeet` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookhead` INT UNSIGNED NOT NULL DEFAULT 10,
    `looklegs` INT UNSIGNED NOT NULL DEFAULT 10,
    `looktype` INT UNSIGNED NOT NULL DEFAULT 136,
    `lookaddons` INT UNSIGNED NOT NULL DEFAULT 0,
    `posx` INT NOT NULL DEFAULT 0,
    `posy` INT NOT NULL DEFAULT 0,
    `posz` INT NOT NULL DEFAULT 0,
    `cap` INT NOT NULL DEFAULT 0,
    `lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
    `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
    `save` TINYINT(1) NOT NULL DEFAULT TRUE,
    `conditions` BLOB NOT NULL COMMENT 'drunk, poisoned etc (maybe also food and redskull)',
    `redskulltime` INT UNSIGNED NOT NULL DEFAULT 0,
    `redskull` TINYINT(1) NOT NULL DEFAULT FALSE,
    `guildnick` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'additional nick in guild - mostly for web interfaces i think',
    `loss_experience` INT NOT NULL DEFAULT 10,
    `loss_mana` INT NOT NULL DEFAULT 10,
    `loss_skills` INT NOT NULL DEFAULT 10,
    `loss_items` INT NOT NULL DEFAULT 10,
    `rank_id` INT NOT NULL COMMENT 'by this field everything with guilds is done - player has a rank which belongs to certain guild',
    `town_id` INT NOT NULL COMMENT 'old masterpos, temple spawn point position',
    `needtobeteleported` INT NOT NULL,
    `ispromoted` INT NOT NULL,
    `havepromotion` INT NOT NULL,	
    `balance` INT NOT NULL DEFAULT 0 COMMENT 'money balance of the player for houses paying',
    PRIMARY KEY (`id`),
    UNIQUE (`name`),
    FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guilds` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'guild name - nothing else needed here',
    `ownerid` INT NOT NULL,
    `creationdata` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guild_ranks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',
    `name` VARCHAR(255) NOT NULL COMMENT 'rank name',
    `level` INT NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_viplist` (
    `player_id` INT UNSIGNED NOT NULL COMMENT 'id of player whose viplist entry it is',
    `vip_id` INT UNSIGNED NOT NULL COMMENT 'id of target player of viplist entry',
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`vip_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_spells` (
    `player_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_storage` (
    `player_id` INT UNSIGNED NOT NULL,
    `key` INT NOT NULL,
    `value` INT NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_skills` (
    `player_id` INT UNSIGNED NOT NULL,
    `skillid` INT UNSIGNED NOT NULL,
    `value` INT UNSIGNED NOT NULL DEFAULT 0,
    `count` INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_items` (
    `player_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB COMMENT 'replaces unique_id, action_id, text, special_desc',
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `houses` (
    `id` INT UNSIGNED NOT NULL,
    `owner` INT NOT NULL,
    `paid` INT UNSIGNED NOT NULL DEFAULT 0,
    `warnings` INT NOT NULL DEFAULT 0,
    `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `house_lists` (
    `house_id` INT UNSIGNED NOT NULL,
    `listid` INT NOT NULL,
    `list` TEXT NOT NULL,
    FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `bans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
    `value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number',
    `param` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'mask',
    `active` TINYINT(1) NOT NULL DEFAULT TRUE,
    `expires` INT UNSIGNED NOT NULL,
    `added` INT UNSIGNED NOT NULL,
    `admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
    `comment` VARCHAR(255) NOT NULL DEFAULT '',
    `reason` INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY  (`id`),
    KEY (`type`, `value`),
    KEY (`expires`)
) ENGINE = InnoDB;

CREATE TABLE `tiles` (
    `id` INT UNSIGNED NOT NULL,
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    `z` INT NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = InnoDB;

CREATE TABLE `tile_items` (
    `tile_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,
    FOREIGN KEY (`tile_id`) REFERENCES `tiles` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_depotitems` (
    `player_id` INT UNSIGNED NOT NULL,
    `depot_id` INT NOT NULL DEFAULT 0,
    `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',
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    INDEX (`player_id`, `depot_id`),
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `global_storage` (
    `key` INT UNSIGNED NOT NULL,
    `value` INT NOT NULL,
    PRIMARY KEY(`key`)
) ENGINE = InnoDB;

DELIMITER |

CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 3 AND `value` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_guilds`
BEFORE DELETE
ON `guilds`
FOR EACH ROW
BEGIN
    UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
    DELETE FROM `guild_ranks` WHERE `guild_id` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`;
    UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|

CREATE TRIGGER `oncreate_guilds`
AFTER INSERT
ON `guilds`
FOR EACH ROW
BEGIN
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
END|

CREATE TRIGGER `oncreate_players`
AFTER INSERT
ON `players`
FOR EACH ROW
BEGIN
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
END|

DELIMITER ;

 

[cigarwin]

chnage links form rapidshare?

 

[Atrix]

Error

SQL query: Edit

INSERT INTO `otserv`.`players` (
`id` ,
`name` ,
`account_id` ,
`group_id` ,
`sex` ,
`married` ,
`lover` ,
`testify` ,
`engaged` ,
`vocation` ,
`experience` ,
`level` ,
`maglevel` ,
`health` ,
`healthmax` ,
`mana` ,
`manamax` ,
`manaspent` ,
`soul` ,
`direction` ,
`lookbody` ,
`lookfeet` ,
`lookhead` ,
`looklegs` ,
`looktype` ,
xxxxxxx`lookaddons` , ERROR ON THIS!!!
`posx` ,
`posy` ,
`posz` ,
`cap` ,
`lastlogin` ,
`lastip` ,
`save` ,
`redskulltime` ,
`redskull` ,
`guildnick` ,
`loss_experience` ,
`loss_mana` ,
`loss_skills` ,
`loss_items` ,
`rank_id` ,
`town_id` ,
`needtobeteleported` ,
`ispromoted` ,
`havepromotion` ,
`balance`
)
VALUES (
NULL , '', '', '', '0', '0', '0', '0', '0', '0', '0', '1', '0', '100', '100', '100', '100', '0', '0', '0', '10', '10', '10', '10', '136', '0', '0', '0', '0', '0', '0', '0', '1', '0', '0', '', '10', '10', '10', '10', '', '', '', '', '', '0'
)

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`otserv/players`, CONSTRAINT `players_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE)

ERROR IS ON ADDONS!!! hihihiihihihihih hot to fix that???

 

[justin234]

What distro is the best 8.21?
I cant tell lol

 

[Pisikopat]

have debug database

 

[SnowFox]

grr i hate rapid share but thanks alot next time use Speedyshare FTW!

 

[gm neo colombia]

New UPDATE!!!
Support Protocol 8.21/8.22

 

[Mikelon]

IT STILL HAVE THIS BUG
IN THE DATABASE ... (IT SUX THIS SERV..)

Player::make() Cannot insert attributes:
Error #1452
INSERT INTO `players` (`id`,`name`,`account_id`,`vocation`,`sex`,`level`,`experience`,`health`,`healthmax`,`looktype`,`maglevel`,`mana`,`manamax`,`cap`,`town_id`,`posx`,`posy`,`posz`) VALUES (NULL,'Mikelon',721578,1,1,8,4200,185,185,130,0,40,40,470,1,410,573,7);
Cannot add or update a child row: a foreign key constraint fails (`otserv/players`, CONSTRAINT `players_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`))

player.php on line: 189
Script was terminated because something unexpected happened. You can report this, if you think it's a bug.

 

[gm neo colombia]

@ Mikelon

You are stupid?
You need create groups_id <_<

Noob!

 

[Xylio]

Account Manager not working ... "111111","tibia" Account number or password is not correct ?? i create database and groups_id

@edit:
ok i will try

 

[gm neo colombia]

you need create account manager in database...

 

[papas]

how need create account manager in database??

 

[gm neo colombia]

@ papas

Yes.. you need insert data of account manager in you database...

@ New UPDATE!

 

[Xylio]

Ok i create account manager and this manager create only account no characters and can't change password on account where is problem ??
@Down:
SQL

 

[koitsalu]

Hey folks, is this XML or SQL?

 

[gm neo colombia]

ONLY MySQL

 

[zaczku]

Love you man, thank you so much!

 

[Noxus]

Nice Server

 

[Mikelon]

em you sux i already created ^^ you fking noob