Core/DBLayer: Move tables reserved_name, gameobject_respawn and creature_respawn from WORLD database to CHARACTER database as it's content is realm-specific and should be preserved (thanks to leak for the cleanup)

*** TO PRESERVE (COPY) THE DATA CONTAINED IN THE OLD TABLES, YOU MUST FOLLOW THE FOLLOWING SQL-RECIPE (REPLACE DATABASENAMES WHERE NEEDED!) ***

-- Move creature_respawn from world to characters db
INSERT INTO `characters`.`creature_respawn` (`guid`, `respawntime`, `instance`)
SELECT `guid, `respawntime` `instance` * FROM `world`.`creature_respawn`;
-- Remove creature_respawn table from world db
DROP TABLE `world`.`creature_respawn`;

-- Move gameobject_respawn from world to characters db
INSERT INTO `characters`.`gameobject_respawn` (`guid`, `respawntime`, `instance`)
SELECT `guid`, `respawntime`, `instance` FROM `world`.`gameobject_respawn`;
-- Remove creature_respawn table from world db
DROP TABLE `world`.`gameobject_respawn`;

-- Move reserved names from world to characters db
INSERT INTO `characters`.`reserved_name` (`name`)
SELECT `name` FROM `world`.`reserved_name`;
-- Remove reserved_names table from world db
DROP TABLE `world`.`reserved_name`;

*** THE ABOVE MUST BE DONE, OR EXISTING INSTANCES WILL BE FULLY RESPAWNED - YOU HAVE BEEN WARNED ***
Closes issue 4842. Closes issue 4849.

--HG--
branch : trunk

7 files changed, 30 insertions, 51 deletions

diff --git a/src/server/game/Battlegrounds/Battleground.cpp b/src/server/game/Battlegrounds/Battleground.cpp
index 1bfb42ff9e4..be341350964 100755
--- a/src/server/game/Battlegrounds/Battleground.cpp
+++ b/src/server/game/Battlegrounds/Battleground.cpp
@@ -217,8 +217,8 @@ Battleground::~Battleground()
     if (GetInstanceID())                                    // not spam by useless queries in case BG templates
     {
         // delete creature and go respawn times
-        WorldDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'",GetInstanceID());
-        WorldDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'",GetInstanceID());
+        CharacterDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'",GetInstanceID());
+        CharacterDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'",GetInstanceID());
         // delete instance from db
         CharacterDatabase.PExecute("DELETE FROM instance WHERE id = '%u'",GetInstanceID());
         // remove from battlegrounds
diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp
index 5faa13ac575..47b27d34d1b 100755
--- a/src/server/game/Globals/ObjectMgr.cpp
+++ b/src/server/game/Globals/ObjectMgr.cpp
@@ -1814,7 +1814,7 @@ void ObjectMgr::LoadCreatureRespawnTimes()
 {
     uint32 count = 0;
 
-    QueryResult result = WorldDatabase.Query("SELECT guid,respawntime,instance FROM creature_respawn");
+    QueryResult result = CharacterDatabase.Query("SELECT guid,respawntime,instance FROM creature_respawn");
 
     if (!result)
     {
@@ -1850,12 +1850,12 @@ void ObjectMgr::LoadCreatureRespawnTimes()
 void ObjectMgr::LoadGameobjectRespawnTimes()
 {
     // remove outdated data
-    PreparedStatement *stmt = WorldDatabase.GetPreparedStatement(WORLD_DEL_GAMEOBJECT_RESPAWN_TIMES);
-    WorldDatabase.Execute(stmt);
+    PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_GAMEOBJECT_RESPAWN_TIMES);
+    CharacterDatabase.Execute(stmt);
 
     uint32 count = 0;
 
-    QueryResult result = WorldDatabase.Query("SELECT guid,respawntime,instance FROM gameobject_respawn");
+    QueryResult result = CharacterDatabase.Query("SELECT guid,respawntime,instance FROM gameobject_respawn");
 
     if (!result)
     {
@@ -7518,18 +7518,18 @@ void ObjectMgr::SaveCreatureRespawnTime(uint32 loguid, uint32 instance, time_t t
         m_CreatureRespawnTimesMtx.release();
     }
 
-    PreparedStatement* stmt = WorldDatabase.GetPreparedStatement(WORLD_DEL_CRESPAWNTIME);
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CRESPAWNTIME);
     stmt->setUInt32(0, loguid);
     stmt->setUInt32(1, instance);
-    WorldDatabase.Execute(stmt);
+    CharacterDatabase.Execute(stmt);
 
     if (t)
     {
-        stmt = WorldDatabase.GetPreparedStatement(WORLD_ADD_CRESPAWNTIME);
+        stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_CRESPAWNTIME);
         stmt->setUInt32(0, loguid);
         stmt->setUInt64(1, uint64(t));
         stmt->setUInt32(2, instance);
-        WorldDatabase.Execute(stmt);
+        CharacterDatabase.Execute(stmt);
     }
 }
 
@@ -7552,9 +7552,9 @@ void ObjectMgr::SaveGORespawnTime(uint32 loguid, uint32 instance, time_t t)
         m_GORespawnTimesMtx.release();
     }
 
-    WorldDatabase.PExecute("DELETE FROM gameobject_respawn WHERE guid = '%u' AND instance = '%u'", loguid, instance);
+    CharacterDatabase.PExecute("DELETE FROM gameobject_respawn WHERE guid = '%u' AND instance = '%u'", loguid, instance);
     if (t)
-        WorldDatabase.PExecute("INSERT INTO gameobject_respawn VALUES ('%u', '" UI64FMTD "', '%u')", loguid, uint64(t), instance);
+        CharacterDatabase.PExecute("INSERT INTO gameobject_respawn VALUES ('%u', '" UI64FMTD "', '%u')", loguid, uint64(t), instance);
 }
 
 void ObjectMgr::DeleteRespawnTimeForInstance(uint32 instance)
@@ -7586,8 +7586,8 @@ void ObjectMgr::DeleteRespawnTimeForInstance(uint32 instance)
         }
         m_CreatureRespawnTimesMtx.release();
     }
-    WorldDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'", instance);
-    WorldDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'", instance);
+    CharacterDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'", instance);
+    CharacterDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'", instance);
 }
 
 void ObjectMgr::DeleteGOData(uint32 guid)
@@ -7725,7 +7725,7 @@ void ObjectMgr::LoadReservedPlayersNames()
 {
     m_ReservedNames.clear();                                // need for reload case
 
-    QueryResult result = WorldDatabase.Query("SELECT name FROM reserved_name");
+    QueryResult result = CharacterDatabase.Query("SELECT name FROM reserved_name");
 
     uint32 count = 0;
 
diff --git a/src/server/game/Instances/InstanceSaveMgr.cpp b/src/server/game/Instances/InstanceSaveMgr.cpp
index aee36dce029..882a8653b35 100755
--- a/src/server/game/Instances/InstanceSaveMgr.cpp
+++ b/src/server/game/Instances/InstanceSaveMgr.cpp
@@ -265,7 +265,10 @@ void InstanceSaveManager::CleanupInstances()
     _DelHelper("character_instance.guid, instance", "character_instance", "LEFT JOIN instance ON character_instance.instance = instance.id WHERE instance.id IS NULL");
     _DelHelper("guid, instance", "group_instance", "LEFT JOIN instance ON group_instance.instance = instance.id WHERE instance.id IS NULL");
 
-    // creature_respawn and gameobject_respawn are in another database
+    // Clean respawn data from non existing instances
+    CharacterDatabase.PExecute("DELETE FROM creature_respawn WHERE instance <> 0 AND instance NOT IN (SELECT id FROM instance)");
+    CharacterDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance <> 0 AND instance NOT IN (SELECT id FROM instance)");
+
     // first, obtain total instance set
     std::set<uint32> InstanceSet;
     QueryResult result = CharacterDatabase.Query("SELECT id FROM instance");
@@ -279,32 +282,6 @@ void InstanceSaveManager::CleanupInstances()
         while (result->NextRow());
     }
 
-    // creature_respawn
-    result = WorldDatabase.Query("SELECT DISTINCT(instance) FROM creature_respawn WHERE instance <> 0");
-    if (result)
-    {
-        do
-        {
-            Field *fields = result->Fetch();
-            if (InstanceSet.find(fields[0].GetUInt32()) == InstanceSet.end())
-                WorldDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'", fields[0].GetUInt32());
-        }
-        while (result->NextRow());
-    }
-
-    // gameobject_respawn
-    result = WorldDatabase.Query("SELECT DISTINCT(instance) FROM gameobject_respawn WHERE instance <> 0");
-    if (result)
-    {
-        do
-        {
-            Field *fields = result->Fetch();
-            if (InstanceSet.find(fields[0].GetUInt32()) == InstanceSet.end())
-                WorldDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'", fields[0].GetUInt32());
-        }
-        while (result->NextRow());
-    }
-
     // characters
     result = CharacterDatabase.Query("SELECT DISTINCT(instance_id) FROM characters WHERE instance_id <> 0");
     if (result)
@@ -368,8 +345,8 @@ void InstanceSaveManager::PackInstances()
         if (*i != InstanceNumber)
         {
             // remap instance id
-            WorldDatabase.PExecute("UPDATE creature_respawn SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
-            WorldDatabase.PExecute("UPDATE gameobject_respawn SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
+            CharacterDatabase.PExecute("UPDATE creature_respawn SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
+            CharacterDatabase.PExecute("UPDATE gameobject_respawn SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
             CharacterDatabase.PExecute("UPDATE characters SET instance_id = '%u' WHERE instance_id = '%u'", InstanceNumber, *i);
             CharacterDatabase.PExecute("UPDATE corpse SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
             CharacterDatabase.PExecute("UPDATE character_instance SET instance = '%u' WHERE instance = '%u'", InstanceNumber, *i);
@@ -420,7 +397,7 @@ void InstanceSaveManager::LoadResetTimes()
         while (result->NextRow());
 
         // update reset time for normal instances with the max creature respawn time + X hours
-        result = WorldDatabase.Query("SELECT MAX(respawntime), instance FROM creature_respawn WHERE instance > 0 GROUP BY instance");
+        result = CharacterDatabase.Query("SELECT MAX(respawntime), instance FROM creature_respawn WHERE instance > 0 GROUP BY instance");
         if (result)
         {
             do
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
index 4963a0b89f3..92257ba9ace 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
@@ -211,5 +211,9 @@ bool CharacterDatabaseConnection::Open()
     PrepareStatement(CHAR_CLEAN_GUILD_BANK_TABS, "DELETE FROM guild_bank_tab WHERE guildId NOT IN (SELECT guildid FROM guild)");
     PrepareStatement(CHAR_CLEAN_GUILD_BANK_RIGHTS, "DELETE FROM guild_bank_right WHERE guildId NOT IN (SELECT guildid FROM guild)");
     PrepareStatement(CHAR_CLEAN_GUILD_BANK_ITEMS, "DELETE FROM guild_bank_item WHERE guildId NOT IN (SELECT guildid FROM guild)");
+    PrepareStatement(CHAR_DEL_GAMEOBJECT_RESPAWN_TIMES, "DELETE FROM gameobject_respawn WHERE respawntime <= UNIX_TIMESTAMP(NOW())");
+    PrepareStatement(CHAR_DEL_CRESPAWNTIME, "DELETE FROM creature_respawn WHERE guid = ? AND instance = ?");
+    PrepareStatement(CHAR_ADD_CRESPAWNTIME, "INSERT INTO creature_respawn VALUES (?, ?, ?)");
+
     return true;
 }
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h
index 041afca2a23..e8fc1b64418 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.h
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.h
@@ -177,6 +177,10 @@ enum CharacterDatabaseStatements
     CHAR_CLEAN_GUILD_BANK_RIGHTS,
     CHAR_CLEAN_GUILD_BANK_ITEMS,
 
+    CHAR_DEL_GAMEOBJECT_RESPAWN_TIMES,
+    CHAR_DEL_CRESPAWNTIME,
+    CHAR_ADD_CRESPAWNTIME,
+
     MAX_CHARACTERDATABASE_STATEMENTS,
 };
 
diff --git a/src/server/shared/Database/Implementation/WorldDatabase.cpp b/src/server/shared/Database/Implementation/WorldDatabase.cpp
index 71cf9c82d45..57c6312f1b8 100755
--- a/src/server/shared/Database/Implementation/WorldDatabase.cpp
+++ b/src/server/shared/Database/Implementation/WorldDatabase.cpp
@@ -29,12 +29,9 @@ bool WorldDatabaseConnection::Open()
         LOAD YOUR PREPARED STATEMENTS HERE
         ##################################
     */
-    PrepareStatement(WORLD_DEL_CRESPAWNTIME, "DELETE FROM creature_respawn WHERE guid = ? AND instance = ?");
-    PrepareStatement(WORLD_ADD_CRESPAWNTIME, "INSERT INTO creature_respawn VALUES (?, ?, ?)");
     PrepareStatement(WORLD_LOAD_QUEST_POOLS, "SELECT entry, pool_entry FROM pool_quest");
     PrepareStatement(WORLD_DEL_CRELINKED_RESPAWN, "DELETE FROM creature_linked_respawn WHERE guid = ?");
     PrepareStatement(WORLD_REP_CRELINKED_RESPAWN, "REPLACE INTO creature_linked_respawn (guid,linkedGuid) VALUES (?, ?)");
-    PrepareStatement(WORLD_DEL_GAMEOBJECT_RESPAWN_TIMES, "DELETE FROM gameobject_respawn WHERE respawntime <= UNIX_TIMESTAMP(NOW())");
     PrepareStatement(WORLD_LOAD_CRETEXT, "SELECT entry, groupid, id, text, type, language, probability, emote, duration, sound FROM creature_text");
     PrepareStatement(WORLD_LOAD_SMART_SCRIPTS,  "SELECT entryorguid, source_type, id, link, event_type, event_phase_mask, event_chance, event_flags, event_param1, event_param2, event_param3, event_param4, action_type, action_param1, action_param2, action_param3, action_param4, action_param5, action_param6, target_type, target_param1, target_param2, target_param3, target_x, target_y, target_z, target_o FROM smart_scripts ORDER BY entryorguid, source_type, id, link");
     PrepareStatement(WORLD_LOAD_SMARTAI_WP,  "SELECT entry, pointid, position_x, position_y, position_z FROM waypoints ORDER BY entry, pointid");
diff --git a/src/server/shared/Database/Implementation/WorldDatabase.h b/src/server/shared/Database/Implementation/WorldDatabase.h
index 1995b8e6301..c502ff019f9 100755
--- a/src/server/shared/Database/Implementation/WorldDatabase.h
+++ b/src/server/shared/Database/Implementation/WorldDatabase.h
@@ -42,12 +42,9 @@ enum WorldDatabaseStatements
         name for a suiting suffix.
     */
 
-    WORLD_DEL_CRESPAWNTIME,
-    WORLD_ADD_CRESPAWNTIME,
     WORLD_LOAD_QUEST_POOLS,
     WORLD_DEL_CRELINKED_RESPAWN,
     WORLD_REP_CRELINKED_RESPAWN,
-    WORLD_DEL_GAMEOBJECT_RESPAWN_TIMES,
     WORLD_LOAD_CRETEXT,
     WORLD_LOAD_SMART_SCRIPTS,
     WORLD_LOAD_SMARTAI_WP,