D2R Data Database Guide

Database: ~/d2data/d2data.db (422 MB) Schema: Most tables use (key TEXT, data TEXT) where data is JSON. Use json_extract() to query fields.

---

Quick Stats

• 433 unique items, 140 set items, 35 sets, 99 complete runewords
• 692 base items (217 armor + 306 weapons + 169 misc)
• 688 magic prefixes, 779 magic suffixes
• 284 property codes
• 227 cube recipes
• 12,383 string translations

---

Table Relationship Map

                    ┌─────────────┐
                    │  uniqueitems │──(code)──┐
                    └─────────────┘           │
                    ┌─────────────┐           ▼
                    │  setitems    │──(item)──▶┌──────────────────┐
                    └─────────────┘           │  armor / weapons  │
                           │                  │  / misc (= items) │
                      (set)│                  └────────┬─────────┘
                           ▼                           │
                    ┌─────────────┐              (type)│
                    │    sets     │                    ▼
                    └─────────────┘           ┌──────────────┐
                                              │  itemtypes   │
                    ┌─────────────┐           └──────┬───────┘
                    │   runes     │──(itype1,   (Equiv1/Equiv2)
                    │ (runewords) │   itype2)──▶  ▲ walks up
                    └──────┬──────┘            hierarchy
                   (Rune1..│Rune4)
                           ▼
                    ┌─────────────┐
                    │    gems     │──(weaponMod/helmMod/shieldMod)
                    │ (gems+runes)│    ◄── gemapplytype selects column
                    └─────────────┘

┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│ magicprefix  │    │ magicsuffix  │    │  automagic   │
│(itype1..7)───┼───▶│(itype1..7)───┼───▶│ (class mods) │
└──────────────┘    └──────────────┘    └──────────────┘
       │                   │
       └───(mod1code)──────┘──────▶┌──────────────┐
                                   │  properties  │
       ┌───(prop1..prop9)─────────▶│  (code→stat) │
       │                           └──────┬───────┘
  uniqueitems/setitems/                   │(stat1)
  runes/cubemain                          ▼
                                   ┌──────────────┐
                                   │ itemstatcost  │
                                   │(stat→display) │
                                   └──────────────┘

┌──────────────┐         ┌──────────────┐
│   cubemain   │         │  v_strings   │
│(input→output)│         │(code→name)   │
└──────────────┘         └──────────────┘
  references item              ▲
  codes & type codes           │
                        allstrings_eng
                        (12,383 entries)

---

Key Lookup Chains

For a Unique Item (e.g. Harlequin Crest)

1. uniqueitems → get code (base item code, e.g. “uap”), plus prop1..prop9 (properties)
2. armor/weapons/misc → lookup by code → get defense, requirements, type, gemsockets, gemapplytype, normcode/ubercode/ultracode (tier codes)
3. itemtypes → lookup by type (e.g. “helm”) → get Equiv1/Equiv2 (parent types), BodyLoc1, MaxSockets1/2/3 by ilvl thresholds
4. Walk up Equiv1/Equiv2 chain: helm → armo (Any Armor) → root
5. v_strings → translate any item code to human-readable name
6. properties → translate prop1..prop9 codes to tooltips and stat names
7. cubemain → search for upgrade/socket recipes matching this item type
8. gems → use gemapplytype (0=weapon, 1=helm, 2=shield) to determine which socketing column applies

For a Runeword (e.g. Spirit)

1. runes → find by *Rune Name, get itype1/itype2 (allowed base types), Rune1..Rune4, T1Code1..T1Code7 (runeword properties)
2. gems → lookup each rune code → see weaponMod/helmMod/shieldMod columns for individual rune properties
3. v_strings → translate rune codes (r07→“Tal Rune”)
4. armor/weapons → find bases matching allowed types with enough sockets
5. itemtypes → walk hierarchy to find ALL subtypes of allowed types (e.g. “shld” includes shie, ashd, head, grim, pala types)

For a Set (e.g. Tal Rasha’s Wrappings)

1. setitems → query all pieces by set field → get item (base code), prop1..prop9, aprop1a..aprop5a (partial set bonuses)
2. sets → query by index → get PCode2a..PCode5a (partial bonuses), FCode1..FCode8 (full set bonuses)
3. armor/weapons/misc → lookup each piece’s base item code

For Magic/Rare Affixes

1. magicprefix/magicsuffix → filter by itype1..itype7 matching item type or its ancestors
2. Exclude by etype1..etype5
3. mod1code..mod3code → the actual properties applied
4. properties → translate mod codes to descriptions
5. group field → affixes in the same group are mutually exclusive

---

Key Field Mappings

gemapplytype (armor/weapons)

• 0 = weaponMod (weapons)
• 1 = helmMod (helms, armor, boots, gloves, belts)
• 2 = shieldMod (shields)

Cube Recipe Input Format

• "weap,bas,uni" = weapon + base quality + unique quality
• "armo,exc,uni" = armor + exceptional tier + unique
• "helm,nor,nos" = helm type + normal tier + not socketed
• "any,rar,nos" = any type + rare quality + not socketed
• "gem2,qty=3" = standard gems, quantity 3

Cube Recipe Output Format

• "useitem,mod,exc" = keep input item, upgrade to exceptional
• "useitem,mod,eli" = keep input item, upgrade to elite
• "useitem,sock=1" = add 1 socket to input item
• "useitem,uns" = unsocket the item
• "usetype,mag" = reroll as magic item of same type

Item Tier Codes (armor/weapons)

• normcode = normal tier code (e.g. “cap”)
• ubercode = exceptional tier code (e.g. “xap”)
• ultracode = elite tier code (e.g. “uap”)

Property Fields

• properties.code → property code used everywhere (e.g. “allskills”, “cast3”)
• properties.*Tooltip → human-readable description
• properties.stat1 → maps to itemstatcost.Stat for display formatting
• properties.func1 → function type that determines how min/max/par are interpreted

---

Important Views

• v_strings = allstrings_eng (code→name, 12,383 translations)
• v_items = combined armor+weapons+misc with translated names
• v_unique_items = uniqueitems with extracted key fields
• v_monster_resists_hell = monster resists in Hell difficulty

---

Useful Tables Not Yet Fully Explored

• treasureclassex / tcprecalc — treasure class definitions and precalculated drop rates
• tcprecalc_droprate — (tc_name, player_count, droprate) — 11,216 entries
• tcprecalc_counts — item drop counts per TC
• monstats / monstats2 — monster definitions, stats, AI
• levels — area/zone definitions
• skills / skilldesc — skill definitions and descriptions
• charstats — base class stats (str, dex, etc per level)
• states — buff/debuff state definitions
• itemratio — quality ratios for item generation (how likely unique vs rare vs magic)
• rareprefix / raresuffix — name fragments for rare items (not properties, just names)
• superuniques — super unique monsters (Baal, Mephisto, etc.)
• experience — XP tables