The purpose of rewriting playercreateinfo_spell is to condense down the rampant duplication in the tables. I have compared the classes, and found that all characters have a common set of hidden skills. The rest of the skills are largely race or class specific, with a few exceptions. Wiping the data, and adding it back in the following query will reduce its size from over 2800 columns to just over 300:
– Class Setup
SET @RACE := 0;
SET @CLASS := 0;
– Delete all current info
DELETE FROM playercreateinfo_spell;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Universal
(@RACE,@CLASS,81,‘Dodge’),
(@RACE,@CLASS,203,‘Unarmed’),
(@RACE,@CLASS,204,‘Defense’),
(@RACE,@CLASS,522,‘SPELLDEFENSE (DND)’),
(@RACE,@CLASS,1843,‘Disarm’),
(@RACE,@CLASS,2382,‘Generic’),
(@RACE,@CLASS,2479,‘Honorless Target’),
(@RACE,@CLASS,3050,‘Detect’),
(@RACE,@CLASS,3365,‘Opening’),
(@RACE,@CLASS,6233,‘Closing’),
(@RACE,@CLASS,6246,‘Closing’),
(@RACE,@CLASS,6247,‘Opening’),
(@RACE,@CLASS,6477,‘Opening’),
(@RACE,@CLASS,6478,‘Opening’),
(@RACE,@CLASS,6603,‘Attack’),
(@RACE,@CLASS,7266,‘Duel’),
(@RACE,@CLASS,7267,‘Grovel’),
(@RACE,@CLASS,7355,‘Stuck’),
(@RACE,@CLASS,8386,‘Attacking’),
(@RACE,@CLASS,9125,‘Generic’),
(@RACE,@CLASS,21651,‘Opening’),
(@RACE,@CLASS,21652,‘Closing’),
(@RACE,@CLASS,22027,‘Remove Insignia’),
(@RACE,@CLASS,22810,‘Opening - No Text’),
(@RACE,@CLASS,61437,‘Opening’),
(@RACE,@CLASS,45927,‘Summon Friend’);
– Warrior
SET @CLASS := 1;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,107,‘Block’),
(@RACE,@CLASS,5301,‘Defensive State (DND)’),
(@RACE,@CLASS,9116,‘Shield’),
(@RACE,@CLASS,32215,‘Victorious State’),
– Armor Types
(@RACE,@CLASS,8737,‘Mail’),
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,78,‘Heroic Strike’),
(@RACE,@CLASS,2457,‘Battle Stance’);
– Paladin
SET @CLASS := 2;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,107,‘Block’),
(@RACE,@CLASS,9116,‘Shield’),
(@RACE,@CLASS,27762,‘Libram’),
(@RACE,@CLASS,60091,‘Judgement Anti-Parry/Dodge Passive’),
– Armor Types
(@RACE,@CLASS,8737,‘Mail’),
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,21084,‘Seal of Righteousness’),
(@RACE,@CLASS,635,‘Holy Light’);
– Hunter
SET @CLASS := 3;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,13358,‘Defensive State (DND)’),
(@RACE,@CLASS,24949,‘Defensive State 2 (DND)’),
(@RACE,@CLASS,34082,‘Advantaged State (DND)’),
– Weapon Types
(@RACE,@CLASS,197,‘Two-Handed Axes’),
– Armor Types
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,75,‘Auto Shot’),
(@RACE,@CLASS,2973,‘Raptor Strike’);
– Rogue
SET @CLASS := 4;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,674,‘Dual Wield’),
(@RACE,@CLASS,16092,‘Defensive State (DND)’),
(@RACE,@CLASS,21184,‘Rogue Passive (DND)’),
– Weapon Types
(@RACE,@CLASS,1180,‘Daggers’),
(@RACE,@CLASS,2764,‘Thrown’),
– Armor Types
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,1752,‘Sinister Strike’),
(@RACE,@CLASS,2098,‘Eviscerate’),
(@RACE,@CLASS,2567,‘Throw’);
– Priest
SET @CLASS := 5;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
– Weapon Types
(@RACE,@CLASS,198,‘One-Handed Maces’),
(@RACE,@CLASS,227,‘Staves’),
(@RACE,@CLASS,5009,‘Wands’),
– Armor Types
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,585,‘Smite’),
(@RACE,@CLASS,2050,‘Lesser Heal’),
(@RACE,@CLASS,5019,‘Shoot’);
– Death Knight
SET @CLASS := 6;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Special
(@RACE,@CLASS,3275,‘Linen Bandage’),
(@RACE,@CLASS,3276,‘Heavy Linen Bandage’),
(@RACE,@CLASS,3277,‘Wool Bandage’),
(@RACE,@CLASS,3278,‘Heavy Wool Bandage’),
(@RACE,@CLASS,7928,‘Silk Bandage’),
(@RACE,@CLASS,7929,‘Heavy Silk Bandage’),
(@RACE,@CLASS,7934,‘Anti-Venom’),
(@RACE,@CLASS,10840,‘Mageweave Bandage’),
(@RACE,@CLASS,10841,‘Heavy Mageweave Bandage’),
(@RACE,@CLASS,10846,‘First Aid’),
(@RACE,@CLASS,18629,‘Runecloth Bandage’),
(@RACE,@CLASS,18630,‘Heavy Runecloth Bandage’),
(@RACE,@CLASS,33391,‘Journeyman Riding’),
– Class Buff
(@RACE,@CLASS,674,‘Dual Wield’),
(@RACE,@CLASS,45903,‘Offensive State (DND)’),
(@RACE,@CLASS,49410,‘Forceful Deflection’),
(@RACE,@CLASS,52665,‘Sigil’),
(@RACE,@CLASS,61455,‘Runic Focus’),
– Weapon Types
(@RACE,@CLASS,196,‘One-Handed Axes’),
(@RACE,@CLASS,197,‘Two-Handed Axes’),
(@RACE,@CLASS,200,‘Polearms’),
(@RACE,@CLASS,201,‘One-Handed Swords’),
(@RACE,@CLASS,202,‘Two-Handed Swords’),
– Armor Types
(@RACE,@CLASS,750,‘Plate’),
(@RACE,@CLASS,8737,‘Mail’),
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,45462,‘Plague Strike’),
(@RACE,@CLASS,45477,‘Icy Touch’),
(@RACE,@CLASS,45902,‘Blood Strike’),
(@RACE,@CLASS,47541,‘Death Coil’),
(@RACE,@CLASS,48266,‘Blood Presence’),
(@RACE,@CLASS,49576,‘Death Grip’),
(@RACE,@CLASS,56816,‘Rune Strike’),
(@RACE,@CLASS,59879,‘Blood Plague’),
(@RACE,@CLASS,59921,‘Frost Fever’);
– Shaman
SET @CLASS := 7;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,107,‘Block’),
(@RACE,@CLASS,9116,‘Shield’),
(@RACE,@CLASS,27763,‘Totem’),
(@RACE,@CLASS,75461,‘Flame Shock Passive’),
– Weapon Types
(@RACE,@CLASS,198,‘One-Handed Maces’),
(@RACE,@CLASS,227,‘Staves’),
– Armor Types
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,331,‘Healing Wave’),
(@RACE,@CLASS,403,‘Lightning Bolt’);
– Mage
SET @CLASS := 8;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
– Weapon Types
(@RACE,@CLASS,227,‘Staves’),
(@RACE,@CLASS,5009,‘Wands’),
– Armor Types
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,133,‘Fireball’),
(@RACE,@CLASS,168,‘Frost Armor’),
(@RACE,@CLASS,5019,‘Shoot’);
– Warlock
SET @CLASS := 9;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,58284,‘Chaos Bolt Passive’),
(@RACE,@CLASS,75445,‘Demonic Immolate’),
– Weapon Types
(@RACE,@CLASS,227,‘Staves’),
(@RACE,@CLASS,1180,‘Daggers’),
(@RACE,@CLASS,5009,‘Wands’),
– Armor Types
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,686,‘Shadow Bolt’),
(@RACE,@CLASS,687,‘Demon Skin’),
(@RACE,@CLASS,5019,‘Shoot’);
– Druid
SET @CLASS := 11;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Class Buff
(@RACE,@CLASS,27764,‘Relics’),
– Weapon Types
(@RACE,@CLASS,227,‘Staves’),
– Armor Types
(@RACE,@CLASS,9077,‘Leather’),
(@RACE,@CLASS,9078,‘Cloth’),
– Starting Skills
(@RACE,@CLASS,5176,‘Wrath’),
(@RACE,@CLASS,5185,‘Healing Touch’);
– Racial Setup
SET @CLASS := 0;
– Human
SET @RACE := 1;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,668,‘Language Common’),
– Race Buff
(@RACE,@CLASS,20597,‘Sword Specialization’),
(@RACE,@CLASS,20598,‘The Human Spirit’),
(@RACE,@CLASS,20599,‘Diplomacy’),
(@RACE,@CLASS,20864,‘Mace Specialization’),
(@RACE,@CLASS,58985,‘Perception’),
(@RACE,@CLASS,59752,‘Every Man for Himself’);
– Orc (Blood Fury,Command)
SET @RACE := 2;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,669,‘Language Orcish’),
– Race Buff
(@RACE,@CLASS,20573,‘Hardiness’),
(@RACE,@CLASS,20574,‘Axe Specialization’),
(@RACE,1,20572,‘Blood Fury’),
(@RACE,3,20572,‘Blood Fury’),
(@RACE,4,20572,‘Blood Fury’),
(@RACE,6,20572,‘Blood Fury’),
(@RACE,7,33697,‘Blood Fury’),
(@RACE,8,33702,‘Blood Fury’),
(@RACE,9,33702,‘Blood Fury’),
(@RACE,1,20575,‘Command’),
(@RACE,3,54562,‘Command’),
(@RACE,4,20575,‘Command’),
(@RACE,6,54562,‘Command’),
(@RACE,7,65222,‘Command’),
(@RACE,8,20575,‘Command’),
(@RACE,9,54562,‘Command’);
– Dwarf
SET @RACE := 3;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,668,‘Language Common’),
(@RACE,@CLASS,672,‘Language Dwarven’),
– Race Buff
(@RACE,@CLASS,2481,‘Find Treasure’),
(@RACE,@CLASS,20594,‘Stoneform’),
(@RACE,@CLASS,20595,‘Gun Specialization’),
(@RACE,@CLASS,20596,‘Frost Resistance’),
(@RACE,@CLASS,59224,‘Mace Specialization’);
– Night Elf
SET @RACE := 4;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,668,‘Language Common’),
(@RACE,@CLASS,671,‘Language Darnassian’),
– Race Buff
(@RACE,@CLASS,20582,‘Quickness’),
(@RACE,@CLASS,20583,‘Nature Resistance’),
(@RACE,@CLASS,20585,‘Wisp Spirit’),
(@RACE,@CLASS,58984,‘Shadowmeld’);
– Forsaken
SET @RACE := 5;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,669,‘Language Orcish’),
(@RACE,@CLASS,17737,‘Language Gutterspeak’),
– Race Buff
(@RACE,@CLASS,5227,‘Underwater Breathing’),
(@RACE,@CLASS,7744,‘Will of the Forsaken’),
(@RACE,@CLASS,20577,‘Cannibalize’),
(@RACE,@CLASS,20579,‘Shadow Resistance’);
– Tauren
SET @RACE := 6;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,669,‘Language Orcish’),
(@RACE,@CLASS,670,‘Language Taurahe’),
– Race Buff
(@RACE,@CLASS,20549,‘War Stomp’),
(@RACE,@CLASS,20550,‘Endurance’),
(@RACE,@CLASS,20551,‘Nature Resistance’),
(@RACE,@CLASS,20552,‘Cultivation’);
– Gnome
SET @RACE := 7;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,668,‘Language Common’),
(@RACE,@CLASS,7340,‘Language Gnomish’),
– Race Buff
(@RACE,@CLASS,20589,‘Escape Artist’),
(@RACE,@CLASS,20591,‘Expansive Mind’),
(@RACE,@CLASS,20592,‘Arcane Resistance’),
(@RACE,@CLASS,20593,‘Engineering Specialization’);
– Troll
SET @RACE := 8;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,669,‘Language Orcish’),
(@RACE,@CLASS,7341,‘Language Troll’),
– Race Class Skills
(@RACE,1,2764,‘Throw’),
– Race Buff
(@RACE,@CLASS,20555,‘Regeneration’),
(@RACE,@CLASS,20557,‘Beast Slaying’),
(@RACE,@CLASS,20558,‘Throwing Specialization’),
(@RACE,@CLASS,26290,‘Bow Specialization’),
(@RACE,@CLASS,26297,‘Berserking’),
(@RACE,@CLASS,58943,‘Da Voodoo Shuffle’);
– Blood Elf
SET @RACE := 10;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,669,‘Language Orcish’),
(@RACE,@CLASS,813,‘Language Thalassian’),
– Race Buff
(@RACE,@CLASS,822,‘Magic Resistance’),
(@RACE,@CLASS,28877,‘Arcane Affinity’),
(@RACE,2,28730,‘Arcane Torrent’),
(@RACE,3,28730,‘Arcane Torrent’),
(@RACE,4,25046,‘Arcane Torrent’),
(@RACE,5,28730,‘Arcane Torrent’),
(@RACE,6,50613,‘Arcane Torrent’),
(@RACE,8,28730,‘Arcane Torrent’),
(@RACE,9,28730,‘Arcane Torrent’);
– Draenei
SET @RACE := 11;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Languages
(@RACE,@CLASS,668,‘Language Common’),
(@RACE,@CLASS,29932,‘Language Draenei’),
– Race Buff
(@RACE,@CLASS,28875,‘Gemcutting’),
(@RACE,1,6562,‘Heroic Presence’),
(@RACE,2,6562,‘Heroic Presence’),
(@RACE,3,6562,‘Heroic Presence’),
(@RACE,5,28878,‘Heroic Presence’),
(@RACE,6,6562,‘Heroic Presence’),
(@RACE,7,28878,‘Heroic Presence’),
(@RACE,8,28878,‘Heroic Presence’),
(@RACE,1,28880,‘Gift of the Naaru’),
(@RACE,2,59542,‘Gift of the Naaru’),
(@RACE,3,59543,‘Gift of the Naaru’),
(@RACE,5,59544,‘Gift of the Naaru’),
(@RACE,6,59545,‘Gift of the Naaru’),
(@RACE,7,59547,‘Gift of the Naaru’),
(@RACE,8,59548,‘Gift of the Naaru’),
(@RACE,1,59221,‘Shadow Resistance’),
(@RACE,2,59535,‘Shadow Resistance’),
(@RACE,3,59536,‘Shadow Resistance’),
(@RACE,5,59538,‘Shadow Resistance’),
(@RACE,6,59539,‘Shadow Resistance’),
(@RACE,7,59540,‘Shadow Resistance’),
(@RACE,8,59541,‘Shadow Resistance’);
– Weapon Setup
– Setup Per Race/Class Based on Original Table Data
– Warrior
SET @CLASS := 1;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Human
(1,@CLASS,196,‘One-Handed Axes’),
(1,@CLASS,198,‘One-Handed Maces’),
(1,@CLASS,201,‘One-Handed Swords’),
(1,@CLASS,202,‘Two-Handed Swords’),
– Orc
(2,@CLASS,196,‘One-Handed Axes’),
(2,@CLASS,197,‘Two-Handed Axes’),
(2,@CLASS,201,‘One-Handed Swords’),
– Dwarf
(3,@CLASS,196,‘One-Handed Axes’),
(3,@CLASS,197,‘One-Handed Axes’),
(3,@CLASS,198,‘One-Handed Maces’),
– Night Elf
(4,@CLASS,198,‘One-Handed Maces’),
(4,@CLASS,201,‘One-Handed Swords’),
(4,@CLASS,202,‘Two-Handed Swords’),
(4,@CLASS,1180,‘Daggers’),
– Forsaken
(5,@CLASS,201,‘One-Handed Swords’),
(5,@CLASS,202,‘Two-Handed Swords’),
(5,@CLASS,1180,‘Daggers’),
– Tauren
(6,@CLASS,196,‘One-Handed Axes’),
(6,@CLASS,198,‘One-Handed Maces’),
(6,@CLASS,199,‘Two-Handed Maces’),
– Gnome
(7,@CLASS,198,‘One-Handed Maces’),
(7,@CLASS,201,‘One-Handed Swords’),
(7,@CLASS,202,‘Two-Handed Swords’),
(7,@CLASS,1180,‘Daggers’),
– Troll
(8,@CLASS,196,‘One-Handed Axes’),
(8,@CLASS,202,‘Two-Handed Swords’),
(8,@CLASS,1180,‘Daggers’),
(8,@CLASS,2567,‘Thrown’),
– Draenei
(11,@CLASS,198,‘One-Handed Maces’),
(11,@CLASS,201,‘One-Handed Swords’),
(11,@CLASS,202,‘Two-Handed Swords’);
– Paladin
SET @CLASS := 2;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Human
(1,@CLASS,198,‘One-Handed Maces’),
(1,@CLASS,199,‘Two-Handed Maces’),
– Dwarf
(3,@CLASS,198,‘One-Handed Maces’),
(3,@CLASS,199,‘Two-Handed Maces’),
– Blood Elf
(10,@CLASS,201,‘One-Handed Swords’),
(10,@CLASS,202,‘Two-Handed Swords’),
– Draenei
(11,@CLASS,198,‘One-Handed Maces’),
(11,@CLASS,199,‘Two-Handed Maces’);
– Hunter
SET @CLASS := 3;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Orc
(2,@CLASS,196,‘One-Handed Axes’),
(2,@CLASS,264,‘Bows’),
– Dwarf
(3,@CLASS,196,‘One-Handed Axes’),
(3,@CLASS,266,‘Guns’),
– Night Elf
(4,@CLASS,264,‘Bows’),
(4,@CLASS,1180,‘Daggers’),
– Tauren
(6,@CLASS,196,‘One-Handed Axes’),
(6,@CLASS,264,‘Bows’),
– Troll
(8,@CLASS,196,‘One-Handed Axes’),
(8,@CLASS,264,‘Bows’),
– Blood Elf
(10,@CLASS,264,‘Bows’),
(10,@CLASS,1180,‘Daggers’),
– Draenei
(11,@CLASS,201,‘One-Handed Swords’),
(11,@CLASS,5011,‘Crossbows’);
– Rogue
– All Rogues start with the same weapon skills, so is defined for class globally
– Priest
– All Priests start with the same weapon skills, so is defined for class globally
– Death Knight
– All Death Knights start with the same weapon skills, so is defined for class globally
– Shaman
– All Shaman start with the same weapon skills, so is defined for class globally
– Mage
– All Shaman start with the same weapon skills, so is defined for class globally
– Warlock
– All Warlocks start with the same weapon skills, so is defined for class globally
– Druid
SET @CLASS := 11;
INSERT INTO playercreateinfo_spell (race,class,Spell,Note) VALUES
– Night Elf
(4,@CLASS,1180,‘Daggers’),
– Tauren
(6,@CLASS,198,‘One-Handed Maces’);
This data can be compared to what’s there, and it’s the same, just in a more efficient manner. /emoticons/default_smile.png