The fields in dumpeditems.sql and what they mean

[kalipsh]

These are my notes for all the fields, made while making an item renderer. Hopefully this collecting of information in one place will be useful to others.

id field:
----------------------------------
INT(11)
Stores the ID number in Everquest of the item. Unique, unsigned, but not consecutative.

minstatus:
----------------------------------
SMALLINT(5)
Used by EqEmu for the minimum status required to summon the item.

Name:
----------------------------------
VARCHAR(64)
Name of the item.

aagi, acha, adex, aint, asta, astr, awis:
----------------------------------
INT(11)
+ ability modifiers on item. Can be +, -, or 0. (AGI, CHA, DEX, INT, STA, STR, WIS respectively).

ac:
----------------------------------
INT(11)
AC on the item, if any.

augslot1type, augslot2type, augslot3type, augslot4type, augslot5type:
----------------------------------
TINYINT(3)
Slot types for LDON augmentations. Can be numbers 0 (for no augmentation allowed at all) or 1-8.
NOTE: No items have anything other than 0 for slot 4 or 5 at this time.

avoidance:
----------------------------------
INT(11)
Avoidance value on item. Only presently set on 8 items, all PoTime.

bagsize, bagslots, bagtype, bagwr:
----------------------------------
INT(11)
All fields dealing with containers. I use bagslots > 0 to determine if the item is a container.
bagsize: Size of the objects the bag can hold (follows same pattern as size field)
bagslots: Number of slots the bag has. Even values from 0-10 presently.
bagtype: What kind of container, used for what can be combined in it, what size it is, and other SoE mysteries.
bagwr: Weight reduction percentage of bag. Values from 0-100 presently.

banedmgamt, banedmgbody, banedmgrace:
----------------------------------
INT(11)
Bane damage data. banedmgbody and banedmgrace will refer to what this is a bane against, but suffers from SoE sloppiness - only one field or the other will be set. In my internal usage, I simply look up banedmgrace if its set, or 1000+banedmgbody if its set in the BaneRaces table in the below BaneRaces.sql file.

bardtype:
----------------------------------
INT(11)
If this does not equal 0, links to a type in the below ItemTypes.sql file. This triggers the bard instrument type line in item links, and determines what bard skills are used to play it.

booktype:
----------------------------------
INT(11)
Is 1 if this is an item with attached book text file, 0 if not.

casttime:
----------------------------------
INT(11)
Cast time in milliseconds, although is used for other things. -1 is a value on a lot of items that have no effect at all. Food and drink have a number in this field as well, for how long it lasts (I believe). Other items have odd values in here. Only use as casttime if spellid > 0.

charmfile:
----------------------------------
INT(11)
Filename on the SoE servers of the text for describing what a charm does, blank if none.

classes:
----------------------------------
INT(11)
Bitfield of classes able to use this item. Bits have the following meanings:
<table>
<tr><th>Bit#<th>Bit Value<td>Class<td>Long class name
<tr><td>1<td>1<td>WAR<td>Warrior
<tr><td>2<td>2<td>CLR<td>Cleric
<tr><td>3<td>4<td>PAL<td>Paladin
<tr><td>4<td>8<td>RNG<td>Ranger
<tr><td>5<td>16<td>SHD<td>Shadowkni ght
<tr><td>6<td>32<td>DRU<td>Druid
<tr><td>7<td>64<td>MNK<td>Monk
<tr><td>8<td>128<td>BRD<td>Bard
<tr><td>9<td>256<td>ROG<td>Rogue
<tr><td>10<td>512<td>SHM<td>Sham an
<tr><td>11<td>1024<td>NEC<td>Necrom ancer
<tr><td>12<td>2048<td>WIZ<td>Wiz ard
<tr><td>13<td>4096<td>MAG<td>Magici an
<tr><td>14<td>8192<td>ENC<td>Enchan ter
<tr><td>15<td>16384<td>BST<td>Beast lord
</table>

color field:
----------------------------------
INT(11)
24-bit RGB value of the default color tint of the item (I believe.)

cr,dr,fr,mr,pr fields:
----------------------------------
INT(11)
Resist fields, can be +, -, or 0. COLD DISEASE FIRE MAGIC POISON, respectively.

damage, delay fields:
----------------------------------
INT(11)
Damage/delay numbers for weapons.

