MW_057DR2 alpha 1 database mods (working trainers)

[cabtiz]

here is some SQL statements that will enable the guildmasters to train you if you are using the 5/20/2004 cvs dump and the MW_057DR2 alpha 1 database. they are all set to class 0 by default in the database so this goes through and parses the npc_types.name field looking for class guildmaster and making the necessary change to the class number in the record. if a zone is populated but the npc's don't have the appropriate titles appended onto their names it obviously won't flag them as the trainers.

following this is various sql statements that will insert various things into the lastname field of the database based on whats in portions of the name field.. I have not had it remove the stuff from the name field as i have not had a chance to play around with why the current cvs will not use the lastname field to put whatever is in it in paranthesis under the npc's name.

UPDATE npc_types SET class = 29
WHERE (right(name,16)="Shaman Guildmast");

UPDATE npc_types SET class = 25
WHERE (right(name,15)="Druid Guildmast");

UPDATE npc_types SET class = 21
WHERE (right(name,16)="Cleric Guildmast");

UPDATE npc_types SET class = 20
WHERE (right(name,17)="Warrior Guildmast");

UPDATE npc_types SET class = 26
WHERE (right(name,14)="Monk Guildmast");

UPDATE npc_types SET class = 22
WHERE (right(name,17)="Paladin Guildmast");

UPDATE npc_types SET class = 24
WHERE (right(name,23)="Shadow Knight Guildmast");

UPDATE npc_types SET class = 23
WHERE (right(name,16)="Ranger Guildmast");

UPDATE npc_types SET class = 28
WHERE (right(name,15)="Rogue Guildmast");

UPDATE npc_types SET class = 34
WHERE (right(name,19)="BeastLord Guildmast");

UPDATE npc_types SET class = 27
WHERE (right(name,19)="Berserker Guildmast");

UPDATE npc_types SET class = 31
WHERE (right(name,16)="Wizard Guildmast");

UPDATE npc_types SET class = 30
WHERE (right(name,21)="Necromancer Guildmast");

UPDATE npc_types SET class = 32
WHERE (right(name,18)="Magician Guildmast");

UPDATE npc_types SET class = 33
WHERE (right(name,19)="Enchanter Guildmast");

Next is some sql statements that will insert "Class Guildmaster" into the lastname field in the database where class is actually the class.

UPDATE npc_types SET lastname = "Shaman Guildmaster"
WHERE (right(name,16)="Shaman Guildmast");

UPDATE npc_types SET lastname = "Druid Guildmaster"
WHERE (right(name,15)="Druid Guildmast");

UPDATE npc_types SET lastname = "Cleric Guildmaster"
WHERE (right(name,16)="Cleric Guildmast");

UPDATE npc_types SET lastname = "Warrior Guildmaster"
WHERE (right(name,17)="Warrior Guildmast");

UPDATE npc_types SET lastname = "Monk Guildmaster"
WHERE (right(name,14)="Monk Guildmast");

UPDATE npc_types SET lastname = "Paladin Guildmaster"
WHERE (right(name,17)="Paladin Guildmast");

UPDATE npc_types SET lastname = "ShadowKnight Guildmaster"
WHERE (right(name,23)="Shadow Knight Guildmast");

UPDATE npc_types SET lastname = "Ranger Guildmaster"
WHERE (right(name,16)="Ranger Guildmast");

UPDATE npc_types SET lastname = "Rogue Guildmaster"
WHERE (right(name,15)="Rogue Guildmast");

UPDATE npc_types SET lastname = "Beastlord Guildmaster"
WHERE (right(name,19)="BeastLord Guildmast");

UPDATE npc_types SET lastname = "Berserker Guildmaster"
WHERE (right(name,19)="Berserker Guildmast");

UPDATE npc_types SET lastname = "Wizard Guildmaster"
WHERE (right(name,16)="Wizard Guildmast");

UPDATE npc_types SET lastname = "Necromancer Guildmaster"
WHERE (right(name,21)="Necromancer Guildmast");

UPDATE npc_types SET lastname = "Magician Guildmaster"
WHERE (right(name,18)="Magician Guildmast");

UPDATE npc_types SET lastname = "Enchanter Guildmaster"
WHERE (right(name,19)="Enchanter Guildmast");

next here is some sql code that will insert "Class Spell Merchant" into the last name field of the database where class = the actual class

UPDATE npc_types SET lastname = "Shaman Spell Merchant"
WHERE (right(name,11)="Shaman Spel");

