New loot system

[cavedude]

My next commit on the EQEmu SVN will include an overhauled loot system. It will contain a SQL that will convert your loot tables to the new system preserving both drop rates and drop behavior. I just wanted to post so that everybody understands the new system.

First, the table changes:

Two new columns have been added to loottable_entries called droplimit and mindrop. Probability has been removed from loottable_entries. Multiplier has been added to lootdrop_entries, and chance in lootdrop_entries has been changed to a float so it can handle decimals.

The biggest change is in the new system, each item is rolled individually instead of against every other item in the same lootdrop table. I have already confirmed on PEQ that there is no performance hit with this change. This means that you could potentially have every item on a single lootdrop table per loottable if you so wish. However, multiple lootdrop tables per loottable is still very much supported. The added columns preserve ALL of the functionality the old system, plus adds some new functionality.

The new columns are:

mindrop: This value forces the lootdrop table to always drop the number of items specified. If all of the items are rolled and mindrop is not met, everything is re-rolled until it is. 0 is no mindrop, so the table could potentially drop no items if none are 100%.

droplimit: The opposite of the above column. Once this value is met, no more items will drop from the lootdrop table, even if entries with a 100% chance are left to roll. There is also a chance that the droplimit is not met, and fewer items than the value specified will drop. 0 means there is no limit, and the lootdrop table could potentially drop every item.

multiplier: Same as before really. The simplest way to explain this is it multiplies the other 2 columns. So, if your mindrop is 2 and your multiplier is 4, the lootdrop will drop AT LEAST 8 items. If your droplimit is 2 and your multiplier is 4, the lootdrop will drop NO MORE than 8 items. multiplier is useful if you want a different number range of items to drop every time, instead of just the number of items in the lootdrop table.

It is important to note using either mindrop and multiplier can cause the same item to be dropped multiple times.

It is also important to note that all 3 columns will effect the true percentage every item has of dropping. droplimit may prevent items from even getting a roll at all, mindrop may potentially give items extra rolls, and multiplier will give every item an extra roll per each value (multiplier of 4 gives every item 3 extra rolls.) Setting mindrop to 0, droplimit to 0, and multiplier to 1 will give you 100% clean percentages, meaning the percentage specified in chance will be the exact chance the item has of dropping.

The order the items are rolled is randomly chosen every time, so no items will have an advantage over others due to location in the list.

Last, the new multiplier column in lootdrop_entries works exactly the same way as the one above, except it's per item. So, if you need rats eyes to potentially drop twice, but rat tail to only drop once you can do so and still have them in the same lootdrop table. Any items with multiplier set here receive an extra roll per value, while the ones with it set to 1 do not. Only the first successful roll counts towards mindrop above. So, if mindrop is set to 1 and ration's multiplier is set to 4, you can still drop 4 rations however no other items in that lootdrop will be able to drop.

Any questions, please ask away :)

[Akkadius]

Will there be punch and pie in this new loot system?

[Burningsoul]

I second the punch and pie. This is huge, and I cannot wait to see what can be done with it. Congrats you guys!

[cavedude]

I've committed this in Rev2213.

I also updated the PEQ PHP Editor when these changes were first put on PEQ (Rev342 for it), so that's all ready to go as well.

I was kinda hoping for pizza and beer, but oh well. Punch and pie it is.

[namini]

I've committed this in Rev2213.

I also updated the PEQ PHP Editor when these changes were first put on PEQ (Rev342 for it), so that's all ready to go as well.

I was kinda hoping for pizza and beer, but oh well. Punch and pie it is.

Let em have their punch and pie -- let's have pizza and beer. I homebrew, so I got the beer.. someone in NY wanna supply the pizza?

[ghanja]

Could someone please provide a, "Using the provided SQL to convert your tables for dummies" instruction please?

I've updated the SVN (http://projecteqemu.googlecode.com/svn/) and it grabbed a few files, I believe the very ones that were changed due to this new loot system being applied/committed.

However, my "DB" directory (http://projecteqdb.googlecode.com/svn/trunk) shows no change.

Just a short to do instruction would be greatly appreciated. Started on customizing, with quite a few Items being deleted, modified and/or added and really hate to lose it all (yes, I backup the DB with a dump every once in awhile, but, I only know how to use that backup in the case where I have to replace the entire DB). Thank you.

[ghanja]

Oh, that was much easier than I thought it would be. Just "execute sql" just as is done upon initial install, though on the "2213_loot_changes.sql" found in the "../utils/sql/svn" directory.

[GeorgeS]

Nice work all, and now to get back to figure this out and how to fix our tools.
I may have to make a large VB class to handle loot now, as updates will only require fixing it and not every tool. This will simplify things... much to do..

GeorgeS

[cavedude]

The PEQ DB will be updated soon. I have another change from KLS that needs to be committed first to EQEmu before I update it. Plus, with my connection it takes a while for me to upload the DB ;)

However, in the meantime you can just source the 2213_loot_changes.sql file that came down in the newest EQEmu update to convert you.

[ChaosSlayerZ]

Speaking of tons and tons of recent changes and improvements (great work btw everyone!) but would any dev be willing to update the official binaries for the server? The last update was back in February.

Thanks ;)

http://code.google.com/p/projecteqemu/downloads/list

[lerxst2112]

It is so easy to build your own.

[Maze_EQ]

Akkadius made it foolproof. (http://http://www.eqemulator.org/forums/showthread.php?t=35722)

[cavedude]

I posted 32bit binaries for Rev2214. Somebody else can handle 64bit and Bots builds. PEQ DB for 2214 is up too.

[ghanja]

So that I do not completely overwrite my database. And I've already updated the tables running the sql update a day ago. But.. I see it grabbed quite a few tables.

What is the proper procedure for updating an existing database with the changes found in this update Cavedude just released?

What I'm thinking is just running a "Execute SQL File..." within Navicat on the database, selecting "Rev2214.sql" from the /peqdatabase/updates directory?

[ChaosSlayerZ]

I posted 32bit binaries for Rev2214. Somebody else can handle 64bit and Bots builds. PEQ DB for 2214 is up too.

thank you for the binaries Cavedude! much appreciate it.

I do have a question on the new system thought:

Probability has been removed from loottable_entries. Multiplier has been added to lootdrop_entries, and chance in lootdrop_entries has been changed to a float so it can handle decimals.

If i remember correctly old system worked like this:

Suppose we have An_Orc who had loottable_id 12345

loottable_id 12345 would have inside of it:

lootdrop_entries 26, mult 1, 33%
lootdrop_entries 85, mult 2, 77%

lootdrop_entries 26 would have inside:
rusty mace 20%
club 70%
short sword 10%

lootdrop_entries 85 would have inside:
bronze bracer 80%
chainmail 20%

So, the orc had 33% chance to trigger entry 26 - to drop anything at all, before we even get into what exactly he would drop

2 opportunities to trigger entry 85 at 77% each

In NEW system if you saying that probabilities are removed, how can you replicate the system above where I want a situation where npc may or may not drop anything at all even before we get to the items?


Also, when you say that items will now "roll individually" - I do know that all %% on items were screwed up and place in list was affecting proper percentages, but "roll individually" sounds somewhat vague - do items total still have to add up to 100%?

thanks!

PS Sorry if I missed something already explained. ;)

[Akkadius]

In NEW system if you saying that probabilities are removed, how can you replicate the system above where I want a situation where npc may or may not drop anything at all even before we get to the items?

I know this is easy to over think and I did it too when I was question Cavedude on changing the system before we bring in the data that I parsed from Magelo.

Think of it as a header.

