Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Database/World Building

Development::Database/World Building World Building forum, dedicated to the EQEmu MySQL Database. Post partial/complete databases for spawns, items, etc.

Reply
 
Thread Tools Display Modes
  #1  
Old 07-05-2006, 12:25 PM
sesmar
I built it I will Support it!
 
Join Date: Jun 2005
Location: Michigan
Posts: 214
Default

For pulling loottable info for the mobs of Gunthak maybe this will point you in the right direction.

Also just a warning this query will take some time to run as it hits 7 different and rather large tables.

Code:
SELECT
  spawnentry.npcID,
  npc_types.loottable_id,
  loottable.name,
  loottable_entries.lootdrop_id,
  lootdrop.name,
  lootdrop_entries.item_id
FROM
  spawnentry
  INNER JOIN spawn2
    ON spawn2.spawngroupID = spawnentry.spawngroupID
  INNER JOIN npc_types
    ON npc_types.id = spawnentry.npcID
  INNER JOIN loottable
    ON loottable.id = npc_types.loottable_id
  INNER JOIN  loottable_entries
    ON loottable_entries.loottable_id = loottable.id
  INNER JOIN lootdrop
    ON lootdrop.id = loottable_entries.lootdrop_id
  INNER JOIN  lootdrop_entries
    ON lootdrop_entries.lootdrop_id = lootdrop.id
WHERE
  spawn2.zone='gunthak'
ORDER BY
  spawnentry.npcID,
  npc_types.loottable_id,
  loottable_entries.lootdrop_id,
  lootdrop_entries.item_id
__________________
Reply With Quote
  #2  
Old 07-05-2006, 04:45 PM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default

The sql for populating those zones worked very well. Had to first remove the previous zone, but that was not hard.

Good job

GeorgeS
__________________
Your source for EQ database tools
Toolshop is open for business


http://www.georgestools.chrsschb.com//
Reply With Quote
  #3  
Old 07-06-2006, 02:06 AM
Angelox
AX Classic Developer
 
Join Date: May 2006
Location: filler
Posts: 2,049
Default

Thanks for all the help - Sesmar's query puts me on the proper track of thinking. The query/sort works well, but still has data spread out through the given table. What I need is, to put them in zone groups, for extraction.
I noticed in npc_types , theres a loottable_id column , and also the zone id is in npc_types.id ,as it appears the first numbers contain the zone in them - example : an entry in npc_types.id 224035 means the npc is from zone 224, and he's entry number 035. So, maybe I could use the zone data from npc_types.id , and loot data from npc_types.lootable_id to sort the other loot-related tables by zone?
Reply With Quote
  #4  
Old 07-06-2006, 11:07 AM
sesmar
I built it I will Support it!
 
Join Date: Jun 2005
Location: Michigan
Posts: 214
Default

I am not exactly sure on how the PEQ does their numbering but I do not think an NPC can belong to a zone based on the npc_types.id because a given NPC can appear in any number of zones. Such as a Gnoll Pup can be in North Qeynos and Qeynos Hills.

In order to get Loot Table information for the mob in a particular zone you will have to start with Spawn2 to get all the Spawn Groups in that zone as any NPC that spawns in the zone will be an entry in a spawn group. Then you link to npc_types using the ID and from there to LootTable information which is spread out over multiple tables.

I used one query to display some of the information you need but my suggestion would be to write the query in stages pull out all the information from each table you need so you can reinsert the loot information with new ID's to fit your DB.
__________________
Reply With Quote
  #5  
Old 07-07-2006, 03:49 AM
Angelox
AX Classic Developer
 
Join Date: May 2006
Location: filler
Posts: 2,049
Default

Quote:
Originally Posted by sesmar
I am not exactly sure on how the PEQ does their numbering but I do not think an NPC can belong to a zone based on the npc_types.id because a given NPC can appear in any number of zones. Such as a Gnoll Pup can be in North Qeynos and Qeynos Hills.

In order to get Loot Table information for the mob in a particular zone you will have to start with Spawn2 to get all the Spawn Groups in that zone as any NPC that spawns in the zone will be an entry in a spawn group. Then you link to npc_types using the ID and from there to LootTable information which is spread out over multiple tables.

I used one query to display some of the information you need but my suggestion would be to write the query in stages pull out all the information from each table you need so you can reinsert the loot information with new ID's to fit your DB.
Actually, the data I pulled from Cavedude's was what had the zoneID in the npc_types.id number - which is a very good idea . Maybe it was done for some other reason, but very effective in isolating the data by zone. It wouldn't be a bad idea to reserve the first three digits in the npc_types.id for the zone number - it also would be a good idea to include zone name in places like loottable.name and lootdrop.name; I'm going back through what I have done and adding this.
Reply With Quote
  #6  
Old 07-07-2006, 08:16 AM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default

I have added 'zonename_loottable_id' to the loottable.name in my editor. It uses that convention as default now, and allows you to change the default under tools menu.

As Sesmar mentioned, npc_id's are reused in many zones, so that convention in the tables is strictly for the datatable developers and may not apply to everyone. Also in the db, I see so many types of level 1 skeleton's it makes you understand why loottables and npc_id's should be shared. Although I do think Angelox's system of unique npc_id's for each specific zone is also a good idea to tracking and managing specific zone's mobs. I use both methods.


GeorgeS
__________________
Your source for EQ database tools
Toolshop is open for business


http://www.georgestools.chrsschb.com//
Reply With Quote
  #7  
Old 07-08-2006, 04:47 AM
fathernitwit
Developer
 
Join Date: Jul 2004
Posts: 773
Default

PEQ makes all NPCs start with zoneID*1000 (and I hope cavedude did this as well). So it makes finding some things easier. if a npc is in multiple zones, we have multiple copies of the NPC... mainly a side effect of packet collects. Anyhow, this is an established rule and can be used. Unfortunately, we did not make such a rule on any of the other tables... although an ambitious perl/php/python person could write a script to re-number all the loot tables and spawn tables base on some rules, which would make it a lot easier to dump entire zones in the future.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 03:42 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3