Trying to understand DOL Database

[Argo]

Rumors say there's a dolwikki somewhere, though i never encountered it in all my years here

regards
Argo

[Leodagan]

I found the article area, looks like I can propose in it

Still need explanation about some "dark" Fields of NPCTemplate to end up my article :

Flags ; smallint(3)
MeleeDamageType ; smallint(3) (Maybe enum of Crush/Slash/Thrust ? Can be Splittable too ? for correct weapon model damage ?)
BodyType ; int(11)
MaxDistance ; int(11) (Max Distance to turn away ? from Spawn ? from player ?)
TetherRange ; int(11) (Something to do with Pets summoner ?)
VisibleWeaponSlots ; tinyint(3) (looks like a binary & code to me...)
ReplaceMobValues ; int(11) (just to be sure ? 1 Replace mob table fields values with template ones ? 0 or NULL don't do anything ? this field should be BOOL ?)
Suffix ; Text (no values in the public db...)
ExamineArticle ; Text (no values in the public db...)
MessageArticle ; Text (no values in the public db...)

I'll omit these one by the time, don't want to write something wrong

EDIT :

FactionID and PathID are only in Mob Table ?

For PathID I can understand it (but it could be templated in some cases... like mobs patrols ?)
But FactionID should definitely be templated ?

[Argo]

Ok, let me enlighten you as far as i can

Flags:
That field is for a mobs status and can be:
ghost, stealth, fly, swimming, statue, torch
hope i have not forgotten a flag.

Meleedamagetype:
this field is for a weapons specific damagetype and can be:
Crush (1), Slash (2) and thrust (3)

Bodytype:
can be:
None = 0,
Animal = 1,
Demon = 2,
Dragon = 3,
Elemental = 4,
Giant = 5,
Humanoid = 6,
Insect = 7,
Magical = 8,
Reptile = 9,
Plant = 10,
Undead = 11

MaxDistance:
I asume this is the distance that the mob can roam around from his spawnpoint, though not really sure about that.

Tetherrange:
That is the distance that the aggro mob follows you before he returns to his spawnpoint.

VisibleWeaponslot:
This field can be:
- One Weapon, no shield -> righthand (240)
- One Weapon with shield righthand (16)
- Twohanded (34)
- Distance (51)
- No weapon at all (255)

ReplaceMobValues:
- 0 or (NULL), don't do anything at all
- 1, take the NPCTemplate Values to replace Mob Values

Suffix, ExamineArticle, MessageArticle:
this is selfexplanatory, and is taken to determine diffrent articles when using another language than english.
example: German language replace 'the' by der die das and so on.

regards
Argo

[Leodagan]

Ok, let me enlighten you as far as i can

Suffix, ExamineArticle, MessageArticle:
this is selfexplanatory, and is taken to determine diffrent articles when using another language than english.
example: German language replace 'the' by der die das and so on.

regards
Argo

Someone has to extract all those brain information

I wrote more than half of the doc about this page, I'll turn it in in morning I think.

I must I admit I went totally off the "language" Fields I was trying to guess dialog events or second names for mobs...

When I read your explanation I realized I had no idea this was implemented in DOL !

Is it a Splittable Field too ? or a reference to a language table ? or just a string... ?

[Argo]

Here's my borderline reached, i couldn't tell you even if i wanted too The Author is Urza i believe, any questions regarding those fields should go in his direction Sorry dude

regards
Argo

[Leodagan]

Here some things I noticed again...

Gender isn't in NPCTemplate table created by server, but GameNPC.cs try to load it from LoadTemplate() ! (Field could be added by hand)

ItemListTemplateID is a Text field but is not Splittable (from GameMerchant.cs)

FactionID isn't in NpcTemplate (from NpcTemplate.cs)

RoamingRange isn't in NpcTemplate and isn't loaded from Template looks like it MUST be set in the Mob table to have any effects...

Documentation has been proposed as an article !

Edit :

Brain and RespawnInterval aren't Templated either...
I found a NPCLanguage table that use examineArticle/Suffix etc language elements should go in there I think...
I think I got to Update the article before it's even validated

[Argo]

As i said before, we are working on it to move all the necessary fields from Mob in to NPCT
About Gender: Dunno if i get you right here but if i set gender in npct it is taken from there, test it yourself

regards
Argo

[Leodagan]

About Gender: Dunno if i get you right here but if i set gender in npct it is taken from there, test it yourself

You Get it Right !

But it's not in the public database schema

You have to guess it

[Argo]

you are right, it is not in the PUBDB but that is because Pub DB is quite old and not really updated like Storm or Storm D2 or my DB Nevertheless gender is used heavily now. it was about time to change that it against some gender

regards
Argo

[Leodagan]

A sample script to convert All NPCT.ItemListTemplateID and merchantID.ItemListID to INTEGER

Code: Select all

UPDATE merchantitem, npctemplate, (SELECT merchantitem.itemListID, @i:=@i+1 AS iterator FROM merchantitem,(SELECT @i:=0) cnt GROUP BY merchantitem.itemListID) uptbl SET merchantitem.ItemlistID = uptbl.iterator, npctemplate.ItemsListTemplateID = uptbl.iterator WHERE merchantitem.ItemlistID = uptbl.ItemlistID AND npctemplate.ItemsListTemplateID = uptbl.ItemlistID

Maybe it can be of any use ?

[Graveen]

A nice script like i love them ! TY !

did you test it against the current codebase and public database ? What miss most is 1) the code side issue, and 2) a proper converter.

