[SQL] Mangos DB mods - PVE, crafting, and more...

[matt71tx]

I wanted to share these SQL queries that I use on my server. I learned a few basics of SQL and created nearly all of them myself since I could never find anyone who offered these types of queries. My server only hosts a very small group of personal friends and we mainly PVE and craft. These queries focus mainly on that.

Feel free to use and edit these queries if you find any useful. The queries are structured to make broad adjustments on the DB table in question.

I use these queries on the TRM's Cthulhu Ftagn Dual-Database MaNGOS Repack (3.3.5.12340) - 32 & 64-bit repack (which is a great repack) and they work great. They should work fine with other mangos based repacks as well.

Check out the details below on what DB tables are effected.

The download link for all the SQL files is at the bottom of this post.


creature_loot_template
-- NPC increase to drop percentage for percentages between 0 and 1

creature_template
-- Money drops for creatures that do not drop money
-- All NPC's give honor points

disenchant_loot_template
-- Increase disenchant min and max resource drop rates

fishing_loot_template
-- Increase fishing min and max resource drop rates

skinning_loot_template
-- Increase skinning min and max resource drop rates

milling_loot_template
-- Increase milling min and max resource drop rates

gameobject_loot_template
-- Increase drop rates on mineral veins and herbs

item.template
-- Soul Shards stack in groups of 50 up to 500 in inventory
-- Adds 4 slots to all bags that originally started with 28 or less
-- Increase all armor by 10%

npc_vendor
-- Remove NPC Vendor time constraints
-- Remove NPC Vendor limits

player_classlevelstats
-- Increase player base HP and Mana levels by 10% for all levels

player_levelstats
-- Increase all player base attributes +1 for all levels

playercreateinfo_item
-- Added 1 Foror's Crate of Endless Resist Gear Storage to all classes.
-- Added 1 Shortsword and 1 Large Round Shield to the Paladin class.

playercreateinfo_spell
-- Added one-Handed Sword skill ability to Human, Dwarf, & Draenei Paladin class.
-- Added one-handed sword skill ability to all Warlocks.
-- Added Regeneration racial bonus to all Priests and Mages. (Except Troll which have this ability already.)
-- Added Expansive Mind racial bonus to all Priests and Mages. (Except Gnome which have this ability already.)
-- Added Regeneration racial bonus to all Rogues. (Except Troll which have this ability already.)
-- Added Endurance racial bonus to all Warriors. (Except Tauren which have this ability already.)
-- Added Endurance racial bonus to all Hunters. (Except Tauren which have this ability already.)
-- Added Regeneration racial bonus to all Hunters. (Except Troll which have this ability already.)
-- Added Heroic Presence racial bonus to all Hunters. (Except Draenei which have this ability already.)
-- Added Expansive Mind racial bonus to all Warlocks. (Except Gnome which have this skill already.)


