API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 27th, 2017
55
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 16.57 KB | None | 0 0
raw download clone embed print report
  1. DROP TRIGGER IF EXISTS `oncreate_players`;
  2.  
  3. DROP TRIGGER IF EXISTS `oncreate_guilds`;
  4.  
  5. DROP TRIGGER IF EXISTS `ondelete_players`;
  6.  
  7. DROP TRIGGER IF EXISTS `ondelete_guilds`;
  8.  
  9. DROP TRIGGER IF EXISTS `ondelete_accounts`;
  10.  
  11.  
  12.  
  13. DROP TABLE IF EXISTS `player_depotitems`;
  14.  
  15. DROP TABLE IF EXISTS `tile_items`;
  16.  
  17. DROP TABLE IF EXISTS `tiles`;
  18.  
  19. DROP TABLE IF EXISTS `bans`;
  20.  
  21. DROP TABLE IF EXISTS `house_lists`;
  22.  
  23. DROP TABLE IF EXISTS `houses`;
  24.  
  25. DROP TABLE IF EXISTS `player_items`;
  26.  
  27. DROP TABLE IF EXISTS `player_namelocks`;
  28.  
  29. DROP TABLE IF EXISTS `player_skills`;
  30.  
  31. DROP TABLE IF EXISTS `player_storage`;
  32.  
  33. DROP TABLE IF EXISTS `player_viplist`;
  34.  
  35. DROP TABLE IF EXISTS `player_spells`;
  36.  
  37. DROP TABLE IF EXISTS `player_deaths`;
  38.  
  39. DROP TABLE IF EXISTS `killers`;
  40.  
  41. DROP TABLE IF EXISTS `environment_killers`;
  42.  
  43. DROP TABLE IF EXISTS `player_killers`;
  44.  
  45. DROP TABLE IF EXISTS `guild_ranks`;
  46.  
  47. DROP TABLE IF EXISTS `guilds`;
  48.  
  49. DROP TABLE IF EXISTS `guild_invites`;
  50.  
  51. DROP TABLE IF EXISTS `global_storage`;
  52.  
  53. DROP TABLE IF EXISTS `players`;
  54.  
  55. DROP TABLE IF EXISTS `accounts`;
  56.  
  57. DROP TABLE IF EXISTS `server_record`;
  58.  
  59. DROP TABLE IF EXISTS `server_motd`;
  60.  
  61. DROP TABLE IF EXISTS `server_reports`;
  62.  
  63. DROP TABLE IF EXISTS `server_config`;
  64.  
  65. DROP TABLE IF EXISTS `account_viplist`;
  66.  
  67.  
  68.  
  69. CREATE TABLE `accounts`
  70.  
  71. (
  72.  
  73.     `id` INT NOT NULL AUTO_INCREMENT,
  74.  
  75.     `name` VARCHAR(32) NOT NULL DEFAULT '',
  76.  
  77.     `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
  78.  
  79.     `salt` VARCHAR(40) NOT NULL DEFAULT '',
  80.  
  81.     `premdays` INT NOT NULL DEFAULT 0,
  82.  
  83.     `lastday` INT UNSIGNED NOT NULL DEFAULT 0,
  84.  
  85.     `email` VARCHAR(255) NOT NULL DEFAULT '',
  86.  
  87.     `key` VARCHAR(32) NOT NULL DEFAULT '0',
  88.  
  89.     `blocked` TINYINT(1) NOT NULL DEFAULT FALSE COMMENT 'internal usage',
  90.  
  91.     `warnings` INT NOT NULL DEFAULT 0,
  92.  
  93.     `group_id` INT NOT NULL DEFAULT 1,
  94.  
  95.     PRIMARY KEY (`id`), UNIQUE (`name`)
  96.  
  97. ) ENGINE = InnoDB;
  98.  
  99.  
  100.  
  101. INSERT INTO `accounts` VALUES (1, '1', '1', '', 65535, 0, '', '0', 0, 0, 1);
  102.  
  103.  
  104.  
  105. CREATE TABLE `players`
  106.  
  107. (
  108.  
  109.     `id` INT NOT NULL AUTO_INCREMENT,
  110.  
  111.     `name` VARCHAR(255) NOT NULL,
  112.  
  113.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  114.  
  115.     `group_id` INT NOT NULL DEFAULT 1,
  116.  
  117.     `account_id` INT NOT NULL DEFAULT 0,
  118.  
  119.     `level` INT NOT NULL DEFAULT 1,
  120.  
  121.     `vocation` INT NOT NULL DEFAULT 0,
  122.  
  123.     `health` INT NOT NULL DEFAULT 150,
  124.  
  125.     `healthmax` INT NOT NULL DEFAULT 150,
  126.  
  127.     `experience` BIGINT NOT NULL DEFAULT 0,
  128.  
  129.     `lookbody` INT NOT NULL DEFAULT 0,
  130.  
  131.     `lookfeet` INT NOT NULL DEFAULT 0,
  132.  
  133.     `lookhead` INT NOT NULL DEFAULT 0,
  134.  
  135.     `looklegs` INT NOT NULL DEFAULT 0,
  136.  
  137.     `looktype` INT NOT NULL DEFAULT 136,
  138.  
  139.     `lookaddons` INT NOT NULL DEFAULT 0,
  140.  
  141.     `maglevel` INT NOT NULL DEFAULT 0,
  142.  
  143.     `mana` INT NOT NULL DEFAULT 0,
  144.  
  145.     `manamax` INT NOT NULL DEFAULT 0,
  146.  
  147.     `manaspent` INT NOT NULL DEFAULT 0,
  148.  
  149.     `soul` INT UNSIGNED NOT NULL DEFAULT 0,
  150.  
  151.     `town_id` INT NOT NULL DEFAULT 0,
  152.  
  153.     `posx` INT NOT NULL DEFAULT 0,
  154.  
  155.     `posy` INT NOT NULL DEFAULT 0,
  156.  
  157.     `posz` INT NOT NULL DEFAULT 0,
  158.  
  159.     `conditions` BLOB NOT NULL,
  160.  
  161.     `cap` INT NOT NULL DEFAULT 0,
  162.  
  163.     `sex` INT NOT NULL DEFAULT 0,
  164.  
  165.     `lastlogin` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  166.  
  167.     `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
  168.  
  169.     `save` TINYINT(1) NOT NULL DEFAULT 1,
  170.  
  171.     `skull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  172.  
  173.     `skulltime` INT NOT NULL DEFAULT 0,
  174.  
  175.     `rank_id` INT NOT NULL DEFAULT 0,
  176.  
  177.     `guildnick` VARCHAR(255) NOT NULL DEFAULT '',
  178.  
  179.     `lastlogout` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  180.  
  181.     `blessings` TINYINT(2) NOT NULL DEFAULT 0,
  182.  
  183.     `balance` BIGINT NOT NULL DEFAULT 0,
  184.  
  185.     `stamina` BIGINT NOT NULL DEFAULT 151200000 COMMENT 'stored in miliseconds',
  186.  
  187.     `direction` INT NOT NULL DEFAULT 2,
  188.  
  189.     `loss_experience` INT NOT NULL DEFAULT 100,
  190.  
  191.     `loss_mana` INT NOT NULL DEFAULT 100,
  192.  
  193.     `loss_skills` INT NOT NULL DEFAULT 100,
  194.  
  195.     `loss_containers` INT NOT NULL DEFAULT 100,
  196.  
  197.     `loss_items` INT NOT NULL DEFAULT 100,
  198.  
  199.     `premend` INT NOT NULL DEFAULT 0 COMMENT 'NOT IN USE BY THE SERVER',
  200.  
  201.     `online` TINYINT(1) NOT NULL DEFAULT 0,
  202.  
  203.     `marriage` INT UNSIGNED NOT NULL DEFAULT 0,
  204.  
  205.     `promotion` INT NOT NULL DEFAULT 0,
  206.  
  207.     `deleted` INT NOT NULL DEFAULT 0,
  208.  
  209.     `description` VARCHAR(255) NOT NULL DEFAULT '',
  210.  
  211.     PRIMARY KEY (`id`), UNIQUE (`name`, `deleted`),
  212.  
  213.     KEY (`account_id`), KEY (`group_id`),
  214.  
  215.     KEY (`online`), KEY (`deleted`),
  216.  
  217.     FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
  218.  
  219. ) ENGINE = InnoDB;
  220.  
  221.  
  222.  
  223. 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, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '');
  224.  
  225.  
  226.  
  227. CREATE TABLE `account_viplist`
  228.  
  229. (
  230.  
  231.     `account_id` INT NOT NULL,
  232.  
  233.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  234.  
  235.     `player_id` INT NOT NULL,
  236.  
  237.     KEY (`account_id`), KEY (`player_id`), KEY (`world_id`), UNIQUE (`account_id`, `player_id`),
  238.  
  239.     FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
  240.  
  241.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  242.  
  243. ) ENGINE = InnoDB;
  244.  
  245.  
  246.  
  247. CREATE TABLE `player_deaths`
  248.  
  249. (
  250.  
  251.     `id` INT NOT NULL AUTO_INCREMENT,
  252.  
  253.     `player_id` INT NOT NULL,
  254.  
  255.     `date` BIGINT UNSIGNED NOT NULL,
  256.  
  257.     `level` INT UNSIGNED NOT NULL,
  258.  
  259.     PRIMARY KEY (`id`), INDEX (`date`),
  260.  
  261.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  262.  
  263. ) ENGINE = InnoDB;
  264.  
  265.  
  266.  
  267. CREATE TABLE `player_depotitems`
  268.  
  269. (
  270.  
  271.     `player_id` INT NOT NULL,
  272.  
  273.     `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',
  274.  
  275.     `pid` INT NOT NULL DEFAULT 0,
  276.  
  277.     `itemtype` INT NOT NULL,
  278.  
  279.     `count` INT NOT NULL DEFAULT 0,
  280.  
  281.     `attributes` BLOB NOT NULL,
  282.  
  283.     KEY (`player_id`), UNIQUE (`player_id`, `sid`),
  284.  
  285.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  286.  
  287. ) ENGINE = InnoDB;
  288.  
  289.  
  290.  
  291. CREATE TABLE `player_items`
  292.  
  293. (
  294.  
  295.     `player_id` INT NOT NULL DEFAULT 0,
  296.  
  297.     `pid` INT NOT NULL DEFAULT 0,
  298.  
  299.     `sid` INT NOT NULL DEFAULT 0,
  300.  
  301.     `itemtype` INT NOT NULL DEFAULT 0,
  302.  
  303.     `count` INT NOT NULL DEFAULT 0,
  304.  
  305.     `attributes` BLOB NOT NULL,
  306.  
  307.     KEY (`player_id`), UNIQUE (`player_id`, `sid`),
  308.  
  309.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  310.  
  311. ) ENGINE = InnoDB;
  312.  
  313.  
  314.  
  315. CREATE TABLE `player_namelocks`
  316.  
  317. (
  318.  
  319.     `player_id` INT NOT NULL DEFAULT 0,
  320.  
  321.     `name` VARCHAR(255) NOT NULL,
  322.  
  323.     `new_name` VARCHAR(255) NOT NULL,
  324.  
  325.     `date` BIGINT NOT NULL DEFAULT 0,
  326.  
  327.     KEY (`player_id`),
  328.  
  329.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  330.  
  331. ) ENGINE = InnoDB;
  332.  
  333.  
  334.  
  335. CREATE TABLE `player_skills`
  336.  
  337. (
  338.  
  339.     `player_id` INT NOT NULL DEFAULT 0,
  340.  
  341.     `skillid` TINYINT(2) NOT NULL DEFAULT 0,
  342.  
  343.     `value` INT UNSIGNED NOT NULL DEFAULT 0,
  344.  
  345.     `count` INT UNSIGNED NOT NULL DEFAULT 0,
  346.  
  347.     KEY (`player_id`), UNIQUE (`player_id`, `skillid`),
  348.  
  349.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  350.  
  351. ) ENGINE = InnoDB;
  352.  
  353.  
  354.  
  355. CREATE TABLE `player_spells`
  356.  
  357. (
  358.  
  359.     `player_id` INT NOT NULL,
  360.  
  361.     `name` VARCHAR(255) NOT NULL,
  362.  
  363.     KEY (`player_id`), UNIQUE (`player_id`, `name`),
  364.  
  365.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  366.  
  367. ) ENGINE = InnoDB;
  368.  
  369.  
  370.  
  371. CREATE TABLE `player_storage`
  372.  
  373. (
  374.  
  375.     `player_id` INT NOT NULL DEFAULT 0,
  376.  
  377.     `key` VARCHAR(32) NOT NULL DEFAULT '0',
  378.  
  379.     `value` VARCHAR(255) NOT NULL DEFAULT '0',
  380.  
  381.     KEY (`player_id`), UNIQUE (`player_id`, `key`),
  382.  
  383.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  384.  
  385. ) ENGINE = InnoDB;
  386.  
  387.  
  388.  
  389. CREATE TABLE `player_viplist`
  390.  
  391. (
  392.  
  393.     `player_id` INT NOT NULL,
  394.  
  395.     `vip_id` INT NOT NULL,
  396.  
  397.     KEY (`player_id`), KEY (`vip_id`), UNIQUE (`player_id`, `vip_id`),
  398.  
  399.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  400.  
  401.     FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  402.  
  403. ) ENGINE = InnoDB;
  404.  
  405.  
  406.  
  407. CREATE TABLE `killers`
  408.  
  409. (
  410.  
  411.     `id` INT NOT NULL AUTO_INCREMENT,
  412.  
  413.     `death_id` INT NOT NULL,
  414.  
  415.     `final_hit` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  416.  
  417.     `unjustified` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  418.  
  419.     PRIMARY KEY (`id`),
  420.  
  421.     FOREIGN KEY (`death_id`) REFERENCES `player_deaths`(`id`) ON DELETE CASCADE
  422.  
  423. ) ENGINE = InnoDB;
  424.  
  425.  
  426.  
  427. CREATE TABLE `player_killers`
  428.  
  429. (
  430.  
  431.     `kill_id` INT NOT NULL,
  432.  
  433.     `player_id` INT NOT NULL,
  434.  
  435.     FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE,
  436.  
  437.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  438.  
  439. ) ENGINE = InnoDB;
  440.  
  441.  
  442.  
  443. CREATE TABLE `environment_killers`
  444.  
  445. (
  446.  
  447.     `kill_id` INT NOT NULL,
  448.  
  449.     `name` VARCHAR(255) NOT NULL,
  450.  
  451.     FOREIGN KEY (`kill_id`) REFERENCES `killers`(`id`) ON DELETE CASCADE
  452.  
  453. ) ENGINE = InnoDB;
  454.  
  455.  
  456.  
  457. CREATE TABLE `houses`
  458.  
  459. (
  460.  
  461.     `id` INT UNSIGNED NOT NULL,
  462.  
  463.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  464.  
  465.     `owner` INT NOT NULL,
  466.  
  467.     `paid` INT UNSIGNED NOT NULL DEFAULT 0,
  468.  
  469.     `warnings` INT NOT NULL DEFAULT 0,
  470.  
  471.     `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,
  472.  
  473.     `name` VARCHAR(255) NOT NULL,
  474.  
  475.     `town` INT UNSIGNED NOT NULL DEFAULT 0,
  476.  
  477.     `size` INT UNSIGNED NOT NULL DEFAULT 0,
  478.  
  479.     `price` INT UNSIGNED NOT NULL DEFAULT 0,
  480.  
  481.     `rent` INT UNSIGNED NOT NULL DEFAULT 0,
  482.  
  483.     `doors` INT UNSIGNED NOT NULL DEFAULT 0,
  484.  
  485.     `beds` INT UNSIGNED NOT NULL DEFAULT 0,
  486.  
  487.     `tiles` INT UNSIGNED NOT NULL DEFAULT 0,
  488.  
  489.     `guild` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  490.  
  491.     `clear` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  492.  
  493.     UNIQUE (`id`, `world_id`)
  494.  
  495. ) ENGINE = InnoDB;
  496.  
  497.  
  498.  
  499. CREATE TABLE `house_auctions`
  500.  
  501. (
  502.  
  503.     `house_id` INT UNSIGNED NOT NULL,
  504.  
  505.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  506.  
  507.     `player_id` INT NOT NULL,
  508.  
  509.     `bid` INT UNSIGNED NOT NULL DEFAULT 0,
  510.  
  511.     `limit` INT UNSIGNED NOT NULL DEFAULT 0,
  512.  
  513.     `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  514.  
  515.     UNIQUE (`house_id`, `world_id`),
  516.  
  517.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE,
  518.  
  519.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
  520.  
  521. ) ENGINE = InnoDB;
  522.  
  523.  
  524.  
  525. CREATE TABLE `house_lists`
  526.  
  527. (
  528.  
  529.     `house_id` INT UNSIGNED NOT NULL,
  530.  
  531.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  532.  
  533.     `listid` INT NOT NULL,
  534.  
  535.     `list` TEXT NOT NULL,
  536.  
  537.     UNIQUE (`house_id`, `world_id`, `listid`),
  538.  
  539.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  540.  
  541. ) ENGINE = InnoDB;
  542.  
  543.  
  544.  
  545. CREATE TABLE `house_data`
  546.  
  547. (
  548.  
  549.     `house_id` INT UNSIGNED NOT NULL,
  550.  
  551.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  552.  
  553.     `data` LONGBLOB NOT NULL,
  554.  
  555.     UNIQUE (`house_id`, `world_id`),
  556.  
  557.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  558.  
  559. ) ENGINE = InnoDB;
  560.  
  561.  
  562.  
  563. CREATE TABLE `tiles`
  564.  
  565. (
  566.  
  567.     `id` INT UNSIGNED NOT NULL,
  568.  
  569.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  570.  
  571.     `house_id` INT UNSIGNED NOT NULL,
  572.  
  573.     `x` INT(5) UNSIGNED NOT NULL,
  574.  
  575.     `y` INT(5) UNSIGNED NOT NULL,
  576.  
  577.     `z` TINYINT(2) UNSIGNED NOT NULL,
  578.  
  579.     UNIQUE (`id`, `world_id`),
  580.  
  581.     KEY (`x`, `y`, `z`),
  582.  
  583.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE
  584.  
  585. ) ENGINE = InnoDB;
  586.  
  587.  
  588.  
  589. CREATE TABLE `tile_items`
  590.  
  591. (
  592.  
  593.     `tile_id` INT UNSIGNED NOT NULL,
  594.  
  595.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  596.  
  597.     `sid` INT NOT NULL,
  598.  
  599.     `pid` INT NOT NULL DEFAULT 0,
  600.  
  601.     `itemtype` INT NOT NULL,
  602.  
  603.     `count` INT NOT NULL DEFAULT 0,
  604.  
  605.     `attributes` BLOB NOT NULL,
  606.  
  607.     UNIQUE (`tile_id`, `world_id`, `sid`), KEY (`sid`),
  608.  
  609.     FOREIGN KEY (`tile_id`) REFERENCES `tiles`(`id`) ON DELETE CASCADE
  610.  
  611. ) ENGINE = InnoDB;
  612.  
  613.  
  614.  
  615. CREATE TABLE `guilds`
  616.  
  617. (
  618.  
  619.     `id` INT NOT NULL AUTO_INCREMENT,
  620.  
  621.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  622.  
  623.     `name` VARCHAR(255) NOT NULL,
  624.  
  625.     `ownerid` INT NOT NULL,
  626.  
  627.     `creationdata` INT NOT NULL,
  628.  
  629.     `checkdata` INT NOT NULL,
  630.  
  631.     `motd` VARCHAR(255) NOT NULL,
  632.  
  633.     PRIMARY KEY (`id`),
  634.  
  635.     UNIQUE (`name`, `world_id`)
  636.  
  637. ) ENGINE = InnoDB;
  638.  
  639.  
  640.  
  641. CREATE TABLE `guild_invites`
  642.  
  643. (
  644.  
  645.     `player_id` INT NOT NULL DEFAULT 0,
  646.  
  647.     `guild_id` INT NOT NULL DEFAULT 0,
  648.  
  649.     UNIQUE (`player_id`, `guild_id`),
  650.  
  651.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  652.  
  653.     FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
  654.  
  655. ) ENGINE = InnoDB;
  656.  
  657.  
  658.  
  659. CREATE TABLE `guild_ranks`
  660.  
  661. (
  662.  
  663.     `id` INT NOT NULL AUTO_INCREMENT,
  664.  
  665.     `guild_id` INT NOT NULL,
  666.  
  667.     `name` VARCHAR(255) NOT NULL,
  668.  
  669.     `level` INT NOT NULL COMMENT '1 - leader, 2 - vice leader, 3 - member',
  670.  
  671.     PRIMARY KEY (`id`),
  672.  
  673.     FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
  674.  
  675. ) ENGINE = InnoDB;
  676.  
  677.  
  678.  
  679. CREATE TABLE `bans`
  680.  
  681. (
  682.  
  683.     `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  684.  
  685.     `type` TINYINT(1) NOT NULL COMMENT '1 - ip banishment, 2 - namelock, 3 - account banishment, 4 - notation, 5 - deletion',
  686.  
  687.     `value` INT UNSIGNED NOT NULL COMMENT 'ip address (integer), player guid or account number',
  688.  
  689.     `param` INT UNSIGNED NOT NULL DEFAULT 4294967295 COMMENT 'used only for ip banishment mask (integer)',
  690.  
  691.     `active` TINYINT(1) NOT NULL DEFAULT TRUE,
  692.  
  693.     `expires` INT NOT NULL,
  694.  
  695.     `added` INT UNSIGNED NOT NULL,
  696.  
  697.     `admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
  698.  
  699.     `comment` TEXT NOT NULL,
  700.  
  701.     `reason` INT UNSIGNED NOT NULL DEFAULT 0,
  702.  
  703.     `action` INT UNSIGNED NOT NULL DEFAULT 0,
  704.  
  705.     `statement` VARCHAR(255) NOT NULL DEFAULT '',
  706.  
  707.     PRIMARY KEY (`id`),
  708.  
  709.     KEY `type` (`type`, `value`),
  710.  
  711.     KEY `active` (`active`)
  712.  
  713. ) ENGINE = InnoDB;
  714.  
  715.  
  716.  
  717. CREATE TABLE `global_storage`
  718.  
  719. (
  720.  
  721.     `key` VARCHAR(32) NOT NULL,
  722.  
  723.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  724.  
  725.     `value` VARCHAR(255) NOT NULL DEFAULT '0',
  726.  
  727.     UNIQUE  (`key`, `world_id`)
  728.  
  729. ) ENGINE = InnoDB;
  730.  
  731.  
  732.  
  733. CREATE TABLE `server_config`
  734.  
  735. (
  736.  
  737.     `config` VARCHAR(35) NOT NULL DEFAULT '',
  738.  
  739.     `value` VARCHAR(255) NOT NULL DEFAULT '',
  740.  
  741.     UNIQUE (`config`)
  742.  
  743. ) ENGINE = InnoDB;
  744.  
  745.  
  746.  
  747. INSERT INTO `server_config` VALUES ('db_version', 26);
  748.  
  749.  
  750.  
  751. CREATE TABLE `server_motd`
  752.  
  753. (
  754.  
  755.     `id` INT UNSIGNED NOT NULL,
  756.  
  757.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  758.  
  759.     `text` TEXT NOT NULL,
  760.  
  761.     UNIQUE (`id`, `world_id`)
  762.  
  763. ) ENGINE = InnoDB;
  764.  
  765.  
  766.  
  767. INSERT INTO `server_motd` VALUES (1, 0, 'Welcome to The Forgotten Server!');
  768.  
  769.  
  770.  
  771. CREATE TABLE `server_record`
  772.  
  773. (
  774.  
  775.     `record` INT NOT NULL,
  776.  
  777.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  778.  
  779.     `timestamp` BIGINT NOT NULL,
  780.  
  781.     UNIQUE (`record`, `world_id`, `timestamp`)
  782.  
  783. ) ENGINE = InnoDB;
  784.  
  785.  
  786.  
  787. INSERT INTO `server_record` VALUES (0, 0, 0);
  788.  
  789.  
  790.  
  791. CREATE TABLE `server_reports`
  792.  
  793. (
  794.  
  795.     `id` INT NOT NULL AUTO_INCREMENT,
  796.  
  797.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  798.  
  799.     `player_id` INT NOT NULL DEFAULT 1,
  800.  
  801.     `posx` INT NOT NULL DEFAULT 0,
  802.  
  803.     `posy` INT NOT NULL DEFAULT 0,
  804.  
  805.     `posz` INT NOT NULL DEFAULT 0,
  806.  
  807.     `timestamp` BIGINT NOT NULL DEFAULT 0,
  808.  
  809.     `report` TEXT NOT NULL,
  810.  
  811.     `reads` INT NOT NULL DEFAULT 0,
  812.  
  813.     PRIMARY KEY (`id`),
  814.  
  815.     KEY (`world_id`), KEY (`reads`),
  816.  
  817.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
  818.  
  819. ) ENGINE = InnoDB;
  820.  
  821.  
  822.  
  823. DELIMITER |
  824.  
  825.  
  826.  
  827. CREATE TRIGGER `ondelete_accounts`
  828.  
  829. BEFORE DELETE
  830.  
  831. ON `accounts`
  832.  
  833. FOR EACH ROW
  834.  
  835. BEGIN
  836.  
  837.     DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;
  838.  
  839. END|
  840.  
  841.  
  842.  
  843. CREATE TRIGGER `oncreate_guilds`
  844.  
  845. AFTER INSERT
  846.  
  847. ON `guilds`
  848.  
  849. FOR EACH ROW
  850.  
  851. BEGIN
  852.  
  853.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
  854.  
  855.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
  856.  
  857.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
  858.  
  859. END|
  860.  
  861.  
  862.  
  863. CREATE TRIGGER `ondelete_guilds`
  864.  
  865. BEFORE DELETE
  866.  
  867. ON `guilds`
  868.  
  869. FOR EACH ROW
  870.  
  871. BEGIN
  872.  
  873.     UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
  874.  
  875. END|
  876.  
  877.  
  878.  
  879. CREATE TRIGGER `oncreate_players`
  880.  
  881. AFTER INSERT
  882.  
  883. ON `players`
  884.  
  885. FOR EACH ROW
  886.  
  887. BEGIN
  888.  
  889.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
  890.  
  891.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
  892.  
  893.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
  894.  
  895.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
  896.  
  897.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
  898.  
  899.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
  900.  
  901.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
  902.  
  903. END|
  904.  
  905.  
  906.  
  907. CREATE TRIGGER `ondelete_players`
  908.  
  909. BEFORE DELETE
  910.  
  911. ON `players`
  912.  
  913. FOR EACH ROW
  914.  
  915. BEGIN
  916.  
  917.     DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;
  918.  
  919.     UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
  920.  
  921. END|
  922.  
  923.  
  924.  
  925. 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!