Vendors of tradeskill goods

[eski2]

For people playing on the minilogin servers, buying a potion from a shaman vendor is not an option. This is a shame as potions add viability to soloing (although i really can't figure the potion belt out, it's more awkward than in other games i've played).

Since the economy of a soloer is isolated and might be really warped (some will just give themselves bazillions of plat because they can, i know i had to see what it was like to be too encumbered to move from plat, ah!), the only way to get them in game is either create an alt and tradeskill them (which is not much fun without macros and a viable economy to gather the ingredients for you) or use gm commands. Gm commands are like game killers, potentially, and i'd like to minimise them in my solo game. I played with the idea of having some means of trading between servers but the potential for abuse was horrible.

(I remember how i felt when my EQ server was overrun by people botting or playing for money in third world sweatshops... i stopped playing because i couldn't compete if i didn't cheat, bit like baseball players and steroids i suppose.)

Would it be hard to select all tradeskill produced items and put them on vendors? i presume the cost of almost every item has already been chosen in Titanium as i used to see player made products on vendors occasionally but it was cheaper to buy at the bazaar. without a working bazaar in a solo game, a vendor would be a good alternative and the prices shouldn't make them over used.

Note to self; must learn SQL. I suspect this is really easy to organise with a few lines of coding.

[ndnet]

Some groundwork for this. If you're able to run SQL queries against your database, these could be helpful in going the rest of the way towards what you're wanting.

"What tradeskill-resultant items are in my databaes?" The recipe names seem to be a good enough description.

SELECT 
a.name as 'Recipe Name',
b.item_id as 'Items Table ID'
FROM
tradeskill_recipe as a,
tradeskill_recipe_entries as b
WHERE
a.id = b.recipe_id AND
b.successcount >= 1

If you want not only the tradeskill items but also their components, you might be more interested in this query. Essentially, the above query without the constraint that the item is returned on success:

SELECT 
a.name as 'Recipe Name',
b.item_id as 'Items Table ID'
FROM
tradeskill_recipe as a,
tradeskill_recipe_entries as b
WHERE
a.id = b.recipe_id

You're gonna get a lot of items from these queries so it might be best to break up your purchasable item list by tradeskill:

SELECT 
a.name as 'Recipe Name',
b.item_id as 'Items Table ID'
FROM
tradeskill_recipe as a,
tradeskill_recipe_entries as b
WHERE
a.id = b.recipe_id AND
a.tradeskill = TRADESKILLNUMBER

Where you match TRADESKILLNUMBER with one of the following values:
55 FISHING
56 MAKE_POISON
57 TINKERING
58 RESEARCH
59 ALCHEMY
60 BAKING
61 TAILORING
62 SENSE_TRAPS
63 BLACKSMITHING
64 FLETCHING
65 BREWING
66 ALCOHOL_TOLERANCE
67 BEGGING
68 JEWELRY_MAKING
69 POTTERY

What's left is to go after the merchantlist and npc_types tables. merchantlist is a very simple table comprised of only merchantid, slot, and itemid -- The merchantid corresponds to the npc_types's field merchant_id -- Match these up and the NPC should sell what's listed in merchantlist (Well you also have to make the npc_type class '41' for merchant).

[ndnet]

A bit more time spent with this. Someone more versed with SQL might be able to whip this into something more optimized, but here's what I came up with.

This bit of SQL accomplishes the following:
- Creates a new npc_type entry and merchantlist entries
Given the following:
- Tradeskill number (from post above)
- MerchantID number (check your database and find a number not in use, plug it in)
- Merchant name and associated values to use in the npc_types entry, e.g. race, class, level, hp, etc -- you can customize this as much as you want.

DROP TABLE IF EXISTS merchantlist_tskills;

CREATE TABLE IF NOT EXISTS
merchantlist_tskills
(slot INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, itemid INTEGER);

INSERT INTO merchantlist_tskills (itemid) (
SELECT 
	tradeskill_recipe_entries.item_id
FROM
	tradeskill_recipe,
	tradeskill_recipe_entries
WHERE
	tradeskill_recipe.id = tradeskill_recipe_entries.recipe_id AND
	tradeskill_recipe.tradeskill = TRADESKILLNUMBER AND
	tradeskill_recipe_entries.successcount = 1
);
INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 'MERCHANTID', slot, itemid FROM merchantlist_tskills);

