Short guide to managing loot templates

Post Reply
User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Short guide to managing loot templates

#1 » Post by Gnurg » 15 Oct 2016 19:06

Seeing as there is interest to update loot tables, I thought I could try to give a quick introduction on how to manage them, so that you wouldn't be dependent on developers. It's not complicated at all, it's just a lot of repetitive work, so once you've seen a few examples, you should be able to manage on your own.

https://trinitycore.atlassian.net/wiki/ ... t_template - has been my source for knowledge, so I would recommend breezing through it, but I will try cover everything I found important in this post. We do as well have developers who might have time to answer a question now and then.

What tables are most relevant for you?
  • creature_loot_template: The items a NPC drops.
  • gameobject_loot_template: The items a chest contains.
  • reference_loot_template: Will be covered further down.
The attributes found in these tables. (They're basically the same for each of the tables above)
  • Entry: Identification of the creature/game object. 448 for Hogger (http://www.wowhead.com/npc=448[/hogger)
  • Item: Identification of the item. 117 would be Tough Jerky (http://www.wowhead.com/item=117/tough-jerky)
  • Reference: Covered further down, can have value 0 for now.
  • Chance: The chance for the item to drop.
  • QuestRequired: 1=True, 0 = False -- Whenever the item can drop without quest or not. Always false if it's not a quest item.
  • LootMode: Leave this at 1 at all times.
  • GroupId: Covered further down, can have value 0 for now.
  • MinCount: How many of the item it should minimum give. If Hogger should drop at least 3 Tough Jerky, this would be set to 3.
  • MaxCount: Same as above, just maximum (obviously should be the same or larger than MinCount)
  • Comment: Write if there is anything important about this row or the name of the item (or leave blank: "").
The three commands you will be using:

Code: Select all

UPDATE <table> SET <attributes to be set> WHERE <conditions to specify what row> ;

Code: Select all

DELETE FROM <table> WHERE <conditions to specify what row>;
Often you want to insert multiple rows at once, so just add a comma (,) for every row and make sure the last one stops with a semicolon (;)

Code: Select all

INSERT INTO <table> (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(<entry>, <item>, <reference>, <chance>, <questrequired>, 1, <groupid>, <mincount>, <maxcount>, "Here you can write a comment"),
(<entry>, <item>, <reference>, <chance>, <questrequired>, 1, <groupid>, <mincount>, <maxcount>, "Typically write the name of the item here");
Important!
In SQL you have something called Primary Key, which should ALWAYS be unique. For *_loot_tables, the primary key is made up of entry and item field. Trying to insert a primary key (entry, item) that already exist will cause an error and result in the SQL to fail. It's therefore, important that whenever you do an INSERT INTO operation, that you DELETE FROM the row you insert.

Writing comments in your SQL:
Sometimes it's nice to just explain what you are doing in the statement. To write a comment that will be ignored by the machine, simply start the line with --. Very often it can say what you're trying to fix about the row.

Code: Select all

-- This is a comment!
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Example 1: Hogger currently drops Tough Jerky, but he should always drop 3 Tough Jerky and the chance should be 50 %.

Code: Select all

-- Hogger should always drop 3 Tough Jerky and the chance should be 50 %
UPDATE `creature_loot_template` SET `Chance`=50, `MinCount`=3, `MaxCount`=3 WHERE `Entry`=448 AND `Item`=117;

Example 2: Hogger drops Shadowmourne! He clearly shouldn't.

Code: Select all

--  Hogger shouldn't drop Shadowmourne! 
DELETE FROM `creature_loot_template` WHERE `Entry`=448 AND `Item`=49623;

Example 3: Hogger is supposed to drop the Big Love Rocket, but he doesn't. Drop rate should be 10 % and he should only drop 1.

Code: Select all

-- First delete the Big Love Rocket, just incase it already is there.
DELETE FROM `creature_loot_template` WHERE `Entry`=448 AND `Item`=50280;

-- Hogger should have 10 % chance to drop Big Love Rocket.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(448, 50250, 0, 10, 0, 1, 0, 1, 1, "Big Lover Rocket");
----------------------------------------------------------------------------------------------------------------------------------------------------------------

When should GroupId have a different value than 0?
Very often a boss will drop only one item from a group of items. GroupId is used to make this pool of items by having the same value for GroupId for all the items in the group. Should item A and item B have GroupId set to 1, then only one of these weapons will drop. If GroupId is set to 0, that means that there isn't any group of items, aka independent items. If you want to create multiple Groups, you just use different values for GroudIp. i.e. Item A and item B with GroupId set to 1, Item C and D with GroupId 2.

There are two ways of setting up the chance for these groups: explicitly-chanced or equal-chanced.
In equal chanced it's 100 % chance that one of the items drop, where each have the same chance. To make it equal chanced, you simply set the chance to 0 for each rowl.
In explicitly-chanced one or no item from the group will, depending on the combinded chance of all the rows in the group. To make it explicitly-chanced, you simply set the chance to what the drop rate for the given is. If the combinded chance goes beyond 100 %, one item will always drop.

*Keep in mind if reference is used, then GroupId will serve a different purpose, we will see that further down.

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

Example 4: Hogger should have 100 % chance to drop either 3 Tough Jerky or 1 Refreshing Spring Water, but not both at he same time. At the moment he can drop both item at the same time.

Code: Select all

-- Remove old Refreshing Spring Water row
DELETE FROM `creature_loot_template` WHERE `Entry`=448 AND `Item`=159;
-- Remove old Tough Jerky row
DELETE FROM `creature_loot_template` WHERE `Entry`=448 AND `Item`=117;

-- Add loot pool for Tough Jerky and Refreshing Spring Water.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(448, 159, 0, 0, 0, 1, 1, 1, 1, "Refreshing Spring Water"),
(448, 117, 0, 0, 0, 1, 1, 3, 3, "Tough Jerkey");
Example 5: Hogger should have a gem collection. He will drop 1 or 0 gems, where King's Amber have 20 % chance and Eye of Zul have 15 % chance, combinded 35 % chance.

Code: Select all

-- Delete just to be safe.
DELETE FROM `creature_loot_template` WHERE `Entry`=448 AND `Item` IN (36922, 36934);

-- Add loot pool for Hogger's gems.
-- PS: Notice I use GroupId 2, as I used GroupId 1 for the example above, so GroupId 1 is already taken.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(448, 36922, 0, 20, 0, 1, 2, 1, 1, "King's Amber"),
(448, 36934, 0, 15, 0, 1, 2, 1, 1, "Eye of Zul");
----------------------------------------------------------------------------------------------------------------------------------------------------------------

When should Reference have a different value than 0?
I would say there are two times when you should use reference loot. When multiple bosses can drop the same group of items (for instance, all bosses in Molten Core should have a 10 % chance to drop a recipe from a group of recipes) and when a boss should drop two or more items from a group of items.

First you need to make reference_loot_template rows for the reference entry, where the entry is an unused value (unless you're a developer you can't know this value!). Once you've made the rows, you only need to reference it in the *_loot_template you needed the reference in.

As you need a developer to find a free reference for you, it would be nice if you used a variable for reference value. SET @ENTRY := 4642; Writing @ENTRY where you would normally put a number would result in writing 4642 in that place. It's value is changed everytime you write SET @ENTRY := <value>;

I did mention that GroupId serves a different purpose when it comes to references and that's because the group id when you make the reference coresponds to the GroupId in the reference_loot_template.. As of now, using multiple GroupIds in a reference_loot_template makes the armory look a bit weird. I would therefore recommend you keep GroupId to 1 whenever working with reference.

For chance for the items inside a reference_loot_template row, it works the same as with group. Either explicitly-chanced or equal-chanced.

Code: Select all

-- Create a variable to store the reference for the developer to set later on.
SET @REF := <free_reference_for_developer_to_find>;

-- Creating the reference rows. (This is done the same way as you did with Groups above, just set GroupId to 1)
INSERT INTO `reference_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(@ENTRY, <item>, 0, <chance>, <questrequired>, 1, 1, <mincount>, <maxcount>, "Item name"),
(@ENTRY, <item>, 0, <chance>, <questrequired>, 1, 1, <mincount>, <maxcount>, "Item name");

-- Create a reference to the reference loot, so we can get <mincount> to <maxcount> items from that reference.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, MaxCount, `Comment`) VALUES
(<creature_entry>, @REF, @REF, 100, <questrequired>, 1, 1, <mincount>, <maxcount>, "Describing comment");
In the comment field here, it would be clever to describe all the items in the reference with a short text, as the items will often be of the same kind. In Icecrown Citadel you would typically have one reference for tokens (as multiple bosses drop them), so a good comment would be "Tier Tokens" when referencing it. In Molten Core each boss got a chance to drop a set of recipes (gathered in a reference), so a good comment would be "Recipes". You get the idea.


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


Example 6: Hogger should have 100 % chance to drop 1-2 of these items I only provided ID for. Same chance for all items.

Code: Select all

SET @ENTRY := <free_reference_for_developer_to_find>;

-- Deletion first.
DELETE FROM `reference_loot_template` WHERE `Entry`= @ENTRY;
DELETE FROM `creature_loot_template` WHERE `Entry`= 448 AND `Item` = @ENTRY;

-- Creating the reference rows for the items.
INSERT INTO `reference_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(@ENTRY, 49623, 0, 0, 0, 1, 1, 1, 1, "Shadowmourne"),
(@ENTRY, 50250, 0, 0, 0, 1, 1, 1, 1, "Big Love Rocket"),
(@ENTRY, 117, 0, 0, 0, 1, 1, 1, 1, "Tough Jerky");

-- Create a reference to the reference loot, so we can get items from that reference.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(448, @ENTRY, @ENTRY, 100, 0, 1, 1, 1, 2, "");
Example 7: Variann Wrynn should as well have 100 % chance to drop the same items as in example 6, but only 1 of the items.

Code: Select all


-- Deletion first
DELETE FROM `creature_loot_template` WHERE `Entry`= 448 AND `Item` = @ENTRY;

-- We already made the reference_loot_template rows, so we only have to reference it. Hopefully @ENTRY has the same value as it had in example 6.
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`) VALUES
(11699, @ENTRY, @ENTRY, 100, 0, 1, 1, 1, 2, "");[
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Working without the database?
One thing that can be complex when working with real examples is that you often can't know if some loot is added to the boss through reference loot or through groups. A page I find really useful is this one: http://shinworld.altervista.org/Keira2/#/. It isn't completely like our database, as it doesn't have the custom fixes we have on TrueWoW, but it's still quite the same as you will notice if you compare it up against our armory.


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

A real example: Fixing Lucifron according to the spreadsheet: https://docs.google.com/spreadsheets/d/ ... edit#gid=0

Step 1. How would you implement it? Groups, references or independent items?
Clearly, there have to be at least two groups and there is no need for references (as just 1 item from each group, no other bosses drop the exactly same group). Groups should be sufficient. Each group should have 100 % chance of giving an item, where one group should be explicitly-chanced and the other equal-chanced.

Step 2: Is it currently implemented in the way we want to implement it? If it is, we only need to use UPDATE, if not we have to DELETE and INSERT after.
Looking up on http://shinworld.altervista.org/Keira2/#/creature/12118 we quickly see by comparing to our armory that one group is implemented using Groups, while the other one is implemented using Reference. The items in the two groups don't match up with how we want to implement it, so we have to delete them before we insert them again in a correct manner. We must make sure we DO NOT delete the wrong items and references. In this example, it's easy to deduct that it is reference 34012 that should be deleted (add the chance for all the items that are listed on our armory that is not visible in Keira, each have 6.25 % chance to drop, together 16 * 6.25 = 100 %!), but if you're unsure which one it is, you should leave the case be.

Step 3: As we're using Groups, we need to decide on what GroupId we will be using. Looking again at http://shinworld.altervista.org/Keira2/#/creature/12118, we see that no GroupIds are taken (besides the one we will be deleting). As we need 2 Groups, we can use GroupId 1 and 2 for our Groups.

Step 4: SQL!

Code: Select all


-- Lucifron (NPC ID 12118)
SET @NPC_ENTRY:= 12118;

--Delete the old rows in the creature_template_loot.
-- `Item` IN (...) basically goes through all the items inside the parathese, typically used if you have to make the same change/deletion for multiple rows.
DELETE FROM `creature_loot_template` WHERE `Entry`=@NPC_ENTRY AND `ITEM` IN (16863, 16805, 18879, 18870, 18875, 18872, 19145, 19146, 18861, 19147, 18878, 17077, 16800, 16829, 16837, 16859, 17109);

-- Delete the reference to the reference loot.
DELETE FROM `creature_loot_template` WHERE `Entry`=@NPC_ENTRY AND `Item`=34012;

-- Freeing the reference loot template.
-- bit risky if you don't know if more is stored on it, but I am quite confident these wrongly placed items are all
DELETE FROM `reference_loot_template` WHERE `Entry`=34012;

-- Group 1 (explicity-chanced.)
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(@NPC_ENTRY, 16863, 0, 33.3, 0, 1, 1, 1, 1, "Gauntlets of Might"),
(@NPC_ENTRY, 16805, 0, 33.3, 0, 1, 1, 1, 1, "Felheart Gloves"),
(@NPC_ENTRY, 18879, 0, 3.34, 0, 1, 1, 1, 1, "Heavy Dark Iron Ring"),
(@NPC_ENTRY, 18870, 0, 3.34, 0, 1, 1, 1, 1, "Helm of the Lifegiver"),
(@NPC_ENTRY, 18875, 0, 3.34, 0, 1, 1, 1, 1, "Salamander Scale Pants"),
(@NPC_ENTRY, 18872, 0, 3.34, 0, 1, 1, 1, 1, "Manastorm Leggings"),
(@NPC_ENTRY, 19145, 0, 3.34, 0, 1, 1, 1, 1, "Robe of Volatile Power"),
(@NPC_ENTRY, 19146, 0, 3.34, 0, 1, 1, 1, 1, "Wristguards of Stability"),
(@NPC_ENTRY, 18861, 0, 3.34, 0, 1, 1, 1, 1, "Flamewaker Legplates"),
(@NPC_ENTRY, 19147, 0, 3.34, 0, 1, 1, 1, 1, "Ring of Spell Power"),
(@NPC_ENTRY, 18878, 0, 3.34, 0, 1, 1, 1, 1, "Sorcerous Dagger"),
(@NPC_ENTRY, 17077, 0, 3.34, 0, 1, 1, 1, 1, "Crimson Shocker");

-- Group 2 (equal-chanced)
INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(@NPC_ENTRY, 16800, 0, 0, 0, 1, 2, 1, 1, "Arcanist Boots"),
(@NPC_ENTRY, 16829, 0, 0, 0, 1, 2, 1, 1, "Cenarion Boots"),
(@NPC_ENTRY, 16837, 0, 0, 0, 1, 2, 1, 1, "Earthfury Boots"),
(@NPC_ENTRY, 16859, 0, 0, 0, 1, 2, 1, 1, "Lawbringer Boots"),
(@NPC_ENTRY, 17109, 0, 0, 0, 1, 2, 1, 1, "Choker of Enlightenment");
Last edited by Gnurg on 11 Nov 2016 16:17, edited 11 times in total.
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Re: Short guide to managing loot templates

#2 » Post by Gnurg » 15 Oct 2016 21:12

I think I am finished with now. Hopefully it will be of use to someone. :-)
If something is unclear or you need assisstance, feel free to ask and I will do my best to respond.

Can be moved to guides, as even though I made it for some testers, it could be of use to other players as well.
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

User avatar
Nymunne
Former Staff
Posts: 1951
Joined: 28 Jun 2015 23:10

Re: Short guide to managing loot templates

#3 » Post by Nymunne » 15 Oct 2016 21:35

Moved upon request.

User avatar
Errorista
Issue Tracker Leader
Posts: 1898
Joined: 02 Sep 2012 14:31

Re: Short guide to managing loot templates

#4 » Post by Errorista » 15 Oct 2016 21:39

what about skinning loot tables or fishing loot tables?

User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Re: Short guide to managing loot templates

#5 » Post by Gnurg » 15 Oct 2016 21:44

Errorista wrote:what about skinning loot tables or fishing loot tables?
Never tried them, but I presume it's pretty much the same. Guide was made for the ones who want to rework the vanilla loot tables, so I won't move into those tables.
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

User avatar
Errorista
Issue Tracker Leader
Posts: 1898
Joined: 02 Sep 2012 14:31

Re: Short guide to managing loot templates

#6 » Post by Errorista » 15 Oct 2016 21:52

ah I see, np, there is just some wrong skinning loot table reported on BT so I wanted to give it a try

User avatar
Nyeriah

Re: Short guide to managing loot templates

#7 » Post by Nyeriah » 21 Oct 2016 19:54

Hey, Gnurg, the guide is great, thanks a lot for the effort you put into it :)

Any loot table behaves the same way, so this is the same for the rest of them (perhaps just not for prospecting).

I have a suggestion though, the loot tables have a "comment" field now, so instead of commenting the name in the sql you could use that field to leave the comment in the database. It's not a big deal, it just makes it a little bit more convenient when looking into it from the database

User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Re: Short guide to managing loot templates

#8 » Post by Gnurg » 21 Oct 2016 20:10

Nyeriah wrote:Hey, Gnurg, the guide is great, thanks a lot for the effort you put into it :)

Any loot table behaves the same way, so this is the same for the rest of them (perhaps just not for prospecting).

I have a suggestion though, the loot tables have a "comment" field now, so instead of commenting the name in the sql you could use that field to leave the comment in the database. It's not a big deal, it just makes it a little bit more convenient when looking into it from the database
What would a useful comment in the comment field be? Name of the item or more special case comments?
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

User avatar
Nyeriah

Re: Short guide to managing loot templates

#9 » Post by Nyeriah » 21 Oct 2016 20:11

Just the name of the item is the standard, but you can use it for whatever you feel useful

User avatar
Polkic
Former Staff
Posts: 1059
Joined: 21 Dec 2012 00:02
Location: Slovenia

Re: Short guide to managing loot templates

#10 » Post by Polkic » 22 Oct 2016 06:08

Loot tables often go with conditions table. That could/should be noted in the comment.
Image

User avatar
Eronox
MVP
Posts: 2331
Joined: 24 Apr 2016 11:03
Location: in ICC & Healing

Re: Short guide to managing loot templates

#11 » Post by Eronox » 22 Oct 2016 10:52

Polkic wrote:Loot tables often go with conditions table. That could/should be noted in the comment.

Would be kind of repeating. Just ss Nyeriah said, the name of the actual item is mostly written in the comment section so the user doesnt have to search in two tables just to get the name
.

There's things that never will be right I know, and things need changin' everywhere you go.
But 'til we start to make a move to make a few things right,
You'll never see me wear a suit of white.

- J.R Cash


User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Re: Short guide to managing loot templates

#12 » Post by Gnurg » 22 Oct 2016 11:55

Polkic wrote:Loot tables often go with conditions table. That could/should be noted in the comment.
Comment: Write if there is anything important about this row or the name of the item (or leave blank: "").

Important would refer to conditon among others.
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

User avatar
Longi
Posts: 768
Joined: 07 Jan 2014 11:14
Location: Cenarion Hold

Re: Short guide to managing loot templates

#13 » Post by Longi » 25 Oct 2016 23:34

Well done! Nice quide :-)


Image



User avatar
Gnurg
Posts: 2420
Joined: 28 Jan 2013 19:38
Location: Oslo, Norway

Re: Short guide to managing loot templates

#14 » Post by Gnurg » 11 Nov 2016 16:17

Updated the guide to highlight that it's important to delete before you insert.
HAI
CAN HAZ STDIO?
VISIBLE "HAI WORLD, IZ GNURF!"
KTHXBYE

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest