Implementing: Tradeskills

[John Adams]

From IRC:

[21:37] <@Jabantiz> was looking at the tradeskill stuff, any one know how the db is suppose to be set up for it
[23:56] <@Jabantiz> found 1096 crafting packets and got the recipe list filled with /test, luckily all thesearch functions are client side

I never got any sample data, because they were still working out the details. I doubt our DB tables are 100% designed at this stage. Feel free to invent some, so I can tear it apart and make it more efficient

I believe it consisted of the same types of data we see in Parser;

• recipes
  recipe_list
  recipe_details
  recipe_components
  character_recipes (the ones the player owns)

This is old thinking, Zcoretri might have had a different design. Hopefully he's back and can answer you.

[Zcoretri]

Wait a minute...time out! You got the recipe list to show in a 1096 client? I have only been able to get it to show in a 1008 client. I had been banging my head for quite some time to get recipes to show in 1096, cause the packets look the same to me from 1008 to 1096.

[Jabantiz]

I pulled a packet from a 1096 log and put it in the /test command and worked fine for me, should have an updated struct for OP_ShowCreateFromRecipeUIMsg later, identified some stuff as well as more bytes then what we had in our current struct.

[Zcoretri]

I pulled a packet from a 1096 log and put it in the /test command and worked fine for me, should have an updated struct for OP_ShowCreateFromRecipeUIMsg later, identified some stuff as well as more bytes then what we had in our current struct.

Isn't there a struct for that already in WorldStructs.xml? or are you updating it?

[Jabantiz]

Here is an updated WS_CreateFromRecipe struct for 1096

Code: Select all

  <Struct Name="WS_CreateFromRecipe" ClientVersion="1096" OpcodeName="OP_ShowCreateFromRecipeUIMsg">
    <Data ElementName="crafting_station" Type="EQ2_16Bit_String" />
    <Data ElementName="unknown1" Type="int32" Size="1" />
    <Data ElementName="recipe_name" Type="EQ2_16Bit_String" />
    <Data ElementName="tier" Type="int8" Size="1" />
    <Data ElementName="unknown3" Type="int32" Size="1" />
    <Data ElementName="product_name" Type="EQ2_16Bit_String" />
    <Data ElementName="icon" Type="int16" />
    <Data ElementName="product_qty" Type="int8" Size="1" />
    <Data ElementName="unknown5" Type="int16" Size="1" />
    <Data ElementName="unknown6" Type="int8" Size="1" />
    <Data ElementName="unknown7" Type="int16" Size="1" />
    <Data ElementName="primary_title" Type="EQ2_16Bit_String" />
    <Data ElementName="num_primary_choices" Type="int8" />
    <Data ElementName="primary_array" Type="Array" ArraySizeVariable="num_primary_choices">
      <Data ElementName="primary_component" Type="EQ2_16Bit_String" />
      <Data ElementName="primary_item_id" Type="int32" Size="1" />
      <Data ElementName="primary_icon" Type="int16" Size="1" />
      <Data ElementName="primary_total_quantity" Type="int16" Size="1" />
      <Data ElementName="primary_supply_depot" Type="int8" Size="1" />
      <Data ElementName="primary_unknown3" Type="int8" Size="1" />
    </Data>
    <Data ElementName="primary_default_selected_id" Type="int32" Size="1" />
    <Data ElementName="primary_unknown5" Type="int32" Size="1" />
    <Data ElementName="num_build_components" Type="int8" />
    <Data ElementName="build_array" Type="Array" ArraySizeVariable="num_build_components">
      <Data ElementName="build_slot" Type="int32" />
      <Data ElementName="build_title" Type="EQ2_16Bit_String" />
      <Data ElementName="build_qty" Type="int8" />
      <Data ElementName="build_unknown1" Type="int32" Size="1" />
      <Data ElementName="num_build_choices" Type="int8" Size="1" />
      <Data ElementName="build_components_array" Type="Array" ArraySizeVariable="num_build_choices">
          <Data ElementName="build_component" Type="EQ2_16Bit_String" />
          <Data ElementName="build_item_id" Type="int32" Size="1" />
          <Data ElementName="build_icon" Type="int16" Size="1" />
          <Data ElementName="build_total_quantity" Type="int16" Size="1" />
          <Data ElementName="build_supply_depot" Type="int8" Size="1" />
          <Data ElementName="build_unknown6" Type="int8" Size="1" />
        </Data>
      <Data ElementName="build_item_selected" Type="int8" Size="1" />
      <Data ElementName="build_selected_item_id" Type="int32" Size="1" />
      <Data ElementName="build_selected_item_qty" Type="int8" Size="1" />
      </Data>
    <Data ElementName="fuel_title" Type="EQ2_16Bit_String" />
    <Data ElementName="fuel_qty" Type="int8" />
    <Data ElementName="num_fuel_choices" Type="int8" Size="1" />
    <Data ElementName="fuel_component_array" Type="Array" ArraySizeVariable="num_fuel_choices">
      <Data ElementName="fuel_component" Type="EQ2_16Bit_String" />
      <Data ElementName="fuel_item_id" Type="int32" Size="1" />
      <Data ElementName="fuel_icon" Type="int16" />
      <Data ElementName="fuel_total_quantity" Type="int16" Size="1" />
      <Data ElementName="fuel_supply_depot" Type="int8" Size="1" />
      <Data ElementName="fuel_unknown4" Type="int8" Size="1" />
    </Data>
    <Data ElementName="fuel_item_selected" Type="int8" Size="1" />
    <Data ElementName="fuel_selected_item_id" Type="int32" Size="1" />
    <Data ElementName="fuel_selected_item_qty" Type="int8" Size="1" />
  </Struct>

