SQL for the db schema of SunDofus

SQL Schema

-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'characters'
-- Holds character information
-- ---

DROP TABLE IF EXISTS `characters`;
		
CREATE TABLE `characters` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL DEFAULT 'NULL',
  `level` INT(11) NOT NULL DEFAULT 1,
  `class` INT(11) NOT NULL DEFAULT 0,
  `gender` INT(11) NOT NULL DEFAULT 0,
  `color_1` INT(11) NOT NULL DEFAULT -1,
  `color_2` INT(11) NOT NULL DEFAULT -1,
  `color_3` INT(11) NOT NULL DEFAULT -1,
  `map_id` INT(11) NOT NULL DEFAULT 0,
  `map_cell` INT(11) NOT NULL DEFAULT 0,
  `map_dir` INT(11) NOT NULL DEFAULT 0,
  `health` INT(11) NOT NULL DEFAULT 0,
  `energy` INT(11) NOT NULL DEFAULT 0,
  `vitality` INT(11) NOT NULL DEFAULT 0,
  `wisdom` INT(11) NOT NULL DEFAULT 0,
  `force` INT(11) NOT NULL DEFAULT 0,
  `intelligence` INT(11) NOT NULL DEFAULT 0,
  `luck` INT(11) NOT NULL DEFAULT 0,
  `agility` INT(11) NOT NULL DEFAULT 0,
  `statpoints` INT(11) NOT NULL DEFAULT 0,
  `spellpoints` INT(11) NOT NULL DEFAULT 0,
  `kamas` BIGINT(100) NOT NULL DEFAULT 0,
  `experience` BIGINT(100) NOT NULL DEFAULT 0,
  `bank_money` INT(11) NOT NULL DEFAULT 0,
  `save_map` INT(11) NOT NULL DEFAULT 0,
  `save_cell` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) COMMENT 'Holds character information';

-- ---
-- Table 'item_instance'
-- 
-- ---

DROP TABLE IF EXISTS `item_instance`;
		
CREATE TABLE `item_instance` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `itemEntry` INT(11) NOT NULL DEFAULT 0,
  `owner` INT(11) NOT NULL DEFAULT 0,
  `count` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
);

-- ---
-- Table 'character_inventory'
-- 
-- ---

DROP TABLE IF EXISTS `character_inventory`;
		
CREATE TABLE `character_inventory` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `position` INT(11) NOT NULL DEFAULT 0,
  `item` INT(11) NOT NULL DEFAULT 0,
  `bank` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is the item in the bank?',
  PRIMARY KEY (`guid`, `position`)
);

-- ---
-- Table 'item_instance_effects'
-- Holds the effects of the items
-- ---

DROP TABLE IF EXISTS `item_instance_effects`;
		
CREATE TABLE `item_instance_effects` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `id` INT(11) NOT NULL DEFAULT 0,
  `effect` VARCHAR(255) NOT NULL,
  `value_1` INT(11) NOT NULL DEFAULT 0,
  `value_2` INT(11) NOT NULL DEFAULT 0,
  `value_3` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`, `id`)
) COMMENT 'Holds the effects of the items';

-- ---
-- Table 'character_alignment'
-- Holds alignment/faction info
-- ---

DROP TABLE IF EXISTS `character_alignment`;
		
CREATE TABLE `character_alignment` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `faction` INT(11) NOT NULL DEFAULT 0,
  `honor` INT(11) NOT NULL DEFAULT 0,
  `deshonor` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) COMMENT 'Holds alignment/faction info';

-- ---
-- Table 'character_spells'
-- 
-- ---

DROP TABLE IF EXISTS `character_spells`;
		
CREATE TABLE `character_spells` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `spellId` INT(11) NOT NULL DEFAULT 1,
  `level` INT(11) NOT NULL DEFAULT 0,
  `position` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`, `spellId`)
);

-- ---
-- Table 'character_zaaps'
-- Stores the zaaps that a character has saved
-- ---

