Go Back   EQEmulator Home > EQEmulator Forums > Support > Support::Windows Servers

Support::Windows Servers Support forum for Windows EQEMu users.

Reply
 
Thread Tools Display Modes
  #1  
Old 07-21-2009, 10:03 AM
Krugus
Sarnak
 
Join Date: Dec 2005
Location: the Void
Posts: 40
Default Some SQL Query's for small population servers.

I was reading in another thread how someone was wanting a SQL Query to run on their server to Adjust Mob hps and dmg output.

Well I'll share what I use on my server for those that want to adjust it and use it as they see fit.

Remember to BACKUP before making any adjustments (good habit to get into).

For Mob HP's I use the following Query: It uses a base hp formula then adjusts it from there based on the mobs Bodytype (Nastier mobs will have more hps IE Dragons, Giants, Avatars etc).

Code:
#HP Adjustment based on level, bodytype, fabled and bonus hps
update npc_types set hp = ((level * level) + (5+(level*5))) where id > 999;
update npc_types set hp = 4.5*hp where id > 999 and bodytype = 19;
update npc_types set hp = 4*hp where id > 999 and bodytype = 16;
update npc_types set hp = 3.52*hp  where id > 999 and bodytype = 30;
update npc_types set hp = 3.51*hp  where id > 999 and bodytype = 10;
update npc_types set hp = 3.5*hp  where id > 999 and bodytype = 9;
update npc_types set hp = 3.1*hp  where id > 999 and bodytype = 29;
update npc_types set hp = 1.5*hp  where id > 999 and bodytype = 6;
update npc_types set hp = 1.32*hp  where id > 999 and bodytype = 34;
update npc_types set hp = 1.31*hp  where id > 999 and bodytype = 26;
update npc_types set hp = 1.3*hp where id > 999 and bodytype = 12;
update npc_types set hp = 1.25*hp  where id > 999 and bodytype = 28;
update npc_types set hp = 1.24*hp  where id > 999 and bodytype = 27;
update npc_types set hp = 1.23*hp  where id > 999 and bodytype = 5;
update npc_types set hp = 1.75*hp  where id > 999 and bodytype = 4;
update npc_types set hp = 1.21*hp  where id > 999 and bodytype = 2;
update npc_types set hp = 1.16*hp  where id > 999 and bodytype = 11;
update npc_types set hp = 1.15*hp  where id > 999 and bodytype = 3;
update npc_types set hp = 1.11*hp where id > 999 and bodytype = 32;
update npc_types set hp = 1.1*hp  where id > 999 and bodytype = 7;
update npc_types set hp = (1.5*hp)+250 where Name LIKE "%Fabled%"; 
update npc_types set hp = hp +(level * 10) where id > 999;#extra hp bonus not multiplied by the above... if this is ran without where id>999 then you must run  update npc_types set hp = hp - (level * 10) where id < 999; to correct it

Ok on to damage: I looked at the avg dmg min & max dmg for each level and it was a very simple design. Max dmg is based on the mobs level x2 till around 40th then its x3 then it goes up from there. Granted some mobs hit alot harder than others for their level (Hill Giants come to mind). After looking over the data, I decided I wanted a smoother line of progression than what they had so I used this: Again it uses a base line for min/max dmg then I moded it further by npc bodytype and at the end if its a fabled mob.

