blob: 26495b3b1c109f3e9ff606b9bfdc10b5b4e464ce (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
-- 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 ;
-- Add index necessary for stats updates
ALTER TABLE `bluePathery`.`solutions`
ADD INDEX `mapIDMoves` ( `mapID` , `moves` );
-- 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;
-- 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
|