loottable_entry:
mysql> select * from loottable_entries WHERE loottable_id
= 1000 limit 1;
+--------------+-------------+------------+-------------+-----------+---------+
| loottable_id | lootdrop_id | multiplier | probability | droplimit | mindrop |
+--------------+-------------+------------+-------------+-----------+---------+
| 1000 | 2014 | 1 | 25 | 1 | 0 |
+--------------+-------------+------------+-------------+-----------+---------+

You have a 25% probability of rolling into the lootdrop:

Inside that lootdrop (2014):

mysql> select * from lootdrop_entries where lootdrop_id = 2014;
+-------------+---------+--------------+------------+--------+----------+----------+------------+
| lootdrop_id | item_id | item_charges | equip_item | chance | minlevel | maxlevel | multiplier |
+-------------+---------+--------------+------------+--------+----------+----------+------------+
| 2014 | 3329 | 1 | 1 | 8.25 | 0 | 127 | 1 |
| 2014 | 3342 | 1 | 1 | 8.5 | 0 | 127 | 1 |
| 2014 | 3353 | 1 | 1 | 8.25 | 0 | 127 | 1 |
+-------------+---------+--------------+------------+--------+----------+----------+------------+

Each item in this lootdrop now is rolled individually. In other words, all three of these items could drop at the same time but they are all rolled individually instead of ONE roll a random number 1-100 and it being (3329 - 8.25%) - (3342 - 8.5%) - (3353 - 8.25%)

Now it is a roll for each item:
3353 - 8.25 (Rolled 10, well shucks)
3329 - 8.5 (Rolled 4, nice it dropped!)
3342 - 8.5 (Rolled 94, no cigar)



Also, when you say that items will now "roll individually" - I do know that all %% on items were screwed up and place in list was affecting proper percentages, but "roll individually" sounds somewhat vague - do items total still have to add up to 100%?

No items do not have to add up to 100% as they are rolled individually, the existing % and drops for PEQ will be audited against the script I am building to dump the new data and audit the existing data.

Make sense?

[cavedude]

Each item is rolled against its own chance now. Before, the items were rolled against all the others in the group which meant only 1 could drop (without multiplier) and the table had to equal 100%. In the new system, the table no longer needs to equal 100%, the table no longer will always drop an item (without probability, the table will essentially always roll true), and the table could potentially drop all of its items.

Probability was a silly redundant system and there was no reason why it couldn't have been combined into chance which is exactly what I've done. If you look at the SQL in the commit, you'll see I multiplied probability by chance to come up with the new chance percentage. The resulting chance is the same percentage as the old system, only it's now using 1 value instead of 2. So for example, if the old system had a probability of 20% and a chance of 50%, the total chance the item would drop is 10%. (20% chance the table would even be picked, and then you'd split that in half since the item only had a 50/50 shot of being picked.) 10% is now what the chance column reflects for that item in the new system. Let me convert your example for you to the new system to visualize it for you:

loottable_id 12345 would have inside of it:

lootdrop_entries 26, mindrop 0, droplimit 1, multiplier 1
lootdrop_entries 85, mindrop 0, droplimit 2, multiplier 2

lootdrop_entries 26 would have inside:
rusty mace 6.6%
club 23.1%
short sword 3.3%

lootdrop_entries 85 would have inside:
bronze bracer 61.6%
chainmail 15.4%

This replicates both the drop rates of the old system (multiply probability by chance after converting to decimal) and the drop behavior as well (lootdrop 26 will only drop 1 item max, lootdrop 85 will drop 2 items max.)

[cavedude]

Akkadius: Probability is no longer used, I haven't dropped it yet because I was still using it as a reference. Everything checks out so it'll be gone in the next revision of the DB. We're using straight percentages now, so the percentages you see on Magelo are the exact ones we'll merge into the DB.

[Akkadius]

Akkadius: Probability is no longer used, I haven't dropped it yet because I was still using it as a reference. Everything checks out so it'll be gone in the next revision of the DB. We're using straight percentages now, so the percentages you see on Magelo are the exact ones we'll merge into the DB.

I know we're using straight percentages, it was my fault for not thinking twice on that. Had to hurry and finish the post so I could finish configuring some things. MY BAD CD!

[ChaosSlayerZ]

ok I got it now ;) thank you guys!

The multiplication of probability by % chance was the part I was missing.
I guess the new system was done for the sake of optimization, thought old one was a bit more straight forward (new system maybe a bit harder to explain to a newbie)

One more question abotu exmaples.

Suppose in old system I have following:

lootdrop_entries 85, multiplier 1, prob 100%

lootdrop_entries 85 would have inside:
item1 10%
item2 10%
item3 10%
item4 10%
item5 10%
item6 10%
item7 10%
item8 10%
item9 10%
item0 10%


so only 1 out of 10 items could drop each time with equal chance (old system RND buginess aside)

how this end up looking in new system?

[sorvani]

the probability in loottable_entries is always 100% now. All tables always drop. there is no field to edit that % in the editor anymore. I would have to look at CD's code again to tell you if it is even used, since the field is still in the DB

When you create a lootdrop the code then randomly picks one of the items in the lootdrop_entries table and starting at that point it then goes one at a time through each item rolling the % chance.

if no item hit's it's individual % then no item is dropped. the min/max numbers affect as cavedude described.

In your example that loot drop converts exactly the same with the addition of a droplimit= 1 (the conversion sets all drop limit's to 1 unless the lootdrop had a mulptiplier originally)

then a random number is rolled form 1 to 10. (result 6 for example) the code then rolls the % listed for item6. if it hits, it checks the total dropped against droplimit and since it is now 1 it will end the loop. if item6 did not drop it would roll item 7, 8, 9, 10, 1, 2, 3, 4, 5 until one dropped or the loop ran out.

[cavedude]

It's actually a LOT more simple than the old way once you grasp it. But yes, there is a learning curve involved coming from the old way.

That example is simple, you just use mindrop and droplimit to force 1 item to drop every time (since probability was 100%) So:

lootdrop_entries 85, mindrop 1, droplimit 1, multiplier 1

lootdrop_entries 85 would have inside:
item1 10%
item2 10%
item3 10%
item4 10%
item5 10%
item6 10%
item7 10%
item8 10%
item9 10%
item0 10%

Since probability was 100%, none of the percentages will change of course. No more and no less than 1 of those 10 items will drop.

But changing it up, let's say the old probability was 80%. Then the same example would look like:

lootdrop_entries 85, mindrop 0, droplimit 1, multiplier 1

lootdrop_entries 85 would have inside:
item1 8%
item2 8%
item3 8%
item4 8%
item5 8%
item6 8%
item7 8%
item8 8%
item9 8%
item0 8%

Notice mindrop is now 0 because we are no longer guaranteed an item, and all the percentages dropped because multiplier is 20% lower. But, they all equal 80% - the old probability :)

Let's have some fun and show off what the new system can do.

Same example above, but:
mindrop 0, droplimit 0: 0 to all 10 of those items will drop.
mindrop 5, droplimit 0: 5-10 of those items will always drop.
mindrop 1, droplimit 2: 1-2 of those items will drop.
mindrop 0, droplimit 0 and all the items have a 50% chance of dropping: 0-10 items can drop, but most of the time 5 will (since "probability" is now 500%.)

[cavedude]

the probability in loottable_entries is always 100% now. All tables always drop. there is no field to edit that % in the editor anymore. I would have to look at CD's code again to tell you if it is even used, since the field is still in the DB


I mentioned above probability is still in the DB as I was still using it to verify everything. But, we're good and it'll be gone in the next revision as it's no longer used.

[ChaosSlayerZ]

ok i think I am getting a grasp of the new concept now.

From what I see if all item in the lootdrop have identical chance to drop, then it doesn't matter what % they are given as long as its equal.

