From ebbafff46aec8d47159489b1b6a46e6ce1f71eb8 Mon Sep 17 00:00:00 2001
From: BlueRaja <BlueRaja.admin@gmail.com>
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