UPDATE npc_types SET lastname = "Druid Spell Merchant"
WHERE (right(name,10)="Druid Spel");

UPDATE npc_types SET lastname = "Cleric Spell Merchant"
WHERE (right(name,11)="Cleric Spel");

UPDATE npc_types SET lastname = "Warrior Spell Merchant"
WHERE (right(name,12)="Warrior Spel");

UPDATE npc_types SET lastname = "Monk Spell Merchant"
WHERE (right(name,9)="Monk Spel");

UPDATE npc_types SET lastname = "Paladin Spell Merchant"
WHERE (right(name,12)="Paladin Spel");

UPDATE npc_types SET lastname = "ShadowKnight Spell Merchant"
WHERE (right(name,18)="Shadow Knight Spel");

UPDATE npc_types SET lastname = "Ranger Spell Merchant"
WHERE (right(name,11)="Ranger Spel");

UPDATE npc_types SET lastname = "Rogue Spell Merchant"
WHERE (right(name,10)="Rogue Spel");

UPDATE npc_types SET lastname = "Beastlord Spell Merchant"
WHERE (right(name,14)="BeastLord Spel");

UPDATE npc_types SET lastname = "Berserker Spell Merchant"
WHERE (right(name,14)="Berserker Spel");

UPDATE npc_types SET lastname = "Wizard Spell Merchant"
WHERE (right(name,11)="Wizard Spel");

UPDATE npc_types SET lastname = "Necromancer Spell Merchant"
WHERE (right(name,16)="Necromancer Spel");

UPDATE npc_types SET lastname = "Magician Spell Merchant"
WHERE (right(name,13)="Magician Spel");

UPDATE npc_types SET lastname = "Enchanter Spell Merchant"
WHERE (right(name,14)="Enchanter Spel");

and finally here is some sql code to insert "####### Supplies" into the lastname field were ####### equals brewing, baking etc etc etc.

UPDATE npc_types SET lastname = "Adventure Recruiter"
WHERE (right(name,17)="Adventure Recruit");

UPDATE npc_types SET lastname = "Adventure Merchant"
WHERE (right(name,16)="Adventure Mercha");

UPDATE npc_types SET lastname = "Raid Recruiter"
WHERE (right(name,12)="Raid Recruit");

UPDATE npc_types SET lastname = "Baking Supplies"
WHERE (right(name,13)="Baking Suppli");

UPDATE npc_types SET lastname = "Brewing Supplies"
WHERE (right(name,14)="Brewing Suppli");

UPDATE npc_types SET lastname = "Magician Supplies"
WHERE (right(name,15)="Magician Suppli");

UPDATE npc_types SET lastname = "Wizard Supplies"
WHERE (right(name,13)="Wizard Suppli");

UPDATE npc_types SET lastname = "Enchanter Supplies"
WHERE (right(name,16)="Enchanter Suppli");

UPDATE npc_types SET lastname = "Fishing Supplies"
WHERE (right(name,14)="Fishing Suppli");

UPDATE npc_types SET lastname = "Necromancer Supplies"
WHERE (right(name,18)="Necromancer Suppli");

UPDATE npc_types SET lastname = "Tinkering Supplies"
WHERE (right(name,16)="Tinkering Suppli");

UPDATE npc_types SET lastname = "Tribute Master"
WHERE (right(name,12)="Tribute Mast");

UPDATE npc_types SET lastname = "Poison Supplies"
WHERE (right(name,13)="Poison Suppli");

UPDATE npc_types SET lastname = "Soulbinder"
WHERE (right(name,8)="Soulbind");

UPDATE npc_types SET lastname = "Spell Components"
WHERE (right(name,14)="Spell Componen");

UPDATE npc_types SET lastname = "Dye Supplies"
WHERE (right(name,10)="Dye Suppli");

[RangerDown]

Thanks a bunch for the SQL!

BTW, I believe that at the moment Emu server code is hard-coded as to what it shows for the (parentheses) name. IE, you'll always see (Emu Shopkeeper) on any merchant and always see (Emu Banker) on the bankers, and the trainers don't have anything on their name tags. My understanding is that part will be recoded as time permits to look at the lastname field.

[rizzen04]

This work for anyone else? nothing ever works for me. It didnt do anything.

[RangerDown]

^
|
|
|

See my post above yours :P

[rizzen04]

Why is it that on the same code version but an older database the trainers work fine though, wouldnt that indicate its a problem with the database and not the code?

[cabtiz]