In other words:
if mindrop 1, droplimit 1, multiplier 1

then
item1 10%
item2 10%
item3 10%
item4 10%
item5 10%
item6 10%
item7 10%
item8 10%
item9 10%
item0 10%

is equal to:

item1 66%
item2 66%
item3 66%
item4 66%
item5 66%
item6 66%
item7 66%
item8 66%
item9 66%
item0 66%


but next question arises about efficiency:

you said that the new code will "keep rolling" until it roll the needed value.
so if items all set to 10% and NONE fall within 10% on the first roll, the system has to roll again and again until one of them does. Of course the chance of that is small - but isn't it a strain on the system?
Theoretically the rolling can go on indefinitely, isn't it?

Another issue arises when more than 1 item suddenly satisfies drop parameters but with droplimit=1, how does system chooses which item drops if say all 10 have been rolled successfully?

Not to criticizing all the hard coding you put into it, but wasn't old system a bit simpler/faster? Since its only rolled once on the item list?
I know that the RND was bugged, and item order affected RND in a bad way, but wasn't fixing RND a more straight forward solution, than the a whole new approach?

Of course, I am not the person to judge the code on its performance, but just trying to think out loud and understand the reasoning beyond the rewamp ;)

[Akkadius]

That was a concern I had for Cavedude at the time but the way he had implemented poses no real performance hit whatsoever.

[cavedude]

From what I see if all item in the lootdrop have identical chance to drop, then it doesn't matter what % they are given as long as its equal.

It doesn't matter what the other items are doing, the only percentage that matters is the current item's chance. But, if neither droplimit or mindrop are set, then the percentage most certainly matters. If it's set to 1%, you'd be lucky to get a single item to drop. It it's set to 90%, you have a good chance of every item dropping. But, not every item has to be equal, and they don't all have to equal 100%... You could have 50 items in a lootdrop table with chances ranging from 0.01% to 100% if you wanted. In that case, yes the NPC could drop all 50 items if you're super lucky and you haven't set a droplimit.

droplimit = 1 the first item that rolls successfully drops, the remaining items aren't even rolled. As I said in the first post the order which each item is rolled is randomized each time, so when you do set a droplimit, the same item won't drop over and over again because it's first in the list.

There isn't a performance hit, PEQ showed no difference in time to boot zones. That was a concern of mine at first too, but KLS believed that if done right, we wouldn't see an impact provided we didn't have super long unlimited lists. It turns out he was correct. So, if you have a long list of 100+ items, be sure to set a droplimit so the server doesn't have to iterate through every item. (Although, the code does set a droplimit for lists 100+ automatically.) Rolling will never go indefinitely, but setting a high mindrop or not limiting long lists certainly can increase the rolls. Basically, if you're smart about your setup you'll be fine. In your example, if all the items failed they are only re-rolled if a multiplier is set to 2 or above or mindrop is 1 or above. If neither are true, then the loop ends and the table drops nothing.

Fixing RNG has nothing to do with this... This new system is far more powerful and indeed simpler than the old one. Seriously, the old system was unnecessarily complicated and backwards. Probability is gone, there is no longer a need to have multiple lootdrop tables, those tables no longer need to equal 100%, and we now have a method to have complete control over how many items drop, whether that be a specific number or a range without fiddling with multipliers or multiple tables.

[sorvani]

From what I see if all item in the lootdrop have identical chance to drop, then it doesn't matter what % they are given as long as its equal.
No it matters. 10% != 66%

lootdrop 20 has mindrop=0 droplimit=1 and multiplier=1

This means that no matter how many items are in the lootdrop that between 0 and 1 of them will drop.

lootdrop 20 has 5 entries.

position 1: itemid 13071 = 90%
position 2: itemid 13072 = 50%
position 3: itemid 13073 = 50%
position 4: itemid 13074 = 50%
position 5: itemid 13075 = 1%

First, the code will roll a random number between 1 and 5.
This will determine which item gets rolled first.
Say the result is 2.
The code then looks at the % for the item in position 2 and rolls against that %.
If it hits, the item is dropped and the loop terminates, no more items are rolled.
If it does not hit, then the code moves on to position 3 and rolls against the % for that item.
This continues with positions 4 then 5 then loops back around to position 1.
If at this time no item has yet dropped, the loop terminates because there is no minimum drop requirement for the lootdrop.

Now, let us change the % for position 1 to 100% and assume that the random starts on position 3. If position 3, 4 or 5 hit, then that means position 1 will not even get rolled even though it is 100%. Similarly, if the random started on position 1 and it was 100% then none of the other 4 positions would even get rolled. the loop would be stopped.

Is that all clear?


Now if you have an item (or more than 1) that you DO want to be 100% Make a separate table for the 100% drop(s).

Lootdrop 21 contains 3 items that should ALWAYS drop.
Set mindrop=3, maxdrop=3 and multiplier=1

Position 1: item123 = 100%
Position 2: item456 = 100%
Position 3: item789 = 100%

No matter where the random 1 to 3 starts this loop each item will drop.


Now you have a loot drop where the mob always drops 2 items from a list of 3 (and can dupplicate a drop)

lootdrop 22 contains 3 items, set mindrop=2, maxdrop=2 multiplier=1

Position 1: item123 = 40%
Position 2: item456 = 40%
Position 3: item789 = 20%

The random 1 to 3 will start at 3 and roll against the 20% for that position.
Then it rolls against hte 40% for position 1 then against the 40% for position 2.
If only 1 of the 3 items dropped, the mindrop=2 has not yet been met so the loop will continue. pos 3, 1, 2 until a second item drops.

[trevius]

I think I am understanding this new system, but haven't been through the code that supports it yet. I do have one concern though based on the stuff I have been reading in this thread. I have seen a few statements in this thread saying that the old system had to total 100% for the chance in the lootdrop_entries for any given lootdrop_id. This is not true, and if I am understanding the new system and SQL provided for converting it, this could break loot drops for any server that was not following that rule.

The way the old system worked with chance is that it totals the chance from all items with the same lootdrop_id in the lootdrop_entries table. It then rolled the chance to drop for each item against the total from all entries for that lootop_id. So, whether you have 10 items all set to 10% chance or 10 items all set to 100% chance, the result would be the same.

Here is an example:

item1 10%
item2 10%
item3 10%
item4 10%
item5 10%
item6 10%
item7 10%
item8 10%
item9 10%
item0 10%

So, the total chance is 100 in this case. It would then roll 1-100 for each item (starting at a random item location in that list) until one of the rolls was less than or equal to 10. This gives it 2 points of randomness when choosing the drops, where the first point is the random point that it starts in the list and the second point is the rolling of each item until it finds a winner.

Here is another example:

item1 100%
item2 100%
item3 100%
item4 100%
item5 100%
item6 100%
item7 100%
item8 100%
item9 100%
item0 100%

In this case, the total is 1000. So in the old system it would then roll 1-1000 for each item, effectively giving each of them a 10% chance to drop, the same as the first example.

I regularly started using values of 100 for chance for lootdrop_ids that were all supposed to have the same drop rate. My reasoning was that it means there is no math involved at all when setting drop rates, because you don't have to manually add up the totals and verify they total up to 100. I don't have to set 5 items each to 20, or 10 items each to 10 or 7 items each to 14 (which would actually be 5 set to 14 and 2 set to 15 to total 100) to make equal drop rates.

My concern is mainly with the SQL to convert the chances. If I am understanding the SQL from Cavedude correctly, my lootdrops that are set to 100 would now each have a 100% chance to drop (assuming probability of 100 was set for them). So, in the new system, it would always pick the first item it rolls for. The only saving point is that the new system should still be picking an item from the list to start rolling at by random, so there would still be a random factor in it. The problem is that there wouldn't be 2 random factors in cases like this like I mentioned previously.

