[3.3.5] DB/Loot: Blacktip Shark

[Exodius]

Blacktip Shark: www.wowhead.com/item=50289

Problem: Does not exist in Fishing Loot templates.

Explanation:
This item was caught during the Kalu'ak Fishing Derby from any fishing pool that would normally contain Pygmy Suckerfish.
Source
So... What I did is, I executed this query:

SELECT * FROM `reference_loot_template` WHERE `item`=40199

And result were 4 reference entries: 11019, 11020, 11021 and 11022.
Based on that, here comes the next query that solves missing Shark.

WoWhead drop chances suggest that percentage ranges from 0.01 to 0.1

-- Insert Blacktip Sharks into Fishing Loot
SET @PYGMY:= 11019; -- Base fishing pool where Pygmy Suckerfish can be fished (11019 through 11022)
SET @SHARK:= 50289; -- Blacktip Shark
SET @EVENT:= 63; -- Kalu'ak Fishing Derby
-- Insert Blacktip Sharks into same Loot pools with Pygmy Suckerfish
DELETE FROM `reference_loot_template` WHERE `entry` IN (@PYGMY,@PYGMY+1,@PYGMY+2,@PYGMY+3) and `item`=@SHARK;
INSERT INTO `reference_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`lootmode`,`groupid`,`mincountOrRef`,`maxcount`) VALUES
(@PYGMY,@SHARK,0.1,1,0,1,1),
(@PYGMY+1,@SHARK,0.1,1,0,1,1),
(@PYGMY+2,@SHARK,0.1,1,0,1,1),
(@PYGMY+3,@SHARK,0.1,1,0,1,1);
-- Blacktip Sharks should be lootable from Fishing Pools only during the event
-- Thanks to w1sht0l1v3
DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId`=4 AND `SourceGroup` IN (@PYGMY,@PYGMY+1,@PYGMY+2,@PYGMY+3) AND `SourceEntry`=@SHARK;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(4,@PYGMY,@SHARK,0,0,12,0,@EVENT,0,0,0,0,'','Loot Item Blacktip Shark only when event Kalu''ak Fishing Derby Fishing Pools is active'),
(4,@PYGMY+1,@SHARK,0,0,12,0,@EVENT,0,0,0,0,'','Loot Item Blacktip Shark only when event Kalu''ak Fishing Derby Fishing Pools is active'),
(4,@PYGMY+2,@SHARK,0,0,12,0,@EVENT,0,0,0,0,'','Loot Item Blacktip Shark only when event Kalu''ak Fishing Derby Fishing Pools is active'),
(4,@PYGMY+3,@SHARK,0,0,12,0,@EVENT,0,0,0,0,'','Loot Item Blacktip Shark only when event Kalu''ak Fishing Derby Fishing Pools is active');

EDIT1: Little correction to Chances. Changed from negative to positive values.
This one needs conditions that Sharks can be fished out only when Kalu'ak Fishing Derby is in effect and Elder Clearwater is spawned.
EDIT2: Added conditions. Thanks to @w1sht0l1v3

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[w1sht0l1v3]

