EQEmulator Forums

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)
-   Support::Windows Servers (https://www.eqemulator.org/forums/forumdisplay.php?f=587)
-   -   Efficient Way to Clean Up Database (Items Mainly) by Zone? (https://www.eqemulator.org/forums/showthread.php?t=41857)

Lane 04-19-2018 02:35 PM
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!

GRUMPY 04-19-2018 03:27 PM
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/

Kingly_Krab 04-19-2018 03:34 PM
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;

Lane 04-19-2018 04:52 PM
Awesome! Thank you both. I will try both approaches :)

GRUMPY 04-19-2018 05:21 PM
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;

Uleat 04-19-2018 08:14 PM
[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.

GRUMPY 04-19-2018 08:47 PM
Quote:
Originally Posted by Uleat (Post 258342)
[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

Uleat 04-19-2018 09:07 PM
I was picking :P

Lane 04-20-2018 02:43 AM
Quote:
Originally Posted by Kingly_Krab (Post 258339)
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!!

Kingly_Krab 04-20-2018 11:51 AM
Quote:
Originally Posted by Lane (Post 258348)
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.

c0ncrete 04-20-2018 06:14 PM
I need to remember AS as superfluous.

Nydosa 04-20-2018 09:00 PM
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.

Gnowm 04-21-2018 08:02 AM
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.

Shin Noir 04-21-2018 08:30 PM
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.


All times are GMT -4. The time now is 02:26 AM.

Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.