Trying to understand DOL Database

[Leodagan]

OK I made a quick and dirty script to convert Dol Pub DB to current SVN DolServer

I'll clean it up and post it if it's useful...

that brought up some questions too

I think some Secondary Key or Old Primary key are still in there and should be deprecated

- crafteditem.crafteditem_id : changed to a unique constraint but is not primary key, and not used to reference anything (it's just a duplicated primary for me...)

- dbhouse.price : not there anymore

- dbhouse.OwnerIDs : not there anymore, dbhouse.OwnerID still here, and in pub db there was data in OwnerIDs and none in OwnerID, maybe I even made something wrong, as being an empty player database these should be empty in database release ?

house_hookpointoffset : deprecated table ? I copied some data to househookpointoffset (replacement ?) that didn't looked like duplicate... (I used "HouseModel, HookpointID" as a primary key)

itemtemplate.itemTemplate_ID : deprecated Field ? Unique constraint, not referenced by anything, not Primary...

keepcomponent.Height : gone... maybe moved to keephookpoint ? I looked in codebase there doesn't seems to be much Height data from DB in keepcomponent code (I saw a query for keephookpoint.Height but maybe it was overwritten farther depending on keep level...)

language : table deprecated in favor of other language tables ?

staterequipment.FreeItem_ID : it was a primary key moved to StarterEquipment_ID which was used as "splittable" field for items i think, there was some fields without data, by watching records I've seen some rows had the "splittable" in the Class field or in the StarterEquipment_ID field... I wrote a query to copy everything from StarterEquipment_ID to Class where Class is empty, then changed FreeItem_ID to StarterEquipment_ID

Above all this there are much "PackageID" fields who aren't created by DolServer, I think they look more like "comments" than any "ID" thing...

For all field that moved to unique constraints I changed value for incremental Integer then moved the field to Auto_increment to prevent duplicate key on further inserts... I'm pretty sure they can simply be deleted, but haven't tested yet...

[Leodagan]

ok I tested, ItemTemplate_ID and crafteditem_id can't be dropped

here is the cleanup mysql script

[Leodagan]

Little SQL Script to Attach Untemplated Mobs to NPCTemplate of the same name, or of another mob with the same name having one...

Code: Select all

UPDATE mob, (SELECT * FROM (SELECT mob.mob_id, mob.name, mob.npctemplateid FROM mob LEFT JOIN npctemplate ON mob.npctemplateid = npctemplate.templateid WHERE npctemplate.templateid IS NULL) a LEFT JOIN (SELECT mob.npctemplateid as fnpctid, mob.name as fname from mob JOIN npctemplate on mob.npctemplateid = npctemplate.templateid GROUP BY npctemplate.templateid) b ON a.name = b.fname LEFT JOIN (SELECT npctemplate.name as lname, npctemplate.templateid as lnpctid FROM npctemplate GROUP BY npctemplate.templateid) c ON a.name = c.lname WHERE (fnpctid IS NOT NULL AND lnpctid IS NOT NULL AND fnpctid = lnpctid) OR (fnpctid IS NOT NULL AND lnpctid IS NULL) OR (fnpctid IS NULL AND lnpctid IS NOT NULL)) z SET mob.npctemplateid = IFNULL(z.fnpctid, z.lnpctid) WHERE mob.mob_id = z.mob_id;

For now it's conservative and only set npctemplateid for records that can only have one (if there is more than one I didn't wrote any condition to guess the best one...)

to list the ones not updated copy/paste the "z" aliased subquery after running the update and remove the "AND fnpctid = lnpctid" where clause.

EDIT : Modified SQL Query to take more case into account.

[Leodagan]

Collection of Update and Cleaning Database Script...

Don't Run it as is, (or do Backups first ! Really don't try anything stupid with those script they can modify a lot of values)
Open the file, look for commented area and use what you're needing...

You should definitely put some Indexes on Mob and NPCTemplate Table to run these scripts in acceptable times (convert NPCTemplate.Name Fom text to varchar(255), and add an Index, or some query will never end ! same can be done for ItemListTemplateID...)

Here is a little summary of what these scripts can do :

