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