Nevar pievienot vairāk kā 25 tēmas
Tēmai ir jāsākas ar burtu vai ciparu, tā var saturēt domu zīmes ('-') un var būt līdz 35 simboliem gara.
190 rindas
8.2 KiB
SQL
190 rindas
8.2 KiB
SQL
/*
|
|
* This Source Code Form is subject to the terms of the Mozilla Public
|
|
* License, v. 2.0. If a copy of the MPL was not distributed with this
|
|
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
|
|
*/
|
|
/**
|
|
* Author: Skylar Ittner
|
|
* Created: Apr 26, 2019
|
|
*/
|
|
|
|
ALTER TABLE `terranquest`.`inventory`
|
|
DROP FOREIGN KEY `fk_inventory_players`;
|
|
|
|
ALTER TABLE `terranquest`.`player_badges`
|
|
DROP FOREIGN KEY `fk_achievements_has_players_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`locations`
|
|
DROP FOREIGN KEY `fk_locations_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`claimedcodes`
|
|
DROP FOREIGN KEY `fk_claimedcodes_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`munzee`
|
|
DROP FOREIGN KEY `fk_munzee_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`messages`
|
|
DROP FOREIGN KEY `fk_messages_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`artifacts`
|
|
DROP FOREIGN KEY `fk_artifacts_players1`;
|
|
|
|
ALTER TABLE `terranquest`.`private_messages`
|
|
DROP FOREIGN KEY `fk_private_messages_players1`,
|
|
DROP FOREIGN KEY `fk_private_messages_players2`;
|
|
|
|
ALTER TABLE `terranquest`.`inventory`
|
|
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`players`
|
|
CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`player_badges`
|
|
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`locations`
|
|
CHANGE COLUMN `owneruuid` `ownerid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`claimedcodes`
|
|
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`munzee`
|
|
CHANGE COLUMN `player_uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`messages`
|
|
CHANGE COLUMN `message` `message` VARCHAR(500) COLLATE 'utf8mb4_bin' NOT NULL ,
|
|
CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`artifacts`
|
|
CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
|
|
CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
|
|
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`private_messages`
|
|
CHANGE COLUMN `from_uuid` `from_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL DEFAULT 0 ,
|
|
CHANGE COLUMN `to_uuid` `to_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`inventory`
|
|
ADD CONSTRAINT `fk_inventory_players`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`player_badges`
|
|
ADD CONSTRAINT `fk_achievements_has_players_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`locations`
|
|
ADD CONSTRAINT `fk_locations_players1`
|
|
FOREIGN KEY (`ownerid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`claimedcodes`
|
|
ADD CONSTRAINT `fk_claimedcodes_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`munzee`
|
|
ADD CONSTRAINT `fk_munzee_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`messages`
|
|
ADD CONSTRAINT `fk_messages_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`artifacts`
|
|
ADD CONSTRAINT `fk_artifacts_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`private_messages`
|
|
ADD CONSTRAINT `fk_private_messages_players1`
|
|
FOREIGN KEY (`from_id`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION,
|
|
ADD CONSTRAINT `fk_private_messages_players2`
|
|
FOREIGN KEY (`to_id`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE `terranquest`.`items`
|
|
ADD COLUMN `weight` INT(3) NOT NULL DEFAULT 1 AFTER `itemcode`;
|
|
|
|
ALTER TABLE `terranquest`.`artifacts`
|
|
CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
|
|
CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ;
|
|
|
|
ALTER TABLE `terranquest`.`players`
|
|
ADD COLUMN `stealth` TINYINT(1) NOT NULL DEFAULT 0 AFTER `kick`;
|
|
|
|
ALTER TABLE `terranquest`.`locations`
|
|
CHANGE COLUMN `osmid` `osmid` VARCHAR(20) NOT NULL ,
|
|
ADD COLUMN `lastactivity` DATETIME NULL DEFAULT NULL AFTER `data`;
|
|
|
|
ALTER TABLE `terranquest`.`messages`
|
|
CHANGE COLUMN `message` `message` TEXT COLLATE 'utf8mb4_bin' NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`private_messages`
|
|
CHANGE COLUMN `message` `message` TEXT NOT NULL ;
|
|
|
|
ALTER TABLE `terranquest`.`items`
|
|
ADD COLUMN `icon` VARCHAR(100) NULL DEFAULT NULL AFTER `weight`,
|
|
ADD COLUMN `color` VARCHAR(45) NULL DEFAULT NULL AFTER `icon`;
|
|
|
|
CREATE TABLE IF NOT EXISTS `terranquest`.`player_stats` (
|
|
`accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL,
|
|
`claims` INT(11) NOT NULL DEFAULT 0,
|
|
`attacks` INT(11) NOT NULL DEFAULT 0,
|
|
`defends` INT(11) NOT NULL DEFAULT 0,
|
|
`scans` INT(11) NOT NULL DEFAULT 0,
|
|
`distancekm` DECIMAL(8,3) NOT NULL DEFAULT 0.0,
|
|
PRIMARY KEY (`accountid`),
|
|
UNIQUE INDEX `accountid_UNIQUE` (`accountid` ASC),
|
|
CONSTRAINT `fk_player_stats_players1`
|
|
FOREIGN KEY (`accountid`)
|
|
REFERENCES `terranquest`.`players` (`accountid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARACTER SET = utf8
|
|
COLLATE = utf8_bin;
|
|
|
|
ALTER TABLE `terranquest`.`badges`
|
|
DROP COLUMN `badgesid`,
|
|
CHANGE COLUMN `badgename` `name` VARCHAR(50) CHARACTER SET 'utf8' NOT NULL ,
|
|
CHANGE COLUMN `badgedesc` `desc` VARCHAR(400) CHARACTER SET 'utf8' NOT NULL ,
|
|
ADD COLUMN `icon` VARCHAR(100) NOT NULL AFTER `desc`,
|
|
ADD COLUMN `color` VARCHAR(45) NOT NULL DEFAULT 'white' AFTER `icon`,
|
|
ADD COLUMN `background` VARCHAR(255) NULL DEFAULT '#008000' AFTER `color`;
|
|
|
|
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');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (2,'Developer','Contributed to TerranQuest code.','game-icon game-icon-computing','#32d74a','black');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (3,'Administrator','A TerranQuest administrator.','game-icon game-icon-processor','#00ff3b','black');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (4,'Moderator','Wielder of the banhammer.','game-icon game-icon-thor-hammer','red','white');
|
|
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');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (6,'Migrator','Played TerranQuest version 1.','game-icon game-icon-upgrade','black','#00c853');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (7,'Attacker','Attacked places 1,000 times.','game-icon game-icon-targeting','red','black');
|
|
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');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (9,'Defender','Defended places 1,000 times.','game-icon game-icon-shield','green','black');
|
|
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');
|
|
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (11,'Finder','Scanned 250 codes.','game-icon game-icon-target-laser','white','black');
|
|
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');
|