Core/CreatureTemplate: Outsourced resistances and spells into creatur… (#23343)

* Core/CreatureTemplate: Outsourced resistances and spells into creature_template_spell and creature_template_resistance

* Rename 2019_99_99_00_world.sql to 2019_06_15_01_world.sql

4 files changed, 214 insertions, 88 deletions

diff --git a/sql/updates/world/3.3.5/2019_06_15_01_world.sql b/sql/updates/world/3.3.5/2019_06_15_01_world.sql
new file mode 100644
index 00000000000..0b6b18c60f9
--- /dev/null
+++ b/sql/updates/world/3.3.5/2019_06_15_01_world.sql
@@ -0,0 +1,59 @@
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET NAMES utf8 */;
+/*!50503 SET NAMES utf8mb4 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+
+DROP TABLE IF EXISTS `creature_template_resistance`;
+CREATE TABLE IF NOT EXISTS `creature_template_resistance` (
+  `CreatureID` MEDIUMINT(8) UNSIGNED NOT NULL,
+  `School` TINYINT(6) UNSIGNED NOT NULL,
+  `Resistance` SMALLINT(6) DEFAULT NULL,
+  `VerifiedBuild` SMALLINT(5) DEFAULT '0',
+  PRIMARY KEY (`CreatureID`, `School`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `creature_template_spell`;
+CREATE TABLE IF NOT EXISTS `creature_template_spell` (
+  `CreatureID` mediumint(8) unsigned NOT NULL,
+  `Index` TINYINT(6) unsigned NOT NULL DEFAULT '0',
+  `Spell` mediumint(8) unsigned DEFAULT NULL,
+  `VerifiedBuild` smallint(5) DEFAULT '0',
+  PRIMARY KEY (`CreatureID`, `Index`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
+/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+
+INSERT INTO creature_template_resistance SELECT entry, 1, resistance1, VerifiedBuild FROM creature_template WHERE resistance1 <> 0;
+INSERT INTO creature_template_resistance SELECT entry, 2, resistance2, VerifiedBuild FROM creature_template WHERE resistance2 <> 0;
+INSERT INTO creature_template_resistance SELECT entry, 3, resistance3, VerifiedBuild FROM creature_template WHERE resistance3 <> 0;
+INSERT INTO creature_template_resistance SELECT entry, 4, resistance4, VerifiedBuild FROM creature_template WHERE resistance4 <> 0;
+INSERT INTO creature_template_resistance SELECT entry, 5, resistance5, VerifiedBuild FROM creature_template WHERE resistance5 <> 0;
+INSERT INTO creature_template_resistance SELECT entry, 6, resistance6, VerifiedBuild FROM creature_template WHERE resistance6 <> 0;
+
+INSERT INTO creature_template_spell SELECT entry, 0, spell1, VerifiedBuild FROM creature_template WHERE spell1 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 1, spell2, VerifiedBuild FROM creature_template WHERE spell2 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 2, spell3, VerifiedBuild FROM creature_template WHERE spell3 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 3, spell4, VerifiedBuild FROM creature_template WHERE spell4 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 4, spell5, VerifiedBuild FROM creature_template WHERE spell5 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 5, spell6, VerifiedBuild FROM creature_template WHERE spell6 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 6, spell7, VerifiedBuild FROM creature_template WHERE spell7 <> 0;
+INSERT INTO creature_template_spell SELECT entry, 7, spell8, VerifiedBuild FROM creature_template WHERE spell8 <> 0;
+
+ALTER TABLE `creature_template`
+	DROP COLUMN `resistance1`,
+	DROP COLUMN `resistance2`,
+	DROP COLUMN `resistance3`,
+	DROP COLUMN `resistance4`,
+	DROP COLUMN `resistance5`,
+	DROP COLUMN `resistance6`,
+	DROP COLUMN `spell1`,
+	DROP COLUMN `spell2`,
+	DROP COLUMN `spell3`,
+	DROP COLUMN `spell4`,
+	DROP COLUMN `spell5`,
+	DROP COLUMN `spell6`,
+	DROP COLUMN `spell7`,
+	DROP COLUMN `spell8`;
diff --git a/src/server/database/Database/Implementation/WorldDatabase.cpp b/src/server/database/Database/Implementation/WorldDatabase.cpp
index 3098c0635af..ce466335f86 100644
--- a/src/server/database/Database/Implementation/WorldDatabase.cpp
+++ b/src/server/database/Database/Implementation/WorldDatabase.cpp
@@ -78,7 +78,7 @@ void WorldDatabaseConnection::DoPrepareStatements()
     PrepareStatement(WORLD_SEL_WAYPOINT_SCRIPT_ID_BY_GUID, "SELECT id FROM waypoint_scripts WHERE guid = ?", CONNECTION_SYNCH);
     PrepareStatement(WORLD_DEL_CREATURE, "DELETE FROM creature WHERE guid = ?", CONNECTION_ASYNC);
     PrepareStatement(WORLD_SEL_COMMANDS, "SELECT name, permission, help FROM command", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_CREATURE_TEMPLATE, "SELECT entry, difficulty_entry_1, difficulty_entry_2, difficulty_entry_3, KillCredit1, KillCredit2, modelid1, modelid2, modelid3, modelid4, name, subname, IconName, gossip_menu_id, minlevel, maxlevel, exp, faction, npcflag, speed_walk, speed_run, scale, `rank`, dmgschool, BaseAttackTime, RangeAttackTime, BaseVariance, RangeVariance, unit_class, unit_flags, unit_flags2, dynamicflags, family, type, type_flags, lootid, pickpocketloot, skinloot, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, spell1, spell2, spell3, spell4, spell5, spell6, spell7, spell8, PetSpellDataId, VehicleId, mingold, maxgold, AIName, MovementType, ctm.Ground, ctm.Swim, ctm.Flight, ctm.Rooted, ctm.Chase, ctm.Random, HoverHeight, HealthModifier, ManaModifier, ArmorModifier, DamageModifier, ExperienceModifier, RacialLeader, movementId, RegenHealth, mechanic_immune_mask, spell_school_immune_mask, flags_extra, ScriptName FROM creature_template ct LEFT JOIN creature_template_movement ctm ON ct.entry = ctm.CreatureId WHERE entry = ?", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_CREATURE_TEMPLATE, "SELECT entry, difficulty_entry_1, difficulty_entry_2, difficulty_entry_3, KillCredit1, KillCredit2, modelid1, modelid2, modelid3, modelid4, name, subname, IconName, gossip_menu_id, minlevel, maxlevel, exp, faction, npcflag, speed_walk, speed_run, scale, `rank`, dmgschool, BaseAttackTime, RangeAttackTime, BaseVariance, RangeVariance, unit_class, unit_flags, unit_flags2, dynamicflags, family, type, type_flags, lootid, pickpocketloot, skinloot, PetSpellDataId, VehicleId, mingold, maxgold, AIName, MovementType, ctm.Ground, ctm.Swim, ctm.Flight, ctm.Rooted, ctm.Chase, ctm.Random, HoverHeight, HealthModifier, ManaModifier, ArmorModifier, DamageModifier, ExperienceModifier, RacialLeader, movementId, RegenHealth, mechanic_immune_mask, spell_school_immune_mask, flags_extra, ScriptName FROM creature_template ct LEFT JOIN creature_template_movement ctm ON ct.entry = ctm.CreatureId WHERE entry = ?", CONNECTION_SYNCH);
     PrepareStatement(WORLD_SEL_WAYPOINT_SCRIPT_BY_ID, "SELECT guid, delay, command, datalong, datalong2, dataint, x, y, z, o FROM waypoint_scripts WHERE id = ?", CONNECTION_SYNCH);
     PrepareStatement(WORLD_SEL_ITEM_TEMPLATE_BY_NAME, "SELECT entry FROM item_template WHERE name = ?", CONNECTION_SYNCH);
     PrepareStatement(WORLD_SEL_CREATURE_BY_ID, "SELECT guid FROM creature WHERE id = ?", CONNECTION_SYNCH);
diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp
index 26bde95c536..0305b35e692 100644
--- a/src/server/game/Globals/ObjectMgr.cpp
+++ b/src/server/game/Globals/ObjectMgr.cpp
@@ -371,7 +371,7 @@ void ObjectMgr::LoadCreatureTemplates()
     // 5. Select "Number to Insert", Initial number 1, Increase by 1
     // 6. Run this regex
     //  a.find    "\/\/[ ]+
-    //  b.replace "\r\n\t\t\/\/
+    //  b.replace "\r\n\t\t\/\/ 
 
     QueryResult result = WorldDatabase.Query(
         //  0
@@ -451,82 +451,54 @@ void ObjectMgr::LoadCreatureTemplates()
         // 37
         "skinloot,"
         // 38
-        "resistance1,"
-        // 39
-        "resistance2,"
-        // 40
-        "resistance3,"
-        // 41
-        "resistance4,"
-        // 42
-        "resistance5,"
-        // 43
-        "resistance6,"
-        // 44
-        "spell1,"
-        // 45
-        "spell2,"
-        // 46
-        "spell3,"
-        // 47
-        "spell4,"
-        // 48
-        "spell5,"
-        // 49
-        "spell6,"
-        // 50
-        "spell7,"
-        // 51
-        "spell8,"
-        // 52
         "PetSpellDataId,"
-        // 53
+        // 39
         "VehicleId,"
-        // 54
+        // 40
         "mingold,"
-        // 55
+        // 41
         "maxgold,"
-        // 56
+        // 42
         "AIName,"
-        // 57
+        // 43
         "MovementType,"
-        // 58
+        // 44
         "ctm.Ground,"
-        // 59
+        // 45
         "ctm.Swim,"
-        // 60
+        // 46
         "ctm.Flight,"
-        // 61
+        // 47
         "ctm.Rooted,"
-        // 62
+        // 48
         "ctm.Chase,"
-        // 63
+        // 49
         "ctm.Random,"
-        // 64
+        // 50
         "HoverHeight,"
-        // 65
+        // 51
         "HealthModifier,"
-        // 66
+        // 52
         "ManaModifier,"
-        // 67
+        // 53
         "ArmorModifier,"
-        // 68
+        // 54
         "DamageModifier,"
-        // 69
+        // 55
         "ExperienceModifier,"
-        // 70
+        // 56
         "RacialLeader,"
-        // 71
+        // 57
         "movementId,"
-        // 72
+        // 58
         "RegenHealth,"
-        // 73
+        // 59
         "mechanic_immune_mask,"
-        // 74
+        // 60
         "spell_school_immune_mask,"
-        // 75
+        // 61
         "flags_extra,"
-        // 76
+        // 62
         "ScriptName"
         " FROM creature_template ct"
         " LEFT JOIN creature_template_movement ctm ON ct.entry = ctm.CreatureId");
@@ -544,6 +516,9 @@ void ObjectMgr::LoadCreatureTemplates()
         LoadCreatureTemplate(fields);
     } while (result->NextRow());
 
+    LoadCreatureTemplateResistances();
+    LoadCreatureTemplateSpells();
+
     // Checking needs to be done after loading because of the difficulty self referencing
     for (auto const& ctPair : _creatureTemplateStore)
         CheckCreatureTemplate(&ctPair.second);
@@ -598,49 +573,139 @@ void ObjectMgr::LoadCreatureTemplate(Field* fields)
     creatureTemplate.SkinLootId       = fields[37].GetUInt32();
 
     for (uint8 i = SPELL_SCHOOL_HOLY; i < MAX_SPELL_SCHOOL; ++i)
-        creatureTemplate.resistance[i] = fields[38 + i - 1].GetInt16();
+        creatureTemplate.resistance[i] = 0;
 
     for (uint8 i = 0; i < MAX_CREATURE_SPELLS; ++i)
-        creatureTemplate.spells[i] = fields[44 + i].GetUInt32();
+        creatureTemplate.spells[i] = 0;
 
-    creatureTemplate.PetSpellDataId = fields[52].GetUInt32();
-    creatureTemplate.VehicleId      = fields[53].GetUInt32();
-    creatureTemplate.mingold        = fields[54].GetUInt32();
-    creatureTemplate.maxgold        = fields[55].GetUInt32();
-    creatureTemplate.AIName         = fields[56].GetString();
-    creatureTemplate.MovementType   = fields[57].GetUInt8();
-    if (!fields[58].IsNull())
-        creatureTemplate.Movement.Ground = static_cast<CreatureGroundMovementType>(fields[58].GetUInt8());
+    creatureTemplate.PetSpellDataId = fields[38].GetUInt32();
+    creatureTemplate.VehicleId      = fields[39].GetUInt32();
+    creatureTemplate.mingold        = fields[40].GetUInt32();
+    creatureTemplate.maxgold        = fields[41].GetUInt32();
+    creatureTemplate.AIName         = fields[42].GetString();
+    creatureTemplate.MovementType   = fields[43].GetUInt8();
+    if (!fields[44].IsNull())
+        creatureTemplate.Movement.Ground = static_cast<CreatureGroundMovementType>(fields[44].GetUInt8());
 
-    if (!fields[59].IsNull())
-        creatureTemplate.Movement.Swim = fields[59].GetBool();
+    if (!fields[45].IsNull())
+        creatureTemplate.Movement.Swim = fields[45].GetBool();
 
-    if (!fields[60].IsNull())
-        creatureTemplate.Movement.Flight = static_cast<CreatureFlightMovementType>(fields[60].GetUInt8());
+    if (!fields[46].IsNull())
+        creatureTemplate.Movement.Flight = static_cast<CreatureFlightMovementType>(fields[46].GetUInt8());
 
-    if (!fields[61].IsNull())
-        creatureTemplate.Movement.Rooted = fields[61].GetBool();
+    if (!fields[47].IsNull())
+        creatureTemplate.Movement.Rooted = fields[47].GetBool();
 
-    if (!fields[62].IsNull())
-        creatureTemplate.Movement.Chase = static_cast<CreatureChaseMovementType>(fields[62].GetUInt8());
+    if (!fields[48].IsNull())
+        creatureTemplate.Movement.Chase = static_cast<CreatureChaseMovementType>(fields[48].GetUInt8());
+
+    if (!fields[49].IsNull())
+        creatureTemplate.Movement.Random = static_cast<CreatureRandomMovementType>(fields[49].GetUInt8());
+
+    creatureTemplate.HoverHeight    = fields[50].GetFloat();
+    creatureTemplate.ModHealth      = fields[51].GetFloat();
+    creatureTemplate.ModMana        = fields[52].GetFloat();
+    creatureTemplate.ModArmor       = fields[53].GetFloat();
+    creatureTemplate.ModDamage      = fields[54].GetFloat();
+    creatureTemplate.ModExperience  = fields[55].GetFloat();
+    creatureTemplate.RacialLeader   = fields[56].GetBool();
+
+    creatureTemplate.movementId            = fields[57].GetUInt32();
+    creatureTemplate.RegenHealth           = fields[58].GetBool();
+    creatureTemplate.MechanicImmuneMask    = fields[59].GetUInt32();
+    creatureTemplate.SpellSchoolImmuneMask = fields[60].GetUInt32();
+    creatureTemplate.flags_extra           = fields[61].GetUInt32();
+    creatureTemplate.ScriptID              = GetScriptId(fields[62].GetString());
+}
+
+void ObjectMgr::LoadCreatureTemplateResistances()
+{
+    uint32 oldMSTime = getMSTime();
 
-    if (!fields[63].IsNull())
-        creatureTemplate.Movement.Random = static_cast<CreatureRandomMovementType>(fields[63].GetUInt8());
+    //                                               0           1       2
+    QueryResult result = WorldDatabase.Query("SELECT CreatureID, School, Resistance FROM creature_template_resistance");
 
-    creatureTemplate.HoverHeight    = fields[64].GetFloat();
-    creatureTemplate.ModHealth      = fields[65].GetFloat();
-    creatureTemplate.ModMana        = fields[66].GetFloat();
-    creatureTemplate.ModArmor       = fields[67].GetFloat();
-    creatureTemplate.ModDamage      = fields[68].GetFloat();
-    creatureTemplate.ModExperience  = fields[69].GetFloat();
-    creatureTemplate.RacialLeader   = fields[70].GetBool();
+    if (!result)
+    {
+        TC_LOG_INFO("server.loading", ">> Loaded 0 creature template resistance definitions. DB table `creature_template_resistance` is empty.");
+        return;
+    }
+
+    uint32 count = 0;
+
+    do
+    {
+        Field* fields = result->Fetch();
+
+        uint32 creatureID = fields[0].GetUInt32();
+        uint8 school      = fields[1].GetUInt8();
+
+        if (school == SPELL_SCHOOL_NORMAL || school >= MAX_SPELL_SCHOOL)
+        {
+            TC_LOG_INFO("sql.sql", "creature_template_resistance has resistance definitions for creature %u but this school %u doesn't exist", creatureID, school);
+            continue;
+        }
+
+        CreatureTemplateContainer::iterator itr = _creatureTemplateStore.find(creatureID);
+        if (itr == _creatureTemplateStore.end())
+        {
+            TC_LOG_INFO("sql.sql", "creature_template_resistance has resistance definitions for creature %u but this creature doesn't exist", creatureID);
+            continue;
+        }
+
+        CreatureTemplate& creatureTemplate = itr->second;
+        creatureTemplate.resistance[school] = fields[2].GetInt16();
+
+        ++count;
+
+    } while (result->NextRow());
+
+    TC_LOG_INFO("server.loading", ">> Loaded %u creature template resistances in %u ms", count, GetMSTimeDiffToNow(oldMSTime));
+}
+
+void ObjectMgr::LoadCreatureTemplateSpells()
+{
+    uint32 oldMSTime = getMSTime();
+
+    //                                               0           1       2
+    QueryResult result = WorldDatabase.Query("SELECT CreatureID, `Index`, Spell FROM creature_template_spell");
+
+    if (!result)
+    {
+        TC_LOG_INFO("server.loading", ">> Loaded 0 creature template spell definitions. DB table `creature_template_spell` is empty.");
+        return;
+    }
+
+    uint32 count = 0;
+
+    do
+    {
+        Field* fields = result->Fetch();
+
+        uint32 creatureID = fields[0].GetUInt32();
+        uint8 index       = fields[1].GetUInt8();
+
+        if (index >= MAX_CREATURE_SPELLS)
+        {
+            TC_LOG_INFO("sql.sql", "creature_template_spell has spell definitions for creature %u with a incorrect index %u", creatureID, index);
+            continue;
+        }
+
+        CreatureTemplateContainer::iterator itr = _creatureTemplateStore.find(creatureID);
+        if (itr == _creatureTemplateStore.end())
+        {
+            TC_LOG_INFO("sql.sql", "creature_template_spell has spell definitions for creature %u but this creature doesn't exist", creatureID);
+            continue;
+        }
+
+        CreatureTemplate& creatureTemplate = itr->second;
+        creatureTemplate.spells[index] = fields[2].GetUInt32();;
+
+        ++count;
+
+    } while (result->NextRow());
 
-    creatureTemplate.movementId            = fields[71].GetUInt32();
-    creatureTemplate.RegenHealth           = fields[72].GetBool();
-    creatureTemplate.MechanicImmuneMask    = fields[73].GetUInt32();
-    creatureTemplate.SpellSchoolImmuneMask = fields[74].GetUInt32();
-    creatureTemplate.flags_extra           = fields[75].GetUInt32();
-    creatureTemplate.ScriptID              = GetScriptId(fields[76].GetString());
+    TC_LOG_INFO("server.loading", ">> Loaded %u creature template spells in %u ms", count, GetMSTimeDiffToNow(oldMSTime));
 }
 
 void ObjectMgr::LoadCreatureTemplateAddons()
@@ -1942,7 +2007,7 @@ void ObjectMgr::LoadCreatures()
 
     //                                               0              1   2    3           4           5           6            7        8             9              10
     QueryResult result = WorldDatabase.Query("SELECT creature.guid, id, map, position_x, position_y, position_z, orientation, modelid, equipment_id, spawntimesecs, spawndist, "
-    //   11               12         13       14            15         16         17          18          19                20                   21
+    //   11               12         13       14            15         16          17          18                19                   20                    21
         "currentwaypoint, curhealth, curmana, MovementType, spawnMask, phaseMask, eventEntry, pool_entry, creature.npcflag, creature.unit_flags, creature.dynamicflags, "
     //   22
         "creature.ScriptName "
diff --git a/src/server/game/Globals/ObjectMgr.h b/src/server/game/Globals/ObjectMgr.h
index b40f5173676..9d18188d014 100644
--- a/src/server/game/Globals/ObjectMgr.h
+++ b/src/server/game/Globals/ObjectMgr.h
@@ -1141,6 +1141,8 @@ class TC_GAME_API ObjectMgr
         void LoadCreatureTemplates();
         void LoadCreatureTemplateAddons();
         void LoadCreatureTemplate(Field* fields);
+        void LoadCreatureTemplateResistances();
+        void LoadCreatureTemplateSpells();
         void CheckCreatureTemplate(CreatureTemplate const* cInfo);
         void CheckCreatureMovement(char const* table, uint64 id, CreatureMovementData& creatureMovement);
         void LoadGameObjectQuestItems();