291 lines
9.5 KiB
SQL
291 lines
9.5 KiB
SQL
/*
|
|
============================================================
|
|
Stats
|
|
============================================================
|
|
*/
|
|
CREATE TABLE stats (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`luck` INTEGER NOT NULL DEFAULT 0,
|
|
`armor` INTEGER NOT NULL DEFAULT 0,
|
|
`focus` INTEGER NOT NULL DEFAULT 0,
|
|
`power` INTEGER NOT NULL DEFAULT 0,
|
|
`resist` INTEGER NOT NULL DEFAULT 0,
|
|
`accuracy` INTEGER NOT NULL DEFAULT 0,
|
|
`ferocity` INTEGER NOT NULL DEFAULT 0,
|
|
`precision` INTEGER NOT NULL DEFAULT 0,
|
|
`toughness` INTEGER NOT NULL DEFAULT 0,
|
|
`penetration` INTEGER NOT NULL DEFAULT 0
|
|
) STRICT;
|
|
|
|
/*
|
|
============================================================
|
|
Characters
|
|
============================================================
|
|
*/
|
|
CREATE TABLE characters (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`user_id` INTEGER NOT NULL,
|
|
`name` TEXT NOT NULL UNIQUE,
|
|
`title_id` INTEGER NOT NULL DEFAULT 1,
|
|
`level` INTEGER NOT NULL DEFAULT 1,
|
|
`xp` INTEGER NOT NULL DEFAULT 0,
|
|
`xp_to_level` INTEGER NOT NULL DEFAULT 100,
|
|
`hp` INTEGER NOT NULL DEFAULT 20,
|
|
`m_hp` INTEGER NOT NULL DEFAULT 20,
|
|
`mp` INTEGER NOT NULL DEFAULT 10,
|
|
`m_mp` INTEGER NOT NULL DEFAULT 10,
|
|
`tp` INTEGER NOT NULL DEFAULT 1,
|
|
`m_tp` INTEGER NOT NULL DEFAULT 1,
|
|
`stats_id` INTEGER NOT NULL,
|
|
`inv_slots` INTEGER NOT NULL DEFAULT 10,
|
|
`att_points` INTEGER NOT NULL DEFAULT 0,
|
|
`bio` TEXT DEFAULT ''
|
|
);
|
|
CREATE INDEX idx_characters_user_id ON characters (`user_id`);
|
|
|
|
CREATE TABLE equipped_items (
|
|
`char_id` INTEGER NOT NULL,
|
|
`head` INTEGER NOT NULL DEFAULT 0,
|
|
`chest` INTEGER NOT NULL DEFAULT 0,
|
|
`boots` INTEGER NOT NULL DEFAULT 0,
|
|
`hands` INTEGER NOT NULL DEFAULT 0,
|
|
`m_hand` INTEGER NOT NULL DEFAULT 0,
|
|
`o_hand` INTEGER NOT NULL DEFAULT 0,
|
|
`rune` INTEGER NOT NULL DEFAULT 0,
|
|
`ring` INTEGER NOT NULL DEFAULT 0,
|
|
`amulet` INTEGER NOT NULL DEFAULT 0,
|
|
`stats_id` INTEGER NOT NULL,
|
|
`max_hp` INTEGER NOT NULL DEFAULT 0,
|
|
`max_mp` INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_equipped_items_char_id ON equipped_items (`char_id`);
|
|
|
|
CREATE TABLE inventory_items (
|
|
`char_id` INTEGER NOT NULL,
|
|
`item_id` INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX idx_inventory_items_char_id ON inventory_items (`char_id`);
|
|
|
|
CREATE TABLE banked_items (
|
|
`char_id` INTEGER NOT NULL,
|
|
`item_id` INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX idx_banked_items_char_id ON banked_items (`char_id`);
|
|
CREATE INDEX idx_banked_items_item_id ON banked_items (`item_id`);
|
|
|
|
CREATE TABLE wallets (
|
|
`char_id` INTEGER NOT NULL,
|
|
`silver` INTEGER NOT NULL DEFAULT 10
|
|
);
|
|
CREATE INDEX idx_wallets_char_id ON wallets (`char_id`);
|
|
|
|
CREATE TABLE bank_accounts (
|
|
`char_id` INTEGER NOT NULL,
|
|
`slots` INTEGER NOT NULL DEFAULT 5,
|
|
`silver` INTEGER NOT NULL DEFAULT 0,
|
|
`tier` INTEGER NOT NULL DEFAULT 0,
|
|
`can_collect` INTEGER NOT NULL DEFAULT 1,
|
|
`last_collect` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_bank_char_id ON bank_accounts (`char_id`);
|
|
|
|
/*
|
|
============================================================
|
|
Blog
|
|
============================================================
|
|
*/
|
|
create table blog (
|
|
`id` integer primary KEY AUTOINCREMENT,
|
|
`author_id` integer not null,
|
|
`title` TEXT not null,
|
|
`slug` TEXT not null unique,
|
|
`content` TEXT not null,
|
|
`created` DATETIME default current_timestamp,
|
|
`updated` DATETIME default current_timestamp
|
|
);
|
|
CREATE INDEX idx_blog_author_id ON blog (`author_id`);
|
|
CREATE INDEX idx_blog_slug ON blog (`slug`);
|
|
|
|
/*
|
|
============================================================
|
|
World
|
|
============================================================
|
|
*/
|
|
CREATE TABLE towns (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`x` INTEGER NOT NULL,
|
|
`y` INTEGER NOT NULL,
|
|
`type` INTEGER NOT NULL,
|
|
`lore` TEXT NOT NULL,
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_towns_location ON towns (`x`, `y`);
|
|
|
|
CREATE TABLE character_locations (
|
|
`char_id` INTEGER NOT NULL,
|
|
`x` INTEGER NOT NULL,
|
|
`y` INTEGER NOT NULL,
|
|
`currently` INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_character_locations_char_id ON character_locations (`char_id`);
|
|
CREATE INDEX idx_character_locations_location ON character_locations (`x`, `y`);
|
|
|
|
/*
|
|
============================================================
|
|
Items
|
|
============================================================
|
|
*/
|
|
CREATE TABLE items (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`type` TEXT NOT NULL DEFAULT 0,
|
|
`subtype` INTEGER NOT NULL DEFAULT 0,
|
|
`rarity` INTEGER NOT NULL DEFAULT 0,
|
|
`forged` INTEGER NOT NULL DEFAULT 0,
|
|
`quality` INTEGER NOT NULL DEFAULT 0,
|
|
`value` INTEGER NOT NULL DEFAULT 0,
|
|
`consumable` INTEGER NOT NULL DEFAULT 0,
|
|
`duration` INTEGER NOT NULL DEFAULT 0,
|
|
`durability` INTEGER NOT NULL DEFAULT 0,
|
|
`m_durability` INTEGER NOT NULL DEFAULT 0,
|
|
`stats_id` INTEGER NOT NULL,
|
|
`reqs` TEXT NOT NULL DEFAULT "",
|
|
`traits` TEXT NOT NULL DEFAULT "",
|
|
`lore` TEXT NOT NULL DEFAULT "",
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
/*
|
|
============================================================
|
|
Titles
|
|
============================================================
|
|
*/
|
|
DROP TABLE IF EXISTS titles;
|
|
CREATE TABLE titles (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL DEFAULT 'Title',
|
|
`lore` TEXT
|
|
);
|
|
|
|
DROP TABLE IF EXISTS owned_titles;
|
|
CREATE TABLE owned_titles (
|
|
`char_id` INTEGER NOT NULL,
|
|
`title_id` INTEGER NOT NULL,
|
|
`awarded` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_owned_titles_char_id ON owned_titles (`char_id`);
|
|
CREATE INDEX idx_owned_titles_owned ON owned_titles (`char_id`, `title_id`);
|
|
|
|
/*
|
|
============================================================
|
|
Blueprints
|
|
============================================================
|
|
*/
|
|
CREATE TABLE item_blueprints (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`type` TEXT NOT NULL DEFAULT 0,
|
|
`subtype` INTEGER NOT NULL DEFAULT 0,
|
|
`rarity` INTEGER NOT NULL DEFAULT 0,
|
|
`value` INTEGER NOT NULL DEFAULT 0,
|
|
`consumable` INTEGER NOT NULL DEFAULT 0,
|
|
`duration` INTEGER NOT NULL DEFAULT 0,
|
|
`durability` INTEGER NOT NULL DEFAULT 0,
|
|
`stats_id` INTEGER NOT NULL,
|
|
`reqs` TEXT NOT NULL DEFAULT "",
|
|
`traits` TEXT NOT NULL DEFAULT "",
|
|
`lore` TEXT NOT NULL DEFAULT "",
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE mob_blueprints (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`type` INTEGER NOT NULL,
|
|
`rank` INTEGER NOT NULL,
|
|
`level` INTEGER NOT NULL,
|
|
`hp` INTEGER NOT NULL,
|
|
`m_hp` INTEGER NOT NULL,
|
|
`mp` INTEGER NOT NULL,
|
|
`m_mp` INTEGER NOT NULL,
|
|
`stats_id` INTEGER NOT NULL,
|
|
`xp` INTEGER NOT NULL,
|
|
`silver` INTEGER NOT NULL,
|
|
`loot` TEXT NOT NULL,
|
|
`lore` TEXT NOT NULL,
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
/*
|
|
============================================================
|
|
Battles
|
|
============================================================
|
|
*/
|
|
CREATE TABLE pve (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`char_id` INTEGER NOT NULL,
|
|
`char_hp` INTEGER NOT NULL,
|
|
`char_m_hp` INTEGER NOT NULL,
|
|
`char_mp` INTEGER NOT NULL,
|
|
`char_m_mp` INTEGER NOT NULL,
|
|
`char_stats_id` INTEGER NOT NULL,
|
|
`mob_id` INTEGER NOT NULL,
|
|
`mob_level` INTEGER NOT NULL,
|
|
`mob_rank` INTEGER NOT NULL,
|
|
`mob_hp` INTEGER NOT NULL,
|
|
`mob_m_hp` INTEGER NOT NULL,
|
|
`mob_mp` INTEGER NOT NULL,
|
|
`mob_m_mp` INTEGER NOT NULL,
|
|
`mob_stats_id` INTEGER NOT NULL,
|
|
`first_turn` INTEGER NOT NULL,
|
|
`turn` INTEGER NOT NULL default 1,
|
|
`winner` INTEGER NOT NULL default 0,
|
|
`can_flee` INTEGER NOT NULL default 1,
|
|
`escaped` INTEGER NOT NULL default 0,
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_pve_char_id ON pve (`char_id`);
|
|
|
|
CREATE TABLE pve_logs (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`fight_id` INTEGER NOT NULL,
|
|
`info` TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_pve_logs_fight_id ON pve_logs (`fight_id`);
|
|
|
|
CREATE TABLE pvp (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`char1_id` INTEGER NOT NULL,
|
|
`char1_hp` INTEGER NOT NULL,
|
|
`char1_max_hp` INTEGER NOT NULL,
|
|
`char1_mp` INTEGER NOT NULL,
|
|
`char1_max_mp` INTEGER NOT NULL,
|
|
`char1_stats_id` INTEGER NOT NULL,
|
|
`char2_id` INTEGER NOT NULL,
|
|
`char2_hp` INTEGER NOT NULL,
|
|
`char2_m_hp` INTEGER NOT NULL,
|
|
`char2_mp` INTEGER NOT NULL,
|
|
`char2_m_mp` INTEGER NOT NULL,
|
|
`char2_stats_id` INTEGER NOT NULL,
|
|
`first_turn` INTEGER NOT NULL,
|
|
`turn` INTEGER NOT NULL default 1,
|
|
`winner` INTEGER NOT NULL default 0,
|
|
`created` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated` DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_pvp_char1_id ON pvp (`char1_id`);
|
|
CREATE INDEX idx_pvp_char2_id ON pvp (`char2_id`);
|
|
|
|
CREATE TABLE pvp_logs (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`fight_id` INTEGER NOT NULL,
|
|
`info` TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_pvp_logs_fight_id ON pvp_logs (`fight_id`);
|