I haven't messed with it yet, but I think it is probably possible to write a different query to do the conversion from the old system to the new that would take this issue into account. It would also correct any issues with loot drops where someone didn't do their math correctly when trying to total 100 in the chance field for a lootdrop_id.

I would guess we can use SUM() to total up the chance field for each lootdrop_id, and then do chance * 100 / total for each individual entry for that lootdrop_id before doing the rest of the math that cavedude provided for calculating the new chance values. For example, if the total is 1000 for 10 items that are each set to 100, SUM() would get 1000. So we would do chance * 100 / 1000 to get a value of 10 for each entry, which is what the chance should have been if following the concept of totaling them all to 100.

Here is another example to break it down and show a more complex result:

item1 50%
item2 50%
item3 20%
item4 20%
item5 60%

The SQL to convert this would then need to total those chances up to 200, and then it would do chance * 100 / 200 that was set there for the following results:

item1 50% (50 * 100 / 200 = 25%)
item2 50% (50 * 100 / 200 = 25%)
item3 20% (20 * 100 / 200 = 10%)
item4 20% (20 * 100 / 200 = 10%)
item5 60% (60 * 100 / 200 = 30%)

So then they would all total 100% chance.

Since SUM() has to be ran in a select and you can't select from a table while updating the same table, I think we would just need to create a temporary table to store the SUM() totals for each lootdrop_id and the id of the lootdrop_id, then we can do the rest of the math in a second query for the updates while selecting the sum values from the temp table. Then just drop the temp table.

The SQL should be something like this (untested):

CREATE TEMPORARY TABLE temp_table (
lootdrop_id INT(11) PRIMARY KEY,
chance_total INT(11) DEFAULT 0
);

INSERT INTO temp_table ( temp_table.lootdrop_id, temp_table.chance_total ) (SELECT lootdrop_entries.lootdrop_id, SUM(lootdrop_entries.chance) FROM lootdrop_entries GROUP BY lootdrop_entries.lootdrop_id);

UPDATE lootdrop_entries, temp_table SET lootdrop_entries.chance_total = (lootdrop_entries.chance_total * 100 / temp_table.chance_total) WHERE lootdrop_entries.lootdrop_id = temp_table.lootdrop_id;

DROP TEMPORARY TABLE temp_table;

This would need to be ran prior to the conversion cavedude committed, which is this:

update lootdrop_entries lde
inner join loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
SET lde.chance = (lte.probability/100)*(lde.chance/100)*100;

I think that should resolve any issues with converting from the old system to the new. The only thing that might need to be accounted for is any lootdrop_ids that are set to a total chance of 0, as I think that might cause an error when trying to divide by 0.

Again, this SQL is untested, so do not run this until it has been tested and verified to work as intended!

If I am misunderstanding something with the new system, please let me know :)

FYI, this is what ChaosSlayer meant by setting them all to 10% or all to 66% is the same thing in the old system. As long as they were equal values, the chances were equal no matter what.

[namini]

Just when I thought I had my head wrapped around the old system. Heh. Guess I'll move onto tradeskills and faction portions of customizing. I trust in those with the experience, but, may I ask if its safe to assume that this change will break Allaclones display of "probability" as well as "loot table" categorizing when displaying an NPC's loot?

[trevius]

Just when I thought I had my head wrapped around the old system. Heh. Guess I'll move onto tradeskills and faction portions of customizing. I trust in those with the experience, but, may I ask if its safe to assume that this change will break Allaclones display of "probability" as well as "loot table" categorizing when displaying an NPC's loot?

Yes, AllaClone will require a fairly minor fix for it to properly pull the new drop rate system. I don't want to side-track this thread, but hopefully Akka and I will get some time soon to fix the clone for the new field changes. We still have quite a lot of work to do on the new stuff we have been working on for AllaClone, but we could always put out an update that doesn't include the stuff we are still working on.

[cavedude]

Trevius, you're correct that if the tables don't add up to 100% there will be trouble converting, though realistically counting errors of a few percent can probably be disregarded off the bat. They won't make much of a difference, and were probably created by a deleted item or a mistake on a single entry anyway.

But for custom setups like yours, you already figured out the solution. You'll basically be doing half of what the code was doing anyway, since in the end the total probability had to equal 100% from a mathematical standpoint. You'll just need to add the extra step of simplifying the chances down to their root percentage.

[ChaosSlayerZ]

Fixing RNG has nothing to do with this... This new system is far more powerful and indeed simpler than the old one. Seriously, the old system was unnecessarily complicated and backwards. Probability is gone, there is no longer a need to have multiple lootdrop tables, those tables no longer need to equal 100%, and we now have a method to have complete control over how many items drop, whether that be a specific number or a range without fiddling with multipliers or multiple tables.

Oh Cavedude I strongly disagree that multiple lootdrop tables are no longer needed.

Look at following example:

So I have a Zombie in old system.
I want zombie to drop 1 body part and 1 weapon item on every kill

So you NEED 2 loottables for this:
loottable 1 - has all the zombie body parts in it
loottable 2 - has all the weapons in it

If you try to put this into same loottable and give a multiplier of 2 - you may end up in getting 2 weapons or 2 body parts per kill instead of 1 body part +1 weapon.

In your new system, this is still the case.

More to that, I don't have just 1 zombie type on my server, I have 50 different zombie TYPES from level 1 to level 50, EACH of whom can drop the SAME body parts, yet different gear.

For each of those npc types its much more practical to use TWO loottables - one to handle common body parts and another to handle gear drops, rather than to keep adding body parts into each and every gear table for each npc types.

Not to mention that gear loottable can be shared with other npcs - orcs, gnolls whatever, who can still drop the same weapon but NOT the zombie parts.

As you can see its way more practical to have:

-a loottable that handles create type unique items (per create type)
AND
-a lootable that handles universally shared items like weapons for given level range

and then use permutation of those to construct complete loottables for any given npc.

level 10 zombie will use:
-level 10 weapons table
-zombie body parts table


level 50 zombie will use:
-level 50 weapons table
-zombie body parts table


level 10 orc will use:
-level 10 weapons table
-orc body parts table


level 50 orc will use:
-level 50 weapons table
-orc body parts table


So as you can see I used 3 loottables to represent 4 different npcs.
And the difference only keeps growing as tables shared among even greater number of mobs, without the need to create unique table for each.

---------------------------------

PART 2

back to the subject on new system in general.
Going back to my example of where new item % drops are essentially multiplications of old probability times old item chance.

Math wise everything is the same, but human intuition wise - its a extra step added to get what you need.

In old system I would say: "I want an orc to drop 1 weapon out of 10 random ones once per 4 kills". And i would set my tables like this:

Loot_entry: mult 1, probability 25%
Loottable: 10 items with 10% chance each

In NEW system this converts to:

mult 1, droplimit 1:
each item set to 2.5%

Everything is identical MATH wise.

BUT in my mind to place data into DB with NEW system in mind, I still have to calculate "what I need" the OLD way, then do the multiplications in my head, and enter data the NEW way.

Now exmaple above is simple - cause its just 25% X 10%

now try soemthign liek this:

I want a mob to have 33% chance to drop something on his table that has 10 items in a following manner:

-item1 12%
-item2 9%
-item3 11%
-item4 8%
-item5 27%
-item6 13%
-item7 8%
-item8 2%
-item9 7%
-item0 3%


In old system I just set probability to 33% and items to what I want them to be.

