Merge

1 files changed, 52 insertions, 34 deletions

diff --git a/db updates.sql b/db updates.sql
index 403fc43..e3eea2c 100644
--- a/db updates.sql
+++ b/db updates.sql
@@ -1,36 +1,54 @@
--- db updates reset after patch on March 24th.
-
---Dialog to challenges:
-ALTER TABLE  `challenges` ADD  `dialogStart` VARCHAR( 200 ) DEFAULT NULL ,
-ADD  `dialogFail` VARCHAR( 200 ) DEFAULT NULL ,
-ADD  `dialogSuccess` VARCHAR( 200 ) DEFAULT NULL ;
-
---InnoDB engine switch:
-ALTER TABLE achievements ENGINE = InnoDB;
-ALTER TABLE challengeMaps ENGINE = InnoDB;
-ALTER TABLE challenges ENGINE = InnoDB;
-ALTER TABLE challengeSolutions ENGINE = InnoDB;
-ALTER TABLE chat ENGINE = InnoDB;
-ALTER TABLE emailQueue ENGINE = InnoDB;
-ALTER TABLE emails ENGINE = InnoDB;
-ALTER TABLE mapOfTheDay ENGINE = InnoDB;
-ALTER TABLE maps ENGINE = InnoDB;
-ALTER TABLE settings ENGINE = InnoDB;
-ALTER TABLE solutions ENGINE = InnoDB;
-ALTER TABLE users ENGINE = InnoDB;
-
---Add index necessary for stats updates
-ALTER TABLE `solutions`
-ADD INDEX `mapIDMoves` ( `mapID` , `moves` );
-
---I forgot this table:
-ALTER TABLE unlocks ENGINE = InnoDB;
-
---Straggling depreciated tables:
-DROP TABLE statistics;
---We must have copied the data from this, right?
-DROP TABLE userData;
-DROP TABLE guestSolutions;
+-- Updates required after 05-28-13:
+
+-- Remove unnecessary table
+DROP TABLE `guestSolutions`;
+DROP TABLE `statistics`;
+
+-- ADD FOREIGN KEY CONSTRAINTS --
+-- Achievements
+DELETE FROM achievements WHERE userID NOT IN (SELECT ID from users);
+ALTER TABLE `achievements` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Challenges
+ALTER TABLE `challenges` ADD FOREIGN KEY ( `mapID` ) REFERENCES `challengeMaps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Challenge solutions
+DELETE FROM challengeSolutions WHERE userID NOT IN (SELECT ID from users);
+ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `challengeID` ) REFERENCES `challenges` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Chat
+ALTER TABLE `chat` CHANGE `userID` `userID` INT( 11 ) NULL;
+UPDATE chat SET userID = NULL WHERE userID <= 0;
+ALTER TABLE `chat` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Email Queue
+ALTER TABLE `emailQueue` ADD FOREIGN KEY ( `emailID` ) REFERENCES `emails` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;
 
+-- Emails
+ALTER TABLE `emails` CHANGE `fromUserID` `fromUserID` INT( 11 ) NULL;
+UPDATE emails SET fromUserID = NULL WHERE fromUserID <= 0;
+ALTER TABLE `emails` ADD FOREIGN KEY ( `fromUserID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
 
---The above was executed on production on 05-28-2013
\ No newline at end of file
+-- MapOfTheDay
+DELETE FROM mapOfTheDay WHERE mapID NOT IN (SELECT ID FROM maps);
+ALTER TABLE `mapOfTheDay` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Solutions
+DELETE FROM `solutions` WHERE userID NOT IN (SELECT ID FROM users);
+ALTER TABLE `solutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `solutions` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Unlocks
+ALTER TABLE `unlocks` ENGINE = InnoDB; -- Looks like this was forgotten?
+DELETE FROM unlocks WHERE userID NOT IN (SELECT ID FROM users);
+ALTER TABLE `unlocks` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- UserData
+ALTER TABLE `userData` ENGINE = InnoDB; -- Looks like this was forgotten?
+ALTER TABLE `userData` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- END ADD FOREIGN KEY CONSTRAINTS
+
+-- This table is depreciated too:
+DROP TABLE userData;