tradeskillrecipe table

[Muuss]

in table 'tradeskillrecipe', the field named 'product' (smallint(6)) is set to 'unique'. If you wanna create 2 recipes with the same product as result, it leads you to use product2 instead of product to set the recipe's result. If you have more than 2 recipes returning both the same product and product2, you're blocked.

Returning several items from tradeskill recipes happens quite a lot when you start using tools, like knives in tailoring or hammers in smithing, and you need to use the failproduct field for the tool in case the combine fails.

So the question is :
Is there an alternate way for those situations or do we really need that 'unique' statement on that field?

L8rs

[Muuss]

i m quoting a (very) old post, from Lurker_005, some of those functionnalities has been implemented, but not all and some seem to remain quite important to my eyes

http://www.everquestserver.com/forums/viewtopic.php?t=3802

I would like to improve the trade skill functions.

return multiple items
- 5 arrows
- product and a tool used making it
return an item on failure
quest combines that the container becomes the item made
zero failure combines
container type check
Edit: found a recipie that returns 2 items on failure.... nother column needed in the table


returning an item and a tool is possible, but not one item and 2 tools (Velious tradeskill quests do that quite often).

So basically, we could still need :

- more products
- alwaysreturn products (2, 3, more ?)
- a 'no fail' flag
- several failproducts
- a 'transform container to [items.id]' field


Table structure as it is now:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| tradeskill | smallint(6) | | | 0 | |
| skillneeded | smallint(6) | | | 0 | |
| trivial | smallint(6) | | | 0 | |
| product | smallint(6) | | UNI | 0 | |
| product2 | smallint(6) | | | 0 | |
| failproduct | smallint(6) | | | 0 | |
| productcount | smallint(6) | | | 0 | |
| i1 | smallint(6) | | | 0 | |
| i2 | smallint(6) | | | 0 | |
| i3 | smallint(6) | | | 0 | |
| i4 | smallint(6) | | | 0 | |
| i5 | smallint(6) | | | 0 | |
| i6 | smallint(6) | | | 0 | |
| i7 | smallint(6) | | | 0 | |
| i8 | smallint(6) | | | 0 | |
| i9 | smallint(6) | | | 0 | |
| i10 | smallint(6) | | | 0 | |
| notes | text | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
[/b]

[Lurker_005]

So the question is :
Is there an alternate way for those situations or do we really need that 'unique' statement on that field?

There are no ill affects from removing the unique flag on the product field. Not sure why the CSV DB hasn't had that changed...

The rest you could ask Trumpcard to look at, he helped impliment the last tradeskill table change.
I suggest you make a proposed new table with what seems needed, with reasons/uses for each change and post them here.

[Muuss]

Ok Lurker, i ll work on this.
I noticed some recipes with trivial and neededskill set to -1 or some set to 255, i ll have to look at the source code to see what's the goal.

[Lurker_005]

I noticed some recipes with trivial and neededskill set to -1 or some set to 255

I don't think it got put in the code, but when making the table, -1 was used to denote nofail combines and 255 (or was it 251)trivial was for stuff that at the time never became trivial.

see http://www.everquestserver.com/forums/viewtopic.php?t=3801 Unfortunatly none of the links work anymore, but all the tradeskils done were put into the AddonDBs and that is on sourceforge

[Muuss]

I worked on the table structure. I found more simple to build 3 new tables than modifying the existing one. I built a table describing the recipe and another one for the components/products, the last one implements limits on the combines, mainly for cultural tradeskills. Below the 3 tables and a few words commenting the fields :


first table

mysql> show columns from tradeskill_recipe;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(64) | | | | |
| tradeskill | smallint(6) | | | 0 | |
| skillneeded | smallint(6) | | | 0 | |
| trivial | smallint(6) | | | 0 | |
| nofail | tinyint(4) | | | 0 | |
| container_id | smallint(6) | | | -1 | |
| notes | tinytext | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+


tradeskil,skillneeded,trivial,notes are same as before.
the 'name' field is only designed for world builders.
i added a nofail flag (0/1)
container_id permits to add quest recipes combinable on particular bags/boxes/chests, that container is destroyed when combining


second table


mysql> show columns from tradeskill_recipe_entries;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| recipe_id | smallint(6) | | | 0 | |
| item_id | smallint(6) | | | 0 | |
| successcount | tinyint(4) | | | 0 | |
| failcount | tinyint(4) | | | 0 | |
| componentcount | tinyint(4) | | | 1 | |
+----------------+-------------+------+-----+---------+----------------+