Here,use this:

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId`=4 AND `SourceGroup`=@GO_LOOT_TEMPLATE AND `SourceEntry`=@ITEM_SHARK;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(4,@GO_LOOT_TEMPLATE,@ITEM_SHARK,0,0,12,0,@EVENT,0,0,0,0,'','Loot Item Blacktip Shark only when event Kalu''ak Fishing Derby Fishing Pools is active');

Just define the proper things.

[untaught]

I use this one but if shark loot is generated not during the event the result is empty loot, because this disables the visibility of the loot not the chance it will be generated.

UPDATE `game_event` SET `start_time`='2012-01-07 14:00:00' WHERE `evententry`=63;

DELETE FROM `gameobject_loot_template` WHERE `item`=50289;
INSERT INTO `gameobject_loot_template` (`entry`, `item`, `ChanceOrQuestChance`, `lootmode`, `groupid`, `mincountOrRef`, `maxcount`) VALUES
('25673','50289','1','1','1','1','1'),
('25665','50289','1','1','1','1','1'),
('25670','50289','1','1','1','1','1'),
('25668','50289','1','1','1','1','1'),
('25674','50289','1','1','1','1','1'),
('25662','50289','1','1','1','1','1'),
('25663','50289','1','1','1','1','1'),
('25664','50289','1','1','1','1','1'),
('25671','50289','1','1','1','1','1');

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId`=4 AND `SourceGroup` IN (25673,25665,25670,25668,25674,25662,25663,25664,25671) AND `SourceEntry`=50289;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`, `SourceGroup`, `SourceEntry`, `SourceId`, `ElseGroup`, `ConditionTypeOrReference`, `ConditionTarget`, `ConditionValue1`, `ConditionValue2`, `ConditionValue3`, `NegativeCondition`, `ErrorType`, `ErrorTextId`, `ScriptName`, `Comment`) VALUES
('4','25673','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25665','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25670','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25668','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25674','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25662','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25663','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25664','50289','0','0','12','0','63','0','0','0','0','0','',NULL),
('4','25671','50289','0','0','12','0','63','0','0','0','0','0','',NULL);

Also correct the start time of the event.
There is an event with entry 64 Kalu'ak Fishing Derby Fishing Pools which is strange because there are no special fishing pools for the derby event at all.

[w1sht0l1v3]

Indeed,i think the proper way to handle this is to spawn kalu'akfishing pools.Sadly,i think this GO is missing from the DB.This implies finding where these pools spawn all over NR during the event,wich will be a pain.

[Exodius]

Well, it will be pain to find them now. Event was removed as of 5.1.0 patch.
Only if people have sniffs about this (and I really doubt that they have sniffs about all of them).
Another thing is that we (I) don't even know if specific pools for this event existed on retail.

I don't think that there should be separate fishing pools... All info that can be found suggest that "Blacktip Shark's favorite meal is Pygmy Suckerfish and that we should fish in the pools that usually contain the Pygmys."
With that logic, it would be "blizzlike" to just insert the sharks into same pools as pygmys and add conditions that they should be in those pools only during the event.

[untaught]

SELECT * FROM `gameobject_loot_template` WHERE `entry` IN (SELECT `data1` FROM `gameobject_template` WHERE `name` IN ('Borean Man O\'War School','Deep Sea Monsterbelly School','Dragonfin Angelfish School',
'Fangtooth Herring School ','Glacial Salmon School','Glassfin Minnow School','Imperial Manta Ray School','Moonglow Cuttlefish School','Musselback Sculpin School','Nettlefish School')) ORDER BY `entry` ASC;

@Exodius I don't see any reference loot in the results of this query so i think your fix is not fully working :(

entry  item   ChanceOrQuestChance  lootmode  groupid  mincountOrRef  maxcount  
25662  41809  90                   1         1        1              1         
25662  46109  0.01                 1         2        1              1         
25662  44475  0                    1         1        1              1         
25662  37705  0                    1         1        1              1         
25663  46109  0.01                 1         2        1              1         
25663  44703  0.5                  1         1        1              1         
25663  44475  0                    1         1        1              1         
25663  37705  0                    1         1        1              1         
25663  41810  90                   1         1        1              1         
25664  44475  0                    1         1        1              1         
25664  37705  0                    1         1        1              1         
25664  41807  90                   1         1        1              1         
25664  46109  0.01                 1         2        1              1         
25665  44475  0                    1         1        1              1         
25665  37705  0                    1         1        1              1         
25665  41806  90                   1         1        1              1         
25665  46109  0.01                 1         2        1              1         
25668  46109  0.01                 1         2        1              1         
25668  44475  0                    1         1        1              1         
25668  37705  0                    1         1        1              1         
25668  41802  90                   1         1        1              1         
25670  46109  0.01                 1         2        1              1         
25670  41801  90                   1         1        1              1         
25670  37705  0                    1         1        1              1         
25670  44475  0                    1         1        1              1         
25671  46109  0.01                 1         5        1              1         
25671  44475  5                    1         4        1              1         
25671  41800  90                   1         1        1              1         
25671  45328  60                   1         6        1              1         
25671  40199  65                   1         2        1              1         
25671  37705  5                    1         3        1              1         
25673  46109  0.01                 1         2        1              1         
25673  44475  0                    1         1        1              1         
25673  41813  90                   1         1        1              1         
25673  37705  0                    1         1        1              1         
25674  44475  0                    1         1        1              1         
25674  37705  0                    1         1        1              1         
25674  41814  90                   1         1        1              1         
25674  46109  0.01                 1         2        1              1         

[Exodius]

Well, this is really confusing. All other similar Loot Templates (Mining, Skinning, Herbalism, classic loot) go through Referenced loot. How come fishing still goes through Gameobject Loot Template? From what I have learned so far, negative values in mincountOrRef table links loot template to reference_loot_template table.

If you check fishing_loot_template, negative values of mincountOrRef directly link the loot template to Referenced Table and I worked by that rule.

If you execute the query

SELECT * FROM `reference_loot_template` WHERE `entry` IN (11019,11020,11021,11022);

you will see more or less same entries as the ones you linked from Gameobject tables so... Which one is correct?
Gameobject or Reference? I really don't know and I see duplicate entries.

Feel free to correct my query as you see fit if my work isn't working as intended.

[untaught]

Fishing_loot_template is for areas/zones loot and is not related to any fishing pool. Fishing pools are GOs and you should change loot in gameobject_loot_template. Entry for gameobject_loot_template which must be used is data1 from the gameobject_template. See http://collab.kpsn.org/display/tc/Loot+template+tc2 comment for gameobject_loot_template in Relations section.

[Exodius]

I see... In that case your query could be correct one.
However, there is no mention / proof that any type of special pools (gameobjects) should be spawned rather than default one.

As seen on Wiki article:
"This item was caught during the Kalu'ak Fishing Derby from any fishing pool that would normally contain Pygmy Suckerfish."

EDIT: Why are there entries in Reference tables then?

[untaught]

I think they are for reference loot in fishing_loot_template, I'm just guessing right now. Have no time to prove my words at the moment.

[Aokromes]

Ok, anything of this mess is a valid fix?

[untaught]

DB says there must be a special pools for the event so mine is WA in this case.

[LuckerNr1]

here you can see he fishes in normal pools
http://www.youtube.com/watch?v=wzZrRifdICc
loot fix is btw the lesser problem. fixing that only one character per week can finish the quest (same for booty bay) is the bigger problem
edit: loot entrys of the questitem should get own groupid since they drop in addition to the normal loot

[Aokromes]

Yeah we need end game event on quest complete.

[LuckerNr1]

the event doesnt end, after first one has turned in the quest, all following get http://www.wowhead.com/quest=24806/better-luck-next-time

[RedSonja]

Trinity Revision: 69a616e+ Database Version: TDB 335.52
Has there been any activity on this? Does anyone know if one of the fixes above works?

[untaught]

Mine works but I think it's not the correct way to fix it.

[RedSonja]

Thank you!

[Pirricli]

@untaught Could be your fix pushed?

[Aokromes]

Anything here can be pushed?

[ghost]

@untaught's suggestion is probably the best effort so far, making it at least possible to catch sharks at all.
Just need to clean up his insert lines to match current TDB 335.61 column names (changed since 2013).

-- Kalu'ak Fishing Derby, Blacktip Shark loot
SET @Shark := 50289;

DELETE FROM `gameobject_loot_template` WHERE `Item`= @Shark;
INSERT INTO `gameobject_loot_template` (`Entry`,`Item`,`Reference`,`Chance`,`QuestRequired`,`LootMode`,`GroupId`,`MinCount`,`MaxCount`,`Comment`) VALUES
(25662, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Glacial Salmon School - Blacktip Shark'),
(25663, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Fangtooth Herring School - Blacktip Shark'),
(25664, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Dragonfin Angelfish School - Blacktip Shark'),
(25665, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Musselback Sculpin School - Blacktip Shark'),
(25668, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Imperial Manta Ray School - Blacktip Shark'),
(25670, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Moonglow Cuttlefish School - Blacktip Shark'),
(25671, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Deep Sea Monsterbelly School - Blacktip Shark'),
(25673, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Nettlefish School - Blacktip Shark'),
(25674, @Shark, 0, 1, 0, 1, 0, 1, 1, 'Glassfin Minnow School - Blacktip Shark');

-- Blacktip Shark loot Conditions
DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId`= 4 AND `SourceGroup` IN (25662,25663,25664,25665,25668,25670,25671,25673,25674) AND `SourceEntry`= @Shark;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorType`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(4, 25662, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Glacial Salmon School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25663, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Fangtooth Herring School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25664, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Dragonfin Angelfish School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25665, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Musselback Sculpin School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25668, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Imperial Manta Ray School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25670, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Moonglow Cuttlefish School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25671, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Deep Sea Monsterbelly School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25673, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Nettlefish School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active"),
(4, 25674, @Shark, 0,0, 12, 0, 63,0,0,0,0,0,'',"Glassfin Minnow School drops Blacktip Shark only when 'Kalu'ak Fishing Derby' is active");

Also notice that there is no need to include the line

UPDATE `game_event` SET `start_time`='2012-01-07 14:00:00' WHERE `evententry`=63;

because that info is already in TDB 335.61.

---

The way I see it, leaving the Comment columns in DB tables only containing NULL reduces the chance that new users will want to take a closer look and verify that the content is working as intended.