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.
- 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: "").
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>;
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");
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");
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");
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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, "");
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");