RecipeDB for use with crafting plugins

[Yasuko]

A while back ago, I wrote a scraper to scrape Recipe information from XIVDB and compiled that information into a sqlite db to use whenever Mastahg put crafting support into RB.. I wanted to put it here incase anyone may find it useful.

So far the DB consists of a few tables.

 Item
tableID | ItemID | ItemName | ItemLevel | RequiredLevel | StackSize | isCraftingItem

(the above item information was gathered through RBs sqlite DB)

 Recipe
tableID | ID | Name | Class | Level | AttemptLevel | Yield

(All of the recipe information was gathered by scraping XIVDB.. They list the attempt level as the same level as the recipe.. I did not notice this till after scraping was done..)

 RecipeMaterial
tableID | ReceipeID | ItemID | ItemName | ItemAmount

A few things about this table.. I just realized that I incorrectly spelt Recipe in "ReceipeID". Also to note, that at the time of putting this db together, I had not gone through and added the ItemID to each item within the RecipeMaterial table.. but you can reference ItemName to the Item table above to get the itemid for each item if needed.

This DB does not have the current 2.5 recipes in it, since it was created a while back ago, and xivdb still is not updated for 2.5 patch.. I just figured it may be useful to someone who is doing a crafting plugin to have all the recipeIDs and materials without needing to reference to XIVDB for every recipe ID..

I'm not too familiar with DB Design and I apologize if I did it incorrectly.. Please let me know if you use it and have found any errors with the recipe information.. All of this was scraped automatically and I had not gone through and verified each recipe with the site.

View attachment RecipeDB.zip

 

[Neverdyne]

I'm really interested in this but for another purpose: vendors. Do you still have your scrapper code somewhere? Do you think it can be modified to scrape vendor information, like what they sell and so forth? Do you also think it can scrape item attributes, like how much strength an item has?

If you wouldn't mind sharing this, I'm definitely interested.

 

[Yasuko]

I'm really interested in this but for another purpose: vendors. Do you still have your scrapper code somewhere? Do you think it can be modified to scrape vendor information, like what they sell and so forth? Do you also think it can scrape item attributes, like how much strength an item has?

If you wouldn't mind sharing this, I'm definitely interested.

The way I scraped information was from using fiddler to find out what post/gets were being sent to the server..

Eventually I was able to find a very basic layout page that had the information I was looking for: http://xivdb.com/modules/content/content.php?lang=1&type=recipe&args=2133

This made it much easier to use htmlagility pack to scrape through the div containers for the correct information, since if you were to go to the actual recipe url Final Fantasy XIV : A Realm Reborn (FFXIV ARR) Database you would have to program it to wait for the entire page to load.. in order for the source to be fully seen.. doing this would mean to use the webbrowser component and wait for it to call back that the page was loaded.. two issues with this way.. First, webbrowser in VS uses IE7 by default, and unless you go into the registry and set your application to specifically use IE9+ for the webbrowser control.. it wouldnt work.. The second problem with this that it would take forever to scrape each page as you are waiting for it to completely load in the webbrowser control before you can even see the information.

*edit: another reason it wouldnt work is because the webpage would not fully load due to all the script errors that would be generated by the webbrowser control

That being said.. If i were to change the Type in the url above to npc, and the args to 1003254 (which is some random NPC i looked up that had a shop) to make the url: http://xivdb.com/modules/content/content.php?lang=1&type=npc&args=1003254 ..it doesnt show any of the shop information that the npc has for sell.. just basic attributes for the npc itself.. I'm sure if i dug around a little more I could find a plain text version for the shop data though.. Other than finding the plain text version, I'm not sure how to bypass xivdb's page loading without having it take forever to scan so many npc args.

 

[kagamihiiragi17]

I'm really interested in this but for another purpose: vendors. Do you still have your scrapper code somewhere? Do you think it can be modified to scrape vendor information, like what they sell and so forth? Do you also think it can scrape item attributes, like how much strength an item has?

If you wouldn't mind sharing this, I'm definitely interested.

Get FFXIV Explorer (google it) and open up 0a0000.index, navigate to exd/item.exh for item info (the columns aren't labeled but you could easily figure out which stat each column is).

For shop info, download this sqlite database: https://dl.dropboxusercontent.com/u/38053386/Rebornbuddy/app_data.sqlite

Get a sqlite database viewer, and navigate to the Item table. Under the data column, some items have a shopnpc tag, which lists the NPC IDs of the shopkeepers that sell that item. For example, index# 2586 is item # 2663, the Stablemaid's Hat. It's sold by NPCs "shopnpc":["1001203","1003259","1003260","1003263","1000215","1000220","1000717","1000535","1002371","1001965","1004422","1003735"].

I thought there was a database of NPC IDs with Vector3s somewhere, but I can't seem to find it at the moment. Either way, that's a bit of data and you can also look through the rest of the data in the sqlite database and the 0a0000.index file, there's a bunch of fun stuff in there.

You can also find a recipe database at 0a0000.index/exd/recipe.exd, dunno if that has more info that might be useful.

EDIT: The database of NPCs with their location and Map ID is 0a0000.index/exd/level.exh. Looks like columns 0-2 are the Vector3 data, probably x, y, z but I'm not sure. No idea what 3/4 are, 5 is the NPC ID, 6/7 no idea, 8 is the map id, and 9 I have no idea. Either way, with that data you can make a pretty good database of what NPC has what items, what map they're on, where they are on that map, etc.