98 lines
3.3 KiB
SQL
98 lines
3.3 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE dungeon (
|
|
dungeon_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE loot_table (
|
|
loot_table_id TEXT PRIMARY KEY,
|
|
rolls_min INTEGER NOT NULL DEFAULT 1 CHECK (rolls_min >= 0),
|
|
rolls_max INTEGER NOT NULL DEFAULT 1 CHECK (rolls_max >= rolls_min)
|
|
) STRICT;
|
|
|
|
CREATE TABLE boss_template (
|
|
boss_key TEXT PRIMARY KEY,
|
|
creature_entry INTEGER NOT NULL UNIQUE CHECK (creature_entry > 0),
|
|
display_name TEXT NOT NULL,
|
|
dungeon_id TEXT NOT NULL REFERENCES dungeon(dungeon_id),
|
|
loot_table_id TEXT NOT NULL REFERENCES loot_table(loot_table_id)
|
|
) STRICT;
|
|
|
|
CREATE TABLE item_template (
|
|
item_id INTEGER PRIMARY KEY CHECK (item_id > 0),
|
|
item_key TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
quality TEXT NOT NULL CHECK (quality IN ('common', 'uncommon', 'rare', 'epic', 'legendary')),
|
|
inventory_slot TEXT NOT NULL,
|
|
armor_type TEXT,
|
|
weapon_type TEXT,
|
|
source_item_level INTEGER NOT NULL CHECK (source_item_level > 0),
|
|
scalable INTEGER NOT NULL DEFAULT 1 CHECK (scalable IN (0, 1)),
|
|
source_url TEXT
|
|
) STRICT;
|
|
|
|
-- Mirrors the itemizable 3.3.5a stat vocabulary instead of flattening every
|
|
-- bonus into one prototype "power" value. Values are authored at the source
|
|
-- item level and scaled together when this game's scalable loot drops.
|
|
CREATE TABLE item_stat (
|
|
item_id INTEGER NOT NULL REFERENCES item_template(item_id) ON DELETE CASCADE,
|
|
stat_key TEXT NOT NULL,
|
|
stat_value REAL NOT NULL,
|
|
PRIMARY KEY (item_id, stat_key)
|
|
) STRICT;
|
|
|
|
CREATE TABLE item_weapon (
|
|
item_id INTEGER PRIMARY KEY REFERENCES item_template(item_id) ON DELETE CASCADE,
|
|
damage_min REAL NOT NULL CHECK (damage_min >= 0),
|
|
damage_max REAL NOT NULL CHECK (damage_max >= damage_min),
|
|
damage_school TEXT NOT NULL CHECK (
|
|
damage_school IN ('physical', 'holy', 'fire', 'nature', 'frost', 'shadow', 'arcane')
|
|
),
|
|
speed_ms INTEGER NOT NULL CHECK (speed_ms > 0)
|
|
) STRICT;
|
|
|
|
CREATE TABLE loot_table_entry (
|
|
loot_table_id TEXT NOT NULL REFERENCES loot_table(loot_table_id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES item_template(item_id),
|
|
weight INTEGER NOT NULL CHECK (weight > 0),
|
|
min_quantity INTEGER NOT NULL DEFAULT 1 CHECK (min_quantity > 0),
|
|
max_quantity INTEGER NOT NULL DEFAULT 1 CHECK (max_quantity >= min_quantity),
|
|
PRIMARY KEY (loot_table_id, item_id)
|
|
) STRICT;
|
|
|
|
CREATE INDEX boss_template_dungeon_idx ON boss_template(dungeon_id);
|
|
CREATE INDEX loot_table_entry_item_idx ON loot_table_entry(item_id);
|
|
CREATE INDEX item_stat_item_idx ON item_stat(item_id);
|
|
|
|
-- This view is the stable query surface for a future in-game loot browser.
|
|
-- The shipped public/data/healer-man-loot.sqlite file includes this view.
|
|
CREATE VIEW boss_loot_browser AS
|
|
SELECT
|
|
d.dungeon_id,
|
|
d.display_name AS dungeon_name,
|
|
b.boss_key,
|
|
b.creature_entry,
|
|
b.display_name AS boss_name,
|
|
b.loot_table_id,
|
|
lt.rolls_min,
|
|
lt.rolls_max,
|
|
i.item_id,
|
|
i.item_key,
|
|
i.display_name AS item_name,
|
|
i.quality,
|
|
i.inventory_slot,
|
|
i.armor_type,
|
|
i.weapon_type,
|
|
i.source_item_level,
|
|
i.scalable,
|
|
e.weight,
|
|
e.min_quantity,
|
|
e.max_quantity,
|
|
i.source_url
|
|
FROM boss_template b
|
|
JOIN dungeon d ON d.dungeon_id = b.dungeon_id
|
|
JOIN loot_table lt ON lt.loot_table_id = b.loot_table_id
|
|
JOIN loot_table_entry e ON e.loot_table_id = b.loot_table_id
|
|
JOIN item_template i ON i.item_id = e.item_id;
|