PDA

View Full Version : Learning about Drops


Theeper
11-15-2004, 02:12 PM
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.

SELECT name, loottable_id FROM npc_types WHERE name = 'Najena';

or ..

SELECT name, loottable_id FROM npc_types WHERE id = '44100';

+--------+--------------+
| name | loottable_id |
+--------+--------------+
| Najena | 224 |
+--------+--------------+

Either way, this gives us her loottable_id which we use to find the groups of items that are within her loot table. The groups are stored in the loottable_entries table.

SELECT loottable_id, multiplier, probability,lootdrop_id FROM loottable_entries WHERE loottable_id = '224';

+--------------+------------+-------------+-------------+
| loottable_id | multiplier | probability | lootdrop_id |
+--------------+------------+-------------+-------------+
| 224 | 1 | 100 | 893 |
| 224 | 1 | 35 | 896 |
+--------------+------------+-------------+-------------+

This shows us that there are two loot groups within Najena's loot table.

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.

SELECT lootdrop_id,chance,item_id FROM lootdrop_entries WHERE lootdrop_id = '893';

+-------------+--------+---------+
| lootdrop_id | chance | item_id |
+-------------+--------+---------+
| 893 | 17 | 1320 |
| 893 | 16 | 7009 |
| 893 | 17 | 7352 |
| 893 | 17 | 10403 |
| 893 | 17 | 12884 |
| 893 | 16 | 13400 |
+-------------+--------+---------+

SELECT lootdrop_id,chance,item_id FROM lootdrop_entries WHERE lootdrop_id = '896';

+-------------+--------+---------+
| lootdrop_id | chance | item_id |
+-------------+--------+---------+
| 896 | 100 | 20445 |
+-------------+--------+---------+

.. or we could use an 'OR' (or '||' if you prefer ) clause to put them in one result.

SELECT lootdrop_id,chance,item_id FROM lootdrop_entries WHERE lootdrop_id = '893' OR lootdrop_id = '896';

+-------------+--------+---------+
| lootdrop_id | chance | item_id |
+-------------+--------+---------+
| 893 | 17 | 1320 |
| 893 | 16 | 7009 |
| 893 | 17 | 7352 |
| 893 | 17 | 10403 |
| 893 | 17 | 12884 |
| 893 | 16 | 13400 |
| 896 | 100 | 20445 |
+-------------+--------+---------+

We can also use the loottable_id to find the coinage the mob will drop. This is stored in the loottable table.

select * from loottable where id = '224';
+-----+--------+---------+---------+---------+
| id | name | mincash | maxcash | avgcoin |
+-----+--------+---------+---------+---------+
| 224 | Najena | 1080 | 7168 | 0 |
+-----+--------+---------+---------+---------+

It appears that the avgcoin column is ignored ?

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.

SELECT id,name FROM items WHERE id = '1302';

+------+-------------+
| id | name |
+------+-------------+
| 1302 | Savants Cap |
+------+-------------+

... and so on ...

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.

SELECT lootdrop_id,name,chance FROM items,lootdrop_entries WHERE items.id = lootdrop_entries.item_id AND lootdrop_id = '893';

+-------------+------------------------------+--------+
| lootdrop_id | name | chance |
+-------------+------------------------------+--------+
| 893 | Flowing Black Robe | 17 |
| 893 | Rusty Spear | 16 |
| 893 | Fine Steel Rapier | 17 |
| 893 | Clawed Knuckle-Ring | 17 |
| 893 | Fine Steel Rapier | 17 |
| 893 | Black Tome with Silver Runes | 16 |
+-------------+------------------------------+--------+

SELECT lootdrop_id,name,chance FROM items,lootdrop_entries WHERE items.id = lootdrop_entries.item_id AND lootdrop_id = '896';

+-------------+---------------------+--------+
| lootdrop_id | name | chance |
+-------------+---------------------+--------+
| 896 | Golden Crescent Key | 100 |
+-------------+---------------------+--------+

and of course, we could string them together to make one result table.

SELECT lootdrop_id,name,chance FROM items,lootdrop_entries WHERE items.id = lootdrop_entries.item_id AND (lootdrop_id = '893' OR lootdrop_id = '896');

+-------------+------------------------------+--------+
| lootdrop_id | name | chance |
+-------------+------------------------------+--------+
| 893 | Flowing Black Robe | 17 |
| 893 | Rusty Spear | 16 |
| 893 | Fine Steel Rapier | 17 |
| 893 | Clawed Knuckle-Ring | 17 |
| 893 | Fine Steel Rapier | 17 |
| 893 | Black Tome with Silver Runes | 16 |
| 896 | Golden Crescent Key | 100 |
+-------------+------------------------------+--------+

This is an incomplete picture because we have left out the probability column. Remember, probability is the percentage of time items will be picked from a loot group.

The following, slightly more complex query provides the probability column back in.

SELECT loottable_entries.lootdrop_id,loottable_entries.mu ltiplier, 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;

+-------------+------------+-------------+------------------------------+--------+
| lootdrop_id | multiplier | probability | name | chance |
+-------------+------------+-------------+------------------------------+--------+
| 893 | 1 | 100 | Flowing Black Robe | 17 |
| 893 | 1 | 100 | Rusty Spear | 16 |
| 893 | 1 | 100 | Fine Steel Rapier | 17 |
| 893 | 1 | 100 | Clawed Knuckle-Ring | 17 |
| 893 | 1 | 100 | Fine Steel Rapier | 17 |
| 893 | 1 | 100 | Black Tome with Silver Runes | 16 |
| 896 | 1 | 35 | Golden Crescent Key | 100 |
+-------------+------------+-------------+------------------------------+--------+


From the above query, we could deduce the following:
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.

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';

+------------------------------+
| name |
+------------------------------+
| Flowing Black Robe |
| Rusty Spear |
| Fine Steel Rapier |
| Clawed Knuckle-Ring |
| Fine Steel Rapier |
| Black Tome with Silver Runes |
| Golden Crescent Key |
+------------------------------+


I hope this helps some of you who are struggling to understand the data relations in the database.

*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.

Muuss
11-16-2004, 10:46 AM
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.