The packet still supports the classic crafting system that lets you swap out components for others, or lower quality ones. I tried to name them as clearly as possible but if you have any questions what an element does let me know, submitting these to dev svn now

[Jabantiz]

I just commited a WS_ShowItemCreation struct to dev svn, put it on my whiteboard as well. The initial struct was made by Zcoretri, I just made a few mods to it. I believe the first unknown (int32 size=2) is actually max progress (always set to 1000), not sure why it is set twice, my best guess would be the highest progress the player has achieved on previous attempts and max progress but can't find a packet with it set to any other value. If that is in fact progress I would guess the next unknown is durability, but those values seem rather high to be that so not sure.

In the array it might actually be 2 elements with a type of EQ2_Item the Item you get and the byproduct for that stage, it is also possible that I used to many bytes for EQ2_Item, or it really isn't EQ2_item as I don't see where quantity comes in, if it is even in this packet. I am done with this struct for now though.

[Jabantiz]

I finally figured out how to get the recipes to load and display from the db (my own stupid mistakes as usual) and implemented basic functions to progress through the stages of crafting an item and finaly got the OP_ShowItemCreationProcessUIMsg to send to the client without crashing the client.


The issue was the client had to send a packet first, OP_BeginItemCreationMsg, if the client didn't send that packet first the client would just freeze and crash. Now that I got this sending I can verify the last 6 elements are infact the skills to display at the bottom of the window, I modified the packet to send skills I had added to my player.

[Jabantiz]

After geting OP_ShowItemCreationProcessUIMsg working I was able to use /test on that opcode and on OP_UpdateItemCreationProcessUIMsg and update the structs for both, mainly WS_UpdateCreateItem though.

Code: Select all

<Struct Name="WS_UpdateCreateItem" ClientVersion="1" OpcodeName="OP_UpdateItemCreationProcessUIMsg">
<Data ElementName="effect" Type="int8" Size="1" />
<Data ElementName="total_durability" Type="int32" Size="1" />
<Data ElementName="total_progress" Type="int32" Size="1" />
<Data ElementName="progress_level" Type="int8" Size="1" />
<Data ElementName="reaction_icon" Type="int16" Size="1" />
<Data ElementName="reaction_name" Type="EQ2_16Bit_String" />
<Data ElementName="durability_change" Type="sint32" Size="1" />
<Data ElementName="progress_change" Type="sint32" Size="1" />
<Data ElementName="spawn_id" Type="int32" Size="1" />
<Data ElementName="unknown" Type="int8" Size="1" />
</Struct>

Effect can have values of 0 to 4, anything higher will not show an effect, 0 is also no effect, 1 is critical success, 2 is success, 3 is failure, and 4 is critical failure. The effect will play on the spawn given by spawn_id. progress_change and durability_change are the numbers to display above the spawn, and progress_level highlights the name of the current step you are on, as well as plays the animation for when it changes (green outline for going up, red for down).

The updated structs, as well as the code to step through the process, is all on dev svn now.

[John Adams]

Wow, excellent work as usual Jabantiz. This is a long time coming, and very exciting.

[alfa]

Well done

[Jabantiz]

I believe most of the packets for crafting (minus consignment) are figured out now, so we need to figure out how we are going to store the data and how the process is going to work.

