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 04-19-2018, 02:35 PM
Lane
Sarnak
 
Join Date: Dec 2010
Posts: 62
Default Efficient Way to Clean Up Database (Items Mainly) by Zone?

Hey all!

I have made some solid efforts into recreating a classic database, however, it's a very time consuming task! As items aren't listed in the database by zone (man I wish there was a column for that!) I've gone through items either line by line, or #aggrozone the entire zone, gone through NPC's and their loottables/drops.

Is there an easier way to do this? I found a query on these forums from 10+ years ago, but wasn't sure if I could apply this to HeidiSQL.

I also don't use EOC or GeorgeS Tools, so not sure if I should go that approach if they have this option.

My goal is to first remove all non classic-kunark-velious NPC's, then remove out of era NPC's, then fix quests.

After a few weeks I'm seemingly done with most of Faydwer, however, have a long way to go. Thanks for the help!
Reply With Quote
  #2  
Old 04-19-2018, 03:27 PM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

Did you consider this ? (EQ Cleanup tool) Might help for a little bit of your goals.

This post shows it's options.
http://www.eqemulator.org/forums/sho...60&postcount=9

The tool is downloaded here:
http://xackery.github.io/eqcleanup/
Reply With Quote
  #3  
Old 04-19-2018, 03:34 PM
Kingly_Krab
Administrator
 
Join Date: May 2013
Location: United States
Posts: 1,589
Default

This may not be the cleanest way to do it, but here's a SQL query you can run to select ALL the item IDs and names from any zone's loot. This should make it a lot easier to find what items you're looking for.
# Is a placeholder for the shortname of the zone, i.e. akheva
Code:
SELECT DISTINCT i.id, i.`name` FROM items i
INNER JOIN lootdrop_entries lde ON i.id = lde.item_id
INNER JOIN loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
INNER JOIN npc_types n ON n.loottable_id = lte.loottable_id
INNER JOIN spawnentry se ON se.npcID = n.id
INNER JOIN spawngroup sg ON sg.id = se.spawngroupID
INNER JOIN spawn2 s2 ON s2.spawngroupID = sg.id
WHERE s2.zone = '#'
ORDER BY i.id ASC;
Reply With Quote
  #4  
Old 04-19-2018, 04:52 PM
Lane
Sarnak
 
Join Date: Dec 2010
Posts: 62
Default

Awesome! Thank you both. I will try both approaches
Reply With Quote
  #5  
Old 04-19-2018, 05:21 PM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

If you want find an item by name SERVER-WIDE in loot tables.
(crude defiant used as example here)

Code:
SELECT le.* FROM items i
INNER JOIN lootdrop_entries le ON i.id = le.item_id
WHERE i.name like "%crude defiant%"
If you want to simply DISABLE that particular kind of item (name) from dropping SERVER-WIDE
This just simply sets it's chance of dropping to 0

Code:
UPDATE lootdrop_entries ld  
INNER JOIN items i ON i.id=ld.item_id 
INNER JOIN loottable_entries lt ON ld.lootdrop_id=lt.lootdrop_id 
SET ld.chance = 0
WHERE i.Name like '%crude defiant%' and ld.chance > 0;
Reply With Quote
  #6  
Old 04-19-2018, 08:14 PM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default

[Sidenote:]

I believe the changes to global loot may affect defiant armor.

IIRC, all defiant armor was removed and you will need to run an optional script to add it back in under global loot.


[Caveat:]

There may be some mid-level defiant armor in existing loot tables.

I can't remember if the `reqlevel=0` gear is removed during the required update process.
__________________
Uleat of Bertoxxulous

Compilin' Dirty
Reply With Quote
  #7  
Old 04-19-2018, 08:47 PM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

Quote:
Originally Posted by Uleat View Post
[Sidenote:]

I believe the changes to global loot may affect defiant armor.

IIRC, all defiant armor was removed and you will need to run an optional script to add it back in under global loot.


[Caveat:]

There may be some mid-level defiant armor in existing loot tables.

I can't remember if the `reqlevel=0` gear is removed during the required update process.
I did receive 388 results running the query with %defiant%, but I also just remembered,
as per a previous thread I asked about the global_loot table. I totally forgot. My badd :P

http://www.eqemulator.org/forums/sho...d.php?p=258142
Reply With Quote
  #8  
Old 04-19-2018, 09:07 PM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default

I was picking :P
__________________
Uleat of Bertoxxulous

Compilin' Dirty
Reply With Quote
  #9  
Old 04-20-2018, 02:43 AM
Lane
Sarnak
 
Join Date: Dec 2010
Posts: 62
Default

Quote:
Originally Posted by Kingly_Krab View Post
This may not be the cleanest way to do it, but here's a SQL query you can run to select ALL the item IDs and names from any zone's loot. This should make it a lot easier to find what items you're looking for.
# Is a placeholder for the shortname of the zone, i.e. akheva
Code:
SELECT DISTINCT i.id, i.`name` FROM items i
INNER JOIN lootdrop_entries lde ON i.id = lde.item_id
INNER JOIN loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
INNER JOIN npc_types n ON n.loottable_id = lte.loottable_id
INNER JOIN spawnentry se ON se.npcID = n.id
INNER JOIN spawngroup sg ON sg.id = se.spawngroupID
INNER JOIN spawn2 s2 ON s2.spawngroupID = sg.id
WHERE s2.zone = '#'
ORDER BY i.id ASC;
This is literally the best thing I've seen in action... THANK YOU!!
Reply With Quote
  #10  
Old 04-20-2018, 11:51 AM
Kingly_Krab
Administrator
 
Join Date: May 2013
Location: United States
Posts: 1,589
Default

Quote:
Originally Posted by Lane View Post
This is literally the best thing I've seen in action... THANK YOU!!
INNER JOIN goes a really long way when it comes to linking tables and information.
Reply With Quote
  #11  
Old 04-20-2018, 06:14 PM
c0ncrete's Avatar
c0ncrete
Dragon
 
Join Date: Dec 2009
Posts: 719
Default

I need to remember AS as superfluous.
__________________
I muck about @ The Forge.
say(rand 99>49?'try '.('0x'.join '',map{unpack 'H*',chr rand 256}1..2):'incoherent nonsense')while our $Noport=1;
Reply With Quote
  #12  
Old 04-20-2018, 09:00 PM
Nydosa's Avatar
Nydosa
Sarnak
 
Join Date: Jan 2013
Posts: 61
Default

I would recommend getting GeorgeS' tool working for viewing total zone loot drops.

Another heavy-handed fix I did for items was remove all items from lootdrop and merchants that had an ID right around when the "newbie" quest show up in the database. I would HIGHLY recommend making a back-up, but around id 19550 they show up in my database, so removing that got rid of 99% of the trash in old-world zones.
Reply With Quote
  #13  
Old 04-21-2018, 08:02 AM
Gnowm
Fire Beetle
 
Join Date: Aug 2009
Location: Australia
Posts: 19
Default

Would searching the lore column for the value 'for the
use of anyone willing to take up arms' (or something close)
find all defiant gear.

Thats just memory, n im not even good at sql but that sounds logical.
Reply With Quote
  #14  
Old 04-21-2018, 08:30 PM
Shin Noir's Avatar
Shin Noir
Legendary Member
 
Join Date: Apr 2002
Location: Seattle, WA
Posts: 502
Default

my xegony project has this feature built in, but it's very ambitious and not nearly ready for usage. But fully aware of this headache.
__________________

~Shin Noir
DungeonEQ.com
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:56 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