Added SQL to set the 'name' column in the 'maps' table

1 files changed, 45 insertions, 1 deletions

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