tradeskillrecipe table
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 |
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/forum...pic.php?t=3802 Quote:
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] |
Re: tradeskillrecipe table
Quote:
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. |
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. |
Quote:
see http://www.everquestserver.com/forum...pic.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 |
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 Code:
mysql> show columns from tradeskill_recipe; 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 Code:
mysql> show columns from tradeskill_recipe_entries; 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 Code:
mysql> show columns from tradeskill_recipe_limits; 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 :) |
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; |
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 |
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 :) |
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.
|
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? |
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 :? |
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 |
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 : Code:
select tradeskill_recipe.id from tradeskill_recipe,tradeskill_recipe_entries where tradeskill_recipe.id=tradeskill_recipe_entries.recipe_id and item_id=[item1id] and componentcount=[item1nb] if there's more than 1 answer, we test them with the second item point A Code:
select tradeskill_recipe.id from tradeskill_recipe,tradeskill_recipe_entries where tradeskill_recipe.id=tradeskill_recipe_entries.recipe_id and tradeskill_recipe.id=[answer1] and item_id=[item1id] and componentcount=[item1nb] 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 : Code:
select distinct tradeskill_recipe.id 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] |
You guys get a concensus on how you want me to change it, and i'll merge it in.
|
Great news :D
Waiting for Krich and Lurker_005's post then ) |
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) Quote:
Regards, krich |
Code:
You can count that I'll be studying your response to try to understand it though btw, if i wrote something idiotic or just wrong, don't hesitate to insult me :) |
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 :)
|
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. |
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. |
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. |
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)
|
All times are GMT -4. The time now is 05:39 PM. |
Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.