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';
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.
Code:
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.
Code:
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.
Code:
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.
Code:
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.
Code:
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.
Code:
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.
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');
+-------------+------------------------------+--------+
| 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.
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;
+-------------+------------+-------------+------------------------------+--------+
| 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.
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';
+------------------------------+
| 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.
Learning about Drops
Linear Mode