Database
The main thing we need is a way to store ingrediants for the recipe, I was originally going to suggest putting the ingrediants in the recipe table but now I believe it would be better in there own table so we can easily support multiple items for 1 component slot (like the original crafting system) this table would need recipe id, item id, quantity needed, and slot. For slot I would suggest 0 being for the primary component, 1-4 for the seconday components, and 5 for fuel.

It would probably be a good idea to have a table for reactions, probably the types of reactions each crafting profession can have and maybe the result for countering, or failing to counter them. Would also probably need a table to hold a list of all possible spells that can counter a reaction.

I am not a database guy so there is probably a better way to do the above suggestions, I think that is all the info still needed to be stored.

Code
For the actual crafting process I think it would be best to have it as 1 instance per zone (like combat and spellprocess currently are). Probably have a struct containing all the relevant info for the person crafting such as the crafter, the crafting table, the recipe, the current progress/durability, the last reaction if any and if it was countered. The actual tradeskill class would hold a list of these structs (1 for every person crafting in this zone) and send the update packets (determine progress/durability gain/loss, reactions, and so on) as well as managing the list. The tradeskill class would also be responsible for starting and ending crafting wich includes locking/unlocking inventory slots and I believe it also sets player speed to 0 on live (need to verify this).


These are just suggestions on how we can accomplish this, feedback is welcomed and feel free to suggest an entirely diffrent way to do it.

[John Adams]

Stealing tables directly from our Parser DB, here's what Zcoretri came up with for parsing Recipes. 9 times out of 10, these become our World tables, too. Feel free to tweak as needed.

Code: Select all

CREATE TABLE `recipes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` int(10) unsigned NOT NULL DEFAULT '0',
  `level` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `skill_level` smallint(5) unsigned NOT NULL DEFAULT '0',
  `technique` int(10) unsigned NOT NULL DEFAULT '0',
  `knowledge` int(10) unsigned NOT NULL DEFAULT '0',
  `device` varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `tradeskill_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `product_classes` bigint(20) unsigned NOT NULL DEFAULT '0',
  `product_icon` smallint(5) unsigned NOT NULL DEFAULT '0',
  `product_name` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `product_qty` smallint(5) unsigned NOT NULL DEFAULT '0',
  `product_item_id` int(10) NOT NULL DEFAULT '0',
  `byproduct_icon` smallint(5) unsigned NOT NULL DEFAULT '0',
  `byproduct_name` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `byproduct_qty` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `byproduct_id` int(10) NOT NULL DEFAULT '0',
  `primary_comp` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `fuel_comp_name` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `fuel_comp_qty` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `recipe_name` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `recipe_description` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'Description',
  `unknown5` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `recipe_id` (`recipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Code: Select all

CREATE TABLE `recipe_list` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` int(10) unsigned NOT NULL DEFAULT '0',
  `tier` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `level` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `icon` smallint(5) unsigned NOT NULL DEFAULT '0',
  `technique` int(10) unsigned NOT NULL DEFAULT '0',
  `knowledge` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(200) COLLATE latin1_general_ci DEFAULT 'Unknown',
  `book` varchar(200) COLLATE latin1_general_ci DEFAULT 'Unknown',
  `unknown1` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown2` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown3` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `recipe_id` (`recipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Code: Select all

CREATE TABLE `recipe_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` int(10) unsigned NOT NULL DEFAULT '0',
  `icon` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` char(200) COLLATE latin1_general_ci NOT NULL,
  `description` char(255) COLLATE latin1_general_ci NOT NULL,
  `technique` int(10) unsigned NOT NULL DEFAULT '0',
  `knowledge` int(10) unsigned NOT NULL DEFAULT '0',
  `level` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `recipe_book` char(200) COLLATE latin1_general_ci NOT NULL,
  `device` char(40) COLLATE latin1_general_ci NOT NULL,
  `unknown1` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown2` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown3` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown4` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown5` int(10) unsigned NOT NULL DEFAULT '0',
  `unknown6` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `recipe_id` (`recipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Code: Select all

CREATE TABLE `recipe_build_comps` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` int(10) unsigned NOT NULL DEFAULT '0',
  `item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `slot_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `comp_name` varchar(100) COLLATE latin1_general_ci NOT NULL DEFAULT 'Unknown',
  `comp_qty` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

If these work for you, I will make up a set with FK/Constraints for the actual tables to push out via DB Patcher.

[Zcoretri]

For now I think the recipe components table will need to be adjusted with an item_id and a slot as per Jabantiz's post above.

[John Adams]

For now I think the recipe components table will need to be adjusted with an item_id and a slot as per Jabantiz's post above.

Adjusted the last table. Is that better?

[Zcoretri]

I think we can get away with a tinyint(3) for slot, its not really an id of anything