[SQL] Adds "Crafty's Pole" Fishing Pole (for 3.3.5a)

Custom Code and Mods Custom Code and Requests
1

This is for the 3.3.5a database of TC.

Did a little digging through the SQLs and found that the spell “Fishing skill increased by 1000” ( “.lookup spell fishing” command returned: 59188 [Fishing Skill +1000, rank 1 enUS] passive) was actually in the database. So I decided to re/create Crafty’s Pole since it’s item entry number (43651) wasn’t being used.

select * from item_template where entry=43651; returned no result, so it wasn’t being used.

I simply copied the Mastercraft Kalu’ak Fishing Pole (entry 44050) and modified it to be Crafty’s Pole. It still has “Allows underwater breathing” spell from the Kalu’ak Pole, but that’s a bonus if you ask me.

– Crafty’s Pole item addition
SET @entry := 43651;
SET @spell := 59188;
DELETE FROM item_template WHERE entry=@entry;
INSERT INTO item_template (entry, class, subclass, SoundOverrideSubclass, name, displayid, Quality, Flags, FlagsExtra, BuyCount, BuyPrice, SellPrice, InventoryType, AllowableClass, AllowableRace, ItemLevel, RequiredLevel, RequiredSkill, RequiredSkillRank, requiredspell, requiredhonorrank, RequiredCityRank, RequiredReputationFaction, RequiredReputationRank, maxcount, stackable, ContainerSlots, StatsCount, stat_type1, stat_value1, stat_type2, stat_value2, stat_type3, stat_value3, stat_type4, stat_value4, stat_type5, stat_value5, stat_type6, stat_value6, stat_type7, stat_value7, stat_type8, stat_value8, stat_type9, stat_value9, stat_type10, stat_value10, ScalingStatDistribution, ScalingStatValue, dmg_min1, dmg_max1, dmg_type1, dmg_min2, dmg_max2, dmg_type2, armor, holy_res, fire_res, nature_res, frost_res, shadow_res, arcane_res, delay, ammo_type, RangedModRange, spellid_1, spelltrigger_1, spellcharges_1, spellppmRate_1, spellcooldown_1, spellcategory_1, spellcategorycooldown_1, spellid_2, spelltrigger_2, spellcharges_2, spellppmRate_2, spellcooldown_2, spellcategory_2, spellcategorycooldown_2, spellid_3, spelltrigger_3, spellcharges_3, spellppmRate_3, spellcooldown_3, spellcategory_3, spellcategorycooldown_3, spellid_4, spelltrigger_4, spellcharges_4, spellppmRate_4, spellcooldown_4, spellcategory_4, spellcategorycooldown_4, spellid_5, spelltrigger_5, spellcharges_5, spellppmRate_5, spellcooldown_5, spellcategory_5, spellcategorycooldown_5, bonding, description, PageText, LanguageID, PageMaterial, startquest, lockid, Material, sheath, RandomProperty, RandomSuffix, block, itemset, MaxDurability, area, Map, BagFamily, TotemCategory, socketColor_1, socketContent_1, socketColor_2, socketContent_2, socketColor_3, socketContent_3, socketBonus, GemProperties, RequiredDisenchantSkill, ArmorDamageModifier, duration, ItemLimitCategory, HolidayId, ScriptName, DisenchantID, FoodType, minMoneyLoot, maxMoneyLoot, flagsCustom, WDBVerified) VALUES
(@entry, 2, 20, -1, ‘Crafty’s Pole’, 20619, 5, 32768, 0, 1, 1280448, 374538, 17, -1, -1, 200, 0, 356, 1, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 722, 1085, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1500, 0, 3, @spell, 1, 0, 0, -1, 0, -1, 11789, 1, 0, 0, -1, 0, -1, 0, 0, 0, 0, -1, 0, -1, 0, 0, 0, 0, -1, 0, -1, 0, 0, 0, 0, -1, 0, -1, 1, ‘Just holding this fishing pole makes you shiver with excitement. This is possibly the most amazing thing you’ve ever seen.’, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 75, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, 0, 0, 0, 0, ‘’, 0, 0, 0, 0, 0, 12340);