DROP TABLE IF EXISTS `character_zaaps`;
		
CREATE TABLE `character_zaaps` (
  `guid` INT(11) NOT NULL DEFAULT 0,
  `zaap` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) COMMENT 'Stores the zaaps that a character has saved';

-- ---
-- Table 'collectors'
-- Collectors for the guilds
-- ---

DROP TABLE IF EXISTS `collectors`;
		
CREATE TABLE `collectors` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name_0` INT(11) NOT NULL DEFAULT 0,
  `name_1` INT(11) NOT NULL DEFAULT 0,
  `owner` INT(11) NOT NULL DEFAULT 0,
  `guild` INT(11) NOT NULL DEFAULT 0,
  `map` INT(11) NOT NULL DEFAULT 0,
  `cell` INT(11) NOT NULL DEFAULT 0,
  `dir` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Collectors for the guilds';

-- ---
-- Table 'creature_template'
-- Stores static information about creatures (monsters)
-- ---

DROP TABLE IF EXISTS `creature_template`;
		
CREATE TABLE `creature_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL DEFAULT 'NULL',
  `gfx` INT(11) NOT NULL DEFAULT 0,
  `alignment` INT(11) NOT NULL DEFAULT -1,
  `color_1` INT(11) NOT NULL DEFAULT -1,
  `color_2` INT(11) NOT NULL DEFAULT -1,
  `color_3` INT(11) NOT NULL DEFAULT -1,
  `ai_type` TINYINT(4) NOT NULL DEFAULT 1,
  `minkamas` INT(11) NOT NULL DEFAULT 0,
  `maxkamas` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Stores static information about creatures (monsters)';

-- ---
-- Table 'creature_loot_template'
-- Loot info for creatures
-- ---

DROP TABLE IF EXISTS `creature_loot_template`;
		
CREATE TABLE `creature_loot_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `item` INT(11) NOT NULL,
  `chance` INT(11) NOT NULL DEFAULT 0,
  `maxcount` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`, `item`)
) COMMENT 'Loot info for creatures';

-- ---
-- Table 'authservers'
-- Holds the authservers passkey and id
-- ---

DROP TABLE IF EXISTS `authservers`;
		
CREATE TABLE `authservers` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `ip` VARCHAR(255) NOT NULL DEFAULT 'NULL',
  `port` INT(11) NOT NULL DEFAULT 0,
  `passkey` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Holds the authservers passkey and id';

-- ---
-- Table 'creature_levels'
-- Stat info for creatures of every level
-- ---

DROP TABLE IF EXISTS `creature_levels`;
		
CREATE TABLE `creature_levels` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `creatureId` INT(11) NOT NULL DEFAULT 0,
  `grade` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  `PA` INT(11) NOT NULL DEFAULT 0,
  `PM` INT(11) NOT NULL DEFAULT 0,
  `hp` INT(11) NOT NULL DEFAULT 0,
  `resist_neutral` INT(11) NOT NULL DEFAULT 0,
  `resist_earth` INT(11) NOT NULL DEFAULT 0,
  `resist_fire` INT(11) NOT NULL DEFAULT 0,
  `resist_water` INT(11) NOT NULL DEFAULT 0,
  `resist_air` INT(11) NOT NULL DEFAULT 0,
  `resist_pa` INT(11) NOT NULL DEFAULT 0,
  `resist_pm` INT(11) NOT NULL DEFAULT 0,
  `wisdom` INT(11) NOT NULL DEFAULT 0,
  `force` INT(11) NOT NULL DEFAULT 0,
  `intelligence` INT(11) NOT NULL DEFAULT 0,
  `luck` INT(11) NOT NULL DEFAULT 0,
  `agility` INT(11) NOT NULL DEFAULT 0,
  `experience` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Stat info for creatures of every level';

-- ---
-- Table 'creature_level_spells'
-- Spells for every creature level
-- ---

