SQL updates reset.

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