Cleaning up the merchantlist table

[eq4me]

As most of you are aware there is lots of stuff on the merchants that is not supposed to be there. Mostly it is newby area crap stuff but there are quite a few good or rare items people had sold on EQLive when the zones merchant lists where grabbed.
With the low population of most Emu servers this is mostly not a bad thing to have some hard to forage/camp tradeskill items like spiderling silk and basic equipment like cloth/chain/bronze armor on the merchants but is is not ideal to have that scattered around like it is now. So I propose that we strip the merchants down to their original items and add basic but hard to come by items in an more controlled fashion.

To start the ball rolling I punched up a small script that reads out all available itemids on the merchantlist table(but not the temp table) and do some statistics on em. For now I checked what name in the items table the id matches. How many merchants have this item, how many mobs drop it and how many tradeskill recipes exist to create such an item. Please be aware that there is no checking if the mob, recipe or merchant actually exists or spawns

The list is based on the last PEQ release but should be mostly the same as peq-luclin-rc1 and Angelox's DB.

http://80.237.220.68/files/items_listed_in_merchantlist_table

There are quite some entries and my first step would be to bulk strip all obviously not for sale items bevore going into details. On first glance there are some strange things like a recipe for Evil Eye Eyestalks. =)

Any help and comments would be appreciated. :)

EDIT: I am not 100% sure if the tradeskill recipes part of the statistic is right. I'll check this after work.

[mwmdragon]

This is exactly what i was refering to when I wrote this post. This would bring the database back from being a split of 3 DBs.

The post i am refering to ...

http://www.eqemulator.net/forums/showpost.php?p=129823&postcount=43

[cavedude]

I know I couldn't do this, and that it can be done as that's how we get faction, loot, etc but I think the ultimate solution would be to create a script that compares our merchantlist to the information found on Allakhazam. This would not be exact as Ally may be missing or have wrong/outdated info but I certainly think it would give us a great idea of items that do not belong on vendors.

[eq4me]

I had a look in lucy.allakhazam.com and the itemid's from the eqemu item table(and thus 13th Floor) and lucy match. Strangely everquest.allakhazam.com has other item id's.
If you look close lucy lists merchants selling that item under "Drops from". Unfortunately the npc id's are not compatible but with a bit of time I could punch up a script that tries to match the name.
It would help if I could have a look at the currently used generation scripts and learn from them.

[eq4me]

I pondered If I could fill the merchant list from scratch but decided against it for now.

As a first step I will identify all not-for-sale items then I will see if Merchants are missing items they should have for sale.
Hopefully I will have the first step done tomorrow.

[eq4me]

Damn,

it seems that the sources I have used so far are quite incomplete. Uppon generating the list of items to be removed I detected quite some stuff that should be on vendors. Mostly tradeskill items but quite a few armorpieces eg. from shadowhaven. I fear I have to incorporate more sources.

Edit: Here are my preliminary results.

http://80.237.220.68/files/preliminary_list.txt

[site3op]

Something to ponder while cleaning up merchant lists...
I've been messing around with a necro on the LotR server (currently at around lvl 36.) The current merchant lists on this server are quite helpful to me in gaining spells. Quite a few necro spells are researched which means many "words" to collect. For one pet spell, I spent roughly 400 plat in combining words until I got lucky. Without this abundance of words spread across many vendors, I would have to wait for the particular drops.

So, a cleaned up vendor listing would mean a necro would go without many important spells for a long time. This could potentially kill any aspiring necro population on servers where populations are very low to begin with.

An alternative would be to create vendors for the various casters that have to research or gain their spells via raiding. I don't think many would even mind if the price is high on said spells...

I've had a couple drinks, so this may be slightly random and off topic...

[eq4me]

Something to ponder while cleaning up merchant lists...
I've been messing around with a necro on the LotR server (currently at around lvl 36.) The current merchant lists on this server are quite helpful to me in gaining spells.
[...]
So, a cleaned up vendor listing would mean a necro would go without many important spells for a long time. This could potentially kill any aspiring necro population on servers where populations are very low to begin with.

All I want to provide is a baseline to build on. The first step is to be as close to EQLive as possible. Then we can customize for a low population. Right now we have a clutter of crappy to excellent items scattered over all vendors.
My first though would be to put all research only spells, some usefull tradeskill items and lowby armor and weapons on new vendors. I mean stuff that is usually given away or sold for a few pp on Live and is considered mandatory for any low level char.

[John Adams]

An alternative would be to create vendors for the various casters that have to research or gain their spells via raiding. I don't think many would even mind if the price is high on said spells...
While I can agree that making things harder for people might cause them to lose interest, at the same time, we're talking about the base, core Emulator data here - not custom, low-pop servers. I personally believe it should be as live-like as possible - at the core - then each server op can add bots or merchants that provide anything lacking (like buff bots, or spell vendors).

Why even bother with Researching skill, then? Just put the Researchable spells on a vendor...

[eq4me]

I finally have enough data to compile a list of purchasable items.

http://80.237.220.68/files/allowed_vendor_items.txt

This should be enough for now to clean up the existing merchantlist table. Unfortunately I can not do this myself due to a lack of time in the next days.
Hopefully someone other has the time and uses this data to shrink the merchantlist table down. While simply grepping out all valid entrys should be done in a matter of minutes I guess you also have to recalculate the slot id for each vendor.
As soon as my time permits I will start to write scripts to generate the merchantlist table from scratch. On a first glance there are quite some merchants missing or the npc's are not flagged as such.

EDIT
Do the items in merchantlist_temp show up on the merchants? My understanding is that this table is dropped on an restart?
The loot_it seems to start at 10 and not 1 for each vendor.

[eq4me]

I've done some research on generation a new merchantlist from scratch this weekend and I have discovered that this will take quite some time. Unfortunately far more time I can spare in the the next few weeks.

So as an interim solution I am going back to trimming down the exisiting merchantlist table.

Provided you have both the peq-luclin-release file load_system.sql and my allowed_vendor_items.txt file in one directory on an Linux Box all you need to do is:


grep "merchantlist " load_system.sql > merchantlist.orig
for i in `cat allowed_vendor_items.txt| cut -d"," -f1` ; do grep ",$i)" merchantlist.orig ; done > merchantlist.new


The above will weed out any item id not listed in allowed_vendor_items.txt and write a new merchantlist table. Be aware that there is no change to the slot id.

Edit: Please be aware that the merchants in cshome and all customized adventure merchants and such will loose most to all of their inventory.