The code for the Guildmasters does work.. after i sourced the first set of sql code all the NPC's that had it indicated they were a guildmaster trained the appropriate classes, and refused anyone not of the appropriate class.

The lastname thing apparently does not work or so I've been told.. I;ve heard people say it does i've heard people say it doesn't.. i;ve not gotten it to work.. looking at the code (I'm not a programer by any means) i don't see an obvious reason as to why it doesn't. but If and when it does work the above .sql code will work to populate the lastname field according to whats at the end of the name in the DB.

I mainly did this to see if i *COULD* do it, (i am starting to learn some sql stuff at work) mainly for my own personal satisfaction. Since it did what i wanted it to do and did make the Trainers functional I figured I would share the code.

[rizzen04]

dont know if this helps but in /common/classes.h GM's are defined:

#define WARRIORGM 20
#define CLERICGM 21
#define PALADINGM 22
#define RANGERGM 23
#define SHADOWKNIGHTGM 24
#define DRUIDGM 25
#define MONKGM 26
#define BARDGM 27
#define ROGUEGM 28
#define SHAMANGM 29
#define NECROMANCERGM 30
#define WIZARDGM 31
#define MAGICIANGM 32
#define ENCHANTERGM 33
#define BEASTLORDGM 34
#define BANKER 40
#define MERCHANT 41

and in /common/classes.cpp:

case WARRIORGM:
return "Warrior Guildmaster";
case CLERICGM:
return "Cleric Guildmaster";
case PALADINGM:
return "Paladin Guildmaster";
case RANGERGM:
return "Ranger Guildmaster";
case SHADOWKNIGHTGM:
return "Shadowknight Guildmaster";
case DRUIDGM:
return "Druid Guildmaster";
case MONKGM:
return "Monk Guildmaster";
case BARDGM:
return "Bard Guildmaster";
case ROGUEGM:
return "Rogue Guildmaster";
case SHAMANGM:
return "Shaman Guildmaster";
case NECROMANCERGM:
return "Necromancer Guildmaster";
case WIZARDGM:
return "Wizard Guildmaster";
case MAGICIANGM:
return "Magician Guildmaster";
case ENCHANTERGM:
return "Enchanter Guildmaster";
case BEASTLORDGM:
return "Beastlord Guildmaster";
case MERCHANT:
return "Merchant";
default:
return "Unknown";

[cabtiz]

Originally Posted by rizzen04

Why is it that on the same code version but an older database the trainers work fine though, wouldnt that indicate its a problem with the database and not the code?

it *WAS* a problem with the database which the first set of sql code fixes. the SQL code I wrote is SPECIFIC to the MW_057DR2_alpha_1 database. in the database all the mobs that had say Wizard Guildmast as part of the name where flagged as a class 0. the sql code looks for specific patterns in the name field and if it finds it it changes the class # field to the appropriate number that the server code recognizes as being that classes trainer.

[rizzen04]

it *WAS* a problem with the database which the first set of sql code fixes. the SQL code I wrote is SPECIFIC to the MW_057DR2_alpha_1 database. in the database all the mobs that had say Wizard Guildmast as part of the name where flagged as a class 0. the sql code looks for specific patterns in the name field and if it finds it it changes the class # field to the appropriate number that the server code recognizes as being that classes trainer.

I am using MW_057DR2_alpha_1 and I did source in the fix you posted and the trainers still do not work. It sourced in without any errors but it didnt change the fact that the trainers do not work.

[cabtiz]

What date are the source files you are using for your server? this is confirmed to work with the cvs dump dated 5/20/2004.

when you sourced it in did you watch and see if it said anything was updated?

before you sourced it in did you type use eq; after you first opened mysql?

[rizzen04]

What date are the source files you are using for your server? this is confirmed to work with the cvs dump dated 5/20/2004.

Using source 5-20-04 MW_057DR2_alpha_1 just made a new database for testing purposes, sourced in MW with no errors, sourced in your update:

mysql> use chris
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source train.sql
Query OK, 11 rows affected (0.10 sec)
Rows matched: 11 Changed: 11 Warnings: 0