recipe_id <=> tradeskill_recipe.id
item_id <=> items.id
successcount -> number returned on success
failcount -> number returned on failure
componentcount -> number needed for the combine

doing that way, we can :

-we won't have to change the table again is VI starts to create recipes with 12 components or more.

- use as many tools as needed that will be returned on failure/success : component=1, successcount=1, failcount=1

- have products returned on success : component=0, successcount=1 (or more), failcount=0

- have products returned on failure : component=0, successcount=0, failcount=1 (or more)

- have components, destroyed when combining : component=1 (or more), successcount=0, failcount=0


For a combine that transforms the original container in a new one :
tradeskill_recipe :
id=auto_increment
nofail=1
container_id = original container
tradeskill=[whatever, since its nofail]

tradeskill_recipe_entries :
item_id=item returned on the combine
successcount=1
failcount=0
componentcount=0

tradeskill_recipe_entries :
components needed for the combine.


third table

mysql> show columns from tradeskill_recipe_limits;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| recipe_id | smallint(6) | | | 0 | |
| class | smallint(6) | | | 0 | |
| race | smallint(6) | | | 0 | |
| god | smallint(6) | | | 0 | |
+-----------+-------------+------+-----+---------+----------------+

fields speaks by themselves i think.
Adding that table allows to limit combines to any combination of race/class/god possible.



If the tradeskill's table changes, i could write a convertor from actual design to whatever it ll become :)

[Muuss]

forgot this )