deity field:
----------------------------------
INT(11)
Bitfield of dieties a player can worship and be able to use this item. Bits have the following meanings:
<table>
<tr><th>Bit#<th>Bit Value<td>Class<td>Long class name
<tr><td>1<td>1<td>Agnostic
<tr><td>2<td>2<td>Bertoxxulous
<tr><td>3<td>4<td>Brell Serilis
<tr><td>4<td>8<td>Cazic Thule
<tr><td>5<td>16<td>Erollisi Marr
<tr><td>6<td>32<td>Bristlebane
<tr><td>7<td>64<td>Innoruuk
<tr><td>8<td>128<td>Karana
<tr><td>9<td>256<td>Mithaniel Marr
<tr><td>10<td>512<td>Prexus
<tr><td>11<td>1024<td>Quellious
<tr><td>12<td>2048<td>Rallos Zek
<tr><td>13<td>4096<td>Rocket Nife
<tr><td>14<td>8192<td>Solusek Ro
<tr><td>15<td>16384<td>The Tribunal
<tr><td>16<td>32768<td>Tunare
<tr><td>17<td>65536<td>Veeshan
</table>

effecttype:
----------------------------------
INT(11)
Determines what type of spell effect this item has, i.e. when and how it can be used.
0 = Combat
1 = Cast
2 = Worn
3 = Clickable from Inventory (ie modrod)
4 = Clickable while Worn (ie mana robe)
5 = Triggered, unlimited charges
255 = Rat Shaped Ring. No, I don't know, either.

elemdmgtype, elemdmgamt:
----------------------------------
INT(11)
Elemental damage fields, used if elemdmgamt > 0.
Elemental damage type:
1 = magic
2 = fire
3 = cold
4 = poison
5 = disease

faction*:
----------------------------------
INT(11)
I have not yet completed decoding these fields. Used by only 4 items, it isn't a terribly high priority. Obviously, these trigger "This item has a faction modifier." line in items. The 4 items are: Scalp of the Ghoul Lord, Amulet of the Haven (only one to use all 4 sets), Blessed Champion's Cape, and Planar Traveler's Manual.

filename:
----------------------------------
VARCHAR(15)
Filename on the SoE server with the book text (I believe.)

focusid:
----------------------------------
INT(11)
Spell number (from the Lucy Spells database) of the Focus Effect on this item, or 0 for none. Spells.sql file pulled from Lucy.

gmflag:
----------------------------------
INT(11)
Always 0, at least in the current item database. Field name suggests gm-only items would have a value other than 0.

hp, mana:
----------------------------------
INT(11)
+hp and +mana on this item. Can be +,-, or 0.

icon:
----------------------------------
INT(11)
Icon # for this item. I use the icon gifs from the EQ CPS system. Finding and using said icon files left as an exercise for the reader.

idfile:
----------------------------------
VARCHAR(30)
Filename on SoE servers containing the identify-spell text for this item.

itemclass:
----------------------------------
INT(11)
What type of item is this? Redundant field, but seems to be up-to-date.
0 = Normal
1 = Container
2 = Book

itemtype:
----------------------------------
INT(11)
From the ItemTypes.sql file below. Notably, itemtype=54 is an AUGMENTATION, and itemtype = 10 is armor.

hasteproclevel:
----------------------------------
INT(11)
This field, which seems to be named for haste proc % perhaps, has some really, really, really odd values in it. More investigation needed.

light:
----------------------------------
INT(11)
How much light does this item put out? Values from -1 to 15.

lore:
----------------------------------
VARCHAR(80)
Text string returned by the identify spell. Note: If prefixed by '*', this is a LORE item. See also: loreflag If prefixed by '*#', this is an ARTIFACT. See also: artifactflag. These prefixes are no longer used, and will only show on items that have not been re-packet-collected recently.

magic:
----------------------------------
INT(11)
1 if MAGIC ITEM, 0 otherwise.

material:
----------------------------------
INT(11)
Guessing a texture ID for the item.

maxcharges:
----------------------------------
INT(11)
Maximum number of charges this item comes with.

nodrop:
----------------------------------
INT(11)
255 if not nodrop, otherwise IS nodrop (values of 0 and 1 are used, the difference is...?)

norent:
----------------------------------
INT(11)
255 if not norent, otherwise IS norent (values of 0 and 1 are used, the difference is...?)