DROP TABLE merchantlist_tskills;

INSERT INTO npc_types (name, level, race, class, gender, texture, merchant_id) VALUES ('MERCHANT_NAME00', 1, 1, 41, 0, 0, MERCHANTID);

When done, you should have a lot of new merchantlist entries and a new npc_type entry. You should then be able to use the #dbspawn command (I believe) to go poop your new merchant out wherever you want and perform other associated world-building magicks to set them up.

To note, I have not tested this in-game. I'm not sure what the ramifications are for having a merchantlist's slot value go so high (prior to this, the max amount of items a merchant has listed in my DB from PEQ is 78.. 80 might be the most the server will recognize).

Let me know how it goes

[eski2]

I think i might have done that right, although my brain kept thinking of drop down boxes and "create new merchant" buttons, as i hate checking things like "what is the next free merchant id?" as if you make a mistake, it's a big one. I still don't know how to back up the database. Oh, is there any way to get eqemu to reload the database without closing and re-oping the application? When i make changes it's tedious to have to close all and reload.

I'll post the results ASAP.

[eski2]

no luck, i can spawn the vendor but he has no inventory.

I use

SELECT 
a.name as 'Recipe Name',
b.item_id as 'Items Table ID'
FROM
tradeskill_recipe as a,
tradeskill_recipe_entries as b
WHERE
a.id = b.recipe_id AND
a.tradeskill = 59

and i can see the right results, exported it looks like
"Recipe Name","Items Table ID"
"essence of concealment",51144
"essence of concealment",16518
"essence of concealment",10062
"essence of concealment",14246
"essence of concealment",65590
"essence of concealment",17901
"essence of concealment",17770
"essence of concealment",10062
"concoction of flame i",51432
"concoction of flame i",51396
etc.

So i must be close. Merchant ID 345178 was unused so i tried

INSERT INTO merchantlist (merchantid, slot, item)
(SELECT '345178', slot, itemid FROM merchantlist_tskills);

DROP TABLE merchantlist_tskills;

INSERT INTO npc_types (name, level, race, class, gender, texture, merchant_id) VALUES ('Alchemy_Vendor', 1, 1, 41, 0, 0, 345178);

Was that what you meant? "SELECT * FROM merchantlist m; " showed the last as 345177, i thought that a new one would have been created?

[ndnet]

Did you run the whole query in the last code block? There was a lot there which set up and populated a temporary table to be used for sticking stuff back into the merchantlist table.

I'll go through it line by line and explain the reasoning:

DROP TABLE IF EXISTS merchantlist_tskills;

This I just added because I was debugging the script a lot so I wanted to ensure some cleanup was done no matter if it finished or not.

CREATE TABLE IF NOT EXISTS
merchantlist_tskills
(slot INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, itemid INTEGER);

This creates the table merchantlist_tskills for our temporary storage. If doing this one line at a time to find where it's stopping, make sure the table exists after this query or the rest is just going to bomb out.

INSERT INTO merchantlist_tskills (itemid) (
SELECT 
	tradeskill_recipe_entries.item_id
FROM
	tradeskill_recipe,
	tradeskill_recipe_entries
WHERE
	tradeskill_recipe.id = tradeskill_recipe_entries.recipe_id AND
	tradeskill_recipe.tradeskill = TRADESKILLNUMBER AND
	tradeskill_recipe_entries.successcount = 1
);

This populates the temporary table we just made with item IDs and slot numbers to get it ready to be shoved into the merchantlist table.

INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 'MERCHANTID', slot, itemid FROM merchantlist_tskills);

This grabs the data from our temporary table, adds an extra field (the merchantid), and throws it all into the merchantlist table, creating the merchant's inventory data. In your response, this doesn't appear to have been run because there were no entries with that merchantid.

DROP TABLE merchantlist_tskills;

This is to clean up the temporary table we made since we no longer need it.

INSERT INTO npc_types (name, level, race, class, gender, texture, merchant_id) VALUES ('MERCHANT_NAME00', 1, 1, 41, 0, 0, MERCHANTID);

And finally this creates a new npc_types entry for the NPC with class of merchant and connected to the right merchant ID from the merchantlist table. This query apparently worked as you were able to spawn the NPC? But I suppose it failed for you somewhere prior to that.

