/*
* 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 ' ) ;