diff options
| -rw-r--r-- | db updates.sql | 252 |
1 files changed, 128 insertions, 124 deletions
diff --git a/db updates.sql b/db updates.sql index ea17b9c..3cef782 100644 --- a/db updates.sql +++ b/db updates.sql @@ -1,127 +1,3 @@ --- 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` ); - - - -- TABLES: -- -- Table structure for table `challengeMaps` @@ -241,3 +117,131 @@ CREATE 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` );
\ No newline at end of file |