Item database project (for all these spreadsheets)

• Disquette · 04/07/07, 2:57 PM

Hi all,

based on initial tests, I think I can have a full armory-sourced set of files with each blizzard item. My questions to you are:

would having this data in a local file format be useful to you?

If so, what file type would be most accessible?
- xml (by far the easiest for me)
- Microsoft Access
- Any format Access can export to (including tab delmited text)

If it needs to be put in a flat-ish file like an access or tab-delimited format, I'd need to know a couple things. the first and obvious ones are:
- are set bonuses necessary to capture? If so, is 5 the max number of set bonuses?
+ what are the max number of on-equip / on-use features per item?
- for example, kiss of the spider has 1 on use (haste rating) and 2 on equip spells (hit rating, crit rating). can an item have 3 of one and 0 of another? can an item have more than 3 combined?

If i could have a couple people help me retrieve data, it could probably be completely refreshed for new items and changed items within 48 hours.

I was choosing between this forum and the UI forum, but it seems more relevant here do to the theorycrafting and spreadsheets which depend on item lists.

sp00n · 04/07/07, 5:11 PM

I'd be very interested in such a file.

The file type doesn't really matter for me, though I first would have to write an XML parser to access this filetype.

As to your questions:
- Personally I do not need set bonusses.
- The "old" sets do have an 8 set item bonus (the burning crusade ones don't as they are less set pieces - at least so far).
- There can only be one "on Use" effect per item. Although there may be some items with "on Use" and "Chance on Hit" or similar.
- Having a quick look at my item list, I've found 1 item that has four "green" on Equip effects: Band of the Exorcist.
- Of course there are items without any "on Use" effects, and also those with one "on Use" and without any "on Equip" effect.

koaschten · 04/07/07, 5:35 PM

the druid melee sets have 4 set boni (2 cat, 2 bear)

• Disquette · 04/07/07, 5:54 PM

to clarify, i'm not concerned if the set bonuses happen at "8 pieces" or "5 pieces", i care about
bonus @ 2
bonus @ 4
bonus @ 6
bonus @ 8
------------
= 4 bonuses

sp00n, thanks for the info on the band of the exorcist.

Regarding both that and the file type, what I'm thinking is that if I write a converter/parser to take the xml and put it in a flat database format, you wont have to write your own parser - you just use the flat file with all the data easily accessible in fields (columns). But I need to know how to structure the database to account for things such as 4 "on equip" or "chance on hit" effects.

Here's a quick example of the blizzard xml file behind the items in the armory, to give you an idea of what we're working with:

<page globalSearch="1" lang="en_us" requestUrl="/item-tooltip.xml">
  <itemTooltip>
    <id>28963</id>
    <name>Voidheart Crown</name>
    <icon>inv_crown_01</icon>
    <overallQualityId>4</overallQualityId>

    <bonding>1</bonding>
    <classId>4</classId>
    <equipData>
      <inventoryType>1</inventoryType>
      <subclassName>Cloth</subclassName>
    </equipData>
    <damageData/>

    <bonusStamina>33</bonusStamina>
    <bonusIntellect>32</bonusIntellect>
    <armor>145</armor>
    <socketData>
      <socket color="Meta"/>
      <socket color="Red"/>
      <socketMatchEnchant>+4 Spell Hit Rating</socketMatchEnchant>

    </socketData>
    <durability current="60" max="60"/>
    <allowableClasses>
      <class>Warlock</class>
    </allowableClasses>
    <requiredLevel>70</requiredLevel>
    <bonusCritSpellRating>15</bonusCritSpellRating>

    <spellData>
      <spell>
        <trigger>1</trigger>
        <desc>Increases damage and healing done by magical spells and effects by up to 34.</desc>
      </spell>
    </spellData>
    <setData>
      <name>Voidheart Raiment</name>

      <item name="Voidheart Crown"/>
      <item name="Voidheart Gloves"/>
      <item name="Voidheart Leggings"/>
      <item name="Voidheart Mantle"/>
      <item name="Voidheart Robe"/>
      <setBonus desc="Your shadow damage spells have a chance to grant you 135 bonus shadow damage for 15 sec and your fire damage spells have a chance to grant you 135 bonus fire damage for 15 sec." threshold="2"/>
      <setBonus desc="Increases the duration of your Corruption and Immolate abilities by 3 sec." threshold="4"/>
    </setData>
  </itemTooltip>

</page>

Grizlock · 04/07/07, 9:29 PM

Perhaps you could put sets in a table with the set name (or unique id) as a primary key. You could then put the set as a foreign key in the item if it is a member of a set. If it doesn't belong to a set then you can just leave the field null.

The set table would only have the set name and bonuses, since the individual pieces can be referenced using the 1:M relationship.

*EDIT*

I really like this idea. With the data in an easily available format you could easily write a generic item ranking tool where the user could put in his own formula for item value or tweak an existing formula.

• Disquette · 04/08/07, 9:12 AM

Originally Posted by Grizlock

I really like this idea. With the data in an easily available format you could easily write a generic item ranking tool where the user could put in his own formula for item value or tweak an existing formula.

that's the idea ;-)

Copernicus · 04/09/07, 12:41 PM

In general, a flat database is more useful because the data is easier to manipulate.

I think set bonuses would not be needed normally, but a flag for which set it's part of would be useful. That way, it's up to the spreadsheet creater to handle set bonuses. The spreadsheet is also going to need to handle all the on-proc or on-use effects anyways to be a useful tool.

A few quick questions for the raw data - Do quest rewards include a "Requires level" field? Do reputation items include a "Requires reputation" field? Finally, does the database list if an item is bind on equip? Minor things, but it would be useful for setting up the standard "Easy to get items" list.

• Disquette · 04/09/07, 1:46 PM

Here are some more extracts so you can see for yourself. They address
- quest rewards
- items with lvl requirements
- items with rep requirements

level and rep requirements:

<page globalSearch="1" lang="en_us" requestUrl="/item-tooltip.xml">
  <itemTooltip>
    <id>29132</id>
    <name>Scryer's Bloodgem</name>
    <icon>inv_misc_gem_bloodstone_02</icon>
    <overallQualityId>3</overallQualityId>
    <bonding>1</bonding>
    <maxCount>1</maxCount>
    <classId>4</classId>
    <equipData>
      <inventoryType>12</inventoryType>
    </equipData>
    <damageData/>
    <requiredLevel>70</requiredLevel>
    <requiredFaction name="The Scryers" rep="6"/>
    <bonusHitSpellRating>32</bonusHitSpellRating>
    <spellData>
      <spell>
        <trigger>0</trigger>
        <desc>Increases spell damage by up to 150 and healing by up to 280 for 15 sec.</desc>
      </spell>
    </spellData>
  </itemTooltip>
</page>

note that that BoP item has the tag of "bonding" = 1.

Compare to the very first item in bliz's database:

<page globalSearch="1" lang="en_us" requestUrl="/item-tooltip.xml">
  <itemTooltip>
    <id>25</id>
    <name>Worn Shortsword</name>
    <icon>inv_sword_04</icon>
    <overallQualityId>1</overallQualityId>
    <bonding>0</bonding>
    <classId>2</classId>
    <equipData>
      <inventoryType>21</inventoryType>
      <subclassName>Sword</subclassName>
    </equipData>
    <damageData>
      <damage>
        <type>0</type>
        <min>1</min>
        <max>3</max>
      </damage>
      <speed>1.9</speed>
      <dps>1.052632</dps>
    </damageData>
    <durability current="20" max="20"/>
    <requiredLevel>1</requiredLevel>
  </itemTooltip>
</page>

where bonding = 0.

So, no soulbinding = 0, BoP = 1, BoE = 1, and BoU = 3 (I checked with a cat carrier)

Yes, BoP and BoE are listed as the same :-\

Copernicus · 04/09/07, 2:46 PM

Wierd, which BoE item do you see as having bonding=1?

Just looking through the database, I grabbed a couple of items (now that I have an idea of what to look for)-

Two simple items, one is the BoE Black Velvet Robes (with bonding=2) and the other is the BoP quest reward of Yeti Fur Cloak (with bonding=1 and no requiredlevel tag).

<page globalSearch="0" lang="en_gb" requestUrl="/item-tooltip.xml">
  <itemTooltip>
    <id>2800</id>
    <name>Black Velvet Robes</name>
    <icon>inv_chest_cloth_48</icon>
    <overallQualityId>3</overallQualityId>
    <bonding>2</bonding>
    <classId>4</classId>
    <equipData>
      <inventoryType>20</inventoryType>
      <subclassName>Cloth</subclassName>
    </equipData>
    <damageData/>
    <bonusIntellect>12</bonusIntellect>
    <bonusStamina>5</bonusStamina>
    <armor>44</armor>
    <durability current="75" max="75"/>
    <requiredLevel>21</requiredLevel>
  </itemTooltip>
</page>

<page globalSearch="0" lang="en_gb" requestUrl="/item-tooltip.xml">
  <itemTooltip>
    <id>2805</id>
    <name>Yeti Fur Cloak</name>
    <icon>inv_misc_cape_04</icon>
    <overallQualityId>2</overallQualityId>
    <bonding>1</bonding>
    <classId>4</classId>
    <equipData>
      <inventoryType>16</inventoryType>
    </equipData>
    <damageData/>
    <bonusAgility>6</bonusAgility>
    <bonusSpirit>3</bonusSpirit>
    <armor>23</armor>
  </itemTooltip>
</page>

As a side note, it's nice to see that the "unique" tag is there, with Scryer's Bloodgem.

• Disquette · 04/09/07, 4:41 PM

apparently i misread. I thought I'd looked at myrmidon's signet earlier, but I just pulled it up again, and it is indeed bonding type 2. So, i guess we're good to go.

• Lurchington · 04/09/07, 4:46 PM

For crafted stuff, it'd be nice to make sure the requirements for a profession and/or a specialization are preserved. Both for the equipping of the item, and for the set bonuses. This is in the same spirit as copernicus's question.

I'd definitely update the hunter_list to be compliant with this, although most of the functionality of it is a bit deprecated by now.

Copernicus · 04/09/07, 5:06 PM

If an item has a requirement to equip it, it will be listed. Spellfire, for example, has

<requiredAbility>Spellfire Tailoring</requiredAbility>

Set bonuses are a seperate beast, because the database needs to be relatively flat and easy to export and use. It would be difficult to interpet sets within the raw database. It should be possible to include a tag to the set's name, the set bonuses, and requirements to use the set- but any interpetation of a set would come from the spreadsheet's creator.

Grizlock · 04/09/07, 5:45 PM

How is the data coming? If you still need somebody to help you pull data I have a PC sitting idle I could use to run the extraction.

• Disquette · 04/09/07, 5:49 PM

my thought on the set bonuses, like the "on equips", is if I know that there are a max number of bonuses, I might just have 4 fields, of which all might be blank. Not quite sure yet. I just hate losing data, but at the same time, I dont want to be responsible for a heftier scripting project than I already have.

If anyone wants a zip of the xml files, PM me and I can send you the link of everything (up through patches - the list is currently 20K files (items) totalling about 11MB).

edit- thanks griz. I now have all the data, but currently need to write the parser and database populater. For reference, it took about 16 hours to get everything. I'm thinking my approach will be this:

- download the xmls
- filter using windows/linux "search within"/grep to find all items where [inventoryType]NotZero[/inventoryType]
- once those files are in a folder, further refine using the same method to get [<overallQualityId]2 or higher[/overallQualityId] (greens/blues/purps)
- then run the parsing / database-inclusion on that.
- strip out the xml header lines using some utility
- join all the files into one big file

everything above can be done with non-custom-written scripts, so that should save time. After we're down to that subset of items (5000?), I'll run the parser/database-inserter

I figure the parsing script will take the longest computer time, hence that strat. Before I can write it, I'll have to figure out all the possible fields, which I'm assuming I can do by a sql query that groups that big flat file by line, after separating everything up to the first greaterthan sign into a 2nd column.

• Lurchington · 04/09/07, 6:48 PM

Originally Posted by Disquette

If anyone wants a zip of the xml files, PM me and I can send you the link of everything (up through patches - the list is currently 20K files (items) totalling about 11MB).

pm the link to me and I can put it on my hosting, my python skills can use the work anyway.

• Lurchington · 04/09/07, 10:46 PM

link received and downloaded.

Good suggestion on the part of Disquette to post any parsing efforts so as not to duplicate work.

I think I'd like to look at a method of implementing some of wowhead's more useful filtering options, and make that part of the conversion to flat file. For example armor type, usable by, required level, item level.

edit: this will be primarily something I proscrastinate with during work, so anything that happens will come about during that time.

• Disquette · 04/10/07, 8:36 AM

Yeah, to follow what lurchington said, please use this thread to let people know how far you are in the parsing creator. We have 3 or more people working on this individually. Alternatively, if we dont want to bog down this thread/forum with extra posts, anyone that wants can email me (disco at discofiend dot com) and I'll get us all on the same email group.

Grizlock · 04/10/07, 9:15 PM

I have a script running to parse each file, and create a tag->value combination for every element in the xml file. Once I have every possible tag, I'm going to run a script to generate a flat file with every tag as a column, and then parse every file again to place each value in the correct field.

Actually I'm starting to regret not combining it all into one pass since there are so many files. Nevertheless, I'll let everyone know when I get it all into a flat file database.

• Disquette · 04/10/07, 10:16 PM

Hey man, awesome to hear. If you still have time to stop it (dont know if you're talking hours or just minutes), you might want to grab just the last 3k files or so. Those are the ones added to the game most recently, and especially if you also filtered out non-equipable and only greens or better, i'm guessing you get every tag already.

I'm interested in how you're accommodating things such as the multiple "on use" bonuses.

• Lurchington · 04/10/07, 11:05 PM

I'm not nearly as familiar with XML syntax as I probably should be. After fiddling with it for a good amount, I think I might be able to more easily jump in when it's a flat file.

Any chance of posting the scripts after it completes? I'm curious to see how you handled some of the stuff I ran into.

edit: oops. didn't refresh for a bit, and caught disquette's post

• Disquette · 05/02/07, 9:35 AM

There was a good bit of discussion going on here, which dropped to nothing after April 11th. Did anyone get a full database parser running?

If not, I'll write one, but I don't want to reinvent the wheel.

The Iron Colonel · 05/02/07, 9:52 AM

I'm not currently developing a spreadsheet; however, in discussions regarding Cheeky's sheet he alluded to a master/standardized gear list that the numerous spreadsheets for various classes could reference. I don't know if the parser could be configured to create tab-delimited files (which could be converted easily to XLS), but I thought I would bring it up here to show that there is at least some interest in this type of project.

Farstrider · 05/02/07, 10:27 AM

XML is usable by excel, correct?

I think that most people would want to set up some sort of lookup for an items stats within a spreadsheet, so an XML that I could export into an excel worksheet, or even just a giant pivot table, would be amazing.

Stirius · 05/02/07, 2:16 PM

I have written a tool in dot.net that parses some basic stats from http://wow.allakhazam.com/dyn/items/iname1.html etc.. I simply wget these 20 .html pages (see the overview at http://wow.allakhazam.com/dyn/items/index.html), copy these into one file and stick it into my parser which outputs an xml-file with all items available in wow. The file looks like this:

  <Table1>
    <Name>Ata'mal Crown</Name>
    <ID>30922</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>114</Armor>
    <Intellect>16</Intellect>
    <Spirit>14</Spirit>
    <Stamina>21</Stamina>
    <SpellDmg>46</SpellDmg>
  </Table1>

It currently exports only these attributes visible in the example, because these are the only stats i needed as a mage :-)
The final .xml-file is about 2.5mb large and can be directly read into excel, where i budgeted my stats and finally got a score for each item.
If anyone is interested, let me know (the .xml or the parser source). Adding additional stats like strength is no problem at all, set-boni and special stuff (e.g. trinkets) is more difficult so i didn't bother.

• Disquette · 05/02/07, 3:37 PM

I would love to have a copy of the parser source! Also, I'm interested in how you put this in excel. When i save the code snippet you provided as an xml file and opened with Excel, I simply got a cell with the values entered as text.

Is there a correct way to get this data into excel so that each attribute is busted out into its own column?