races:
----------------------------------
INT(11)
Bitfield of races able to equip/use this item. Bitfield is as follows:
<table>
<tr><th>Bit#<th>Bit Value<td>Race<td>Long race name
<tr><td>1<td>1<td>HUM<td>Human
<tr><td>2<td>2<td>BAR<td>Barbari an
<tr><td>3<td>4<td>ERU<td>Erudite
<tr><td>4<td>8<td>ELF<td>Wood elf
<tr><td>5<td>16<td>HIE<td>High elf
<tr><td>6<td>32<td>DEF<td>Dark elf
<tr><td>7<td>64<td>HEF<td>Half elf
<tr><td>8<td>128<td>DWF<td>Dwarf
<tr><td>9<td>256<td>TRL<td>Troll
<tr><td>10<td>512<td>OGR<td>Ogre
<tr><td>11<td>1024<td>HFL<td>Halfli ng
<tr><td>12<td>2048<td>GNM<td>Gno me
<tr><td>13<td>4096<td>IKS<td>Iks ar
<tr><td>14<td>8192<td>VAH<td>Vah Shir
</table>

range:
----------------------------------
INT(11)
Range modifier on bows and arrows.

reclevel:
----------------------------------
INT(11)
Recommended level, 0 if none.

recskill:
----------------------------------
INT(11)
Recommended skill level, 0 if none. This is the skill rank, the itemtype field determines the skill. Most notably, itemtype=10 is for armor, and this comes out as "Defense" in recommended skill lines. I originally had this item type as "Headwear" and others have it as "Armor". I've changed mine to "Defense" (in the .zip file below) to make using this easier.

reqlevel:
----------------------------------
INT(11)
Required level, 0 if none.

sellrate:
----------------------------------
FLOAT
No idea. None.

shielding:
----------------------------------
INT(11)
Shielding + percentage, 0 if none. PoTime stuff.

size:
----------------------------------
INT(11)
Size of the item:
0 = TINY
1 = SMALL
2 = MEDIUM
3 = LARGE
4 = GIANT
5 = ungodly giant? Only 4 items, all show GIANT.

skillmodtype, skillmodvalue:
----------------------------------
INT(11)
Skill modifiers. Skillmodtype comes from the Skills.sql file below, skill mod value is the percentage.

slots:
----------------------------------
Bitfield of what slots this item can be equiped into. Bitfield is as follows:
<table>
<tr><th>Bit#<th>Bit Value<td>Slot
<tr>1<td>1<td>CHARM
<tr>2<td>2<td>EAR
<tr>3<td>4<td>HEAD
<tr>4<td>8<td>FACE
<tr>5<td>16<td>EAR
<tr>6<td>32<td>NECK
<tr>7<td>64<td>SHOULDERS
<tr>8<td>128<td>ARMS
<tr>9<td>256<td>BACK
<tr>10<td>512<td>WRIST
<tr>11<td>1024<td>WRIST
<tr>12<td>2048<td>RANGE
<tr>13<td>4096<td>HANDS
<tr>14<td>8192<td>PRIMARY
<tr>15<td>16384<td>SECONDARY
<tr>16<td>32768<td>FINGER
<tr>17<td>65536<td>FINGER
<tr>18<td>131072<td>CHEST
<tr>19<td>262144<td>LEGS
<tr>20<td>524288<td>FEET
<tr>21<td>1048576<td>WAIST
<tr>22<td>2097152<td>AMMO
</table>

spellid:
----------------------------------
INT(11)
Spell effect on this item, from the Lucy Spells db, or 0 or -1 or -10 or 65535 for none. Keeping in mind the highest valid spell id around 4600 right now and the lowest valid spell id is 4, I just take any values less than 0 or more than 20000 and set them to 0 when I import and clean the data.

spellshield:
----------------------------------
INT(11)
Spellshield + %. PoTime items.

strikethrough:
----------------------------------
INT(11)
Strikethrough + %. PoTime items.

stunresist:
----------------------------------
INT(11)
Stun Resist + %. PoTime items.

tradeskills:
----------------------------------
INT(11)
1 if item can be used in tradeskills, 0 if not.

unknown*:
----------------------------------
INT(11)
Presently guessed at for some of these fields. Note the 'aliases' I have listed here (extradmgskill, etc) are not real - it's just how I'm referring to them elsewhere in this document until real names are assigned.

unknown002 - Always 0.

unknown004 - bazaarprice - Trader's price for an item in the bazaar. I'm guessing this is only set on items collected from the bazaar, and contains the price in cp.