- Clean up MerchantItem row from unknown Item.id_nb
- Clean up Mob/NPCTemplate row from unknown MerchantItem.ItemListID
- Clean up LootTemplate from unknown Item.id_nb
- Clean up MobxLootTemplate from unknown LootTemplate.TemplateName
- Clean up Mob row from unknown Path.ID
- Clean up Mob row from unknown Faction.ID
- Clean up Mob/NPCTemplate row from unknown NPCEquipment.TemplateID (on non-splittable/non-deprecated only, query can detect this...)
- Clean up Mob/NPCTemplate row from unknown Race.ID
- Set All Mob/NPCTemplate "Name" with different Case to the same Case (uses a counter to guess which version to use, do not rely on this query too much but it's usefull for the remaining actions)
- Check for NPCTemplate Attached to Mob but with different Names, If NPCT has ReplaceMobValues=1 and no other name is guessed, Mob Name is replaced, Else we try to find another name matching in Mob.Name or NPCTemplate.Name and try to get NPCT.TemplateId to correct it, If nothing is successful NPCT is detach from Mob !
- Reattach all Mob without NPCTemplateID to existing same Named mobs with valid NPCTID, or an NPCTemplate with a corresponding Name

Those cleanup and Attach/Detach ID from Mob/NPCTemplate and all table going around is needed for a script I'm writing that can create NPCTemplate for non-templated Mobs treating them as Batch of "Name" and "Region"

I'll release this script as well when I'll finish writing it (and a backup failure made me lose 2 days of work)

[Graveen]

Excellent ! Thank you !

[Argo]

- Set All Mob/NPCTemplate "Name" with different Case to the same Case (uses a counter to guess which version to use, do not rely on this query too much but it's usefull for the remaining actions)
- Check for NPCTemplate Attached to Mob but with different Names, If NPCT has ReplaceMobValues=1 and no other name is guessed, Mob Name is replaced, Else we try to find another name matching in Mob.Name or NPCTemplate.Name and try to get NPCT.TemplateId to correct it, If nothing is successful NPCT is detach from Mob !
- Reattach all Mob without NPCTemplateID to existing same Named mobs with valid NPCTID, or an NPCTemplate with a corresponding Name

Those cleanup and Attach/Detach ID from Mob/NPCTemplate and all table going around is needed for a script I'm writing that can create NPCTemplate for non-templated Mobs treating them as Batch of "Name" and "Region"

Did you take in to account that we speak also about mobs with the same name but diffrent regions and diffrent levels? Don't wanna critizise your work but somewhere in my stomach i got this warning feeling.

regards
Argo

[Leodagan]

For the first part I don't take Region into account (playing with ID's) there is not enough mob multi-templated, in Public DB, to take this into account (that means duplicate TemplateID in NPCTemplate then the DolServer codebase make a random on Mob spawn between them)

If you want to detect multi-template use :

Code: Select all

SELECT * FROM (SELECT COUNT(*) AS `cnt`, `TemplateId` FROM `npctemplate` GROUP BY `TemplateId` ORDER BY `TemplateId`) a WHERE `cnt` > 1

NPCTemplate has no information about the Mob Whereabout, this would need a JOIN to the Mob table and use location or Region to prevent attaching wrong ID, honestly on my DB the previous query return 4 records... on 5600 total I take the risk to have to finish editing 4 records by hand

All update query use JOIN to a select statement where I query the records I'm going to update, just use the sub-query to have a Display of what records are updated ! (use it before updating, or there won't be records left matching the query)

For the part about creating template, I use Mob.Name+Mob.Region as "Primary Key" (which mean all distinct combinations), it create templates for any mob name not having same region (preventing my updates to get over a "Loading" screen)
I still had a Problem with "Moss Sheerie" there seems to be a low level spot in "Lough Derg" and a high level spot in "Shannon Estuary" they are both in Region 200 (Hibernia)

Same as before I intend to edit those by hand, it can even be detected as I don't overwrite mobs value, I think the next step after linking every mob to at least one mob template will be to "split" mob template depending on Mob values differs too much to "Averaged" auto-template, this could be reduced with a "Range" split using mob location to target a "Spot"
Recent MySQL implement Geographic functions, maybe I can use them to query mobs "by spawn",

At least I'm not destroying data (too much...)
I was planning to work on a NPCTemplate splitter anyway, I had too much "WeaponVisibleSlots" unsolvable as I can't guess which mobs will spawn with which type of Weapon, this will need a dedicated script to detect equipment and set WeaponVisibleSlots and even MeleeDamageType accordingly, by splitting I mean using the same NPCTemplateID to impovre Randomness (so Server codebase can random between a two handed slash damage mob template, and a one handed crush damage mob template, and should not set 1 handed blunt to a 2 handed item slot with a thrust damage type...)

[Argo]

To detect a region's content i use something like this:

Code: Select all

select * from mob where (x > 417850 and x < 483250) and (y < 678910 and y > 616400) and region = 1 and realm = 0 and name like "%" order by name

mob can also be worldobject, region can be any region we have and realm can be 0/1/2/3

with this i can in this case exactly determine what is in Avalon Marsh. i got a whole bunch of these snipplets for nearly every region in every realm, otherwise i could never attach the right npctemplate to the right mob. Think about it for a minute Working with the DB needs preparations or you mess it up.

