From ebbafff46aec8d47159489b1b6a46e6ce1f71eb8 Mon Sep 17 00:00:00 2001 From: BlueRaja Date: Sun, 17 Feb 2013 03:18:56 -0600 Subject: Added SQL to set the 'name' column in the 'maps' table --- db updates.sql | 46 +++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 45 insertions(+), 1 deletion(-) (limited to 'db updates.sql') diff --git a/db updates.sql b/db updates.sql index 3f2c593..100c9ed 100644 --- a/db updates.sql +++ b/db updates.sql @@ -56,4 +56,48 @@ WHERE users.ID = userData.userID; DROP TABLE `userData`; ALTER TABLE `users` ADD `dateChatGaggedUntil` DATETIME NULL , -ADD `isOptedOutOfEmails` BOOLEAN NOT NULL DEFAULT '0'; \ No newline at end of file +ADD `isOptedOutOfEmails` BOOLEAN NOT NULL DEFAULT '0'; + +-- Set the name column in maps table +UPDATE maps +INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId +SET maps.name = 'Simple' +WHERE mapOfTheDay.mapType = 1 +AND maps.name IS NULL; + +UPDATE maps +INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId +SET maps.name = 'Normal' +WHERE mapOfTheDay.mapType = 2 +AND maps.name IS NULL; + +UPDATE maps +INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId +SET maps.name = 'Complex' +WHERE mapOfTheDay.mapType = 3 +AND maps.name IS NULL; + +UPDATE maps +INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId +SET maps.name = 'Ultra Complex' +WHERE mapOfTheDay.mapType = 5 +AND maps.name IS NULL; + +-- Most special maps (and some others) have the map-name in the map code. Copy it to the name column. +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 special maps +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 = ''); \ No newline at end of file -- cgit v1.2.3