|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Support::Windows Servers Support forum for Windows EQEMu users. |
04-19-2018, 02:35 PM
|
Sarnak
|
|
Join Date: Dec 2010
Posts: 63
|
|
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!
|
04-19-2018, 03:34 PM
|
Administrator
|
|
Join Date: May 2013
Location: United States
Posts: 1,593
|
|
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;
|
04-19-2018, 04:52 PM
|
Sarnak
|
|
Join Date: Dec 2010
Posts: 63
|
|
Awesome! Thank you both. I will try both approaches
|
04-20-2018, 02:43 AM
|
Sarnak
|
|
Join Date: Dec 2010
Posts: 63
|
|
Quote:
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
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!!
|
04-20-2018, 11:51 AM
|
Administrator
|
|
Join Date: May 2013
Location: United States
Posts: 1,593
|
|
Quote:
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.
|
04-20-2018, 06:14 PM
|
|
Dragon
|
|
Join Date: Dec 2009
Posts: 719
|
|
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;
|
04-19-2018, 05:21 PM
|
Discordant
|
|
Join Date: Oct 2016
Posts: 445
|
|
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;
|
04-19-2018, 08:14 PM
|
|
Developer
|
|
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
|
|
[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
|
04-19-2018, 08:47 PM
|
Discordant
|
|
Join Date: Oct 2016
Posts: 445
|
|
Quote:
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
|
04-19-2018, 09:07 PM
|
|
Developer
|
|
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
|
|
I was picking :P
__________________
Uleat of Bertoxxulous
Compilin' Dirty
|
04-20-2018, 09:00 PM
|
|
Sarnak
|
|
Join Date: Jan 2013
Posts: 61
|
|
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.
|
04-21-2018, 08:02 AM
|
Fire Beetle
|
|
Join Date: Aug 2009
Location: Australia
Posts: 19
|
|
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.
|
04-21-2018, 08:30 PM
|
|
Legendary Member
|
|
Join Date: Apr 2002
Location: Seattle, WA
Posts: 505
|
|
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.
|
Thread Tools |
|
Display Modes |
Hybrid Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -4. The time now is 12:46 AM.
|
|
|
|
|
|
|
|
|
|
|
|
|