Devlog #37 - Mining & Recipes

For issues related to Patreon please DM me directly on Patreon.
For bugs in the game or website please post to #bugs on Discord

https://discord.gg/fantasyonline2

Mining has arrived in patch #9!

https://www.patreon.com/posts/patch-9-mining-92127223

Now that we having a Mining Tool and Iron Veins that drop Iron items I think we need to setup the core of the crafting system. That way we can all clear up our inventories after a long day of mining in the caves!

First we need to design the database table for RecipeDefinitions just like we've done before for MobDefinitions, ItemDefinitions, etc. Each recipe will have an id, a type, a level requirement, a coin cost, an xp reward, an array of ingredient item definition ids, an array of result item definition ids, a time to craft, and a gem cost to finish the crafting process immediately. 

That design leads to something like this:

CREATE TABLE RecipeDefinitions (

 Id int(10) unsigned NOT NULL AUTO_INCREMENT,

 Type tinyint(3) unsigned NOT NULL,

 LevelRequirement int(10) unsigned NOT NULL,

 CoinCost int(10) unsigned NOT NULL,

 XPReward int(10) unsigned NOT NULL,

 IngredientItemDefinitionIds json NOT NULL,

 ResultItemDefinitionIds json NOT NULL,

 TimeToFinish int(10) unsigned NOT NULL,

 GemCost int(10) unsigned NOT NULL,

 PRIMARY KEY (Id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Now we can add our first recipe! 

INSERT INTO RecipeDefinitions (Id, Type, LevelRequirement, CoinCost, XPReward, IngredientItemDefinitionIds, ResultItemDefinitionIds, TimeToFinish, GemCost) VALUES

(1, 0, 10, 10, 10, '[197, 197]', '[198]', 10, 1);

Iron Ore has id 197 and Iron Ore Chunk has id 198. So the recipe above requires level 10, costs 10 coins, gives 10 xp, requires two stacks of Iron Ore, and results in one Iron Ore Chunk. The recipe takes 10 minutes to complete and can be finished immediately for 1 Gem. This is the exact first recipe from FO. I think the design is still great so this is how crafting will work in FO2.

Now that we have the recipe definitions table setup we need a place to hold recipes that are cooking! 

CREATE TABLE RecipesCooking (

 Id int(10) unsigned NOT NULL AUTO_INCREMENT,

 CharacterId int(10) unsigned NOT NULL,

 RecipeDefinitionId int(10) unsigned NOT NULL,

 Created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

 Finished datetime DEFAULT NULL,

 PRIMARY KEY (Id),

 KEY fk_recipescooking_character (CharacterId),

 KEY fk_recipescooking_recipedefinition (RecipeDefinitionId),

 CONSTRAINT fk_recipescooking_character FOREIGN KEY (CharacterId) REFERENCES Characters (Id),

 CONSTRAINT fk_recipescooking_recipedefinition FOREIGN KEY (RecipeDefinitionId) REFERENCES RecipeDefinitions (Id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

So when you start crafting something a row will be added to the RecipesCooking table. As you can see this is per character. Nice. Now comes the time consuming parts. Loading it on the server, sending it to the client, and making the crafting UI.

First we'll load it on the server. Getting the data from MySQL to Java is a process called Object–relational mapping. It's just mapping a MySQL row to a Java Object. There are a ton of Java libraries out there than can do it "automatically" but I've never been satisfied with them. The "automatically" part usually involves a lot of configuration that's annoying and easy to break. They also add memory overhead and caching mechanisms that don't always work how I want them to. For these reasons I just query MySQL directly using the SmartfoxServer database manager and then fill out matching Java Objects myself.

So in Java we execute the query:

dbManager.executeQuery("SELECT * FROM RecipeDefinitions", new Object[] )

And from there we have an ISFSArray which is an interface to an SFSArray which is Smarfox's generic array container. When we query the database using Smartfox, Smartfox then maps the query to an SFSArray full of SFSObjects which can contain many different kinds of primitive types.

So then we iterate the SFSArray and extract data from the SFSObjects and put it into ReceipeDefinition class instances:

public final class RecipeDefinition

public final int id;

public final int t;

public final int lr;

public final int cc;

public final int xp;

public final String iids;

public final String rids;

public final int ttf;

public final int gc;

RecipeDefinition(ISFSObject recipeDefinition)

id = recipeDefinition.getInt("Id");

t = recipeDefinition.getInt("Type");

lr = recipeDefinition.getInt("LevelRequirement");

cc = recipeDefinition.getInt("CoinCost");

xp = recipeDefinition.getInt("XPReward");

iids = recipeDefinition.getUtfString("IngredientItemDefinitionIds");

rids = recipeDefinition.getUtfString("ResultItemDefinitionIds");

ttf = recipeDefinition.getInt("TimeToFinish");

gc = recipeDefinition.getInt("GemCost");

Simple as that. Now we've successfully pulled the recipe data from MySQL and put it into memory in Java. This Object–relational mapping process happens only once when the server starts. It allows for all the data to be easily and quickly accessible by the game code.

You might be wondering why the Java Object member variable names are so short. This is my way of reducing the size of the JSON that will be sent to the client. You see, this class RecipeDefinition will need to be further converted to JSON using the Jackson ObjectMapper class.

Here is an example of an ItemDefinition which is sent to the client if you have a Standard Pickaxe in your inventory. 

    "id": 196,

    "sfn": "weapon-pickaxe",

    "ty": 2,

    "st": 7,

    "q": 0,

    "lr": 20,

    "sr": "",

    "bt": 0,

    "sta": ""mnd": 1, "mxd": 2, "atkr": 25, "atks": 1000",

    "ss": 0,

    "vbc": 0,

    "vbp": 0,

    "vsc": 1,

    "vsp": 10,

    "t":

        "en":

            "n": "Standard Pickaxe",

            "d": "A Miner's best friend."

        

    

So this is what the result of sending a Java class through the Jackson ObjectMapper is. That's how a few people have been able to make apps and websites that show all the items in the game. They just capture this JSON from the network traffic when the game client loads.

From here we now need to implement the user messages related to cooking our recipe definitions. We need get recipe by type, start cooking a recipe, and force finish a recipe.

We also need to setup the crafting window on the client. Let's make a base window and work from there.

In the upcoming week I'll be filling this window out and implementing the crafting related server side messages. Then all we'll need is a crafting npc, and to hook it all up and test. Iron crafting coming soon!

There will be another one time Gem reward event coming up on Thanksgiving which is on November 23rd this year. I will post the amounts for each Gamer tier once that has been decided. The Gem reward amounts will be the largest yet.

Don't forget to vote for the December Patreon exclusive item!
https://www.patreon.com/posts/which-exclusive-92116547

Have Fun & Keep Gaming!

See you next patch!