Start with all spells

[Sandile]

Im working on a querry to add all your class spells to your spell book when you create your character. However it doesn't work as intended, any help would be apprechiated.

Here is what I've come up with so far.

I took one of each class trainer and selected all the spells they teached and tried to insert the data into the playercreateinfo_spells table and then change their EntryID to the indexid of the trainers class.

ALTER table trainer_spells CHANGE entry indexid int (11) ;
ALTER table trainer_spells CHANGE learn_spell spellid int (11) ;

I changed this so I could easily select the data and transfer it to the playercreateinfo_spells table. I will change this back later with this querry:

ALTER table trainer_spells CHANGE indexid entry  int (11);
ALTER table trainer_spells CHANGE spellid learn_spell  int (11);

After that I inserted all the trainers from the trainer_spells table to the playercreateinfo_spells table with this querry.

-- PALADIN
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 928 AND spellid IS NOT NULL;

-- DEATH KNIGHT
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 28474 AND spellid IS NOT NULL;

-- WARRIOR
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 16771 AND spellid IS NOT NULL;

-- PRIEST
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 4090 AND spellid IS NOT NULL;

-- ROGUE
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 4214 AND spellid IS NOT NULL;

-- DRUID
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 4218 AND spellid IS NOT NULL;

-- HUNTER
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 5115 AND spellid IS NOT NULL;

-- WARLOCK
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 5173 AND spellid IS NOT NULL;

-- SHAMAN
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 23127 AND spellid IS NOT NULL;

-- MAGE
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT indexid, spellid
FROM trainer_spells
WHERE indexid = 17514 AND spellid IS NOT NULL;

The last part is to change the Trainers EntryID's to the classes indexid's
I tried to do that with this query.

-- Paladin
UPDATE playercreateinfo_spells SET indexid=9  WHERE indexid=928 ;
 
-- Death Knight
UPDATE playercreateinfo_spells SET indexid=56  WHERE indexid=28474 ;

-- Warrior
UPDATE playercreateinfo_spells SET indexid=1  WHERE indexid=16771 ;

-- Priest
UPDATE playercreateinfo_spells SET indexid=25  WHERE indexid=4090 ;

-- Rogue
UPDATE playercreateinfo_spells SET indexid=18  WHERE indexid=4214 ;

-- Druid
UPDATE playercreateinfo_spells SET indexid=42  WHERE indexid=4218 ;

-- Hunter
UPDATE playercreateinfo_spells SET indexid=12  WHERE indexid=5115 ;

-- Warlock
UPDATE playercreateinfo_spells SET indexid=38  WHERE indexid=5173 ;

-- Shaman
UPDATE playercreateinfo_spells SET indexid=48  WHERE indexid=23127 ;

-- Mage
UPDATE playercreateinfo_spells SET indexid=34  WHERE indexid=17514 ;

The above piece of code would only give the starting spells to one of each class, but its easy to use the same thing and just change the indexid to match another race.

Everything seems to work well until I get to the last part where I change the indexid.
This is because the spellid's already exist for the real Indexid so the update gives me duplicate errors on each one of them. And of course that makes the current UPDATE statement stop running.

Anyone have any tips on how to make a succesfull data transfer?

[Matis02]

Why don't you use c++?
SERVER_HOOK_EVENT_ON_NEW_CHARACTER

Or do you want to individually add all spells to selected character?

[TheSpidey]

Why don't you use c++?
SERVER_HOOK_EVENT_ON_NEW_CHARACTER

Or do you want to individually add all spells to selected character?

Actually, using playercreateinfo_spells is the best practice here. The core already goes over that table and adds spells to the player, so no reason to reproduce the code in a script.
The only problem I see with the OP's post is that since he adds spells from trainers, he also adds ranks for spells received from talents.

[Matis02]

Yea i forgot about learning talent spells. :P

[Sandile]

I know that I add talent spells of higher rank, but thats easy to remove when I have everything working. I thought it would be easier to manually remove all the talent spells than to manualy add all spells.

[Sandile]

Bump - Any help would be apprechiated. Rewriting the whole script aswell. As long as I get the same results.

[Matis02]

It works fine for me. Try this script:

ALTER TABLE `playercreateinfo_spells` change `indexid` `indexid` int(30) UNSIGNED DEFAULT '0' NOT NULL;

-- PALADIN
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 928 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=9  WHERE indexid=928 ;
 
-- DEATH KNIGHT
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 28474 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=56  WHERE indexid=28474 ;

-- WARRIOR
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 16771 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=1  WHERE indexid=16771 ;

-- PRIEST
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 4090 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=25  WHERE indexid=4090 ;

-- ROGUE
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 4214 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=18  WHERE indexid=4214 ;

-- DRUID
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 4218 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=42  WHERE indexid=4218 ;

-- HUNTER
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 5115 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=12  WHERE indexid=5115 ;

-- WARLOCK
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 5173 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=38  WHERE indexid=5173 ;

-- SHAMAN
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 23127 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=48  WHERE indexid=23127 ;

-- MAGE
REPLACE INTO playercreateinfo_spells (indexid, spellid)
SELECT entry, learn_spell
FROM trainer_spells
WHERE entry = 17514 AND learn_spell IS NOT NULL;

UPDATE playercreateinfo_spells SET indexid=34  WHERE indexid=17514 ;

ALTER TABLE `playercreateinfo_spells` change `indexid` `indexid` tinyint(3) UNSIGNED DEFAULT '0' NOT NULL;