In NEW system I have to do ALL OF THE WORK ABOVE PLUS do all the multiplication of item chance times the 33% and then put them in the DB.

I honestly don't consider this approach easier :(


The addition of droplimit and mindrop is GOOD.
But removal of loot_entry probability is not so great in my opinion - it makes things much harder to calculate in your mind and requires unnecessary per-calculation steps.

[cavedude]

First off, you're over thinking everything including my explanations! :)

Obviously we still need multiple lootdrop tables, that's why I didn't touch that. What I mean simply is we now have ways to reduce all of our tables down to 1 if we so choose. The old way, we essentially were forced into using multiple tables.

Okay, completely forget about the old system... Probability never existed, in fact EQEmu never has had a loot system before today. As a human, when you flip a coin what is the chance it'll land on heads? 50%. Now do you think well, it becomes 25% because there is a 50% chance I won't even flip the coin? No. You will always assume whatever prerequisite is already met before figuring out chance. That's what I've done. That table is always going to roll true, you're always going to flip that coin. Now what do you want the actual chance of an item dropping to be? We only multiplied chance by probability to convert to the new system. From here on out, forget about doing that in your head. How often do you want the item to drop, figure that out and put it in your DB!

[chrsschb]

Going from 1) Probability 2) Multiplier 3) Chance Per Item

To 1) Mindrop 2) Maxdrop 3) Multiplier 4) Chance per Item

= Easier?


Does not compute. I understand what you're trying to accomplish, but the math is more difficult. My tables are already setup so that if I want something to always drop, it does, and if I want it to be rare, it is. I'm not seeing how this system is going to simplify that.

I'm still going to have to have multiple lootdrop entries in order to control the differences between loot sets (quest items, random loot, guaranteed drops, etc) so nothing there has changed.

/mindblown

[ChaosSlayerZ]

Okay, completely forget about the old system... Probability never existed, in fact EQEmu never has had a loot system before today. As a human, when you flip a coin what is the chance it'll land on heads? 50%. Now do you think well, it becomes 25% because there is a 50% chance I won't even flip the coin? No. You will always assume whatever prerequisite is already met before figuring out chance. That's what I've done. That table is always going to roll true, you're always going to flip that coin. Now what do you want the actual chance of an item dropping to be? We only multiplied chance by probability to convert to the new system. From here on out, forget about doing that in your head. How often do you want the item to drop, figure that out and put it in your DB!

Cavedude, this approach looks easy when you only have 1 item in question that you setting chances for.

Obviously if my only dilemma to drop a Rusty Mace at 25% chance I am not going to set it to 50% check to drop anything at all and then another 50% check to drop the Rusty Mace.

However once multiple items come into play the new approach becomes a more complicated way of looking at things.

Imagine following (old system):

Imagine you have a orc who may or may not have a BAG.
In side that bag he may have 1 out of 10 items each with different % chances.

So old system would very roll to see if an orc had a BAG at all, and then it would start looking at - what exactly was in the bag?

So if I want to set it up such that Orc has 33% to "have the bag" (in other words "have anything to drop at all"), and in that bag I want an Ultra Rare Sword to only drop 1 time out of 50, I would use the Old system to set:

-general lootentry to 33%
-specific item in that loottable to 2%
-all other items in that loottable to other % as it suits their rarity

And that's it - system runs itself.

But in new system to achieve all this I have pre-calculate all that and THEN multiply all my values to 33% and then put them into the table.

This leads to: my simple system of:

-orc has "a bag with loot" 1/3 of the time
-the bag has Uber Sword 1/50 of the time

to "Mob drops sword 0.66% of the time" which is counter-intuitive to me ... :confused:

Now imagine there are 9 other items "in the bag" and I will have multiply each and all of them by 33% to get the values for the new system.

I am not talking about just internal math/coding inside the server- I am talking about the way human brain approaches this. When you want a mob to drop something you not thinking in terms of "I want this mod drop to Uber Sword 0.66% of the time and a Chain Bracer 8.25% of the time".

This is most definitely not an easier way to think (as a human).
Most people (server devs I guess) when they are designing the loottables they won't thinking in terms: "I want the Uber Sword to drop 0.66% of the time" They would think in terms: "I want an orc to drop a bag with loot 1/3 of the time, and 1 out of 50 there will be an Unber Sword there"

Extend this logic to 10 or even more items pet loottable and you see how much more complicated setting up the data becomes.
You are forced to move from nice round numbers like:

-mace drops 10%
-sword drops 15%
etc

to things like:

-mace drops 3.33%
-sword drops 1.678%

etc

Again, the final math will be the same, but setting up the tables is definitely becomes a headache. :(

[ChaosSlayerZ]

ADDITION

I pondered on this some more and found another SIGNIFICANT problem concerning removal of probability rolls.

You have a loottable that has some weapons on it.

You have following npc types:

orc pawn
orc trainee
orc apprenties
orc warrior
orc centurion
orc legionary
orc warlord

You want EACH of them to have a chance to drop the weapons from your weapon loottable, BUT you want each of them to have different drop chances like this:



orc pawn 10%
orc trainee 20%
orc apprentice 33%
orc warrior 50%
orc centurion 66%
orc legionary 77%
orc warlord 90%

In OLD system what you do is:
-you make a SINGLE weapons table and then assign it to each orc type with a different probability chance.
DONE

In NEW system, I have to create SEVEN different loottables, ALL of them containing identical items but with different item chances multiplied by the probability factor I no longer have access to.

So aside from having to do things like: "multiply 25% item drop chance by the missing 33% probability factor" I have to do this for SEVEN different loottables! Not to mention create the otherwise identical loot-table SEVEN times over.

So my work load just went from "creating loottable ONCE and giving npcs types 7 modifiers to drop it", went to creating 7 NEW lootables over and over and doing my best of not mess things up when putting in the numbers like 6.75%, 13.75% etc.

Now imagine that beside orcs, there are gnolls, kobolts, ratmen, and whoever else, all whom use to have a SINGLE weapons loottable.
NOW, For EACH of them you will need an INDIVIDUAL loottable entry.


So, which one you think is the better "user interface"? ;)

[image]

Looks to me like you could essentially just not update your loottable.cpp and loottable.h then you just have to worry about the zonedb.h using the old function:
void AddLootDropToNPC(NPC* npc,int32 lootdrop_id, ItemList* itemlist);

If you are not interested in the new solution then this is another option.

[werebat]

Currently I have every item in the database (except epics) capable of dropping at a 2% chance for all mobs and it works great. I have not updated yet so I am curious what changes I will need to make in my script. It does look promising though. I plan on updating when the newest db is available.

[cavedude]

I'm not going to be around much for the next 2 or so weeks, but I plan on taking a look at re-introducing probability with its usage being optional sometime after then in October. It'll also be best to hold off on feedback about the system until then as well. It'll give everybody a chance to actually use it, and honestly anything said during that time will be lost as I won't be able to keep up with the forums.

[ChaosSlayerZ]

Thank you for your consideration Cavedude.
I would not be stirring things up about it, if I would not have genuinely believe that we are stand to loose a helpful feature here.

[sorvani]

adding it back in as a server rule would be good then it can be enabled or not by custom content providers like many other features.

[Traul]

Came in to post exactly what ChaosSlayerz did, particularly his ADDITION post. This change has made things much more difficult for me.

Couldn't we simply re-add the probability on individual loottables and have it default to 100 when creating a new one?

[Akkadius]

I can say that I've got loot dumped for all expansions, I just need to refine it and comb through it with Cavedude before finalization.

http://i.imgur.com/3f1tL.jpg
http://i.imgur.com/tETry.jpg
http://i.imgur.com/OBW21.jpg
http://i.imgur.com/uD0NQ.jpg

