Game server and admin dashboard for TerranQuest.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

migrate_v1.sql 8.2KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189
  1. /*
  2. * This Source Code Form is subject to the terms of the Mozilla Public
  3. * License, v. 2.0. If a copy of the MPL was not distributed with this
  4. * file, You can obtain one at http://mozilla.org/MPL/2.0/.
  5. */
  6. /**
  7. * Author: Skylar Ittner
  8. * Created: Apr 26, 2019
  9. */
  10. ALTER TABLE `terranquest`.`inventory`
  11. DROP FOREIGN KEY `fk_inventory_players`;
  12. ALTER TABLE `terranquest`.`player_badges`
  13. DROP FOREIGN KEY `fk_achievements_has_players_players1`;
  14. ALTER TABLE `terranquest`.`locations`
  15. DROP FOREIGN KEY `fk_locations_players1`;
  16. ALTER TABLE `terranquest`.`claimedcodes`
  17. DROP FOREIGN KEY `fk_claimedcodes_players1`;
  18. ALTER TABLE `terranquest`.`munzee`
  19. DROP FOREIGN KEY `fk_munzee_players1`;
  20. ALTER TABLE `terranquest`.`messages`
  21. DROP FOREIGN KEY `fk_messages_players1`;
  22. ALTER TABLE `terranquest`.`artifacts`
  23. DROP FOREIGN KEY `fk_artifacts_players1`;
  24. ALTER TABLE `terranquest`.`private_messages`
  25. DROP FOREIGN KEY `fk_private_messages_players1`,
  26. DROP FOREIGN KEY `fk_private_messages_players2`;
  27. ALTER TABLE `terranquest`.`inventory`
  28. CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  29. ALTER TABLE `terranquest`.`players`
  30. CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  31. ALTER TABLE `terranquest`.`player_badges`
  32. CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  33. ALTER TABLE `terranquest`.`locations`
  34. CHANGE COLUMN `owneruuid` `ownerid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
  35. ALTER TABLE `terranquest`.`claimedcodes`
  36. CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  37. ALTER TABLE `terranquest`.`munzee`
  38. CHANGE COLUMN `player_uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  39. ALTER TABLE `terranquest`.`messages`
  40. CHANGE COLUMN `message` `message` VARCHAR(500) COLLATE 'utf8mb4_bin' NOT NULL ,
  41. CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
  42. ALTER TABLE `terranquest`.`artifacts`
  43. CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
  44. CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
  45. CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
  46. ALTER TABLE `terranquest`.`private_messages`
  47. CHANGE COLUMN `from_uuid` `from_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL DEFAULT 0 ,
  48. CHANGE COLUMN `to_uuid` `to_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
  49. ALTER TABLE `terranquest`.`inventory`
  50. ADD CONSTRAINT `fk_inventory_players`
  51. FOREIGN KEY (`accountid`)
  52. REFERENCES `terranquest`.`players` (`accountid`)
  53. ON DELETE NO ACTION
  54. ON UPDATE NO ACTION;
  55. ALTER TABLE `terranquest`.`player_badges`
  56. ADD CONSTRAINT `fk_achievements_has_players_players1`
  57. FOREIGN KEY (`accountid`)
  58. REFERENCES `terranquest`.`players` (`accountid`)
  59. ON DELETE NO ACTION
  60. ON UPDATE NO ACTION;
  61. ALTER TABLE `terranquest`.`locations`
  62. ADD CONSTRAINT `fk_locations_players1`
  63. FOREIGN KEY (`ownerid`)
  64. REFERENCES `terranquest`.`players` (`accountid`)
  65. ON DELETE NO ACTION
  66. ON UPDATE NO ACTION;
  67. ALTER TABLE `terranquest`.`claimedcodes`
  68. ADD CONSTRAINT `fk_claimedcodes_players1`
  69. FOREIGN KEY (`accountid`)
  70. REFERENCES `terranquest`.`players` (`accountid`)
  71. ON DELETE NO ACTION
  72. ON UPDATE NO ACTION;
  73. ALTER TABLE `terranquest`.`munzee`
  74. ADD CONSTRAINT `fk_munzee_players1`
  75. FOREIGN KEY (`accountid`)
  76. REFERENCES `terranquest`.`players` (`accountid`)
  77. ON DELETE NO ACTION
  78. ON UPDATE NO ACTION;
  79. ALTER TABLE `terranquest`.`messages`
  80. ADD CONSTRAINT `fk_messages_players1`
  81. FOREIGN KEY (`accountid`)
  82. REFERENCES `terranquest`.`players` (`accountid`)
  83. ON DELETE NO ACTION
  84. ON UPDATE NO ACTION;
  85. ALTER TABLE `terranquest`.`artifacts`
  86. ADD CONSTRAINT `fk_artifacts_players1`
  87. FOREIGN KEY (`accountid`)
  88. REFERENCES `terranquest`.`players` (`accountid`)
  89. ON DELETE NO ACTION
  90. ON UPDATE NO ACTION;
  91. ALTER TABLE `terranquest`.`private_messages`
  92. ADD CONSTRAINT `fk_private_messages_players1`
  93. FOREIGN KEY (`from_id`)
  94. REFERENCES `terranquest`.`players` (`accountid`)
  95. ON DELETE NO ACTION
  96. ON UPDATE NO ACTION,
  97. ADD CONSTRAINT `fk_private_messages_players2`
  98. FOREIGN KEY (`to_id`)
  99. REFERENCES `terranquest`.`players` (`accountid`)
  100. ON DELETE NO ACTION
  101. ON UPDATE NO ACTION;
  102. ALTER TABLE `terranquest`.`items`
  103. ADD COLUMN `weight` INT(3) NOT NULL DEFAULT 1 AFTER `itemcode`;
  104. ALTER TABLE `terranquest`.`artifacts`
  105. CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
  106. CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ;
  107. ALTER TABLE `terranquest`.`players`
  108. ADD COLUMN `stealth` TINYINT(1) NOT NULL DEFAULT 0 AFTER `kick`;
  109. ALTER TABLE `terranquest`.`locations`
  110. CHANGE COLUMN `osmid` `osmid` VARCHAR(20) NOT NULL ,
  111. ADD COLUMN `lastactivity` DATETIME NULL DEFAULT NULL AFTER `data`;
  112. ALTER TABLE `terranquest`.`messages`
  113. CHANGE COLUMN `message` `message` TEXT COLLATE 'utf8mb4_bin' NOT NULL ;
  114. ALTER TABLE `terranquest`.`private_messages`
  115. CHANGE COLUMN `message` `message` TEXT NOT NULL ;
  116. ALTER TABLE `terranquest`.`items`
  117. ADD COLUMN `icon` VARCHAR(100) NULL DEFAULT NULL AFTER `weight`,
  118. ADD COLUMN `color` VARCHAR(45) NULL DEFAULT NULL AFTER `icon`;
  119. CREATE TABLE IF NOT EXISTS `terranquest`.`player_stats` (
  120. `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL,
  121. `claims` INT(11) NOT NULL DEFAULT 0,
  122. `attacks` INT(11) NOT NULL DEFAULT 0,
  123. `defends` INT(11) NOT NULL DEFAULT 0,
  124. `scans` INT(11) NOT NULL DEFAULT 0,
  125. `distancekm` DECIMAL(8,3) NOT NULL DEFAULT 0.0,
  126. PRIMARY KEY (`accountid`),
  127. UNIQUE INDEX `accountid_UNIQUE` (`accountid` ASC),
  128. CONSTRAINT `fk_player_stats_players1`
  129. FOREIGN KEY (`accountid`)
  130. REFERENCES `terranquest`.`players` (`accountid`)
  131. ON DELETE NO ACTION
  132. ON UPDATE NO ACTION)
  133. ENGINE = InnoDB
  134. DEFAULT CHARACTER SET = utf8
  135. COLLATE = utf8_bin;
  136. ALTER TABLE `terranquest`.`badges`
  137. DROP COLUMN `badgesid`,
  138. CHANGE COLUMN `badgename` `name` VARCHAR(50) CHARACTER SET 'utf8' NOT NULL ,
  139. CHANGE COLUMN `badgedesc` `desc` VARCHAR(400) CHARACTER SET 'utf8' NOT NULL ,
  140. ADD COLUMN `icon` VARCHAR(100) NOT NULL AFTER `desc`,
  141. ADD COLUMN `color` VARCHAR(45) NOT NULL DEFAULT 'white' AFTER `icon`,
  142. ADD COLUMN `background` VARCHAR(255) NULL DEFAULT '#008000' AFTER `color`;
  143. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (1,'Beta Tester','Took part in the version 1 beta.','game-icon game-icon-ladybug','black','#5bca6c');
  144. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (2,'Developer','Contributed to TerranQuest code.','game-icon game-icon-computing','#32d74a','black');
  145. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (3,'Administrator','A TerranQuest administrator.','game-icon game-icon-processor','#00ff3b','black');
  146. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (4,'Moderator','Wielder of the banhammer.','game-icon game-icon-thor-hammer','red','white');
  147. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (5,'Alpha Tester','Helped during the version 1 alpha.','game-icon game-icon-spotted-bug','black','#ff5c31');
  148. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (6,'Migrator','Played TerranQuest version 1.','game-icon game-icon-upgrade','black','#00c853');
  149. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (7,'Attacker','Attacked places 1,000 times.','game-icon game-icon-targeting','red','black');
  150. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (8,'Assassin','Attacked places 10,000 times.','game-icon game-icon-multiple-targets','red','black');
  151. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (9,'Defender','Defended places 1,000 times.','game-icon game-icon-shield','green','black');
  152. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (10,'Guard','Defended places 10,000 times.','game-icon game-icon-shield-reflect','green','black');
  153. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (11,'Finder','Scanned 250 codes.','game-icon game-icon-target-laser','white','black');
  154. INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (12,'Hoarder','Scanned 1,000 codes.','game-icon game-icon-open-treasure-chest','#ff6f00','white');