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

(cherry picked from commit d5fb0a30ec62f3799416326d81d5d14992f9a6df)

1 files changed, 59 insertions, 0 deletions

diff --git a/sql/updates/world/master/2021_12_11_08_world_2019_06_15_01_world.sql b/sql/updates/world/master/2021_12_11_08_world_2019_06_15_01_world.sql
new file mode 100644
index 00000000000..8e2bceaa6b1
--- /dev/null
+++ b/sql/updates/world/master/2021_12_11_08_world_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` INT(10) UNSIGNED NOT NULL,
+  `School` TINYINT(3) UNSIGNED NOT NULL,
+  `Resistance` SMALLINT(6) NOT NULL,
+  `VerifiedBuild` INT(11) NOT NULL 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` INT(10) unsigned NOT NULL,
+  `Index` TINYINT(3) unsigned NOT NULL DEFAULT '0',
+  `Spell` INT(10) unsigned NOT NULL,
+  `VerifiedBuild` INT(11) NOT NULL 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`;