Filebeam - Beam up that File Scottie! (http://filebeam.com/4d162762f6ec8f2e1b4bff56db9f483e)

OR

Mangos DB SQL mods - Pastebin.com (http://pastebin.com/zLEK9tTK)

[Deathbringer]

Epic :D
+rep

[matt71tx]

I recently spent some time and made this for myself but perhaps someone else may find my work useful to them.

This SQL query modifies the hunter quests which all hunters have to go through to get their pet. It basically removes the first two pets that you have to tame and jumps right to last one to get your taming skills. An effort still has to be made to get the taming skills but this makes the overall process a little quicker. I also edited the text the quest NPC says to you so that it does not reference the quests that I removed.

I use these queries on the TRM's Cthulhu Ftagn Dual-Database MaNGOS Repack (3.3.5.12340) - 32 & 64-bit repack (which is a great repack) and they work great. They should work fine with other mangos based repacks as well.


(DOWNLOAD REMOVED - NEEDS TO BE UPDATED FOR CURRENT VERSION OF MANGOS)

[Dimman]

Cool, will test this later on :3 +r

[Deathbringer]

Works Perfect :D
Thanks

[matt71tx]

Recently made a query to modify pets...

-- Increase pet base HP, mana, and armor levels by 10% for all levels

http://filebeam.com/c66626f261628be8c96fb18d219cda6a (http://filebeam.com/c66626f261628be8c96fb18d219cda6a)

OR

Pet_levelstats MOD - Pastebin.com (http://pastebin.com/qKRmm6SK)

[Stormy]

Wow, these look great, going to give them a try. Thank You +Rep

[matt71tx]

Removed this query since it was replaced with a newer one.

[Dimman]

You should use pastebin or something instead :3
Some anti viruses dosent like beam & its easier to copy from the web and save it as an SQL :)

[Twiztid7652]

Recently made a query to modify pets...

-- Increase pet base HP, mana, and armor levels by 10% for all levels

http://filebeam.com/a941d78ddbe1951b096284605f252e56 (http://filebeam.com/a941d78ddbe1951b096284605f252e56)

OR

Mangos pet DB SQL mods - Pastebin.com (http://pastebin.com/TByzPJWv)

ya i tried this it worked great.. but all pets HP and everything DROPPED instead of gaining.. dunno what happened there..

[matt71tx]

ya i tried this it worked great.. but all pets HP and everything DROPPED instead of gaining.. dunno what happened there..

You are correct. There is a mistake in the calculation. I have fixed this and replaced the old query with an updated one. Sorry to anyone who attempted to use it.
(http://filebeam.com/fb311e213631273bd82300d5412fb35e)

[matt71tx]

After being disappointed that it was not possible to complete the entire death knight starting quests (due to mangos or DB limitations) I set out to restructure the entire line of quests so that it would be possible for any player (not just one using GM commands) to get to the end and have all the skills/equipment that is available.

To do this I had to remove some quests that would not work and move the rewards from these removed quests to others that were still functional. I also reduced some of the requirements to complete a few of the quests. Some quests will be complete when they are accepted so they can be turned in right away. Some were not modified at all. On average is takes less than an hour to complete the entire series of quests to the end.

I use the query for these changes with TRM's Cthulhu Ftagn Dual-Database MaNGOS Repack (3.3.5.12340) - 32 & 64-bit repack with the CU_0070 update for it. The query may possibly work fine with other mangos based repacks but I have not tested any of them.

Overall I am satisfied with my efforts so I thought I would share my work in case anyone else may find it useful.


(DOWNLOAD REMOVED - NEEDS TO BE UPDATED FOR CURRENT VERSION OF MANGOS)


Here is the order of the quests from start to end and a brief comment on what was changed:

In Service Of The Lich King
The Emblazoned Runeblade
Runeforging: Preparation For Battle
The Endless Hunger
The Eye Of Acherus
The Might Of The Scourge
Report To Scourge Commander Thalanor
The Scarlet Harvest
If Chaos Drives, Let Suffering Hold The Reins [Reduced to killing 5 Scarlet Crusaders & 5 Citizens of Havenshire - Added rewards from Grand Theft Palomino, Into the Realm of Shadows, Tonight We Dine In Havenshire, Death's Challenge.]
Gothik the Harvester
The Gift That Keeps On Giving [Completed upon accepting.]
An Attack Of Opportunity [Added rewards from Massacre At Light's Point.]
Victory At Death's Breach!
The Will Of The Lich King
The Crypt of Remembrance [Added rewards from The Plaguebringer's Request, Noth's Special Brew.]
Nowhere To Run And Nowhere To Hide [Added rewards from How To Win Friends And Influence Enemies, Lambs To The Slaughter.]
Behind Scarlet Lines
The Path Of The Righteous Crusader [Added rewards from Brothers In Death, Bloody Breakout, A Special Surprise, A Sort Of Homecoming.]
Ambush At The Overlook
A Meeting With Fate
The Scarlet Onslaught Emerges
Scarlet Armies Approach...
The Scarlet Apocalypse
An End To All Things... [Completed upon accepting.]
The Lich King's Command
The Light of Dawn [Completed upon accepting. - Assigned new quest taker.]
Taking Back Acherus [Assigned new quest giver.]
The Battle For The Ebon Hold [Completed upon accepting.]

[Dimman]

UPDATE `pet_levelstats` SET `hp` = *4 WHERE `hp` > '10';
UPDATE `pet_levelstats` SET `armor` = *4 WHERE `armor` > '10';
UPDATE `pet_levelstats` SET `mana` = *4 WHERE `mana` > '10';



Try that Qry

[matt71tx]

With this modification the death knight starts in Stormwind or Orgrimmar just like you would at the end of the starter quests but a small custom quest line has been added to give the death knight all the items and skills that are normally awarded during the entire quest series.

Here is the new quest order listing for this mod:

Warchief's Blessing [Horde] or Where Kings Walk [Alliance]
Death Knight assistance. [new custom quest]
More Death Knight assistance. [new custom quest]
Final Death Knight assistance. [new custom quest]

Included with the mod are the changes that need to be made to the playercreateinfo table for the new death knight starting areas.


(DOWNLOAD REMOVED - NEEDS TO BE UPDATED FOR CURRENT VERSION OF MANGOS)


I use the query for these changes with TRM's Cthulhu Ftagn Dual-Database MaNGOS Repack (3.3.5.12340) - 32 & 64-bit repack. The query may possibly work fine with other mangos based repacks but I have not tested any of them.