[werebat]

I am using the new loot system with the defiant armor sql I created. I have chance at 2, mindrop at 0 and droplimit at 1 since I would like a rare drop rate wit no more than one item. But the items are dropping at an incredibly rate. I had 4 pieces drop off of 6 kills. Not sure if I have something set up wrong or if the code needs to be fixed. Under the old system the rates were pretty accurate.

[sorvani]

New system works fine on my test server and I is live on PEQ also. Post your SQL.

[werebat]

I think I figured it out. It has to do with the number of items in the lootdrop. I had 30 items at a chance of 2. So when a kill happens, it was rolling for all 30 items at a 2% chance. Computers do not have a true random like a dice roll, instead it uses an algorithm to simulate the random numbers. So a 2% chance on 30 rolls would mean that 60% of the time you were likely to get a drop. Off 10 kills that is 6 drops which is about what was happening. So I changed the chance to 0.5 and now it is about 1 out of every 6 drops. So it appears your chance should be (desired % drop / # items in drop) to drop properly. I am not sure how low the chance can be set, but if you have a very large number of items in your drop, then it may not be feasible depending on limits. At least thats how its working for me. The old way with probabilty worked fine since there was only one roll made against it. The new system has its merits too so its just a matter of figuring out how to make it work for your purposes. One suggestion would be to randomize the seed before each roll. I may try this when I get a chance but for now the above step seems to working.

[ChaosSlayerZ]

that's exactly the problem with new system
you still have to pre-calc all the chance like in old system and then manually convert it to the new system.

For example if in old system you had a 3% chance to drop SOMETHING out of a list of say 25 items (4% per item) - you just set the probability to 3%, and fill out the item table with even chance per item adding up to 100%.

in new system you now have multiply all 4% by 3%, and set each item to 0.12% for each item. Ton of headache for no gain, not to mention loss of functionality.

[Elysius]

Cavedude you're the man but can we please change this back to the old way? I've stopped working on my server because the amount of work having to make a separate loottable for almost every single NPC is pretty daunting. :(

[revloc02c]

I'm not going to be around much for the next 2 or so weeks, but I plan on taking a look at re-introducing probability with its usage being optional sometime after then in October.
I see pros and cons of both the new system and the old system. I am wondering if you can just implement both by using the probability column as the determining factor:
If probability = 0 use the new system
If probability != 0 use the old system

Just a thought.

(Except the old system would be even cooler if the new columns Mindrop and Droplimit, and minLVL and maxLVL were included in it.)

[ChaosSlayerZ]

IMHO the only problem with old system was uneven RND.

The problem with new system beyond the screw up of loot table design user-friendly interface, is a loss of a significant feature such as utilization of a single loot entry in potential scenarios and variations.

If probability just added on top the new system I am not sure how this will work with each item on the list rolling by itself. Since new list is designed with no probability in mind. But I suppose this can be fixed with setting drop limit.


Again, IMHO simply keeping the old system (+fixing the RND number generation) AND adding the new features like Mindrop is the best way to go.

[chrsschb]

I've been using the old system for over 5 years now and what you guys call "weird RND" is truthfully part of what made EQ, well, EQ. The numbers ARE random, and I've seen every item in a table drop fairly closely to it's proposed drop rate when looking at the whole picture, not just a single person's bad luck.

2% drop rate on super rare item:

Guy one gets item in 5 kills
Guy two gets item in 45 kills

50 / 100 = 2%

Sucks for guy two, BUT THAT IS WHAT WE KNOW AND REMEMBER!

[ChaosSlayerZ]

chrsschb, your example if perfectly normal - but thats not what meant by broken RND.

Let me give you an example:

you have loot table of 10 items, 10% each, and multiplayer of 5. (100% prob)
In theory, you should get up to 5 different items per kill.
What happens in reality, is that RND generates numbers in close series.
So when RND is asked to generate 5 numbers in a row from 1 to 100, it doesn't pick 5, 20, 77, 13, 56. It picks 88,81,84,89,91. So when it comes to loot table, you end up getting 4 copies of item that sits in 80-90 range and 1 item in 91+.

This doesn't happen every-time but VERY often.

[chrsschb]

chrsschb, your example if perfectly normal - but thats not what meant by broken RND.

Let me give you an example:

you have loot table of 10 items, 10% each, and multiplayer of 5. (100% prob)
In theory, you should get up to 5 different items per kill.
What happens in reality, is that RND generates numbers in close series.
So when RND is asked to generate 5 numbers in a row from 1 to 100, it doesn't pick 5, 20, 77, 13, 56. It picks 88,81,84,89,91. So when it comes to loot table, you end up getting 4 copies of item that sits in 80-90 range and 1 item in 91+.

This doesn't happen every-time but VERY often.

I haven't ran into this issue, sorry.

[Traul]

I really like ChaosSlayerz proposed modifications. Is there any update/progress on this at all?

[prickle]

The changes seem ok to me. However, the only problem i have with it is the PEQEdit interface wasn't updated to account for the changes. So, when i though i was setting a 5% chance to get one of 30 items, i was actually setting a 5% chance to get each of 30 items with no limit, because the PEQEdit interface did not have the mindrop and droplimit fields and the default for both is 0.

Fortunately, it appears that GeorgeS GUI tools have been updated to account for these changes, which helps with correcting the drop rates.

[cavedude]

PEQ PHP editor was updated way back in September, Rev 342. Make sure to grab it from SVN and not the download. I'm not going to be maintaining the downloads anymore. I just don't have the time.

I'll get back to the loot changes soon. I'm going to add probability back as an optional part of the system due to the fact that you can no longer have the same lootdrop in multiple loottables with different percentages. But, I'm 95% certain that is the only functionality the new system lost over the old, so no further changes will be needed. Of course, if we come up with something else the new system cannot do that the old could, I'll look at that as well.

For those who plain just don't like it, you can always revert locally. It's only 1 method, part of another method, and a few database calls. That code is hardly ever touched, so you could probably continue on with it in a conflicted state and never be bothered by it.

[ChaosSlayerZ]

you mean the probability, not multiplier, right? ;)

[cavedude]

Sorry, yes! I'll fix my post.

[ChaosSlayerZ]

thank you Cavedude ;)

[ChaosSlayerZ]

So Cavedude, when can we expect the new source revision out with the changes discussed? ;)

[Tabasco]

As he noted, the adjustments are pretty minor. This works for me:
http://dungeoncrawl.us.to/eq/lootstuff.diff.txt

This is an excellent change overall and it has greatly simplified how I do loot.

Edit: You'll need to add a probability field to the table, I have mine in as a float.

[Caryatis]

Whoa dude slow down, you expect us to do a slight amount of work? No thanks, Ill just wait until somebody does it for me.

[sorvani]

Whoa dude slow down, you expect us to do a slight amount of work? No thanks, Ill just wait until somebody does it for me.

don't forget the part about bitching about it.

[Drajor]

As he noted, the adjustments are pretty minor. This works for me:
http://dungeoncrawl.us.to/eq/lootstuff.diff.txt

This is an excellent change overall and it has greatly simplified how I do loot.

Edit: You'll need to add a probability field to the table, I have mine in as a float.

Thanks, working for me :)

[cavedude]

Thanks for that Tabasco, this was going to be my next task after I got the new PEQ forums sorted. Even though it was a minor change, it was a big help to me thanks!

This is committed in Rev 2260.

[ChaosSlayerZ]

thanks Cavedude ;)

[ghanja]

I'm sorry for not following exactly in all this. Has the change been reverted entirely, or is there now chance and probability?

[Akkadius]

I'm sorry for not following exactly in all this. Has the change been reverted entirely, or is there now chance and probability?

