Trying to understand DOL Database

[Leodagan]

hello,

I did not find any articles describing DOL Database Schema and especially Foreign Keys...

I tried to figure as much as possible from field name/type, and began to design a simple diagram for "mob" Table.

mob_diagram.png (87.21 KiB) Viewed 2444 times

I still have some questions about tables "mobdroptemplate" and "droptemplatexitemtemplate", those aren't populated in public DB release, and look like history tables about what dropped from mobs...

Can someone told me if I made any error in the diagram ? or if there is already some kind of documentation around DOL Database ?

[Graveen]

Your DB schema seems complete.

Notice Mob is still used but we are pushing to NPCtemplate (mob should in fine only contains position & heading).

MobXLootTemplate.Name is also checked against NPCTemplate.TemplateId. This is recent so perhaps we could improve this with more indexes.

Generally, the database is lacking some strict foregin keys, but the dependency is not handled code-side, so they are not added.

[Leodagan]

Thx for the quick reply.

I updated the key between MobXLootTemplate.MobName and NPCTemplate.Name,

I hope you mistaken with NPCTemplate.TemplateID, as it is an Integer... (which is used by Mob.NPCTemplateID)

And it's ok to not implement foreign key constraints in the database, this can lead to much problems maintaining the schema, and it would be mostly impossible to edit data by hand (seeing the amout of constraints just around mobs...)

But there are real problems with some of the fields used for "foreign key" lookup being text fields...
Example :
Mob.EquipmentTemplateId
Mob.ItemsListTemplateId
Mob.NPCTemplateId