Query OK, 0 rows affected (0.10 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 56 rows affected (0.10 sec)
Rows matched: 56 Changed: 56 Warnings: 0

Query OK, 53 rows affected (0.10 sec)
Rows matched: 53 Changed: 53 Warnings: 0

Query OK, 8 rows affected (0.10 sec)
Rows matched: 8 Changed: 8 Warnings: 0

Query OK, 37 rows affected (0.10 sec)
Rows matched: 37 Changed: 37 Warnings: 0

Query OK, 21 rows affected (0.10 sec)
Rows matched: 21 Changed: 21 Warnings: 0

Query OK, 0 rows affected (0.09 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 37 rows affected (0.10 sec)
Rows matched: 37 Changed: 37 Warnings: 0

Query OK, 5 rows affected (0.10 sec)
Rows matched: 5 Changed: 5 Warnings: 0

Query OK, 0 rows affected (0.10 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 23 rows affected (0.10 sec)
Rows matched: 23 Changed: 23 Warnings: 0

Query OK, 19 rows affected (0.10 sec)
Rows matched: 19 Changed: 19 Warnings: 0

Query OK, 23 rows affected (0.10 sec)
Rows matched: 23 Changed: 23 Warnings: 0

Query OK, 24 rows affected (0.09 sec)
Rows matched: 24 Changed: 24 Warnings: 0

mysql> quit
Bye

changed db.ini to read from database:chris started up world and zone.

Server started up without errors, logged in, trainers dont work.

[cabtiz]

Number of changes made look correct.. and you are actually going to npc's for training that have "Classname Guildmast" at the end of the name correct?

some of the classes like.. druid and ranger didn't have that tagged onto the end of ther names so as a result those trainers won't work..

if you know an NPC should be a trainer and doesn't have that tagged into their name you can manually update their class in the npc_types table in the database with the corresponding classnumber as you posted in your list of classes above.

All my sql statements do is make the changes based on information already contained in the database so there are some class trainers that simply won't work because their name did not reflect that they were a guildmaster so they wouldn't get changed.

Its one of those things where the database isn't complete so i can only work with whats present in it. which reinforces the fact that more people need to run the packet collector to fill in all the missing information.

I've long since retired from EQ and have no active accounts or every session I played would have been collected. The extent of my contributions to the project will basically be to add functionality when i see a means to do it. To that end I am about halfway done with re-numbering all of the old perl_quests to work with the MW_057DR2 DB since all the npcid's changed with this DB. and they may change again in the future as well.

again some way work others might not.. but i feel that anything that makes the world more complete is a good thing. I do know that most of the trainers in the freeport area do work. there are some classes that no matter where you go will not work..

Making an individual change in the DB is relatively simple if you know a mob should be a trainer for a particular class.

[rizzen04]

Ok, that makes sense. I couldnt get any of the trainers to work that are in PoK but I had only tried a few classes, ranger and druid I couldnt get to work in kelethin.

[sotonin]

That SQL is wrong. You listed 27 as berserker everywhere. When in fact it is Bard.

Replace Berserker with Bard everywhere and it should make bard GMS work

[sotonin]

OK. I tried above code on a fresh DB. it didnt work. after some modification i got the trainers to work properly. Of course after we release the db it wont matter. But this is a quick fix in case you ever fubar your gms or something

UPDATE npc_types SET class = 29 WHERE lastname LIKE 'Shaman_Guildmast%';
UPDATE npc_types SET class = 25 WHERE lastname LIKE 'Druid_Guildmast%';
UPDATE npc_types SET class = 21 WHERE lastname LIKE 'Cleric_Guildmast%';
UPDATE npc_types SET class = 20 WHERE lastname LIKE 'Warrior_Guildmast%';
UPDATE npc_types SET class = 26 WHERE lastname LIKE 'Monk_Guildmast%';
UPDATE npc_types SET class = 22 WHERE lastname LIKE 'Paladin_Guildmast%';
UPDATE npc_types SET class = 24 WHERE lastname LIKE 'Shadow_Knight_Guildmast%';
UPDATE npc_types SET class = 23 WHERE lastname LIKE 'Ranger_Guildmast%';
UPDATE npc_types SET class = 28 WHERE lastname LIKE 'Rogue_Guildmast%';
UPDATE npc_types SET class = 34 WHERE lastname LIKE 'BeastLord_Guildmast%';
UPDATE npc_types SET class = 27 WHERE lastname LIKE 'Bard_Guildmast%';
UPDATE npc_types SET class = 27 WHERE lastname LIKE 'Berserker_Guildmast%';
UPDATE npc_types SET class = 31 WHERE lastname LIKE 'Wizard_Guildmast%';
UPDATE npc_types SET class = 30 WHERE lastname LIKE 'Necromancer_Guildmast%';
UPDATE npc_types SET class = 32 WHERE lastname LIKE 'Magician_Guildmast%';
UPDATE npc_types SET class = 33 WHERE lastname LIKE 'Enchanter_Guildmast%';