Vendors of tradeskill goods

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

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