Elitist Jerks - Item database project (for all these spreadsheets)

Page 1 of 6

Show 15 post(s) from this thread on one page

Elitist Jerks (http://elitistjerks.com/forums.php)

- Class Mechanics (http://elitistjerks.com/f31/)

- - Item database project (for all these spreadsheets) (http://elitistjerks.com/f31/t10809-item_database_project_all_these_spreadsheets/)

Disquette

04/07/07 1:57 PM

Item database project (for all these spreadsheets)

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 4: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 4:35 PM

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

Disquette

04/07/07 4: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:

Code:

<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 8: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 8:12 AM

Quote:

Originally Posted by Grizlock (Post 321014)

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 11:41 AM

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 12: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:

Code:

<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:

Code:

<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 1: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).

Code:

<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>

Code:

<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 3: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 3: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 4:06 PM

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

Code:

<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 4: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 4: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 5:48 PM

Quote:

Originally Posted by Disquette (Post 322074)

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.

All times are GMT -4. The time now is 6:57 AM.

Page 1 of 6

Last »

Show 15 post(s) from this thread on one page