Yes, there is now chance AND probability

[thepoetwarrior]

I think I figured it out. It has to do with the number of items in the lootdrop. I had 30 items at a chance of 2. So when a kill happens, it was rolling for all 30 items at a 2% chance. Computers do not have a true random like a dice roll, instead it uses an algorithm to simulate the random numbers. So a 2% chance on 30 rolls would mean that 60% of the time you were likely to get a drop. Off 10 kills that is 6 drops which is about what was happening. So I changed the chance to 0.5 and now it is about 1 out of every 6 drops. So it appears your chance should be (desired % drop / # items in drop) to drop properly. I am not sure how low the chance can be set, but if you have a very large number of items in your drop, then it may not be feasible depending on limits. At least thats how its working for me. The old way with probabilty worked fine since there was only one roll made against it. The new system has its merits too so its just a matter of figuring out how to make it work for your purposes. One suggestion would be to randomize the seed before each roll. I may try this when I get a chance but for now the above step seems to working.

I'm having the same problem with something like a 2% chance for 1 of 30 items in list to drop is now having 60% chance of dropping an item. How can this be fixed?

[cavedude]

I'm having the same problem with something like a 2% chance for 1 of 30 items in list to drop is now having 60% chance of dropping an item. How can this be fixed?

There is nothing to "fix." If you have 30 items with 2% chance that each will drop, then you have a 60% chance that at least 1 will drop (30*2.) That's basic mathematical probability and how our loot system should have worked from the beginning. But, I'm not going to get into that rant.

Now, to change it to behavior like you need it to you have 2 ways.

First, probability (which I almost considered changing the name to probability_modifier because that is what it really is) is back, so you can set that to control the total drop rate of the group of items.

Next, you can lower the chance of the items. (Remember, the new system now accepts floats so you can have chances below 1%.) This basically is just the drawn out way of doing the above. But from a mathematical standpoint, it is the correct way of doing so.

[thepoetwarrior]

I know the new loot system is working on intended, just need to fix coverting properly the old loot to have loot drop rates as intended.

I was thinking that probability was the issue too.

Here is a quick sample:

http://i47.tinypic.com/2w3zzpw.png

I'm guessing if I copy the probability from the old loottable_entries to the new loottable_entries then I would see the desired drop rates we had before?

The problem with manually fixing all the drop rates below 1% is 6 years worth of custom content would be a nightmare to fix manually.

[cavedude]

If you intend on using probability, then both probability and chance would need to be changed to what you had in the old system. If you didn't change chance, then yes change probability back to what it was and you'll be good to go.

If you used the script on SVN to convert your tables over, then loot will drop at the same rate it did before. The only exception would be multiple lootdrops using the same chance but different probability would not have been converted properly. If you did convert, I'd suggest restoring from a backup and forget about the conversion process. That was an oversight on my part brought to my attention in this thread. Though, I am assuming you didn't convert, since your rates are off.

[thepoetwarrior]

I've restored from backup already and rolled back.

I think some of the loots got updated, and some didn't. Should be able to run probability on 100% for everything since chance should have been updated to usually a decimal number for rare loots.

Will try to re run the script again later.

[cavedude]

If you restored your database back to the way it was before the changes, run these queries to restore your functionality as well:

update loottable_entries set mindrop = multiplier, droplimit = multiplier, multiplier = 1 where probability = 100;
update loottable_entries set droplimit = 1 where probability != 100;

This will remove multiplier for tables that have a 100% probability (since mindrop/droplimit can now handle that, although you can still use multiplier if you wish using droplimit) and it forces all other lootdrops to only drop 1 item at a time max (unless a multiplier is set, of course) which duplicates the behavior of the old system. Chance and probability no longer needs to be changed since probability is back.

The difference is in the old system, a lootdrop could only drop a single item unless a multiplier was set, but that still was determined by probability. Now in the new system, we can control exactly how many items will or could drop per lootdrop and it can be as many or as few as you need.

[jasmine88]

nice info keep it up..........

[thepoetwarrior]

After much frustration I gave up on updating the source for a bit. Now trying to give it another shot, and this time with Trevius suggested code as well. As you can see it seem to run fine and no errors:

(On a brief side note, after last source update attempt, with help from Secrets and Akkadius, figured out the zone crashing and npc eating items handed in was due to some plugins, now trying to resolve final issue - loot).

alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
alter table lootdrop_entries change `chance` `chance` float not null default 1;
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;

CREATE TEMPORARY TABLE temp_table (
lootdrop_id INT(11) PRIMARY KEY,
chance INT(11) DEFAULT 0
);
INSERT INTO temp_table ( temp_table.lootdrop_id, temp_table.chance ) (SELECT lootdrop_entries.lootdrop_id, SUM(lootdrop_entries.chance) FROM lootdrop_entries GROUP BY lootdrop_entries.lootdrop_id);
UPDATE lootdrop_entries, temp_table SET lootdrop_entries.chance = (lootdrop_entries.chance * 100 / temp_table.chance) WHERE lootdrop_entries.lootdrop_id = temp_table.lootdrop_id;
DROP TEMPORARY TABLE temp_table;

update lootdrop_entries lde
inner join loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
SET lde.chance = (lte.probability/100)*(lde.chance/100)*100;

update loottable_entries set mindrop = multiplier where probability = 100;
update loottable_entries set droplimit = multiplier where probability = 100;
update loottable_entries set multiplier = 1 where probability = 100;

update loottable_entries set droplimit = 1 where probability != 100;

update lootdrop_entries set chance = 100 where chance > 100;

alter table loottable_entries drop `probability`;

ALTER TABLE `loottable_entries` ADD `probability` FLOAT NOT NULL DEFAULT '100';

/* ************************************* */

[SQL] alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
Affected rows: 23935
Time: 0.060ms

[SQL]
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
Affected rows: 23935
Time: 0.050ms

[SQL]
alter table lootdrop_entries change `chance` `chance` float not null default 1;
Affected rows: 87010
Time: 0.210ms

[SQL]
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;
Affected rows: 87010
Time: 0.210ms

[SQL]

CREATE TEMPORARY TABLE temp_table (
lootdrop_id INT(11) PRIMARY KEY,
chance INT(11) DEFAULT 0
);
Affected rows: 0
Time: 0.050ms

[SQL]
INSERT INTO temp_table ( temp_table.lootdrop_id, temp_table.chance ) (SELECT lootdrop_entries.lootdrop_id, SUM(lootdrop_entries.chance) FROM lootdrop_entries GROUP BY lootdrop_entries.lootdrop_id);
Affected rows: 23170
Time: 0.220ms

[SQL]
UPDATE lootdrop_entries, temp_table SET lootdrop_entries.chance = (lootdrop_entries.chance * 100 / temp_table.chance) WHERE lootdrop_entries.lootdrop_id = temp_table.lootdrop_id;
Affected rows: 4470
Time: 0.340ms

[SQL]
DROP TEMPORARY TABLE temp_table;
Affected rows: 0
Time: 0.010ms

[SQL]

update lootdrop_entries lde
inner join loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
SET lde.chance = (lte.probability/100)*(lde.chance/100)*100;
Affected rows: 80171
Time: 0.840ms

[SQL]

update loottable_entries set mindrop = multiplier where probability = 100;
Affected rows: 1790
Time: 0.010ms

[SQL]
update loottable_entries set droplimit = multiplier where probability = 100;
Affected rows: 1790
Time: 0.010ms

[SQL]
update loottable_entries set multiplier = 1 where probability = 100;
Affected rows: 318
Time: 0.000ms

[SQL]

update loottable_entries set droplimit = 1 where probability != 100;
Affected rows: 22145
Time: 0.070ms

[SQL]

update lootdrop_entries set chance = 100 where chance > 100;
Affected rows: 0
Time: 0.000ms

[SQL]

alter table loottable_entries drop `probability`;
Affected rows: 23935
Time: 0.040ms

[SQL] ALTER TABLE `loottable_entries` ADD `probability` FLOAT NOT NULL DEFAULT '100';
Affected rows: 23935
Time: 0.050ms


Now I'm trying to compare the results of the new system to the old system, and maybe I don't understand the new system, but seems that some stuff didn't convert.

I'm not going to argue that the new loot system is broken. I'm sure it is working as intended. Its just the 5-6 years of custom content we have is not converting over properly, or at least it doesn't appear to.

The screenshot of the tables below is an example from our hohonora zone where players farm tokens to be turned in later for boss spawns. Originally each trash would have an 8 percent chance to drop 1 of 8 different tokens.

Now I understand the new loot system did away with the 'list' of items, but the new results now after conversion show the loot would drop either 0 or 1 of those 8 tokens, but when you step trying 20% chance, if fail then 15% chance, all the way down the list, that adds up to 100% chance (average) that a player would get 1 token.

So now instead of getting a token 8% of the time, its almost 100%, but more realistically players were reporting token drops about 8 of every 10 mobs (80% chance average getting tokens).

If my perception is wrong, then please correct me based on the screenshot provided below. I'm sure the new loot system is working as intended, and working better than the old system. I'm sure the conversion query is working as intended as well. Nothing seemed to break, but our loot tables don't seem to have drop rates as intended anymore.

Trying to give this update / loot system another shot, and not trying to stir up any pointless debates about 'fixing' the new loot system. Just want to be able to move forward with our custom loot drops as intended.

http://i45.tinypic.com/vg34gn.png

[Secrets]

You are dropping probability and then re-adding it. Meaning you have a 100% chance to get every single loot drop.

Simply do not run the last two queries:

alter table loottable_entries drop `probability`;
Affected rows: 23935
Time: 0.040ms

[SQL] ALTER TABLE `loottable_entries` ADD `probability` FLOAT NOT NULL DEFAULT '100';
Affected rows: 23935
Time: 0.050ms

It's causing them to be fubared as a result.

edit: read cavedude's post. it will fix things!

[cavedude]

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on loot:

alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
alter table lootdrop_entries change `chance` `chance` float not null default 1;
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;
update loottable_entries set droplimit = 1 where probability != 100;
update loottable_entries set mindrop = multiplier, droplimit = multiplier, multiplier = 1 where probability = 100;


This will convert your tables to the new system, while keeping the old functionality. The problem was when I removed probability the conversion SQL was needed. Since it's added back, the only change needed is to set droplimit to 1 so that the tables will only drop 1 item at a time like in the old system. Multiplier ignores droplimit so that will still work as intended. Your chance and probability do not need to change at all (which is your issue in the example - your probability changed from 8% to 100%, but you didn't change chance to compensate.)

