diff options
| -rw-r--r-- | db updates.sql | 255 |
1 files changed, 1 insertions, 254 deletions
diff --git a/db updates.sql b/db updates.sql index 739c6e3..c6f6aa9 100644 --- a/db updates.sql +++ b/db updates.sql @@ -1,255 +1,2 @@ --- TABLES: --- --- Table structure for table `challengeMaps` --- +-- db updates reset after patch on March 24th. -CREATE TABLE `challengeMaps` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `code` varchar(512) COLLATE latin1_general_ci NOT NULL, - `name` varchar(100) COLLATE latin1_general_ci NOT NULL, - `challengeTier` int(11) NOT NULL, - `challengeSuborder` int(11) NOT NULL, - PRIMARY KEY (`ID`), - KEY `challengeTier` (`challengeTier`,`challengeSuborder`) -) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ; - --- --- Table structure for table `challengeSolutions` --- - -CREATE TABLE `challengeSolutions` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `userID` int(11) NOT NULL, - `challengeID` int(11) NOT NULL, - `solution` varchar(512) COLLATE latin1_general_ci NOT NULL, - `moves` int(11) NOT NULL, - `dateSolved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`), - UNIQUE KEY `userID_challengeID` (`userID`,`challengeID`) -) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=110 ; - --- -------------------------------------------------------- - --- --- Table structure for table `challenges` --- - -CREATE TABLE `challenges` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `mapID` int(11) NOT NULL, - `inequality` enum('greater than','less than','equal') COLLATE latin1_general_ci NOT NULL DEFAULT 'greater than' COMMENT 'Whether we want to get more than, less than, or equal to the goal.', - `goal` int(11) NOT NULL COMMENT 'The amount we want to get for this map', - `ordering` int(11) NOT NULL COMMENT 'Used to order challenges on the challenge page. Lower values come first', - `enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Only enabled challenges will be displayed', - `hint` varchar(512) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Hint to be given to the player (for the intro puzzles - if hint is null, it won''t be shown)', - `restrictWallCount` int(11) DEFAULT NULL COMMENT 'Restriction on the (max) number of walls that can be used', - `restrictWallPlacement` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that disallows certain places for walls', - `restrictTeleportCount` int(11) DEFAULT NULL COMMENT 'Restriction on the number of teleports used', - `restrictTeleportsUsed` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction disallowing the use of certain teleports', - `restrictStartPoint` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that the maze must start at the given start-point', - `restrictEndPoint` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that the maze must end at the given end point', - PRIMARY KEY (`ID`), - KEY `mapID` (`mapID`,`enabled`) -) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ; - --- -------------------------------------------------------- - --- --- Table structure for table `chat` --- - -CREATE TABLE `chat` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `userID` int(11) NOT NULL, - `message` varchar(511) COLLATE latin1_general_ci NOT NULL, - `dateSent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`) -) ENGINE=MyISAM AUTO_INCREMENT=8829 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8829 ; - --- -------------------------------------------------------- - --- --- Table structure for table `emailQueue` --- - -CREATE TABLE `emailQueue` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `emailID` int(11) NOT NULL, - `priority` tinyint(4) NOT NULL DEFAULT '100', - PRIMARY KEY (`ID`), - KEY `emailID` (`emailID`,`priority`) -) ENGINE=MyISAM AUTO_INCREMENT=233 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=233 ; - --- -------------------------------------------------------- - --- --- Table structure for table `emails` --- - -CREATE TABLE `emails` ( - `ID` int(11) NOT NULL AUTO_INCREMENT, - `fromUserID` int(11) NOT NULL, - `to` varchar(512) COLLATE latin1_general_ci NOT NULL, - `subject` varchar(512) COLLATE latin1_general_ci NOT NULL, - `body` mediumtext COLLATE latin1_general_ci NOT NULL, - `dateSubmited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `dateSent` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `error` varchar(512) COLLATE latin1_general_ci DEFAULT NULL, - PRIMARY KEY (`ID`) -) ENGINE=MyISAM AUTO_INCREMENT=233 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1 COMMENT='Queued emails' AUTO_INCREMENT=233 ; - --- -------------------------------------------------------- - --- --- Table structure for table `settings` --- - -CREATE TABLE `settings` ( - `name` varchar(16) COLLATE latin1_general_ci NOT NULL, - `value` varchar(128) COLLATE latin1_general_ci NOT NULL, - PRIMARY KEY (`name`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; - --- --- Dumping data for table `settings` --- - -INSERT INTO `settings` VALUES ('Email_Last_Sent', '1362982189'); - - - - - - - - --- Remove 'on update CURRENT_TIMESTAMP' from dateLogin -ALTER TABLE `users` CHANGE `dateLogin` `dateLogin` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; - -ALTER TABLE `mapOfTheDay` DROP INDEX `UniqueTypeDate`; -DELETE FROM mapOfTheDay WHERE ID IN (2224, 2334, 2443, 2469, 2328); -DELETE FROM solutions WHERE ID = 29157; -CREATE UNIQUE INDEX `UniqueTypeDate` ON `mapOfTheDay` ( `mapDate` , `mapType` ); - -ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0'; - -ALTER TABLE `users` ADD `totalMazes` INT NOT NULL DEFAULT '0', -ADD `totalMoves` INT NOT NULL DEFAULT '0'; - -ALTER TABLE `solutions` ADD `isTiedForHighScore` BOOLEAN NOT NULL DEFAULT '0'; - -ALTER TABLE `users` ADD `challengeTier` INT NOT NULL , -ADD `championPoints` INT NOT NULL , -ADD `totalWins` INT DEFAULT '0' NOT NULL , -ADD `totalTies` INT DEFAULT '0' NOT NULL ; - -ALTER TABLE `solutions` ADD `championPointsWorth` INT DEFAULT '0' NOT NULL , -ADD `isHighScore` TINYINT( 1 ) NOT NULL ; - --- Add the 'mapExpireTime' column -ALTER TABLE `mapOfTheDay` ADD `mapExpireTime` DATETIME NULL DEFAULT NULL AFTER `mapDate`; - -UPDATE mapOfTheDay -SET mapExpireTime = DATE_ADD(mapDate, INTERVAL 1 DAY) -WHERE mapType <> 5; - -UPDATE mapOfTheDay -SET mapExpireTime = DATE_ADD(mapDate, INTERVAL 2 DAY) -WHERE mapType = 5; - -ALTER TABLE `mapOfTheDay` CHANGE `mapExpireTime` `mapExpireTime` DATETIME NOT NULL; - -ALTER TABLE `mapOfTheDay` ADD INDEX ( `mapExpireTime` , `mapType` ); - --- Move shit from userData table to users -ALTER TABLE `users` ADD `displayColor` VARCHAR( 7 ) NOT NULL DEFAULT '#cccccc' , -ADD `wallColor` VARCHAR( 7 ) NOT NULL DEFAULT '#666666' , -ADD `wallEmblem` VARCHAR( 64 ) NOT NULL DEFAULT 'blank.png'; - -UPDATE users, userData -SET users.displayColor = userData.displayColor, - users.wallColor = userData.wallColor, - users.wallEmblem = userData.wallEmblem -WHERE users.ID = userData.userID; - -DROP TABLE `userData`; - -ALTER TABLE `users` ADD `dateChatGaggedUntil` DATETIME NULL , -ADD `isOptedOutOfEmails` BOOLEAN NOT NULL DEFAULT '0'; - - --- Most special maps (and some others) have the map-name in the map code. Copy it to the name column. --- Add column: -ALTER TABLE `maps` ADD `name` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `code`; - --- Insert values -UPDATE maps -INNER JOIN -( - -- Get the code substring, starting at the map name - SELECT SUBSTRING(code, LENGTH(SUBSTRING_INDEX(code, '.', 5))+2) AS codeSubstring, Id - FROM (SELECT code, Id FROM maps) AS mapsInner -) AS mapsOuter ON maps.Id = mapsOuter.Id -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = SUBSTRING(codeSubstring, 1, INSTR(codeSubstring, ':')-2) -WHERE maps.name IS NULL; - --- Rename the remaining maps -UPDATE maps -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = 'Simple' -WHERE mapOfTheDay.mapType = 1 -AND (maps.name IS NULL OR maps.name = ''); - -UPDATE maps -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = 'Normal' -WHERE mapOfTheDay.mapType = 2 -AND (maps.name IS NULL OR maps.name = ''); - -UPDATE maps -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = 'Complex' -WHERE mapOfTheDay.mapType = 3 -AND (maps.name IS NULL OR maps.name = ''); - -UPDATE maps -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = 'Special' -WHERE mapOfTheDay.mapType = 4 -AND (maps.name IS NULL OR maps.name = ''); - -UPDATE maps -INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId -SET maps.name = 'Ultra Complex' -WHERE mapOfTheDay.mapType = 5 -AND (maps.name IS NULL OR maps.name = ''); - --- Need to support bigger solutions for these crazy new unlimited-tower maps -ALTER TABLE `solutions` CHANGE `solution` `solution` VARCHAR( 4095 ) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL; - --- Fix Typo -UPDATE unlocks SET name = 'Light Diagonal Cover' WHERE name = 'Light Diagnol Cover'; - --- Change index to a unique index --- If any duplicates exist you can find them with this: --- select mapID, count(*) from `solutions` group by mapID, userID HAVING count(*) > 1 -ALTER TABLE `solutions` DROP INDEX `userID` , -ADD UNIQUE `userIDmapID` ( `userID` , `mapID` ); - --- The new code relies on these being unique, so we'll enforce that through indices. Make sure you --- verify these ARE unique in the DB before adding these indices (they weren't in my test DB) --- Should probably run these two commands by themselves, one at a time -ALTER TABLE `challengeMaps` ADD UNIQUE ( `challengeTier` , `challengeSuborder`); - -ALTER TABLE `challenges` -DROP INDEX `mapID` , -ADD UNIQUE `mapID` ( `mapID` , `ordering` ); - --- Optimizing the daily update queries -ALTER TABLE `solutions` ADD INDEX ( `isHighScore` ); - -ALTER TABLE `users` ADD `wallOrientation` TINYINT NOT NULL DEFAULT '0' AFTER `wallEmblem`; - -ALTER TABLE `solutions` DROP INDEX `userIDmapID` , -ADD UNIQUE `mapIDuserID` ( `mapID` , `userID` );
\ No newline at end of file |