DROP TABLE IF EXISTS `creature_level_spells`;
		
CREATE TABLE `creature_level_spells` (
  `level_id` INT(11) NOT NULL DEFAULT 0,
  `spell` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`level_id`, `spell`)
) COMMENT 'Spells for every creature level';

-- ---
-- Table 'guild_emblem'
-- 
-- ---

DROP TABLE IF EXISTS `guild_emblem`;
		
CREATE TABLE `guild_emblem` (
  `guild` INT(11) NOT NULL DEFAULT 0,
  `backgroundId` INT(11) NOT NULL DEFAULT 0,
  `backgroundColor` INT(11) NOT NULL DEFAULT 0,
  `emblemId` INT(11) NOT NULL DEFAULT 0,
  `emblemColor` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guild`)
);

-- ---
-- Table 'guilds'
-- 
-- ---

DROP TABLE IF EXISTS `guilds`;
		
CREATE TABLE `guilds` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  `level` INT(11) NOT NULL DEFAULT 0,
  `experience` BIGINT(110) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'guild_member'
-- Stores members of the guilds
-- ---

DROP TABLE IF EXISTS `guild_member`;
		
CREATE TABLE `guild_member` (
  `guild` INT(11) NOT NULL DEFAULT 0,
  `member` INT(11) NOT NULL DEFAULT 0,
  `rank` INT(11) NOT NULL DEFAULT 0,
  `rights` INT(11) NOT NULL DEFAULT 0,
  `exp_given` INT(11) NOT NULL DEFAULT 0,
  `exp_pct` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guild`, `member`)
) COMMENT 'Stores members of the guilds';

-- ---
-- Table 'guild_stats'
-- Stat information for guilds
-- ---

DROP TABLE IF EXISTS `guild_stats`;
		
CREATE TABLE `guild_stats` (
  `guild` INT(11) NOT NULL DEFAULT 0,
  `max_collectors` INT(11) NOT NULL DEFAULT 0,
  `collector_pods` INT(11) NOT NULL DEFAULT 0,
  `collector_prospection` INT(11) NOT NULL DEFAULT 0,
  `collector_wisdom` INT(11) NOT NULL DEFAULT 0,
  `points` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guild`)
) COMMENT 'Stat information for guilds';

-- ---
-- Table 'collector_spells'
-- Spells for the collectors of the guilds
-- ---

DROP TABLE IF EXISTS `collector_spells`;
		
CREATE TABLE `collector_spells` (
  `guild` INT(11) NOT NULL DEFAULT 0,
  `spell` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`guild`, `spell`)
) COMMENT 'Spells for the collectors of the guilds';

-- ---
-- Table 'item_template'
-- Item template information
-- ---

DROP TABLE IF EXISTS `item_template`;
		
CREATE TABLE `item_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  `description` BLOB NOT NULL,
  `type` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  `weight` INT(11) NOT NULL DEFAULT 0,
  `price` INT(11) NOT NULL DEFAULT 0,
  `gfx` INT(11) NOT NULL DEFAULT 0,
  `consumes` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is destroyed after use',
  `usable` TINYINT(1) NOT NULL DEFAULT 0,
  `conditions` BLOB NOT NULL COMMENT 'ToDo: Remove this once the general Conditions system is impl',
  `weapons_info` BLOB NOT NULL COMMENT 'ToDo: Figure out what this is and separate it into another t',
  PRIMARY KEY (`id`)
) COMMENT 'Item template information';

-- ---
-- Table 'item_effect_template'
-- 
-- ---

DROP TABLE IF EXISTS `item_effect_template`;
		