If you have both, i'm ok to make the changes.

Thank you vm !

[Leodagan]

There was an error in my script, it was creating a new "key" value for each row of the "join" table, it has been long since I used those kind of query.

I corrected it, it should just create a new "key" integer for each merchantitem.itemListID, then join this table with genuine merchantitem and finally update it with npctemplate using a multiple table update

I still need to test it !:)

And it shouldn't break the current codebase, this replace a GUID String by an unique Integer "as a string", there are still other steps to convert to a real INT or Auto_Increment, and the DOLServer Code must be edited to take this field as Integer

[Leodagan]

Sorry for multiplosting :

Code: Select all

SELECT * FROM `npctemplate` JOIN `merchantitem` on `npctemplate`.`ItemsListTemplateID` = `merchantitem`.`ItemListID` WHERE `npctemplate`.`ItemsListTemplateID` IS NOT NULL AND `npctemplate`.`ItemsListTemplateID` NOT LIKE '';

This query doesn't give any results, on any database I test (Misi, Sniperwolf, Public DB)
Looks like there isn't any NPCT x MerchantItem relations...

And the empty fields `npctemplate`.`ItemsListTemplateID` are joining with empty `merchantitem`.`ItemListID` which is bad !
And my Update script won't update records that doesn't join so there could be leftover... ( finally just take my small script as an example... )

I think this is gonna be much harder than expected as there are specifics cases that need to be taken care of

I'm beginning to update my DOLServer repository I think I'll try to submit some code modifications once I got a robust SQL Script.

EDIT :

I'm having trouble testing, as DOLPUBDB can't be imported 'as is' in latest DOLServer Schema, an Unique constraints is now on table crafteditem that prevent to import duplicated records presents in DOLPUBDB...

For example a adamantium_boned_gauntlets check for a certain level in CraftingSkill Armoring and Tailoring... and have both entry with same "CraftedItem_ID"

[Graveen]

In the SVN, you have various primary key tries, some where successfull (in branch/).

We are not really maintaining the public database, but perhaps it is time to release a new one, compatible with latest builds.

[Leodagan]

branch/ is bit wide to search for elements

If I understand it right, in old release when armoring needed tailoring to make the base leather/cloth stuff for scale armor it was multiples records in CraftedItem table to represent those needs ?

Now I see the codebase use a switch to guess what materials are used and attach corresponding needed secondary skills (like cloth, leather work, but NOT tailoring which is a main skill !)

Moreover, I can't find any occurence of the "CraftedItem_ID" Field in the GameServer Code even in DOLDatabase/Tables/CraftedItem.cs... is it a deprecated Field ?