Code:
update npc_types set mindmg= 1 ,maxdmg = 1 where id > 999 and level = 0;
update npc_types set mindmg= level*0.5 ,maxdmg = level*2 where id > 999 and level > 0 and level < 20; #level 1-19
update npc_types set mindmg= level*0.5 ,maxdmg = level*2.25 where id > 999 and level > 19 and level < 30;#level 20-29
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.5 where id > 999 and level > 29 and level < 35;#level 30-34
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.75 where id > 999 and level > 34 and level < 40;#level 35-39
update npc_types set mindmg= level*0.75 ,maxdmg = level*3 where id > 999 and level > 39 and level < 45;#level 40-44
update npc_types set mindmg= level*1 ,maxdmg = level*3.5 where id > 999 and level > 44 and level < 50;#level 45-49
update npc_types set mindmg= level*1 ,maxdmg = level*4 where id > 999 and level > 49 and level < 55;#level 50-54
update npc_types set mindmg= level*1.5 ,maxdmg = level*4.5 where id > 999 and level > 54 and level < 60;#level 55-59
update npc_types set mindmg= level*2 ,maxdmg = level*4.75 where id > 999 and level > 59 and level < 65;#level 60-64
update npc_types set mindmg= level*2 ,maxdmg = level*5 where id > 999 and level > 64 and level < 70;#level 65-69
update npc_types set mindmg= level*2.5 ,maxdmg = level*5.5 where id > 999 and level > 69 and level < 75;#level 70-74
update npc_types set mindmg= level*3 ,maxdmg = level*6 where id > 999 and level > 74 and level < 80;#level 75-79
update npc_types set mindmg= level*3.5 ,maxdmg = level*6.5 where id > 999 and level > 79;#level 80+
#adjust for body type from level 30 onward
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 2;#lycanthrope
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 7;#magical
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 3;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 11;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 5;#construct
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 6;#extraplanar
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 12;#vampire
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 4;#giant
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 26;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 29;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 30;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 32;#dragon
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 9;#bane giant
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 10;#unknown
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 16;#
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 19;#
#adjust for Fabled Mob
update npc_types set mindmg = mindmg*1.1,maxdmg = maxdmg*1.2 where id > 99 and Name LIKE "%Fabled%";
Now the next Query I use on my Item database. I took advantage of minstatus table not being used (at least in my DB it wasn't). I wanted to change up the prices on all wearable items across the board. Granted its not a perfect solution but atm it will do for now. It still needs more tweaking.
I was tired of the simple store bought Plate/Chain mail items being grossly over priced so I came up with this to adjust not only the simple items but also the more powerful magical items as well. In simple terms it most of the attributes of an item and put a number number on it. Based on that number and other factors it will come up with a price for said item and when its done it will set minstatus back to 0. It should not effect not wearable items like spells, crafting supplies, gems etc but it did effect the food items with stats:

Code:
update items set minstatus = aagi + ac + accuracy + acha + adex + aint + asta + astr + attack + avoidance + awis + banedmgamt + banedmgraceamt + extradmgamt + damage*3 + damageshield + elemdmgamt + haste*3 + hp + regen + mana + manaregen + enduranceregen + spellshield + strikethrough;
update items set minstatus = minstatus + range where itemtype = 27;
update items set minstatus = minstatus + range where itemtype = 5;
update items set minstatus = minstatus + ac  where material = 1;
update items set minstatus = minstatus + ac * 2 where material = 2;
update items set minstatus = minstatus + ac * 2.5 where material = 10;
update items set minstatus = minstatus + ac * 3 where material = 3;
update items set minstatus = minstatus * (1 + (damage / delay)) where delay > 0;
update items set minstatus = minstatus + damage *2 where itemtype = 1;
update items set minstatus = minstatus + damage *2 where itemtype = 4;
update items set minstatus = minstatus + damage *2 where itemtype = 35;
update items set price = minstatus * (100 * (1 + magic * 1.125) ) where minstatus > 0 and itemtype != 27;
update items set price = (minstatus * (10*(1+ magic *1.123)))/3 where minstatus > 0 and itemtype = 27;
update items set price = ((price / 100)*20)/2 where itemtype = 7;
update items set price = ((price / 100)*20)/2 where itemtype = 19;
update items set price = 0 where summonedflag = 1;
update items set price = 0 where norent = 0;
update items set minstatus = 0;
Other misc Query's: Fixing Potions to cast fast and have a quick reuse timer and make all items useable by any race (class still restricted though):

Code:
#Potion Casting Time Fix
update items set casttime = 100, casttime_ = 100, recastdelay = 5 where itemtype =21;
#Make all items usable by any RACE
update items set races = 65535 where races > 0;

Anyways just wanted to share some of the SQL Query's I've used on my little server. Feel free to adjust them how you like, just remember to make regular backups before making any adjustments (good habit to get into!)
Reply With Quote
  #2  
Old 07-21-2009, 01:56 PM
vales
Discordant
 
Join Date: May 2006
Posts: 458
Default

Wow. Just, wow.

I'll be giving these a run when I get a moment. Thanks so much for putting these up! Awesome, awesome stuff!
Reply With Quote
  #3  
Old 07-21-2009, 08:54 PM
Krugus
Sarnak
 
Join Date: Dec 2005
Location: the Void
Posts: 40
Default

More than welcome. Love the custom load screens you done up so when I saw you asking someone about a query you found for Wow Servers, I figured why not post what I had? They are easy to adjust, just do some simple math and see what you come out with.

The funny thing is, I started out doing a Lineage II EMU server years ago and came up with a simple query formula for hps based on level. So I've tweaked this over the years to suit whatever EMU I'm working on

Anyways here's an Example of before and after the Query's

Lord Nagafen:
before: 33,349 hps Damage: 140 to 300 pts
after: 10,796 hps Damage: 83 to 273 pts

Granted some mobs will have more hps or do more damage (or both in some cases) than what they had listed before. That is where you can go in and tweak the formula then rerun the Query. ATM My giants are MEAN. They are GIANTS. They have more hit points than the normal giants did in game.

Example:
35th level hill giant
before: 1575 hps Damage 14 to 70pts
after: 2809 hps Damage 26 to 106pts

Want to tone the giants down? Just change the Adjustment on the bodytype:

Code:
update npc_types set hp = 1.75*hp  where id > 999 and bodytype = 4;
Change the multiplier from 1.75 to 1.1 would put them around 1545 hps (1405 is the avg hps a 35th level mob would have with this query before any multipliers or hp mods) then remove the bonus hps at the end of the query or mod it down to something smaller than 10hps per level (at the moment that would add 350 hps to a 35th level mob).

Sorry I didn't add comments on the Body types to make it easier to know which is what (seems I did on the dmg query though )
Reply With Quote
  #4  
Old 07-22-2009, 02:17 AM
Krugus
Sarnak
 
Join Date: Dec 2005
Location: the Void
Posts: 40
Default Merc Quest Script

We have bots enabled on my server so I've modified the Bot quest to fit our needs. Removed the item requirements and changed up the level range and coin cost and of course added saylinks

I've also added a Bot Command Quest script to the bots on my server. You can read more on that topic here

Modified Congdar's Aediles_Thrall.pl quest
Code:
############################################
# ZONE: Bazaar
# DATABASE: PEQ PoP-LoY
# LAST EDIT DATE: July 10, 2009
# VERSION: 1.0
# DEVELOPER: Congdar
# Modified by: Krugus on July 20, 2009
# Mod's are: Removed Item requirements, adjusted coin cost and level req
# and added saylinks
#
# *** NPC INFORMATION ***
#
# NAME: Aediles Thrall
# TYPE: NPC
# RACE: Dwarf
# LEVEL: 71
#
# *** ITEMS GIVEN OR TAKEN ***
##
#
# *** QUESTS INVOLVED IN ***
#
# 1 - Custom Quest - Buying Merc's (bots)
#
#
# *** QUESTS AVAILABLE TO ***
#
# 1 - Anyone meeting level requirements
#
#
############################################

# Some global settings for the quest.  Set them to your preferred levels and plat costs
# Level options for when a character can acquire more slaves(bots)
my $firstbotlevel  = 15;
my $firstbotcost   = 10;
my $secondbotlevel = 25;
my $secondbotcost  = 20;
my $thirdbotlevel  = 30;
my $thirdbotcost   = 40;
my $fourthbotlevel = 35;
my $fourthbotcost  = 80;
my $fifthbotlevel  = 40;
my $fifthbotcost   = 100;

# They have enough for one group, now they start creating raid bots up to the quest::spawnbotcount() limit.
# A rule setting EQOffline:SpawnBotCount of 11 is two full groups including you.
my $nextbotlevel   = 45;
my $nextbotcost    = 100;
my $nextextracost  = 200;
my $qglobals = 0;

sub EVENT_SPAWN {
   quest::set_proximity($x - 50, $x + 50, $y - 50, $y + 50);
}

sub EVENT_ENTER {
   if(quest::botquest()) {
      if(!defined $qglobals{bot_spawn_limit}) {
         quest::setglobal("bot_spawn_limit", 0, 5, "F");
      }
   }
}

sub EVENT_SAY {
my $hel = quest::saylink("help");
my $commd = quest::saylink("command");
my $tal = quest::saylink("talents");
my $indi = quest::saylink("individuals");
my $inst = quest::saylink("interested");
my $novi = quest::saylink("Novice");
my $appr = quest::saylink("Apprentice");
my $expt = quest::saylink("Expert");
my $capt = quest::saylink("Captain");
my $mstr = quest::saylink("Master");
my $army = quest::saylink("Army");
my $cause = quest::saylink("cause");

   if(defined $qglobals{bot_spawn_limit} && (quest::spawnbotcount() > $qglobals{bot_spawn_limit})) {
      if(($ulevel >= $firstbotlevel) && ($qglobals{bot_spawn_limit} <= 0)) {
         quest::settimer("face", 25);
         if($text=~/Hail/i) {
            quest::say("Greetings $name!  Lookin' fer a bit o' $hel with yer adventurin'?");
         }
         if($text=~/help/i) {
            quest::say("You look like the sort that was born to $commd others into battle.");
         }
         if($text=~/command/i) {
            quest::say("I belong to a discreet group of mercenaries that sell our $tal to those we deem fit to command.");
         }
         if($text=~/talents/i) {
            quest::say("We have a wide varity of $indi, who vary in skill and talent.");
         }
         if($text=~/individuals/i) {
		quest::say("Need a lockpicked? got ya covered.  Need a special critter tracked down? got ya covered.   Need extra muscle? got ya covered!  Need a healer? got ya covered!  You name it we got it!  So are you $inst?");
         }
         if($text=~/interested/i) {
            quest::say("Good! Now that I got your attention.  Here is how it works:  You start off $novi Level Command and with experince and coin you buy your way up the ladder!  Har Har Har!");
         }
         if($text=~/Novice/i) {
            quest::say("At Novice level command rank, you get only one Merc to command at at time.  It will cost $firstbotcost platinum pieces.");
         }
      }
      elsif(($ulevel >= $secondbotlevel) && ($qglobals{bot_spawn_limit} <= 1)) {
         if($text=~/Hail/i) {
            quest::say("Ahh, a returnin' customer.  Reports in from the Filed says you are ready to command another Rank.  Are you read to upgrade to $appr?");
         }
         if($text=~/Apprentice/i) {
		quest::say("At Apprentice level command rank, you get to command two Merc's at a time.  It will cost $secondbotcost platinum for the upgraded rank.");
         }
      }
      elsif(($ulevel >= $thirdbotlevel) && ($qglobals{bot_spawn_limit} <= 2)) {
         if($text=~/Hail/i) {
            quest::say("Ahh, a returnin' customer.  Reports in from the Filed says you are ready to command another Rank.  Are you read to upgrade to $expt?");
         }
         if($text=~/Expert/i) {
            quest::emote("looks around the room");
		quest::say("At Expert level command rank, you get to command three Merc's at a time.  It will cost $thirdbotcost platinum for the upgraded rank.");
         }
      }
      elsif(($ulevel >= $fourthbotlevel) && ($qglobals{bot_spawn_limit} <= 3)) {
         if($text=~/Hail/i) {
            quest::say("Ahh, a returnin' customer.  Reports in from the Filed says you are ready to command another Rank.  Are you read to upgrade to $capt?");
         }
         if($text=~/Captain/i) {
            quest::say("At Captain level command rank, you get to command four Merc's at a time.  It will cost $fourthbotcost platinum for the upgraded rank.");
         }
      }
      elsif(($ulevel >= $fifthbotlevel) && ($qglobals{bot_spawn_limit} <= 4)) {
         if($text=~/Hail/i) {
            quest::say("Ahh, a returnin' customer.  Reports in from the Filed says you are ready to command another Rank.  Are you read to upgrade to $mstr?");
         }
         if($text=~/Master/i) {
            quest::say("At Master level command rank, you get to command five Merc's at a time.  It will cost $fifthbotcost platinum for the upgraded rank.");
         }
      }
      elsif(($ulevel >= $nextbotlevel) && ($qglobals{bot_spawn_limit} <= 5)) {
         if($text=~/Hail/i) {
            quest::say("Ahh, Your make'n quite a name for yourself!  I see you ARE fit for command!  I see you are wanting to start your own little privite $army");
         }
         if($text=~/Army/i) {
            quest::emote("looks around the room");
            quest::say("If ya need another warm body for your cause, I can see that you get to command six Merc's at a time for the low price of $nextbotcost platinum.");
         }
      }
      elsif(($ulevel >= $nextbotlevel) && ($qglobals{bot_spawn_limit} >= 6)) {
         if($text=~/Hail/i) {
            quest::say("Need another for your $cause?");
         }
         if($text=~/cause/i) {
            quest::say("If ya needings another warm body for your cause.  I can get ya another Merc to join yas for just another mere $nextbotcost platinum.");
         }
      }
      else {
         if($text=~/Hail/i) {
            quest::say("eh? Come back when yer a bit more experinced and coin purse abit heavier. Only then I kin help ye.");
            quest::settimer("face", 5);
         }
      }
   }
   else {
      if($text=~/Hail/i) {
         quest::say("eh? Mind yer own business, go away!");
         quest::settimer("face", 5);
         my $sbcount = quest::spawnbotcount();
         $client->Message(6,"You have $qglobals{bot_spawn_limit} out of $sbcount possible Merc's.");
      }
   }
}

sub EVENT_TIMER {
  if($timer eq "face") {
     my $facemob = $entity_list->GetMobByNpcTypeID(151071);#NPC: Aspen
     $npc->FaceTarget($facemob);
     quest::stoptimer("face");
  }
}

sub EVENT_ITEM {
   if(defined $qglobals{bot_spawn_limit} && (quest::spawnbotcount() > $qglobals{bot_spawn_limit})) {
      my $success = 0;
      if(($ulevel >= $firstbotlevel) && ($qglobals{bot_spawn_limit} <= 0)) {
         if(($platinum == $firstbotcost)) { 
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $secondbotlevel) && ($qglobals{bot_spawn_limit} <= 1)) {
         if(($platinum == $secondbotcost)) { 
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $thirdbotlevel) && ($qglobals{bot_spawn_limit} <= 2)) {
         if(($platinum == $thirdbotcost)) { 
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $fourthbotlevel) && ($qglobals{bot_spawn_limit} <= 3)) {
         if(($platinum == $fourthbotcost)) {
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $fifthbotlevel) && ($qglobals{bot_spawn_limit} <= 4)) {
         if(($platinum == $fifthbotcost)) {
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $nextbotlevel) && ($qglobals{bot_spawn_limit} <= 5)) {
         if(($platinum == $nextbotcost)) {
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      elsif(($ulevel >= $nextbotlevel) && ($qglobals{bot_spawn_limit} >= 6)) {
         if(($platinum == $nextextracost) || ($platinum == $nextbotcost)) {
            $success = $qglobals{bot_spawn_limit}+1;
         }
      }
      if($success > 0) {
         quest::say("Thanks $name!");
         quest::setglobal("bot_spawn_limit", $success, 5, "F");
         $client->Message(6,"You receive a character flag!");
         $client->Message(6,"You can now create and spawn a Merc! See: '#bot help create' and '#bot spawn' commands.");
         my $sbcount = quest::spawnbotcount();
         $client->Message(6,"You have $success out of $sbcount possible Merc's.");
         $success = 0;
      }
      else {
         quest::say("I don't need this.");
         plugin::return_items(\%itemcount);
         if($platinum != 0 || $gold !=0 || $silver != 0 || $copper != 0) {
            quest::givecash($copper, $silver, $gold, $platinum);
         }
      }
   }
   else {
      quest::say("I don't need this.");
      plugin::return_items(\%itemcount);
      if($platinum != 0 || $gold !=0 || $silver != 0 || $copper != 0) {
         quest::givecash($copper, $silver, $gold, $platinum);
      }
   }
   quest::settimer("face", 45);
}
sub EVENT_SIGNAL {
   if($signal == 1) {
      if((defined $qglobals{bot_spawn_limit}) && ($qglobals{bot_spawn_limit} > 0)) {
         quest::say("Hey! Nothing to see here, Move along!");
      }
   }
}
# END of FILE Zone:bazaar -- Aediles_Thrall.pl
Reply With Quote
  #5  
Old 07-22-2009, 12:20 PM
vales
Discordant
 
Join Date: May 2006
Posts: 458
Default

Whoa! They just keep rolling in! I'm really surprised no one else has commented on this.

Well, you have my thanks, that's for sure.
Reply With Quote
  #6  
Old 02-13-2023, 05:55 PM
thefusz
Sarnak
 
Join Date: Feb 2010
Posts: 35
Wink I messed up lol

So what would be the easiest way to undo doing this (obv without just restoring the backup).


Quote:
Originally Posted by Krugus View Post
I was reading in another thread how someone was wanting a SQL Query to run on their server to Adjust Mob hps and dmg output.

Well I'll share what I use on my server for those that want to adjust it and use it as they see fit.

Remember to BACKUP before making any adjustments (good habit to get into).

For Mob HP's I use the following Query: It uses a base hp formula then adjusts it from there based on the mobs Bodytype (Nastier mobs will have more hps IE Dragons, Giants, Avatars etc).

Code:
#HP Adjustment based on level, bodytype, fabled and bonus hps
update npc_types set hp = ((level * level) + (5+(level*5))) where id > 999;
update npc_types set hp = 4.5*hp where id > 999 and bodytype = 19;
update npc_types set hp = 4*hp where id > 999 and bodytype = 16;
update npc_types set hp = 3.52*hp  where id > 999 and bodytype = 30;
update npc_types set hp = 3.51*hp  where id > 999 and bodytype = 10;
update npc_types set hp = 3.5*hp  where id > 999 and bodytype = 9;
update npc_types set hp = 3.1*hp  where id > 999 and bodytype = 29;
update npc_types set hp = 1.5*hp  where id > 999 and bodytype = 6;
update npc_types set hp = 1.32*hp  where id > 999 and bodytype = 34;
update npc_types set hp = 1.31*hp  where id > 999 and bodytype = 26;
update npc_types set hp = 1.3*hp where id > 999 and bodytype = 12;
update npc_types set hp = 1.25*hp  where id > 999 and bodytype = 28;
update npc_types set hp = 1.24*hp  where id > 999 and bodytype = 27;
update npc_types set hp = 1.23*hp  where id > 999 and bodytype = 5;
update npc_types set hp = 1.75*hp  where id > 999 and bodytype = 4;
update npc_types set hp = 1.21*hp  where id > 999 and bodytype = 2;
update npc_types set hp = 1.16*hp  where id > 999 and bodytype = 11;
update npc_types set hp = 1.15*hp  where id > 999 and bodytype = 3;
update npc_types set hp = 1.11*hp where id > 999 and bodytype = 32;
update npc_types set hp = 1.1*hp  where id > 999 and bodytype = 7;
update npc_types set hp = (1.5*hp)+250 where Name LIKE "%Fabled%"; 
update npc_types set hp = hp +(level * 10) where id > 999;#extra hp bonus not multiplied by the above... if this is ran without where id>999 then you must run  update npc_types set hp = hp - (level * 10) where id < 999; to correct it

Ok on to damage: I looked at the avg dmg min & max dmg for each level and it was a very simple design. Max dmg is based on the mobs level x2 till around 40th then its x3 then it goes up from there. Granted some mobs hit alot harder than others for their level (Hill Giants come to mind). After looking over the data, I decided I wanted a smoother line of progression than what they had so I used this: Again it uses a base line for min/max dmg then I moded it further by npc bodytype and at the end if its a fabled mob.

Code:
update npc_types set mindmg= 1 ,maxdmg = 1 where id > 999 and level = 0;
update npc_types set mindmg= level*0.5 ,maxdmg = level*2 where id > 999 and level > 0 and level < 20; #level 1-19
update npc_types set mindmg= level*0.5 ,maxdmg = level*2.25 where id > 999 and level > 19 and level < 30;#level 20-29
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.5 where id > 999 and level > 29 and level < 35;#level 30-34
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.75 where id > 999 and level > 34 and level < 40;#level 35-39
update npc_types set mindmg= level*0.75 ,maxdmg = level*3 where id > 999 and level > 39 and level < 45;#level 40-44
update npc_types set mindmg= level*1 ,maxdmg = level*3.5 where id > 999 and level > 44 and level < 50;#level 45-49
update npc_types set mindmg= level*1 ,maxdmg = level*4 where id > 999 and level > 49 and level < 55;#level 50-54
update npc_types set mindmg= level*1.5 ,maxdmg = level*4.5 where id > 999 and level > 54 and level < 60;#level 55-59
update npc_types set mindmg= level*2 ,maxdmg = level*4.75 where id > 999 and level > 59 and level < 65;#level 60-64
update npc_types set mindmg= level*2 ,maxdmg = level*5 where id > 999 and level > 64 and level < 70;#level 65-69
update npc_types set mindmg= level*2.5 ,maxdmg = level*5.5 where id > 999 and level > 69 and level < 75;#level 70-74
update npc_types set mindmg= level*3 ,maxdmg = level*6 where id > 999 and level > 74 and level < 80;#level 75-79
update npc_types set mindmg= level*3.5 ,maxdmg = level*6.5 where id > 999 and level > 79;#level 80+
#adjust for body type from level 30 onward
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 2;#lycanthrope
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 7;#magical
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 3;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 11;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 5;#construct
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 6;#extraplanar
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 12;#vampire
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 4;#giant
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 26;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 29;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 30;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 32;#dragon
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 9;#bane giant
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 10;#unknown
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 16;#
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 19;#
#adjust for Fabled Mob
update npc_types set mindmg = mindmg*1.1,maxdmg = maxdmg*1.2 where id > 99 and Name LIKE "%Fabled%";
Now the next Query I use on my Item database. I took advantage of minstatus table not being used (at least in my DB it wasn't). I wanted to change up the prices on all wearable items across the board. Granted its not a perfect solution but atm it will do for now. It still needs more tweaking.
I was tired of the simple store bought Plate/Chain mail items being grossly over priced so I came up with this to adjust not only the simple items but also the more powerful magical items as well. In simple terms it most of the attributes of an item and put a number number on it. Based on that number and other factors it will come up with a price for said item and when its done it will set minstatus back to 0. It should not effect not wearable items like spells, crafting supplies, gems etc but it did effect the food items with stats:

Code:
update items set minstatus = aagi + ac + accuracy + acha + adex + aint + asta + astr + attack + avoidance + awis + banedmgamt + banedmgraceamt + extradmgamt + damage*3 + damageshield + elemdmgamt + haste*3 + hp + regen + mana + manaregen + enduranceregen + spellshield + strikethrough;
update items set minstatus = minstatus + range where itemtype = 27;
update items set minstatus = minstatus + range where itemtype = 5;
update items set minstatus = minstatus + ac  where material = 1;
update items set minstatus = minstatus + ac * 2 where material = 2;
update items set minstatus = minstatus + ac * 2.5 where material = 10;
update items set minstatus = minstatus + ac * 3 where material = 3;
update items set minstatus = minstatus * (1 + (damage / delay)) where delay > 0;
update items set minstatus = minstatus + damage *2 where itemtype = 1;
update items set minstatus = minstatus + damage *2 where itemtype = 4;
update items set minstatus = minstatus + damage *2 where itemtype = 35;
update items set price = minstatus * (100 * (1 + magic * 1.125) ) where minstatus > 0 and itemtype != 27;
update items set price = (minstatus * (10*(1+ magic *1.123)))/3 where minstatus > 0 and itemtype = 27;
update items set price = ((price / 100)*20)/2 where itemtype = 7;
update items set price = ((price / 100)*20)/2 where itemtype = 19;
update items set price = 0 where summonedflag = 1;
update items set price = 0 where norent = 0;
update items set minstatus = 0;
Other misc Query's: Fixing Potions to cast fast and have a quick reuse timer and make all items useable by any race (class still restricted though):

Code:
#Potion Casting Time Fix
update items set casttime = 100, casttime_ = 100, recastdelay = 5 where itemtype =21;
#Make all items usable by any RACE
update items set races = 65535 where races > 0;

Anyways just wanted to share some of the SQL Query's I've used on my little server. Feel free to adjust them how you like, just remember to make regular backups before making any adjustments (good habit to get into!)
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 06:24 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3