CREATE TABLE tradeskill_recipe (
id int(11) NOT NULL auto_increment,
name varchar(64) NOT NULL default '',
tradeskill smallint(6) NOT NULL default '0',
skillneeded smallint(6) NOT NULL default '0',
trivial smallint(6) NOT NULL default '0',
nofail tinyint(4) NOT NULL default '0',
container_id smallint(6) NOT NULL default '-1',
notes tinytext,
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE tradeskill_recipe_entries (
id int(11) NOT NULL auto_increment,
recipe_id smallint(6) NOT NULL default '0',
item_id smallint(6) NOT NULL default '0',
successcount tinyint(4) NOT NULL default '0',
failcount tinyint(4) NOT NULL default '0',
componentcount tinyint(4) NOT NULL default '1',
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE tradeskill_recipe_limits (
id int(11) NOT NULL auto_increment,
recipe_id smallint(6) NOT NULL default '0',
class smallint(6) NOT NULL default '0',
race smallint(6) NOT NULL default '0',
god smallint(6) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

[krich]

Good stuff Muus! I hope this change goes in as it will allow much more flexibility in tradeskill recipes.

As part of the overall project, the Mwnpcmovdb team is working on a tradeskill database that is based on the EQTraders.com database. If you have any interest in assisting, please let me know. We could use the help (especially if your recommendations are adopted)

Regards,

krich

[Muuss]

I m kinda busy ATM with the spawn editor i wanna finish. If you use the recipe editor and need changes, i m totally open to make them.
and thx for the job done on the DBs :)

[Muuss]

I could be usefull to add a field in the tradeskill_recipe_limits table, with the birthtown. Agnostic humans have different cultural skills on live, from freeport or qeynos.

[Lurker_005]

Definatly more versatile. It is harder to follow/debug the raw SQL, but with a good editor that isn't an issue.
Will this cover all the needs for tradeskills? Any problems/missing features?

Anyone want to try and work out the code changes needed for this?

[Muuss]

I ll update my recipe editor if needed (and if people judge it usefull).
Tho for the source code, i can try but it ll take me a lots of time since i m far of being a C++ coder :?

[krich]

I was thinking about this enhancement over the last day or so. This new format works well when you know the product and you want to find the ingredients, but I can't come up with a proper SQL statement that will pull a recipe out of this structure given only the ingredients. (Of course it could be due to my relatively light SQL experience).

Consider a simple recipe: Tarnished Dagger
Ingredients: Rusty Dagger, Sharpening Stone

What would the SQL Statement(s)/C++ Code be to pull that out of the database? Remember, when the player clicks combine, all we know at that point are the ingredients.

Regards,

krich

[Muuss]

I think the question is : what is the MYsql statement to do this :)
it would be possible in 1 round if mysql would work with subselects (select into where clauses). Since its not, i think several passes will be needed to find the recipe.

like for example :

select tradeskill_recipe.id from tradeskill_recipe,tradeskill_recipe_entries where tradeskill_recipe.id=tradeskill_recipe_entries.rec ipe_id and item_id=[item1id] and componentcount=[item1nb]
-> returns the recipes which need item1nb of item1id

if there's more than 1 answer, we test them with the second item

point A
select tradeskill_recipe.id from tradeskill_recipe,tradeskill_recipe_entries where tradeskill_recipe.id=tradeskill_recipe_entries.rec ipe_id and tradeskill_recipe.id=[answer1] and item_id=[item1id] and componentcount=[item1nb]

if there's a result to this, we keep that recipe_id as a possible result, and process to the next previous answers.

At the end, if we have only 1 recipe_id left, we found the recipe, if no, 2 possibilities :
- our recipe has 2 components and both are a part of the components of another recipe (or there's 2 similar recipes, but this may consider as an error from the world builder)
- there are several recipes that match 1st and 2nd components, we have to test for the 3rd one and restart at point A.


That's not the only way to do this, another one would be to do a query, of the kind :


select distinct tradeskill_recipe.id
from tradeskill_recipe,tradeskill_recipe_entries
where tradeskill_recipe.id=tradeskill_recipe_entries.rec ipe_id and
(
(item_id=[item1id] and componentcount=[item1count])
or (item_id=[item2id] and componentcount=[item3count])
..
or (item_id=[itemnid] and componentcount=[itemncount])
)

that will return all the recipes containing each of our items (with the good componentcounts). Then process backway and test all the recipes found to see if they match the items from the combine container...


I ve tested this, its longer to describe than to do. Of course, its more time consuming than the queries on the previous table, but tradeskill combines arent that frequent... another possibility, wait till subselects are implemented into mysql :)


[/u]

[Trumpcard]

You guys get a concensus on how you want me to change it, and i'll merge it in.

[Muuss]

Great news :D

Waiting for Krich and Lurker_005's post then )

[krich]

Well, you successfully identified where my SQL knowledge is lacking. You lost me on the first query. It all sounds good to me though! :P
(You can count that I'll be studying your response to try to understand it though)

Of course, its more time consuming than the queries on the previous table, but tradeskill combines arent that frequent...

Actually, the current tradeskill queries are god awful ugly (but there is no other way) and I think they don't quite work perfectly.

Regards,

krich

[Muuss]

You can count that I'll be studying your response to try to understand it though

Well, i m sure you will !
btw, if i wrote something idiotic or just wrong, don't hesitate to insult me :)

[Muuss]

I was thinking to the tables.... if that format is maintained, it could be a good idea to add a key on reciped_id/item_id on the second table, to limit the possibilities to have 2 times the same item listed in the recipe. It would facilitates the table exploitation if there's no need to check that an item could be use 2 times in the same recipe :)

[Lurker_005]

My gut tells me there ought to be a better way to make this so that the lookup is easier... but since I can't think of it what you propose looks pretty good, and it adds some needed functionality to tradeskills.

If this turns out to be slow or problematic, then we can always fall back to the flat DB table with added fields.

[Muuss]

Yeah, the lookup might be a problem.
A solution to accelerate it would be to add a numeric field to the main table containing the number of components needed for the recipe. This would seriously reduce the amount of recipes that could match a combine. In counterpart, the field has to be filled by the world builder, or automatically by the tool that edits the recipes.

[Muuss]

About the 'flat' table, what causes me a trouble is that there's no limit to the amount of items returned by a recipe, successfull or not,

So basically, we have 10 fields only for the components, with a flag saying if the item is returned on failure/success (typically, for the tools). (say 10 more fields, or even 20) : 20 or 30 fields for the components.

then we have the products, lets start with a maximum of 5 returned products, a field for the item_id : 5, a field saying how many items are returned on success, another field for the failures : 5+5 fields, for a total of 15.

Added : other skills, trivial,skill,id,neededskill.... again a few fields : ~10

we're yet at around 45/55 fields, and are limited to 5 returned items.

To this, we still have to implement the race/class/god/town limits, as we won't have any lookup troubles with this, the table i suggested still works in the case of a flat table.

All this is why i didnt suggested a flat table. My opinion is that we should avoid to implement stuff if yet when we do it, we see where the limits could be. This will prolly allow to create all the live recipes but limit custom ones.

[Muuss]

Trumpcard, would you post or PM the definitive mysql structure of the table(s) when possible plz, so i can start to work on an editor and eventually to a convertor, thankees)