API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 22nd, 2014
249
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 18.14 KB | None | 0 0
raw download clone embed print report
  1. DROP TRIGGER IF EXISTS `oncreate_players`;
  2. DROP TRIGGER IF EXISTS `oncreate_guilds`;
  3. DROP TRIGGER IF EXISTS `ondelete_players`;
  4. DROP TRIGGER IF EXISTS `ondelete_guilds`;
  5. DROP TRIGGER IF EXISTS `ondelete_accounts`;
  6.  
  7. DROP TABLE IF EXISTS `player_depotitems`;
  8. DROP TABLE IF EXISTS `tile_items`;
  9. DROP TABLE IF EXISTS `tiles`;
  10. DROP TABLE IF EXISTS `bans`;
  11. DROP TABLE IF EXISTS `house_lists`;
  12. DROP TABLE IF EXISTS `houses`;
  13. DROP TABLE IF EXISTS `player_items`;
  14. DROP TABLE IF EXISTS `player_namelocks`;
  15. DROP TABLE IF EXISTS `player_skills`;
  16. DROP TABLE IF EXISTS `player_storage`;
  17. DROP TABLE IF EXISTS `player_viplist`;
  18. DROP TABLE IF EXISTS `player_spells`;
  19. DROP TABLE IF EXISTS `player_deaths`;
  20. DROP TABLE IF EXISTS `killers`;
  21. DROP TABLE IF EXISTS `environment_killers`;
  22. DROP TABLE IF EXISTS `player_killers`;
  23. DROP TABLE IF EXISTS `guild_ranks`;
  24. DROP TABLE IF EXISTS `guilds`;
  25. DROP TABLE IF EXISTS `guild_invites`;
  26. DROP TABLE IF EXISTS `global_storage`;
  27. DROP TABLE IF EXISTS `players`;
  28. DROP TABLE IF EXISTS `accounts`;
  29. DROP TABLE IF EXISTS `server_record`;
  30. DROP TABLE IF EXISTS `server_motd`;
  31. DROP TABLE IF EXISTS `server_reports`;
  32. DROP TABLE IF EXISTS `server_config`;
  33. DROP TABLE IF EXISTS `account_viplist`;
  34. DROP TABLE IF EXISTS `poll_votes`;
  35. DROP TABLE IF EXISTS `polls`;
  36.  
  37. CREATE TABLE `accounts`
  38. (
  39.         `id` INT NOT NULL AUTO_INCREMENT,
  40.         `name` VARCHAR(32) NOT NULL DEFAULT '',
  41.         `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
  42.         `premdays` INT NOT NULL DEFAULT 0,
  43.         `lastday` INT UNSIGNED NOT NULL DEFAULT 0,
  44.         `email` VARCHAR(255) NOT NULL DEFAULT '',
  45.         `key` VARCHAR(20) NOT NULL DEFAULT '0',
  46.         `blocked` TINYINT(1) NOT NULL DEFAULT FALSE COMMENT 'internal usage',
  47.         `warnings` INT NOT NULL DEFAULT 0,
  48.         `group_id` INT NOT NULL DEFAULT 1,
  49.         `vip_time` INT(15) NOT NULL DEFAULT 0,
  50.         PRIMARY KEY (`id`), UNIQUE (`name`)
  51. ) ENGINE = InnoDB;
  52.  
  53. INSERT INTO `accounts` VALUES (1, '1', '1', 65535, 0, '', '0', 0, 0, 1, 0);
  54.  
  55. CREATE TABLE `players`
  56. (
  57.         `id` INT NOT NULL AUTO_INCREMENT,
  58.         `name` VARCHAR(255) NOT NULL,
  59.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  60.         `group_id` INT NOT NULL DEFAULT 1,
  61.         `account_id` INT NOT NULL DEFAULT 0,
  62.         `level` INT NOT NULL DEFAULT 1,
  63.         `vocation` INT NOT NULL DEFAULT 0,
  64.         `health` INT NOT NULL DEFAULT 150,
  65.         `healthmax` INT NOT NULL DEFAULT 150,
  66.         `experience` BIGINT NOT NULL DEFAULT 0,
  67.         `lookbody` INT NOT NULL DEFAULT 0,
  68.         `lookfeet` INT NOT NULL DEFAULT 0,
  69.         `lookhead` INT NOT NULL DEFAULT 0,
  70.         `looklegs` INT NOT NULL DEFAULT 0,
  71.         `looktype` INT NOT NULL DEFAULT 136,
  72.         `lookaddons` INT NOT NULL DEFAULT 0,
  73.         `maglevel` INT NOT NULL DEFAULT 0,
  74.         `mana` INT NOT NULL DEFAULT 0,
  75.         `manamax` INT NOT NULL DEFAULT 0,
  76.         `manaspent` INT NOT NULL DEFAULT 0,
  77.         `soul` INT UNSIGNED NOT NULL DEFAULT 0,
  78.         `town_id` INT NOT NULL DEFAULT 0,
  79.         `posx` INT NOT NULL DEFAULT 0,
  80.         `posy` INT NOT NULL DEFAULT 0,
  81.         `posz` INT NOT NULL DEFAULT 0,
  82.         `conditions` BLOB NOT NULL,
  83.         `cap` INT NOT NULL DEFAULT 0,
  84.         `sex` INT NOT NULL DEFAULT 0,
  85.         `lastlogin` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  86.         `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
  87.         `save` TINYINT(1) NOT NULL DEFAULT 1,
  88.         `skull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  89.         `skulltime` INT NOT NULL DEFAULT 0,
  90.         `rank_id` INT NOT NULL DEFAULT 0,
  91.         `guildnick` VARCHAR(255) NOT NULL DEFAULT '',
  92.         `lastlogout` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  93.         `blessings` TINYINT(2) NOT NULL DEFAULT 0,
  94.         `balance` BIGINT NOT NULL DEFAULT 0,
  95.         `stamina` BIGINT NOT NULL DEFAULT 151200000 COMMENT 'stored in miliseconds',
  96.         `direction` INT NOT NULL DEFAULT 2,
  97.         `loss_experience` INT NOT NULL DEFAULT 100,
  98.         `loss_mana` INT NOT NULL DEFAULT 100,
  99.         `loss_skills` INT NOT NULL DEFAULT 100,
  100.         `loss_containers` INT NOT NULL DEFAULT 100,
  101.         `loss_items` INT NOT NULL DEFAULT 100,
  102.         `premend` INT NOT NULL DEFAULT 0 COMMENT 'NOT IN USE BY THE SERVER',
  103.         `online` TINYINT(1) NOT NULL DEFAULT 0,
  104.         `marriage` INT UNSIGNED NOT NULL DEFAULT 0,
  105.         `marrystatus` INT UNSIGNED NOT NULL DEFAULT 0,
  106.         `promotion` INT NOT NULL DEFAULT 0,
  107.         `deleted` TINYINT(1) NOT NULL DEFAULT FALSE,
  108.         `description` VARCHAR(255) NOT NULL DEFAULT '',
  109.         PRIMARY KEY (`id`), UNIQUE (`name`, `deleted`),
  110.         KEY (`account_id`), KEY (`group_id`),
  111.         KEY (`online`), KEY (`deleted`),
  112.         FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
  113. ) ENGINE = InnoDB;
  114.  
  115. INSERT INTO `players` VALUES (1, 'Account Manager', 0, 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 32360, 31782, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, '');
  116.  
  117. CREATE TABLE `account_viplist`
  118. (
  119.         `account_id` INT NOT NULL,
  120.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  121.         `player_id` INT NOT NULL,
  122.         KEY (`account_id`), KEY (`player_id`), KEY (`world_id`), UNIQUE (`account_id`, `player_id`),
  123.         FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
  124.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  125. ) ENGINE = InnoDB;
  126.  
  127. CREATE TABLE `player_deaths`
  128. (
  129.         `id` INT NOT NULL AUTO_INCREMENT,
  130.         `player_id` INT NOT NULL,
  131.         `date` BIGINT UNSIGNED NOT NULL,
  132.         `level` INT UNSIGNED NOT NULL,
  133.         PRIMARY KEY (`id`), INDEX (`date`),
  134.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  135. ) ENGINE = InnoDB;
  136.  
  137. CREATE TABLE `player_depotitems`
  138. (
  139.         `player_id` INT NOT NULL,
  140.         `sid` INT NOT NULL COMMENT 'any given range, eg. 0-100 is reserved for depot lockers and all above 100 will be normal items inside depots',
  141.         `pid` INT NOT NULL DEFAULT 0,
  142.         `itemtype` INT NOT NULL,
  143.         `count` INT NOT NULL DEFAULT 0,
  144.         `attributes` BLOB NOT NULL,
  145.         KEY (`player_id`), UNIQUE (`player_id`, `sid`),
  146.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  147. ) ENGINE = InnoDB;
  148.  
  149. CREATE TABLE `player_items`
  150. (
  151.         `player_id` INT NOT NULL DEFAULT 0,
  152.         `pid` INT NOT NULL DEFAULT 0,
  153.         `sid` INT NOT NULL DEFAULT 0,
  154.         `itemtype` INT NOT NULL DEFAULT 0,
  155.         `count` INT NOT NULL DEFAULT 0,
  156.         `attributes` BLOB NOT NULL,
  157.         KEY (`player_id`), UNIQUE (`player_id`, `sid`),
  158.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  159. ) ENGINE = InnoDB;
  160.  
  161. CREATE TABLE `player_namelocks`
  162. (
  163.         `player_id` INT NOT NULL DEFAULT 0,
  164.         `name` VARCHAR(255) NOT NULL,
  165.         `new_name` VARCHAR(255) NOT NULL,
  166.         `date` BIGINT NOT NULL DEFAULT 0,
  167.         KEY (`player_id`),
  168.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  169. ) ENGINE = InnoDB;
  170.  
  171. CREATE TABLE `player_skills`
  172. (
  173.         `player_id` INT NOT NULL DEFAULT 0,
  174.         `skillid` TINYINT(2) NOT NULL DEFAULT 0,
  175.         `value` INT UNSIGNED NOT NULL DEFAULT 0,
  176.         `count` INT UNSIGNED NOT NULL DEFAULT 0,
  177.         KEY (`player_id`), UNIQUE (`player_id`, `skillid`),
  178.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  179. ) ENGINE = InnoDB;
  180.  
  181. CREATE TABLE `player_spells`
  182. (
  183.         `player_id` INT NOT NULL,
  184.         `name` VARCHAR(255) NOT NULL,
  185.         KEY (`player_id`), UNIQUE (`player_id`, `name`),
  186.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  187. ) ENGINE = InnoDB;
  188.  
  189. CREATE TABLE `player_storage`
  190. (
  191.         `player_id` INT NOT NULL DEFAULT 0,
  192.         `key` INT UNSIGNED NOT NULL DEFAULT 0,
  193.         `value` VARCHAR(255) NOT NULL DEFAULT '0',
  194.         KEY (`player_id`), UNIQUE (`player_id`, `key`),
  195.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  196. ) ENGINE = InnoDB;
  197.  
  198. CREATE TABLE `player_viplist`
  199. (
  200.         `player_id` INT NOT NULL,
  201.         `vip_id` INT NOT NULL,
  202.         KEY (`player_id`), KEY (`vip_id`), UNIQUE (`player_id`, `vip_id`),
  203.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  204.         FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  205. ) ENGINE = InnoDB;
  206.  
  207. CREATE TABLE `killers`
  208. (
  209.         `id` INT NOT NULL AUTO_INCREMENT,
  210.         `death_id` INT NOT NULL,
  211.         `final_hit` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  212.         `unjustified` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  213.         PRIMARY KEY (`id`),
  214.         FOREIGN KEY (`death_id`) REFERENCES `player_deaths`(`id`) ON DELETE CASCADE
  215. ) ENGINE = InnoDB;
  216.  
  217. CREATE TABLE `player_killers`
  218. (
  219.         `kill_id` INT NOT NULL,
  220.         `player_id` INT NOT NULL,
  221.         FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE,
  222.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  223. ) ENGINE = InnoDB;
  224.  
  225. CREATE TABLE `environment_killers`
  226. (
  227.         `kill_id` INT NOT NULL,
  228.         `name` VARCHAR(255) NOT NULL,
  229.         FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE
  230. ) ENGINE = InnoDB;
  231.  
  232. CREATE TABLE `houses`
  233. (
  234.         `id` INT UNSIGNED NOT NULL,
  235.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  236.         `owner` INT NOT NULL,
  237.         `paid` INT UNSIGNED NOT NULL DEFAULT 0,
  238.         `warnings` INT NOT NULL DEFAULT 0,
  239.         `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
  240.         `name` VARCHAR(255) NOT NULL,
  241.         `town` INT UNSIGNED NOT NULL DEFAULT 0,
  242.         `size` INT UNSIGNED NOT NULL DEFAULT 0,
  243.         `price` INT UNSIGNED NOT NULL DEFAULT 0,
  244.         `rent` INT UNSIGNED NOT NULL DEFAULT 0,
  245.         `doors` INT UNSIGNED NOT NULL DEFAULT 0,
  246.         `beds` INT UNSIGNED NOT NULL DEFAULT 0,
  247.         `tiles` INT UNSIGNED NOT NULL DEFAULT 0,
  248.         `guild` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  249.         `clear` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  250.         UNIQUE (`id`, `world_id`)
  251. ) ENGINE = InnoDB;
  252.  
  253. CREATE TABLE `house_auctions`
  254. (
  255.         `house_id` INT UNSIGNED NOT NULL,
  256.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  257.         `player_id` INT NOT NULL,
  258.         `bid` INT UNSIGNED NOT NULL DEFAULT 0,
  259.         `limit` INT UNSIGNED NOT NULL DEFAULT 0,
  260.         `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  261.         UNIQUE (`house_id`, `world_id`),
  262.         FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE,
  263.         FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
  264. ) ENGINE = InnoDB;
  265.  
  266. CREATE TABLE `house_lists`
  267. (
  268.         `house_id` INT UNSIGNED NOT NULL,
  269.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  270.         `listid` INT NOT NULL,
  271.         `list` TEXT NOT NULL,
  272.         UNIQUE (`house_id`, `world_id`, `listid`),
  273.         FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  274. ) ENGINE = InnoDB;
  275.  
  276. CREATE TABLE `house_data`
  277. (
  278.         `house_id` INT UNSIGNED NOT NULL,
  279.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  280.         `data` LONGBLOB NOT NULL,
  281.         UNIQUE (`house_id`, `world_id`),
  282.         FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  283. ) ENGINE = InnoDB;
  284.  
  285. CREATE TABLE `tiles`
  286. (
  287.         `id` INT UNSIGNED NOT NULL,
  288.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  289.         `house_id` INT UNSIGNED NOT NULL,
  290.         `x` INT(5) UNSIGNED NOT NULL,
  291.         `y` INT(5) UNSIGNED NOT NULL,
  292.         `z` TINYINT(2) UNSIGNED NOT NULL,
  293.         UNIQUE (`id`, `world_id`),
  294.         KEY (`x`, `y`, `z`),
  295.         FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  296. ) ENGINE = InnoDB;
  297.  
  298. CREATE TABLE `tile_items`
  299. (
  300.         `tile_id` INT UNSIGNED NOT NULL,
  301.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  302.         `sid` INT NOT NULL,
  303.         `pid` INT NOT NULL DEFAULT 0,
  304.         `itemtype` INT NOT NULL,
  305.         `count` INT NOT NULL DEFAULT 0,
  306.         `attributes` BLOB NOT NULL,
  307.         UNIQUE (`tile_id`, `world_id`, `sid`), KEY (`sid`),
  308.         FOREIGN KEY (`tile_id`) REFERENCES `tiles`(`id`) ON DELETE CASCADE
  309. ) ENGINE = InnoDB;
  310.  
  311. CREATE TABLE `guilds`
  312. (
  313.         `id` INT NOT NULL AUTO_INCREMENT,
  314.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  315.         `name` VARCHAR(255) NOT NULL,
  316.         `ownerid` INT NOT NULL,
  317.         `creationdata` INT NOT NULL,
  318.         `motd` VARCHAR(255) NOT NULL,
  319.         PRIMARY KEY (`id`),
  320.         UNIQUE (`name`, `world_id`)
  321. ) ENGINE = InnoDB;
  322.  
  323. CREATE TABLE `guild_invites`
  324. (
  325.         `player_id` INT NOT NULL DEFAULT 0,
  326.         `guild_id` INT NOT NULL DEFAULT 0,
  327.         UNIQUE (`player_id`, `guild_id`),
  328.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  329.         FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
  330. ) ENGINE = InnoDB;
  331.  
  332. CREATE TABLE `guild_ranks`
  333. (
  334.         `id` INT NOT NULL AUTO_INCREMENT,
  335.         `guild_id` INT NOT NULL,
  336.         `name` VARCHAR(255) NOT NULL,
  337.         `level` INT NOT NULL COMMENT '1 - leader, 2 - vice leader, 3 - member',
  338.         PRIMARY KEY (`id`),
  339.         FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
  340. ) ENGINE = InnoDB;
  341.  
  342. CREATE TABLE `bans`
  343. (
  344.         `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  345.         `type` TINYINT(1) NOT NULL COMMENT '1 - ip banishment, 2 - namelock, 3 - account banishment, 4 - notation, 5 - deletion',
  346.         `value` INT UNSIGNED NOT NULL COMMENT 'ip address (integer), player guid or account number',
  347.         `param` INT UNSIGNED NOT NULL DEFAULT 4294967295 COMMENT 'used only for ip banishment mask (integer)',
  348.         `active` TINYINT(1) NOT NULL DEFAULT TRUE,
  349.         `expires` INT NOT NULL,
  350.         `added` INT UNSIGNED NOT NULL,
  351.         `admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
  352.         `comment` TEXT NOT NULL,
  353.         `reason` INT UNSIGNED NOT NULL DEFAULT 0,
  354.         `action` INT UNSIGNED NOT NULL DEFAULT 0,
  355.         `statement` VARCHAR(255) NOT NULL DEFAULT '',
  356.         PRIMARY KEY (`id`),
  357.         KEY `type` (`type`, `value`),
  358.         KEY `active` (`active`)
  359. ) ENGINE = InnoDB;
  360.  
  361. CREATE TABLE `global_storage`
  362. (
  363.         `key` INT UNSIGNED NOT NULL,
  364.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  365.         `value` VARCHAR(255) NOT NULL DEFAULT '0',
  366.         UNIQUE  (`key`, `world_id`)
  367. ) ENGINE = InnoDB;
  368.  
  369. CREATE TABLE `server_config`
  370. (
  371.         `config` VARCHAR(35) NOT NULL DEFAULT '',
  372.         `value` INT NOT NULL,
  373.         UNIQUE (`config`)
  374. ) ENGINE = InnoDB;
  375.  
  376. INSERT INTO `server_config` VALUES ('db_version', 23);
  377.  
  378. CREATE TABLE `server_motd`
  379. (
  380.         `id` INT UNSIGNED NOT NULL,
  381.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  382.         `text` TEXT NOT NULL,
  383.         UNIQUE (`id`, `world_id`)
  384. ) ENGINE = InnoDB;
  385.  
  386. INSERT INTO `server_motd` VALUES (1, 0, 'Welcome to The Forgotten Server!');
  387.  
  388. CREATE TABLE `server_record`
  389. (
  390.         `record` INT NOT NULL,
  391.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  392.         `timestamp` BIGINT NOT NULL,
  393.         UNIQUE (`record`, `world_id`, `timestamp`)
  394. ) ENGINE = InnoDB;
  395.  
  396. INSERT INTO `server_record` VALUES (0, 0, 0);
  397.  
  398. CREATE TABLE `server_reports`
  399. (
  400.         `id` INT NOT NULL AUTO_INCREMENT,
  401.         `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  402.         `player_id` INT NOT NULL DEFAULT 1,
  403.         `posx` INT NOT NULL DEFAULT 0,
  404.         `posy` INT NOT NULL DEFAULT 0,
  405.         `posz` INT NOT NULL DEFAULT 0,
  406.         `timestamp` BIGINT NOT NULL DEFAULT 0,
  407.         `report` TEXT NOT NULL,
  408.         `reads` INT NOT NULL DEFAULT 0,
  409.         PRIMARY KEY (`id`),
  410.         KEY (`world_id`), KEY (`reads`),
  411.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  412. ) ENGINE = InnoDB;
  413.  
  414. CREATE TABLE `polls`
  415. (
  416.         `id` INTEGER AUTO_INCREMENT,
  417.         `player_id` INT(11) NOT NULL,
  418.         `poll` VARCHAR(255) NOT NULL,
  419.         `options` VARCHAR(255) NOT NULL,
  420.         `timestamp` INT(11) NOT NULL,
  421.         PRIMARY KEY (`id`),
  422.         FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  423. ) ENGINE = InnoDB;
  424.  
  425. CREATE TABLE `poll_votes`
  426. (
  427.   `poll_id` INT(11) NOT NULL,
  428.   `votes` VARCHAR(255) NOT NULL,
  429.   `account_id` VARCHAR(255) NOT NULL,
  430.   FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`) ON DELETE CASCADE
  431. ) ENGINE = InnoDB;
  432.  
  433.  
  434. DELIMITER |
  435.  
  436. CREATE TRIGGER `ondelete_accounts`
  437. BEFORE DELETE
  438. ON `accounts`
  439. FOR EACH ROW
  440. BEGIN
  441.         DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;
  442. END|
  443.  
  444. CREATE TRIGGER `oncreate_guilds`
  445. AFTER INSERT
  446. ON `guilds`
  447. FOR EACH ROW
  448. BEGIN
  449.         INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
  450.         INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
  451.         INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
  452. END|
  453.  
  454. CREATE TRIGGER `ondelete_guilds`
  455. BEFORE DELETE
  456. ON `guilds`
  457. FOR EACH ROW
  458. BEGIN
  459.         UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
  460. END|
  461.  
  462. CREATE TRIGGER `oncreate_players`
  463. AFTER INSERT
  464. ON `players`
  465. FOR EACH ROW
  466. BEGIN
  467.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
  468.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
  469.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
  470.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
  471.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
  472.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
  473.         INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
  474. END|
  475.  
  476. CREATE TRIGGER `ondelete_players`
  477. BEFORE DELETE
  478. ON `players`
  479. FOR EACH ROW
  480. BEGIN
  481.         DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;
  482.         UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
  483. END|
  484.  
  485. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!