Are all TEXT field, I had to convert them to varchar to put an index on it ! Without this varchar+index modification doing a JOIN between tables on a TEXT field was taking more than 10 minutes (I think the DOLServer Core doesn't use JOIN)

[Graveen]

Yes. This is a recurrent problem of DOL. We are switching (since 1 year or 2) from Text IDs (generated from a GUID) to integer ones, but this is actually 'possible' via DOLDatabase but not mandatory (because a lot of availables ressources 'd not be compatibles). Related to indexes, we tryed a FULLTEXT index, but it was not totally successfull, because we had to switch to MyISAM engine but i prefer relying on InnoDB, although it is slower).

Honestly, we are aware the DB should benefits a large amount of love, both on design side and schema tuning, but we don't have the manpower to do it (if you are interested, please think to provide migration scripts ).

IIRC, NPCT.TemplateId is the key. The goal is to be able to assign different loottables to identical mobs, so the loot table is not based on mob name.

[Argo]

Please do not change the way we deploy loot at this time. Best will be not to touch loottemplate and mobxloottemplate at all. We finally have a way to deploy loot the way we want to and i don't want it to be changed.

[Leodagan]

mobxloottemplate using MobName as Foreign Key doesn't matter to me...

Ok it could be easier to modify loot drop table using the mobID and change loot between two same mobs at different location (example Leprechaun in Cursed Forest or in New Frontier don't drop same stuff...) but not if it break anything.

I'm just trying to understand those choices

I will surely provide SQL Update Script for Text Fields and Adding Indexes to see which fields are important, if I got enough time I'll try to add comments on fields too !

[Argo]

We currently attach loot by Mobname or by NPCTemplate, which i clearly prefer since it gives me the possibility to create loottables to whatever condition attached. i can create loottables by name, by levelrange, by zone and many more. A flexibility i do not wanna miss anymore. I think that is the way it should be to give DAoC the dynamic in loot which it needs.

[Leodagan]

If I got it right, MobXLootTemplate.MobName references either Mob.Name or NPCTemplate.TemplateID ?

Is there any other table referencing Loot ? I don't see how you can set a certain loot table to a specific level range or zone ? (Maybe switching between templated and single mobs ?)

And using NPCTemplate to "pattern" mobs how can you set a level range ? Looks like each NPCTemplate can only create a certain level mob, if I need a mob spawning with levels between 9 and 11 do I need 3 NPCTemplates ?


EDIT : Ok I think I got something... (which explains my empty tables)

I read documentation of Loot Classes :
http://www.dolserver.net/doc/class_d_o_ ... plate.html
http://www.dolserver.net/doc/class_d_o_ ... ml#details

- class DOL.GS.LootGeneratorMobTemplate uses a relation between MobDropTemplate and DropTemplateXItemTemplate
- class DOL.GS.LootGeneratorTemplate uses a relation between MobxLootTemplate and LootTemplate

Table MobDropTemplate has the same structure as MobxLootTemplate and DropTemplateXItemTemplate same structure as LootTemplate...

Is one of these Class related to NPCTemplate specific drops ? Both documentation are much saying the same things that's it's about "MOB" specific loot...

[Argo]

Take a look at this,

mobxloottemplate:

Code: Select all

INSERT INTO `mobxloottemplate` (`MobName`, `LootTemplateName`, `DropCount`, `MobXLootTemplate_ID`) VALUES ('14603', 'GSLoot', 1, 'GSLT_3');

loottemplate:

Code: Select all

INSERT INTO `loottemplate` (`TemplateName`, `ItemTemplateID`, `Chance`, `Count`, `LootTemplate_ID`) VALUES ('GSLoot', 'Bronze_Torn_Cap_mid', 20, 1, 'GS_BronzeTornCap_Mid');

As you can see does mobname in mobxloottemplate refer to loottemplate/Templatename. Imagine yourself what you can do with it. Possibilities are endless,

instead of naming the template GSLoot (which stands in this case for Grenlock's Sound loot) you could also call it LVl3-5Loot and gather all the lvl 3 to lvl 5 loot there and attach it to according mobs. Or name it NamedMobLoot and attach it to nameds and gather in there all the very special loot for nameds, call it CamelotHillLoot and gather all the loot of camelot hills there... It also helps to differ loot from mobs with the same name but diffrent levels in diffrent regions and so on.... As i mentioned before, only your imagination draws the borderline. Think about it carefully

regards
Argo

[Leodagan]

OK forget what I wrote, I finally read the code of GameServer\gameutils\LootGeneratorTemplate.cs !

So there is what I understand :
- LootGeneratorMobTemplate is deprecated
- LootGeneratorTemplate is using different lookup
- 1st : Try to Match Mob.NPCTemplateId to MobxLootTemplate.MobName
- 2nd : Try to Match Mob.Name to MobxLootTemplate.MobName
- finally : Try to Match directly Mob.Name to LootTemplate.TemplateName

I have to say it's far from relational DB standard use, trying to match different keys from the same table with differents Fields Type (INT Lookup on a VARCHAR key, is a bit ugly...) but I'm ok it's working and I'll take that into account (and have to modify the diagram again...)

By the way thx for your update, I see this can be very flexible by attaching multiple loottemplate to the same mob (by TemplateID or by MobName) but still it doesn't allow to attach to a region specific mob or level specific mob, if I use the same named Mob with same attached NPCTemplate and one of them is level 5 the other one is level 50 they will get the same Loot Templates ?

[Argo]

By the way thx for your update, I see this can be very flexible by attaching multiple loottemplate to the same mob (by TemplateID or by MobName) but still it doesn't allow to attach to a region specific mob or level specific mob, if I use the same named Mob with same attached NPCTemplate and one of them is level 5 the other one is level 50 they will get the same Loot Templates ?

No, they do not. Take for example a decayed zombie. One is in Camelot hills and the other one is in Avalon Marsh. Both have to be templated in npctemplate. Now lets say the one in camelot hills has the templateid 12000 and the one in Avalon Marsh has the templateid 13000. now you can create two diffrent loottemplates. lets call the first CH_DecayedZombie and the other loottemplate we call AM_DecayedZombie

loottemplate:
in CH_DecayedZombie you gather all the loot for the camelot hills decayed zombie and in AM_DecayedZombie you gather all the loot for the Decayed Zombie in Avalon Marsh.

TemplateName, ItemTemplateID, Chance, Count, LootTemplate_ID
CH_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID
CH_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID
CH_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID
AM_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID
AM_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID
AM_DecayedZombie, Some_Loot_ID_NB, 25, 1, SomeLootTemplate_ID

mobxloottemplate:
mobxloottemplate would look now something like this:
MobName, LootTemplateName, Dropcount, MobXLootTemplate_ID
12000, CH_DecayedZombie, 1, SomeTemplateId_XXX
13000, AM_DecayedZombie, 1, SomeTemplateId_XXX

As you can see, both are decayed Zombies, one is lvl <10 the other is > lvl 30, both have the same name, but diffrent levels and diffrent regions and most important... DIFFRENT loot. THAT was the intention when we created this system. You can even expand it and differ in a region into diffrent levels, e.g. AM_DecayedZombie33_35, AM_DecayedZombie36_38, AM_DecayedZombie38_40. Toy a little bit around with it, only your imagination is your limit.

regards
Argo

[Argo]

just read this question:

• And using NPCTemplate to "pattern" mobs how can you set a level range ? Looks like each NPCTemplate can only create a certain level mob, if I need a mob spawning with levels between 9 and 11 do I need 3 NPCTemplates ?

if you want a mob spawning between the levels 9-11, all you got to do is to enter the range in npctemplate/level 9-11 that's all. You can even mix it: 1;3;7;9-11;23;45;48-50 We got an incredible flexibility in npctemplate, same goes there for Equipmenttemplateid. It has come over the years to that flexibility, since i am workin for quite a lot of years now on the database's content and i love it the way it is now. it helps us to create a randomized worldenvironment like never before. A mob can change in gender, race, level, equipment and so on. Nothing except the basics is forseeable now. you must admit, that makes a game worth playing more then once.

Take a look at this example:

Code: Select all

INSERT INTO `npctemplate` (`TemplateId`, `Name`, `ClassType`, `GuildName`, `Model`, `Size`, `Level`, `MaxSpeed`, `EquipmentTemplateID`, `ItemsListTemplateID`, `Flags`, `MeleeDamageType`, `ParryChance`, `EvadeChance`, `BlockChance`, `LeftHandSwingChance`, `Spells`, `Styles`, `Strength`, `Constitution`, `Dexterity`, `Quickness`, `Intelligence`, `Piety`, `Charisma`, `Empathy`, `Abilities`, `AggroLevel`, `AggroRange`, `Race`, `BodyType`, `MaxDistance`, `TetherRange`, `VisibleWeaponSlots`, `Gender`, `ReplaceMobValues`, `PackageID`, `NpcTemplate_ID`, `TranslationId`, `Suffix`, `ExamineArticle`, `MessageArticle`) VALUES (12307, 'bandit', 'DOL.GS.GameNPC', NULL, '27;28;48;74;716;1008', '45-50', '5;6;8', 200, 'd84da2d9-2154-40c9-8168-abf93fa3310b', NULL, 0, 1, 5, 5, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 75, 300, 2005, 6, 3000, 3000, 34, 001, 1, 'Camelot Hills', 'Bandit_CH_M_96_T', NULL, NULL, NULL, NULL); INSERT INTO `npctemplate` (`TemplateId`, `Name`, `ClassType`, `GuildName`, `Model`, `Size`, `Level`, `MaxSpeed`, `EquipmentTemplateID`, `ItemsListTemplateID`, `Flags`, `MeleeDamageType`, `ParryChance`, `EvadeChance`, `BlockChance`, `LeftHandSwingChance`, `Spells`, `Styles`, `Strength`, `Constitution`, `Dexterity`, `Quickness`, `Intelligence`, `Piety`, `Charisma`, `Empathy`, `Abilities`, `AggroLevel`, `AggroRange`, `Race`, `BodyType`, `MaxDistance`, `TetherRange`, `VisibleWeaponSlots`, `Gender`, `ReplaceMobValues`, `PackageID`, `NpcTemplate_ID`, `TranslationId`, `Suffix`, `ExamineArticle`, `MessageArticle`) VALUES (12307, 'bandit', 'DOL.GS.GameNPC', NULL, '27;28;48;74;716;1008', '45-50', '5;6;8', 200, 'da425c5f-9b0e-4bfe-8a98-37b267ee7987', NULL, 0, 1, 5, 5, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 75, 300, 2005, 6, 3000, 3000, 34, 001, 1, 'Camelot Hills', 'Bandit_CH_M_97_T', NULL, NULL, NULL, NULL); INSERT INTO `npctemplate` (`TemplateId`, `Name`, `ClassType`, `GuildName`, `Model`, `Size`, `Level`, `MaxSpeed`, `EquipmentTemplateID`, `ItemsListTemplateID`, `Flags`, `MeleeDamageType`, `ParryChance`, `EvadeChance`, `BlockChance`, `LeftHandSwingChance`, `Spells`, `Styles`, `Strength`, `Constitution`, `Dexterity`, `Quickness`, `Intelligence`, `Piety`, `Charisma`, `Empathy`, `Abilities`, `AggroLevel`, `AggroRange`, `Race`, `BodyType`, `MaxDistance`, `TetherRange`, `VisibleWeaponSlots`, `Gender`, `ReplaceMobValues`, `PackageID`, `NpcTemplate_ID`, `TranslationId`, `Suffix`, `ExamineArticle`, `MessageArticle`) VALUES (12307, 'bandit', 'DOL.GS.GameNPC', NULL, '65;5;43;724;52;1020', '45-50', '5;6;8', 200, 'e2ca085e-dcfe-4729-b0fb-06abae8cbb68', NULL, 0, 2, 5, 5, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 75, 300, 2005, 6, 3000, 3000, 240, 002, 1, 'Camelot Hills', 'Bandit_CH_F_100_R', NULL, NULL, NULL, NULL); INSERT INTO `npctemplate` (`TemplateId`, `Name`, `ClassType`, `GuildName`, `Model`, `Size`, `Level`, `MaxSpeed`, `EquipmentTemplateID`, `ItemsListTemplateID`, `Flags`, `MeleeDamageType`, `ParryChance`, `EvadeChance`, `BlockChance`, `LeftHandSwingChance`, `Spells`, `Styles`, `Strength`, `Constitution`, `Dexterity`, `Quickness`, `Intelligence`, `Piety`, `Charisma`, `Empathy`, `Abilities`, `AggroLevel`, `AggroRange`, `Race`, `BodyType`, `MaxDistance`, `TetherRange`, `VisibleWeaponSlots`, `Gender`, `ReplaceMobValues`, `PackageID`, `NpcTemplate_ID`, `TranslationId`, `Suffix`, `ExamineArticle`, `MessageArticle`) VALUES (12307, 'bandit', 'DOL.GS.GameNPC', NULL, '65;5;43;724;52;1020', '45-50', '5;6;8', 200, 'e3898489-9c41-4402-9771-ac7cf8fddf24', NULL, 0, 1, 5, 5, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 75, 300, 2005, 6, 3000, 3000, 240, 002, 1, 'Camelot Hills', 'Bandit_CH_F_101_R', NULL, NULL, NULL, NULL);

regards
Argo

[Leodagan]

No, they do not. Take for example a decayed zombie. One is in Camelot hills and the other one is in Avalon Marsh. Both have to be templated in npctemplate. Now lets say the one in camelot hills has the templateid 12000 and the one in Avalon Marsh has the templateid 13000. now you can create two diffrent loottemplates. lets call the first CH_DecayedZombie and the other loottemplate we call AM_DecayedZombie

You just confirm what I asked, to make difference in loot template, NPCTemplate(s) have to be "cloned", it's still a better way than linking to each mob_id or having mobs "untemplated"

just read this question:

if you want a mob spawning between the levels 9-11, all you got to do is to enter the range in npctemplate/level 9-11 that's all. You can even mix it: 1;3;7;9-11;23;45;48-50 We got an incredible flexibility in npctemplate, same goes there for Equipmenttemplateid. It has come over the years to that flexibility, since i am workin for quite a lot of years now on the database's content and i love it the way it is now. it helps us to create a randomized worldenvironment like never before. A mob can change in gender, race, level, equipment and so on. Nothing except the basics is forseeable now. you must admit, that makes a game worth playing more then once.

[...]

regards
Argo

Ah the multiples parts fields... I saw some of these going through the NPCTemplate Table of Dol Public DB !

I've seen mobs with a lot of Models like Wandering Spirit or Player-Controlled Pets, that make sense when thinking about summoned pets which can have differents sizes and races

that would have been a future question anyway (even If I think there is most of the answers in DOL Code), but is there anyway to know which Fields accept those "ranges" and "collections" ? Are the Fields from the "mob" table as flexible ? (or all the newest code is targetted to NPCTemplate ?)

does these ranges/collections mecanism work with Mob Location ? (X,Y,Z) that could randomize Spots respawn (but I think Spawn/Spots was a missing feature of DOL, and elevation would be hard to guess on a random X,Y location...)

THAT was the intention when we created this system. You can even expand it and differ in a region into diffrent levels, e.g. AM_DecayedZombie33_35, AM_DecayedZombie36_38, AM_DecayedZombie38_40. Toy a little bit around with it, only your imagination is your limit.

Be assured that I'm definitely not spitting on the work done in DolServer Project, even if some implementations looks weird to me I don't have any critic as long as it can be explained, and all your explanations are going into docs I'm writing, that I'll release when they look nice

[Argo]

I am willing to answer all the questions that you have as far as i can since most of those changes are my suggestions and ideas

Mob x, y, z can't be randomized in this way. I believe all the fields that can be splitted in the npctemplate table can also be splitted this way in mob table. BUT and this is a very very big BUT.... be aware that we plan to get rid of mob entries as far as possible in future. We are going to use npctemplate extensively in future. most of the entries in mob will disapear into NPCTemplate. Thus do not rely on Mob too much. Graveen can tell you more about it. Well, if you think about it then you will come by yourself to the point where you recognize that all values concerning a mob should be in NPCTemplate and mob itself should only contain x, y, z and mobname or what i tend to more the npctemplateid. Speak with Graveen about it, it is his baby

Splittable fields in NPCTemplate:
Model, size, level, equipmenttemplateid, spells, styles. where styles uses other seperators like the rest, like: Class|style;class|style

regards
Argo

[Leodagan]

I am willing to answer all the questions that you have as far as i can since most of those changes are my suggestions and ideas

Mob x, y, z can't be randomized in this way. I believe all the fields that can be splitted in the npctemplate table can also be splitted this way in mob table. BUT and this is a very very big BUT.... be aware that we plan to get rid of mob entries as far as possible in future. We are going to use npctemplate extensively in future. most of the entries in mob will disapear into NPCTemplate. Thus do not rely on Mob too much.

It was just curiosity about the mob fields, I won't rely on it if it's about to be deprecated, I think simple SQL query could move all specific mob table data into NPCTemplate and attach the resulting ID to the source Mob.TemplateID field

Graveen can tell you more about it. Well, if you think about it then you will come by yourself to the point where you recognize that all values concerning a mob should be in NPCTemplate and mob itself should only contain x, y, z
and mobname or what i tend to more the npctemplateid. Speak with Graveen about it, it is his baby

I noticed the interest around NPCTemplate being Class-like (as in OO) of Mobs, and Mobs being the Instances of these class with their location, I'll send a MP to Graveen if he has anything to add to our discussion, even if I'm not talking about possible code expand, relying on NPCTemplate would ease creating a Spawn/Spot-Area Class that will provide instances of NPCTemplate in a designated area instead of fixed point location

Splittable fields in NPCTemplate:
Model, size, level, equipmenttemplateid, spells, styles. where styles uses other seperators like the rest, like: Class|style;class|style

regards
Argo

That's great Now I have all the Mob Table Schema explained and even some Data format, Thx.

Do you have some Wiki/Documentation Project which I can contribute to or should I post it in Forum ?