Learning about Drops
I just started messing with the EQEmu database and wanted to understand the structure and relations between tables. Without any visual tools to work with other than phpMyAdmin, I decided to do it manually. I use the wonderful PEQ Kunark DB and a terminal window.
Some of this is probably here on the site already and most of you probably already know it, but I am sure some do not. I had a basic understanding of how loots work just from playing for years. I learned more from tweaking the DB and checking the results. All of the items that a NPC might drop belong to that NPC's loot table. An NPC loot table contains one or more loot groups. Each group may contain one or more items. When a mob is killed, there is a probability that an item from each group will drop. There is then a chance of which item will drop from that group. For our purposes, will be concerned with the following tables ... npc_types loottable loottable_entries lootdrop_entries items To find a particular NPC's loot table will require a few basic SQL queries that we can easily enter at the MySQL command prompt. Let's get Najena's loot table by using her name, or ID number if we know it. Code:
SELECT name, loottable_id FROM npc_types WHERE name = 'Najena'; Code:
SELECT loottable_id, multiplier, probability,lootdrop_id FROM loottable_entries WHERE loottable_id = '224'; We get the lootdrop_id column which we use to find out which items are assigned to that Group. They are listed in the lootdrop_entries table. Code:
SELECT lootdrop_id,chance,item_id FROM lootdrop_entries WHERE lootdrop_id = '893'; Code:
SELECT lootdrop_id,chance,item_id FROM lootdrop_entries WHERE lootdrop_id = '893' OR lootdrop_id = '896'; Code:
select * from loottable where id = '224'; The item_id column is used to match the items with their names in the items table. Keeping in mind these items are in different loot groups, although they appear in one table here. Now, we just need to identify each item by name. Code:
SELECT id,name FROM items WHERE id = '1302'; As you can see, it would be tedious to do each item individually, and redundant to string together 'AND' (or &&'s) clauses. So we make use of relational SQL and make fewer, however more complicated queries. The following queries show the items with their 'chance' for each. Code:
SELECT lootdrop_id,name,chance FROM items,lootdrop_entries WHERE items.id = lootdrop_entries.item_id AND lootdrop_id = '893'; Code:
SELECT lootdrop_id,name,chance FROM items,lootdrop_entries WHERE items.id = lootdrop_entries.item_id AND (lootdrop_id = '893' OR lootdrop_id = '896'); The following, slightly more complex query provides the probability column back in. Code:
SELECT loottable_entries.lootdrop_id,loottable_entries.multiplier, loottable_entries.probability,items.name,lootdrop_entries.chance FROM npc_types, loottable_entries, lootdrop_entries, items WHERE npc_types.loottable_id = loottable_entries.loottable_id AND loottable_entries.lootdrop_id = lootdrop_entries.lootdrop_id AND items.id = lootdrop_entries.item_id AND npc_types.name ='Najena' GROUP BY items.id; 1. With a multiplier of 1 for each group, we know they will only drop one item each. 2. One item will always drop from Group 893. That item will be chosen based on it's chance. 3. There's a 35% probability an item from Group 896 will drop (will be a Golden Crescent Key because it's the only item in the group). Someone with more algebra knowlege than me could make an equation or Ven diagram that shows the exact percent chance any given item will drop. The following query will give us a quick loot list for an NPC type if we only want to know the item names. Code:
SELECT items.name FROM npc_types, loottable_entries, lootdrop_entries, items WHERE npc_types.loottable_id = loottable_entries.loottable_id AND loottable_entries.lootdrop_id = lootdrop_entries.lootdrop_id AND items.id = lootdrop_entries.item_id AND npc_types.name ='Najena'; *note - You probably don't have to have the table names in some of the above queries, however there is some ambiguity within the Emu DB structure. It is a good habit when writing SQL queries though. -- edit, the code blocks broke my SQL result tables, WTB monospaced fonts. |
Algebra to know the global chances to see an item to drop is kinda easy since all is in percents, just multiply the chance for the group by the chance of each item of the group, and multiplly it again by 100.
IE, a group with 30% probability contains 2 items, one at 20%, one at 80%. First item will have 0.3*0.2*100=6% chances to drop Second item will have 0.3*0.8*100=24% chances to drop each time the mob spawns. |
All times are GMT -4. The time now is 12:26 PM. |
Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.