I'm going to go ahead and change the SQL on SVN to reflect this.

[thepoetwarrior]

Thanks you so much! Will give it a shot. Makes sense how the drop/add changed it all to 100%

As always, I back up the tables before doing any changes, so I can easily start over again to try.

[thepoetwarrior]

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on loot:

alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
alter table lootdrop_entries change `chance` `chance` float not null default 1;
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;
update loottable_entries set droplimit = 1;

This will convert your tables to the new system, while keeping the old functionality. The problem was when I removed probability the conversion SQL was needed. Since it's added back, the only change needed is to set droplimit to 1 so that the tables will only drop 1 item at a time like in the old system. Multiplier ignores droplimit so that will still work as intended. Your chance and probability do not need to change at all (which is your issue in the example - your probability changed from 8% to 100%, but you didn't change chance to compensate.)

I'm going to go ahead and change the SQL on SVN to reflect this.

On page 5 you posted this:

update loottable_entries set mindrop = multiplier, droplimit = multiplier, multiplier = 1 where probability = 100;

Which would probably be good to run after your new 5 lines in the loot change query?

Before in the old system it had always 5 gems drop out of a list of 11 gems, but after running the 5 line query there would only be about 1-5 gems, not always 5. This seems that it might fix it. Players will be testing it soon to verify.

On a good note, loots are not flooding the economy like before. Thanks for allowing to some what use the old system in the new systems structure.

[cavedude]

Run that query last. Sorry about that I had forgotten about tables with a 100% probability and a multiplier set last night. I'll also add that query to SVN.

With probability back the new system can function exactly like the old if needed. But it is my hope that over time, server ops can incorporate the new features of loot into their server as it really is a more robust much better overall system.

[thepoetwarrior]

Yup, I ran 100% probability query last and so far the users are saying loot is back to normal.

I appreciate being able to keep old system drop rates that were intended, and I plan to use the new system the way it was meant to be used for future content.

I have an item with 1/666 chance for drop rate which required a chance of a chance, but now I can just make it 0.0015 chance. So I'll definitely be taking advantage of that float!

Thanks again

[cavedude]

Akkadius wrote up a kick-ass script to grab the loot data from Magelo and insert it into the DB. Many NPCs have a long list of < 1% drops. I've been testing out all those NPCs and the new system is handling it perfectly. Some of the NPCs have 100+ < 1% items and you'll be lucky if one of them drops on PEQ, which is in-tune with Live. So yeah, that's my favorite part of the new system as well.

[Gregk]

This new script and database update are still in the works, correct?

[cavedude]

The Magelo loot/merchant dump will be in the next PEQ database for OoW and after. GoD and before are already pretty good due to manual changes by the PEQ team.

[Akkadius]

The Magelo loot/merchant dump will be in the next PEQ database for OoW and after. GoD and before are already pretty good due to manual changes by the PEQ team.

Good stuff! I grabbed the database a while ago from the BETA backend download :)

[thepoetwarrior]

After about 1/2 day of testing, players report loot is working 99.9% as before. Really hard to find anything 'not intended' drop rates now.

Thanks eqemu team!

[cavedude]

The PEQ DB with Magelo's loot/merchants merged in for OoW to VoA (RoF is not on Magelo yet) is now up on PEQ SVN: https://code.google.com/p/projecteqdb/downloads/list

In addition, since Derision updated EQExtractor to work with the newest patch, Robregen got collects for all the RoF zones. They are in this DB as well. Of course, they cannot be played yet as we don't have a compatible client, but the f2p Steam client is being worked on currently :)

We are missing factions and the NPCs all need a mass stat update, but realistically there is nothing stopping folks from playing in the newer zones up to what UF supports. There isn't any quests or events of course, but you can certainly run around, kill things, and collect loot now.

[c0ncrete]

awyeaaaah, christmas!

[Burningsoul]

New PEQ 2 days before my birthday? You guys rock, keep up the incredible work!

[azog]

Is there a copy of the Magelo to DB script? or could someone upload a sql for zone (moores) all I have for drops is item (Watchtower Remains) for everyone in the zone.

Im at db rev 2294

[cavedude]

I'm not sure how that happened, but it's fixed now in PEQ. It'll be in the next version of the DB. If I can easily get a diff, I'll post that. If not, you'll have to wait for the next revision of the DB.

[cavedude]

I attached a diff of the fixed Moors loot. You'll either need your DB to be named PEQ, or do a search/replace for `peq` and replace it with your `database`

[azog]

Thanks cavedude, yes my db is peq, but I don't have permission to download the zip file.

[cavedude]

I uploaded it to PEQ, it'll also be in the next revision of the DB: http://www.peqtgc.com/moors_loot.zip

[azog]

Thanks cavedude