Efficient Way to Clean Up Database (Items Mainly) by Zone?

[Lane]

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]

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]

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

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]

Awesome! Thank you both. I will try both approaches

[GRUMPY]

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

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

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]

[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]

Originally Posted by Uleat

[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]

I was picking :P

[Lane]

Originally Posted by Kingly_Krab

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

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]

Originally Posted by Lane

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]

I need to remember AS as superfluous.

[Nydosa]

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]

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]

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.