CREATE TABLE `item_effect_template` (
  `item` INT(11) NOT NULL DEFAULT 0,
  `id` INT(11) NOT NULL DEFAULT 0,
  `effect` VARCHAR(255) NOT NULL,
  `value_1` INT(11) NOT NULL DEFAULT 0,
  `value_2` INT(11) NOT NULL DEFAULT 0,
  `value_3` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`, `item`)
);

-- ---
-- Table 'item_sets'
-- Item set definition
-- ---

DROP TABLE IF EXISTS `item_sets`;
		
CREATE TABLE `item_sets` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Item set definition';

-- ---
-- Table 'item_set_items'
-- List of items of each itemset
-- ---

DROP TABLE IF EXISTS `item_set_items`;
		
CREATE TABLE `item_set_items` (
  `itemset` INT(11) NOT NULL DEFAULT 0,
  `item` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`itemset`, `item`)
) COMMENT 'List of items of each itemset';

-- ---
-- Table 'item_set_bonus'
-- Bonuses for the itemsets
-- ---

DROP TABLE IF EXISTS `item_set_bonus`;
		
CREATE TABLE `item_set_bonus` (
  `itemset` INT(11) NOT NULL DEFAULT 0,
  `pieces` INT(11) NOT NULL DEFAULT 2,
  `effect` INT(11) NOT NULL DEFAULT 0,
  `value` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`itemset`, `pieces`, `effect`)
) COMMENT 'Bonuses for the itemsets';

-- ---
-- Table 'usable_item_effects'
-- Holds info about which items are usable and what their effects are.
-- ---

DROP TABLE IF EXISTS `usable_item_effects`;
		
CREATE TABLE `usable_item_effects` (
  `item` INT(11) NOT NULL DEFAULT 0,
  `effect` INT(11) NOT NULL DEFAULT 0,
  `value_1` INT(11) NOT NULL DEFAULT 0,
  `value_2` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`item`, `effect`)
) COMMENT 'Holds info about which items are usable and what their effec';

-- ---
-- Table 'level_info'
-- Experience requirements for each level
-- ---

DROP TABLE IF EXISTS `level_info`;
		
CREATE TABLE `level_info` (
  `level` INT(11) NOT NULL DEFAULT 0,
  `character` BIGINT(110) NOT NULL DEFAULT 0,
  `job` BIGINT(110) NOT NULL DEFAULT 0,
  `mount` BIGINT(110) NOT NULL DEFAULT 0,
  `alignment` BIGINT(110) NOT NULL DEFAULT 0,
  `living` BIGINT(110) NOT NULL DEFAULT 0,
  `incarnation` BIGINT(110) NOT NULL DEFAULT 0,
  `guild` BIGINT(110) NOT NULL DEFAULT 0,
  PRIMARY KEY (`level`)
) COMMENT 'Experience requirements for each level';

-- ---
-- Table 'zaaps'
-- Zaap templates
-- ---

DROP TABLE IF EXISTS `zaaps`;
		
CREATE TABLE `zaaps` (
  `map` INT(11) NOT NULL DEFAULT 0,
  `cell` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`map`)
) COMMENT 'Zaap templates';

-- ---
-- Table 'zaapis'
-- Zaapi templates
-- ---

DROP TABLE IF EXISTS `zaapis`;
		
CREATE TABLE `zaapis` (
  `map` INT(11) NOT NULL DEFAULT 0,
  `cell` INT(11) NOT NULL DEFAULT 0,
  `zone` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`map`)
) COMMENT 'Zaapi templates';

-- ---
-- Table 'spells_learn_level'
-- Spells and their level requirements
-- ---

DROP TABLE IF EXISTS `spells_learn_level`;
		
CREATE TABLE `spells_learn_level` (
  `class` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  `spell` INT(11) NOT NULL DEFAULT 0,
  `position` INT(11) NOT NULL DEFAULT -1,
  PRIMARY KEY (`class`, `level`, `spell`)
) COMMENT 'Spells and their level requirements';

-- ---
-- Table 'map_template'
-- Maps
-- ---

DROP TABLE IF EXISTS `map_template`;
		
CREATE TABLE `map_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `date` INT(11) NOT NULL DEFAULT 0,
  `width` INT(11) NOT NULL DEFAULT 0,
  `height` INT(11) NOT NULL DEFAULT 0,
  `key` BLOB NOT NULL,
  `data` BLOB NOT NULL,
  `capabilities` INT(11) NOT NULL DEFAULT 0,
  `groups` INT(11) NOT NULL DEFAULT 0,
  `groupsize` INT(11) NOT NULL DEFAULT 0,
  `pos_x` INT(11) NOT NULL DEFAULT 0,
  `pos_y` INT(11) NOT NULL DEFAULT 0,
  `subarea` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Maps';

-- ---
-- Table 'creature_spawns'
-- Holds information about which creatures are spawned on which maps, the emulator will pick a random creature and then pick a random level from that creature to build the mob lists
-- ---

DROP TABLE IF EXISTS `creature_spawns`;
		
CREATE TABLE `creature_spawns` (
  `map` INT(11) NOT NULL DEFAULT 0,
  `creature` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`map`, `creature`, `level`)
) COMMENT 'Holds information about which creatures are spawned on which';