unknown005 - charmstr - I believe this is the percentage of a charm's full power that is currently used. Usually 0. Only items with other values are 4 Intricate Wooden Figurines (values 10, 100, 100, and 30), Adventurer's Stone (14), and Magneta Amulet (100), most likely indicating what the various people who collected these items had.

unknown018 - No pattern detectable. SoE on crack, here. Some items have -1, most have 0, some have 1, one has 20 (Smooth Stone Chips), and sixty have 255.

unknown019 - No pattern detectable. Most items are zero, 964 are -1, and 200 are 1.

unknown020 - Guide Issue Bridles have 3 here, all other items have 0.

unknown061 - Most items have 0 here. (Red|Blue|Green|White) Sparklers, Earring of Z, Wand of Memory, and Stone of Gnoming have 344 here, Stanos' Pouch has 9, and the Vale Studded Mask, Vale Reinforced (Skullcap|Mask|Gorget|Tunic) have -1. Other Vale stuff is 0, as usual. Odd.

unknown067 - Usually 0. 8 items have different values, ranging from five items with -1, Humming Orb with 1000, Mask of Espionage with 5000, and Fisherman's Companion with 10000.

unknown069 - Always 0.

unknown070 - No pattern detectable. Only weapons have a non-0 value here, and only some weapons.

unknown076 - extradmgskill - Extra damage skill
unknown077 - extradmgamt - Extra damage amount. Look up unknown076 in skills table - when using that skill, you gain unknown077 damage.

unknown097 - ldontheme - LDoN theme an item can be bought from. 0 = Any, 1 = Guk, 2 = Miragul, 3 = Mistmoore, 4 = Rujarkian, 5=Takish
unknown098 - ldonpoints - LDoN points required to purchase
unknown099 - ldonvendor - 1 for LDoN merchant purchasable items, 0 otherwise

unknown105 - Only books and notes seem to have values here, with all other items having 0. Some have -1, others have numbers from 2 to 22 inclusive.

unknown107 - Repeat of banedmgamt, but only on some bane items. Only items with banedmgbody = 0 have values here, but not all items with banedmgbody=0 do.

unknown108 - augrestrict - Restrictions on items this augment can be put into. 0 = no restrictions or not an augment. Otherwise:
1 => "Armor Only",
2 => "Weapons Only",
3 => "1H Weapons Only",
4 => "2H Weapons Only",
5 => "1H Slash Only",
6 => "1H Blunt Only",
7 => "Piercing Only",
8 => "Hand to Hand Only",
9 => "2H Slash Only",
10 => "2H Blunt Only",
11 => "2H Pierce Only",
12 => "Bows Only",
13 => "Shields Only",

unknown113 - Always 0, so far.

unknown115 - Endurance.

casttime2:
----------------------------------
INT(11)
Looks like the casttime field, again.

augtype:
----------------------------------
INT(11)
Augmentation item type, if itemtype = 54, or Solvent slot number if typetype = 55.

weight:
----------------------------------
INT(11)
Weight in tenths of stones. (IE something that EQ says has a WT of 10.0 will have 100 here.)

comment:
----------------------------------
VARCHAR(255)
Unused, presently. At least in publicly available database.

loreflag:
----------------------------------
TINYINT(3)
Instead of the old method of * in the text, items now have a 1 here if they are a LORE ITEM, 0 if not. Note this will only be valid on recently-collected items, so you should check both. If loreflag=1, the lore will not have the * marker.

pendingloreflag:
----------------------------------
TINYINT(3)
Always 0. Lore items didnt exist when EQ was first released, the items they decided to tag as lore were marked as "Pending lore" for a few months to give people a heads up to get the duplicates out of their inventory before they were deleted. Has no effect on gameplay.