Picture to show it ingame…

[ATTACH]997._xfImport[/ATTACH]

I figure you can use it for a GM Item or a GM Event Reward kinda thing. Just thought I would share this.

2

That’s funny, I have Crafty’s Pole on 10 characters (using the 4.3.4 branch and appropriate DB) without altering item_template at all. Crafty’s Pole is defined via the item-sparse.db2 file, making the item_template entry an override that merely redefines it. Or, was this specifically for the master (3.3.5a) branch?

3

For 3.3.5a branch, Edited post to reflect that. Didn’t know 4.3.4 branch could do that.

4

While wowhead suggests the pole was added during Cata, I have seen the “Added in patch” give an incorrect version. If you like, I could extract the real definition from the db2 and post it, unless you want to just extract that for yourself. I’ve been tempted to do a similar process for Frostmourne (item 36942) to update for level 85.

5

If you want to. Will it work on 3.3.5a? I’m still new to messing around with TC. I’d love to have Frostmourne in 3.3.5a for an insane GM Event I have an idea for.

6

Frostmourne is actually something that was added in WotLK, never put in a loot table for players, and left in the data for Cata. It’s already an item level 200, requires level 80 to use 2H sword. While it seems that you should already have the information, here’s what I have:

36942, 6, 0, 8192, 0.9587, 1, 1, 2937322, 587464, 17, -1, -1, 200, 80, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4000, 0, 43827, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 1, Frostmourne, , 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.4, 0, 0

The format of that data is located in https://github.com/TrinityCore/TrinityCore/blob/4.3.4/src/server/game/DataStores/DB2Structure.h (ItemSparseEntry, obviously).

Oh, and silly me, here’s the Crafty’s Pole data, too:

43651, 5, 0, 6299648, 1.0187, 1, 1, 1872692, 374538, 17, -1, -1, 200, 0, 356, 1, 0, 0, 0, 0, 0, 1, 1, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3000, 1077936128, 59188, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 1, Crafty’s Pole, , 0, 0, 898111, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 32768, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.4, 0, 0

Same format as the Frostmourne data.

7

I noticed those two are for 4.3.4 but I’m using 3.3.5a. I compared the two src/server/game/DataStores/DBStructure (and DB2Structure) and 3.3.5a doesn’t have the ItemSparseEntry section.

So I’m going to have to play around with my SQLs and see if I can get the Frostmourne in there somehow.

If you could use HeidiSQL, find the Frostmourne item entry line and…

  • Left-click on it

  • Then Right-click and select “Export grid rows…”

  • Set “Output target” to “Copy to clipboard”

  • Set “Output format” to “SQL INSERTs”

  • Set “Row selection” to “Selection (1 rows)”

  • Hit OK

  • Paste that in a Code (SQL) reply here

That way I can compare what your SQL looks like and what mine has and I can go from there.

I’d appreciate that, thanks. /emoticons/default_biggrin.png

8

The database does not have the contents of item-sparse.db2 added to it. The item_template table is used only as an addition (or update) to the data files extracted from the MPQs. You have the structures for both item-sparse.db2 and item_template. Porting it to 3.3.5a (via item_template) should be easily accomplished. I know you can do what needs to be done.

9

Thing is, I’m still new to TC and I have no idea where to start lol. I’m a Gamer not a Coder. SQLs were easy to figure out. I’ll go searchin through everything until I find something that looks like what I need. lol

10

And yet, you managed to copy one item to another and change it to almost perfectly match the real stats of a newer item that wasn’t supposed to be available to you. I think you aren’t giving yourself enough credit. The DB2Structure.h shows you what order the provided data is in. You know how to export the SQL data, so I assume you can easily see the structure of the item_template table. It isn’t a direct copy and paste, but I’m sure you can compare 2 lists and copy appropriate information to the correct column in an SQL file.

11