-- ---
-- Table 'npc_template'
-- Holds template and spawn information about non-player characters
-- ---

DROP TABLE IF EXISTS `npc_template`;
		
CREATE TABLE `npc_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  `gfx` INT(11) NOT NULL DEFAULT 0,
  `size` INT(11) NOT NULL DEFAULT 0,
  `sex` TINYINT(1) NOT NULL DEFAULT 0,
  `color_1` INT(11) NOT NULL DEFAULT -1,
  `color_2` INT(11) NOT NULL DEFAULT -1,
  `color_3` INT(11) NOT NULL DEFAULT -1,
  `gossip_menu` INT(11) NOT NULL DEFAULT -1,
  `map` INT(11) NOT NULL DEFAULT 0,
  `cell` INT(11) NOT NULL DEFAULT 0,
  `dir` INT(11) NOT NULL DEFAULT 0,
  `movement` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Should the NPC move',
  PRIMARY KEY (`id`)
) COMMENT 'Holds template and spawn information about non-player charac';

-- ---
-- Table 'npc_vendor'
-- Holds information about which items a specific NPC sells
-- ---

DROP TABLE IF EXISTS `npc_vendor`;
		
CREATE TABLE `npc_vendor` (
  `npc` INT(11) NOT NULL DEFAULT 0,
  `item` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`npc`, `item`)
) COMMENT 'Holds information about which items a specific NPC sells';

-- ---
-- Table 'npc_equip_template'
-- Holds information about the visual items that an NPC wears in the map.
-- ---

DROP TABLE IF EXISTS `npc_equip_template`;
		
CREATE TABLE `npc_equip_template` (
  `npc` INT(11) NOT NULL DEFAULT 0,
  `item` INT(11) NOT NULL DEFAULT 0 COMMENT 'Has to be converted to hex',
  `slot` INT(11) NOT NULL DEFAULT 0 COMMENT 'The table will be sorted by this field',
  PRIMARY KEY (`npc`, `item`, `slot`)
) COMMENT 'Holds information about the visual items that an NPC wears i';

-- ---
-- Table 'gossip_menu'
-- Holds gossip information
-- ---

DROP TABLE IF EXISTS `gossip_menu`;
		
CREATE TABLE `gossip_menu` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `conditions` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Holds gossip information';

-- ---
-- Table 'gossip_menu_params'
-- Player parameters for gossip menus
-- ---

DROP TABLE IF EXISTS `gossip_menu_params`;
		
CREATE TABLE `gossip_menu_params` (
  `menu_id` INT(11) NOT NULL DEFAULT 0,
  `param` INT(11) NOT NULL DEFAULT 0,
  `slot` INT(11) NOT NULL DEFAULT 0 COMMENT 'The table will be ordered by this field',
  PRIMARY KEY (`menu_id`, `param`)
) COMMENT 'Player parameters for gossip menus';

-- ---
-- Table 'gossip_menu_options'
-- Possible answers to the gossip menus
-- ---

DROP TABLE IF EXISTS `gossip_menu_options`;
		
CREATE TABLE `gossip_menu_options` (
  `menu_id` INT(11) NOT NULL DEFAULT 0,
  `answer_id` INT(11) NOT NULL DEFAULT 0,
  `conditions` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`menu_id`, `answer_id`)
) COMMENT 'Possible answers to the gossip menus';

-- ---
-- Table 'gossip_menu_option_effects'
-- Effects applied to the player when a gossip answer is selected
-- ---

DROP TABLE IF EXISTS `gossip_menu_option_effects`;
		
CREATE TABLE `gossip_menu_option_effects` (
  `option_id` INT(11) NOT NULL DEFAULT 0,
  `effect` INT(11) NOT NULL DEFAULT 0,
  `value_1` INT(11) NOT NULL DEFAULT 0,
  `value_2` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`option_id`)
) COMMENT 'Effects applied to the player when a gossip answer is select';

-- ---
-- Table 'triggers'
-- Triggers on the maps
-- ---

DROP TABLE IF EXISTS `triggers`;
		
CREATE TABLE `triggers` (
  `map` INT(11) NOT NULL DEFAULT 0,
  `cell` INT(11) NOT NULL DEFAULT 0,
  `action` INT(11) NOT NULL DEFAULT 0,
  `value_1` INT(11) NOT NULL DEFAULT 0,
  `value_2` INT(11) NOT NULL DEFAULT 0,
  `conditions` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`map`)
) COMMENT 'Triggers on the maps';

-- ---
-- Table 'spells_template'
-- Spell template information
-- ---

DROP TABLE IF EXISTS `spells_template`;
		
CREATE TABLE `spells_template` (
  `id` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  `sprite` INT(11) NOT NULL DEFAULT -1,
  `sprite_param_1` INT(11) NOT NULL DEFAULT 0,
  `sprite_param_2` INT(11) NOT NULL DEFAULT 0,
  `sprite_param_3` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Spell template information';

-- ---
-- Table 'spells_level_template'
-- Information about spells at a certain level
-- ---

DROP TABLE IF EXISTS `spells_level_template`;
		
CREATE TABLE `spells_level_template` (
  `spell` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 1,
  `cost` INT(11) NOT NULL DEFAULT 6,
  `range_min` INT(11) NOT NULL DEFAULT 0,
  `range_max` INT(11) NOT NULL DEFAULT 0,
  `alterable_range` TINYINT(1) NOT NULL DEFAULT 0,
  `crit_chance` INT(11) NOT NULL DEFAULT 0,
  `fail_chance` INT(11) NOT NULL DEFAULT 0,
  `in_line` TINYINT(1) NOT NULL DEFAULT 0,
  `in_view_line` TINYINT(1) NOT NULL DEFAULT 0,
  `max_per_turn` INT(11) NOT NULL DEFAULT 0,
  `max_per_player` INT(11) NOT NULL DEFAULT 0,
  `cooldown` INT(11) NOT NULL DEFAULT 0,
  `radius` INT(11) NOT NULL DEFAULT 0,
  `fail_end_turn` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`spell`, `level`)
) COMMENT 'Information about spells at a certain level';

-- ---
-- Table 'spells_effect_template'
-- Template effects for spells
-- ---

DROP TABLE IF EXISTS `spells_effect_template`;
		
CREATE TABLE `spells_effect_template` (
  `spell` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 0,
  `critical` TINYINT(1) NOT NULL DEFAULT 0,
  `id` INT(11) NOT NULL DEFAULT 0,
  `round` INT(11) NOT NULL DEFAULT 0,
  `chance` INT(11) NOT NULL DEFAULT 0,
  `target` INT(11) NOT NULL DEFAULT 23,
  `effect` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`spell`, `level`, `critical`, `id`)
) COMMENT 'Template effects for spells';

-- ---
-- Table Properties
-- ---

ALTER TABLE `characters` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_instance` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `character_inventory` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_instance_effects` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `character_alignment` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `character_spells` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `character_zaaps` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `collectors` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `creature_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `creature_loot_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `authservers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `creature_levels` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `creature_level_spells` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `guild_emblem` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `guilds` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `guild_member` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `guild_stats` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `collector_spells` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_effect_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_sets` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_set_items` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `item_set_bonus` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `usable_item_effects` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `level_info` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `zaaps` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `zaapis` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `spells_learn_level` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `map_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `creature_spawns` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `npc_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `npc_vendor` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `npc_equip_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `gossip_menu` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `gossip_menu_params` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `gossip_menu_options` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `gossip_menu_option_effects` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `triggers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `spells_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `spells_level_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `spells_effect_template` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `characters` ADD FOREIGN KEY (map_id) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `characters` ADD FOREIGN KEY (save_map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_instance` ADD FOREIGN KEY (itemEntry) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_instance` ADD FOREIGN KEY (owner) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_inventory` ADD FOREIGN KEY (guid) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_inventory` ADD FOREIGN KEY (item) REFERENCES `item_instance` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_instance_effects` ADD FOREIGN KEY (guid) REFERENCES `item_instance` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_alignment` ADD FOREIGN KEY (guid) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_spells` ADD FOREIGN KEY (guid) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_spells` ADD FOREIGN KEY (spellId) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_zaaps` ADD FOREIGN KEY (guid) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `character_zaaps` ADD FOREIGN KEY (zaap) REFERENCES `zaaps` (`map`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `collectors` ADD FOREIGN KEY (owner) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `collectors` ADD FOREIGN KEY (guild) REFERENCES `guilds` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `collectors` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_loot_template` ADD FOREIGN KEY (id) REFERENCES `creature_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_loot_template` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_levels` ADD FOREIGN KEY (creatureId) REFERENCES `creature_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_level_spells` ADD FOREIGN KEY (level_id) REFERENCES `creature_levels` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_level_spells` ADD FOREIGN KEY (spell) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `guild_emblem` ADD FOREIGN KEY (guild) REFERENCES `guilds` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `guild_member` ADD FOREIGN KEY (guild) REFERENCES `guilds` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `guild_member` ADD FOREIGN KEY (member) REFERENCES `characters` (`guid`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `guild_stats` ADD FOREIGN KEY (guild) REFERENCES `guilds` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `collector_spells` ADD FOREIGN KEY (guild) REFERENCES `guilds` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `collector_spells` ADD FOREIGN KEY (spell) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_effect_template` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_set_items` ADD FOREIGN KEY (itemset) REFERENCES `item_sets` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_set_items` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `item_set_bonus` ADD FOREIGN KEY (itemset) REFERENCES `item_sets` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `usable_item_effects` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `zaaps` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `zaapis` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `spells_learn_level` ADD FOREIGN KEY (spell) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_spawns` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `creature_spawns` ADD FOREIGN KEY (creature) REFERENCES `creature_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_template` ADD FOREIGN KEY (gossip_menu) REFERENCES `gossip_menu` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_template` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_vendor` ADD FOREIGN KEY (npc) REFERENCES `npc_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_vendor` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_equip_template` ADD FOREIGN KEY (npc) REFERENCES `npc_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `npc_equip_template` ADD FOREIGN KEY (item) REFERENCES `item_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `gossip_menu_params` ADD FOREIGN KEY (menu_id) REFERENCES `gossip_menu` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `gossip_menu_options` ADD FOREIGN KEY (menu_id) REFERENCES `gossip_menu` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `triggers` ADD FOREIGN KEY (map) REFERENCES `map_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `spells_level_template` ADD FOREIGN KEY (spell) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `spells_effect_template` ADD FOREIGN KEY (spell) REFERENCES `spells_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;