artifactflag:
----------------------------------
TINYINT(3)
Instead of the old method (which was a *# as the first two characters of lore), items now have a 1 here if they are an ARTIFACT, 0 if not. Note this will only be valid on recently-collected items, so you should check both. If artifactflag=1, the lore will not have the *# marker.

summonedflag:
----------------------------------
TINYINT(3)
Always 0 in the database, used by the emu to flag player-summoned items.

FOR ITEM RENDERERS:
-------------------------
The EQ client spits out the items for item data in the following order. Note this is only valid for non-AUGMENTATION items, augments have a different layout and different meanings for fields.

FLAGS LINE - In order of MAGIC ITEM, LORE ITEM, ARTIFACT, NO DROP, NO RENT, AUGMENTATION, CONTAINER
SLOTS LINE - If any, what slots are equipable. In order of the slots bitfield, above.
TRADESKILLS LINE - If tradeskills > 0, print out "This item can be used in tradeskills."
EXPENDABLE LINE - If item is expendable, "EXPENDABLE Charges: " (charges field)
BARD INSTRUMENT LINE - If item is a Instrument type, print out its itemtype here.
WEAPON DATA LINES - If item has damage > 0, print out "Skill: <itemtype> Atk Delay: <delay>", then "DMG: <damage>"
AC LINE - If has ac > 0, print out "AC: <ac>"
ELEMENTAL DAMAGE LINE - If elemdmgamt > 0, print out "<elemdmgtype> DMG: <elemdmgamt>"
BANE DAMAGE LINE - If banerace > 0, print out "Bane DMG: <banerace> +<banedmg>"
SKILL MODIFIER LINE - If skillmodvalue > 0, print out "Skill Mod: <skillmod> +<skillmodvalue>"
SPELL EFFECT LINE - If spellid > 0, print out "Effect: <spellid> <spell type>". Spell types may be:
"(Combat)", "(Casting Time: <time>)", "(Must Equip. Casting Time: <time>)", "(Worn)", or nothing.
<time> will be the casting time in seconds, or "Instant" if cast time is 0.
FOCUS EFFECT LINE - If focusid > 0, print out "Focus Effect: <spell name of focusid>"
FACTION MOD LINE - If any of the factionamt# are > 0, print out "This item has a faction modifier."
STAT MODIFIERS LINE - In order of: STR DEX STA CHA WIS INT AGI HP MANA
RESISTS LINE - In order of: FIRE DISEASE COLD MAGIC POISON
SHIELDLING LINE - If shielding > 0, print out "Shielding: +<shielding>%"
AVOIDANCE LINE - If avoidance > 0, print out "Avoidance: +<avoidance>"
SPELLSHIELD LINE - If spellshield > 0, print out "Spell Shield: +<spellshield>%"
STRIKETHROUGH LINE - If strikethrough > 0, print out "Strikethrough: +<strikethrough>%"
STUNRESIST LINE - If stunresist > 0, print out "Stun Resist: +<stunresist>%"
ACCURACY LINE - If accuracy > 0, print out "Accuracy: +<accuracy>"
EXTRA DMG LINE - If extradmgamt > 0, print out "<extradmgskill> DMG: <extradmgamt>"
RECOMMENDED LEVEL - If reclevel > 0, print out "Recommended level of <reclevel>."
REQUIRED LEVEL - If reqlevel > 0, print out "Required level of <reqlevel>."
RECOMMENDED SKILL LINE - If recskill > 0, print out "Recommended skill of <recskill> <itemtype>."
WEIGHT LINE - Overloaded, a bit:
If CONTAINER: "WT: <weight> Weight Reduction: <bagwr>%"
If RANGE WEAPON: "WT: <weight> Range: <range> Size: <size>"
Otherwise: "WT: <weight> Size: <size>"
CONTAINER LINE - If CONTAINER, "Capacity: <bagsolts> Size Capacity: <bagsize>"
CLASSES LINE - If all classes set, "Class: ALL", otherwise if any set, "Class: <class>". Classes in order of bitfield, above.
RACES LINE - If all races set, "Race: ALL", otherwise if any set, "Race: <race>". Races in order of bitfield, above.
DEITIES LINE - If diety > 0, print "Deity: <deity>"
AUGMENTATION SLOTS - If aug#slot > 0, print out "Slot <slot number>, type <aug#slot>: empty"

SQL FILES:
<a href="http://www.geocities.com/kalipsh/sqlfiles.zip">sqlfiles.zip</a>

CLOSING NOTES:
I fully expect my understanding of some of these fields to be wrong. Replies with corrections are appreciated!

I pull spells from the Lucy database. See <a href="http://lucy.fnord.net/phpBB2/viewtopic.php?t=56">this forum</a> for instructions on how to create the MySQL table and import the data. Hint - quickest way to import is LOAD DATA LOCAL INFILE 'spelldata.txt' INTO TABLE Spells FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;

Thanks to:
Edgar1898 for information on unknown004 and minstatus.
ric0h for information on summonedflag
ksmith for information on recskill and augrestrict.
quagmire for information on pendinglore, augrestrict, and endurance.

Also, no idea why the HTML isn't working, trying to fix...