Like I said, SQLs are actually pretty easy after you’ve played around with em long enough.

For now (for testing purposes anyway) I’m going to take the lazy way out on the Frostmourne and simply Copy the Shadowmourne’s line and see if I can get it to what I think the Frostmourne should be, double the power of the Shadowmourne.

Thing is, I need to find the correct DisplayID to use so it actually looks like the Frostmourne the Lich King is holding.

From what I gather at this point, I think I’m going to have to pull the 4.3.4 source and compile that and check the item table in SQL. Then compare the 335 and 434 tables and copy over the proper info, that is if the DisplayID for Frostmourne is in the 335 DB.

I’ll mess around with that later, I’m still testing 335 stuff to make sure it works for my players lol.

12

Silly me, I copied the wrong Frostmourne version. You actually want to use Frostmourne. You should already have the other version available, but this is am improved version that was added with patch 4.0.3. Also, I failed to realized that I needed to grab the entry from item.db2 as well as item-sparse.db2, so here they are for the Cata version:

ItemEntry: 33475, 2, 8, 8, 0, 46609, 17, 1

ItemSparseEntry: 33475, 6, 0, 8192, 1.0174, 1, 1, 4531696, 906339, 17, -1, -1, 296, 80, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 4, 5, 7, -1, -1, -1, -1, -1, -1, -1, 197, 150, 263, 0, 0, 0, 0, 0, 0, 0, 35, 20, 53, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4000, 0, 43827, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, 1, Frostmourne, , 0, 0, 627397, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.4, 0, 0

While I’m at it, here’s the rest of the Crafty’s Pole data:

ItemEntry: 43651, 2, 20, -1, 1, 20619, 17, 1

You already have the ItemSparseEntry data, so no point in posting it again.

The format of ItemEntry and ItemSparseEntry are both covered in the DB2Structure.h file I linked for you. From what I see, the item_template table is the combination of both structures. All you need is to copy the fields from the data that I’ve posted into the correct order for your SQL. Don’t doubt yourself. You’ve already done something that proves you can do this.

13

Okay, quick question on the Frostmourne.

Going by what you posted and the DB2Structure.h page you linked, I’m trying to follow what goes where…

Name is obviously ‘Frostmourne’

In the ‘ItemSparseEntry’ you have posted there is

Frostmourne, , 0, 0, 627397

Frostmourne is obviously the ‘Name’

Counting the spaces over I hit 627397 four places over. If I follow the DB2S.h page right, that makes that ‘Description’

In my 335a table, ‘description’ is what adds the Quotes at the bottom of the item, look at the screenshot of my Crafty’s Pole for an example of that.

Now either that ‘627397’ is a description (quote) in your Cata version in some kind of “string_table”. OR I miscounted and it is ‘PageText’ since when I look at my item_template some items have numbers in the PageText field.

So for now, I’m going to leave that out and test what I’ve gotten thus far.

EDIT: Okay! I got it! Going to make a new topic for my 335a Frostmourne.

14

Yeah, I had just noticed the structure as being a little off from the actual file, too (yeah, I decided to play with it). I had, at one point, located another Web page that might be a little more accurate. Obviously, a 6 digit number isn’t a very good “description”, is it. I’m going to go look for that page, again, and see if I can’t make some sort of db2->sql converter. Since I do most of my database editing on a Mac, I’m likely to write my conversion utility in something like perl. I’d consider writing it in C++, but that seems like a bloated way to make such a simple utility. Plus, I’m still more of a C coder than a C++ coder.

Notice that I say “coder” not “scripter”. I personally find it just plain wrong to call C++ code that is compiled a script. Yeah, I’m old school. To me, it’s only a script if it’s interpreted as it is executed. In fact, I think my .pl files are considered to be perl scripts because they haven’t been compiled into .pm files.

15

I think this is closer to the raw file format for the item-sparse.db2 file. If I understood properly, though, most of the actual string data in that file may only be getting referenced to a table of strings at the end of the file. Luckily, that string data should be easily added by visiting places like wowhead.