just my 2 cents

Argo

[Leodagan]

You can't really mess it up...

My updates "Make" Data out of existing one or Change ID Links, at worst you got some links to hand-edit, but most of other queries can work above my changes as I try to stay in relative constraints...

Every links I "detach" doesn't delete data (except for link table like mobxloottemplate) I keep the orphan record, it can be use later to check if automatic update didn't change too much previous records (and orphan are easy to delete afterward)

About your location code I had something similar in mind, but you still have a problem too, Spawn Spots could be across a sub-region border...

I tend more to try implementing a "Spot" Manager I would rather check mob distance between them and use a treshold value to label it "Spot" and handle it like this.

But I'll need a Table with sub-region border, for the moment I can't convert Database Location to Ingame /loc, It could help me to generate graphics map or import from fan-made sources.

[Argo]

you might wanna take a look at allakhazam, maybe you'll find there what you are searching for.

regards
Argo

[Leodagan]

Here is the script for creating NPC Templates from untemplated Mobs...

It's written in PHP and should work on any Web server with PHP and "mysqli" library

The code to compare data is in a big switch that use different rules depending on Field type, it's easy to edit the "case" in the switch to change behavior of the script depending on your needs !

SQL Queries can be edited too if GROUPing option doesn't fit your needs...

It's a fast release and not meant to be "clean", but I made all the error checking that should prevent bugs.

Tested on a DolServer, there is still some EquipmentTemplateID not resolving (could be from the fact I'm not checking splittables Fields from Mob/NPCT in my clean ups...)

[Leodagan]

For Mob Zone Location I made these :

Add some indexes to have faster queries

Code: Select all

-- DOL gLoc to ingame /loc -- Needed Indexes : ALTER TABLE `zones` ADD INDEX ( `RegionID` ) ALTER TABLE `zones` ADD INDEX ( `OffsetY` ) ALTER TABLE `zones` ADD INDEX ( `OffsetX` ) ALTER TABLE `zones` ADD INDEX ( `Width` ) ALTER TABLE `zones` ADD INDEX ( `Height` ) ALTER TABLE `mob` ADD INDEX ( `X` ) ALTER TABLE `mob` ADD INDEX ( `Y` )

View to convert Zone offset and Size to "gLoc"

Code: Select all

-- Needed View : CREATE VIEW `inzones` AS SELECT `Zones_ID`, `ZoneID`, `RegionID`, `Name`, `OffsetY`*8192 as startY, `OffsetX`*8192 as startX, `OffsetY`*8192+`Width`*8192 as endY, `OffsetX`*8192+`Height`*8192 as endX, `Realm` FROM `zones`;

Sample query to get mobs in specified Zone...

Code: Select all

-- Select example : SELECT * FROM `mob` LEFT JOIN inzones ON (`mob`.X BETWEEN inzones.startX AND inzones.endX) AND (`mob`.Y BETWEEN inzones.startY AND inzones.endY) AND `mob`.Region = inzones.`RegionID` WHERE inzones.name LIKE 'example'

Creating a new view, to get mob zone, and convert "gLoc" to ingame "/loc"

Code: Select all

-- New View for mob_id, mob_name, npctemplateid, mob_zone, mob_locX, mob_locY, mob_locZ CREATE VIEW mob_loc as SELECT `mob`.`Mob_ID`, `mob`.`Name`, `mob`.`Guild`, `mob`.`NPCTemplateID`, `inzones`.`Name` as Zone, (`mob`.`Y`-`inzones`.`startY`) as locY, (`mob`.`X`-`inzones`.`startX`) as locX, `mob`.`Z` as locZ, `mob`.`Heading` FROM `mob` LEFT JOIN `inzones` ON (`mob`.`X` BETWEEN `inzones`.`startX` AND `inzones`.`endX`) AND (`mob`.`Y` BETWEEN `inzones`.`startY` AND `inzones`.`endY`) AND `mob`.`Region` = `inzones`.`RegionID`;

This view can now be join to any query to detect Ingame Zone and Loc !!

[Graveen]

Sorry i have not a lot of time actually, but i keep your SQL at warm.

/bow

[Leodagan]

Don't be sorry

I'm kind of using this topic for "backup" I write those queries on the fly, most of times I drop them and sometimes I need them back so they're here !

but you should definitely take a look at the templating script, I used it to auto-create 4600 templates, I launched it on other data base by lurking the forum, with Migration Script/Cleaning Script/Templating Script I converted the entries to latest DB and Templating, Inserted them in public DB like a charm

[Graveen]

Only wimps use tape backup: real men just upload their important stuff on ftp, and let the rest of the world mirror it