Make sure that you're running everything, though, as starting in the middle will wind up with some odd stuffs.

[eski2]

I got sick of bashing my head against the wall because of my sql ignorance.
I exported the list of

SELECT 
a.name as 'Recipe Name',
b.item_id as 'Items Table ID'
FROM
tradeskill_recipe as a,
tradeskill_recipe_entries as b
WHERE
a.id = b.recipe_id AND
a.tradeskill = 59

to CSV, imported it into excel and exported it as tab delimited, then used GeorgeS' merchant tool to paste in the list that way. Very clumsy, but it seemed to work. I think i'm stuffing up the

INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 'MERCHANTID', slot, itemid FROM merchantlist_tskills);

section. I'll try booting up and if it worked i'll post, otherwise time to call it a night.

[eski2]

partial success. i think i hit the limit on vendor items, and also mutiple dose potions are being sold as single use. Some items were selling for 0, presumably they are supposed to be no drop or something. But this is sort of working!

[ndnet]

Could you post the limit on the number of items allowable by vendors? I'd imagine it's something like 80, but it'd be nice to know what limitations you ran into when trying this.

Thanks

[eski2]

I don't know how to do that, except counting the items manually? Part of the problem is that many things with the same name have different item ID's and i think what is displayed is not what is really there. I'll see what i can do.

"Recipe Name","Items Table ID"
"essence of concealment",51144
"essence of concealment",16518
"essence of concealment",10062
"essence of concealment",14246
"essence of concealment",65590
"essence of concealment",17901
"essence of concealment",17770
"essence of concealment",10062

[eski2]

Is there a log or something in the eq client that would help?

[eski2]

Maybe i'm wrong? maybe it is all there; i see a 5 dose of assailing (which has only one when you buy it) going alphabetically to zombie skin?

[eski2]

Originally Posted by ndnet

...

INSERT INTO merchantlist_tskills (itemid) (
SELECT 
	tradeskill_recipe_entries.item_id
FROM
	tradeskill_recipe,
	tradeskill_recipe_entries
WHERE
	tradeskill_recipe.id = tradeskill_recipe_entries.recipe_id AND
	tradeskill_recipe.tradeskill = TRADESKILLNUMBER AND
	tradeskill_recipe_entries.successcount = 1
);

I'm uncertain as to what are things i should change in this code quote, but i'm assuming all i have to do is swap TRADESKILLNUMBER for 60 if i want a baker merchant.

Originally Posted by ndnet

INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 'MERCHANTID', slot, itemid FROM merchantlist_tskills);

This grabs the data from our temporary table, adds an extra field (the merchantid), and throws it all into the merchantlist table, creating the merchant's inventory data. In your response, this doesn't appear to have been run because there were no entries with that merchantid.

If i do this verbatim, i get the error "Incorrect integer value: 'MERCHANTID' for column 'merchantid' at row 1" so i am guessing MERCHANTID has to be picked from

SELECT * FROM merchantlist m;

and looking at the last entry, which gives "345202 0 0" , so i blindly substitute in
INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 345202, slot, itemid FROM merchantlist_tskills);
and get "Duplicate entry '345202-22129' for key 2" and then i realise i really don't know SQL at all. I try incrementing and get "Duplicate entry '345203-22129' for key 2". Maybe i need to type
INSERT INTO merchantlist (345203, slot, item) ?

I really don't know what i'm doing :P

[ndnet]

MERCHANTID has to be a unique number that you picked after finding one that isn't already in use in your merchantlist table. As for TRADESKILLNUMBER, you have that assumption correct.

[GeorgeS]

Actually, with merchant editor, assigning all tradeskill items to random merchants is very doable.

(1) Identify all tradeskill items and create a tab delimited file itenmane <tab> itemid.
(2) Import into merchant editor with import item list menu item
(3) Items appear in item buffer
(4) click expand and make sure add to selected merchants=100%
(5) Zone name = ALL, search and result of all merchants are loaded
(6) Select ALL merchants - shift mouse click selection
(7) Click on Select ALL merchants - wait a while till done
(7a) Click on save


Now you've appended all merchants with all items in buffer list.
What I do is add selected merchants=33%, so each item has a 33% chance of being added. This in effect randomizes the merchant sell lists...

Hope that helps

GeorgeS