143 lines
6.2 KiB
SQL
143 lines
6.2 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
PRAGMA journal_mode = WAL;
|
|
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY,
|
|
username TEXT NOT NULL,
|
|
canonical_username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
password_salt TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INTEGER PRIMARY KEY,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS sessions_token_hash_idx ON sessions(token_hash);
|
|
CREATE INDEX IF NOT EXISTS sessions_expires_at_idx ON sessions(expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS hunter_saves (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
hunter_name TEXT NOT NULL,
|
|
save_json TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS boss_kill_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
boss_id TEXT NOT NULL,
|
|
kills INTEGER NOT NULL DEFAULT 0 CHECK (kills >= 0),
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id, boss_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS boss_kill_rank_idx
|
|
ON boss_kill_records (boss_id, kills DESC, updated_at ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS roguelike_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
highest_round INTEGER NOT NULL DEFAULT 0 CHECK (highest_round >= 0),
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS roguelike_rank_idx
|
|
ON roguelike_records (highest_round DESC, updated_at ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS rogue_trials_endless_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
highest_boss_kills INTEGER NOT NULL DEFAULT 0 CHECK (highest_boss_kills >= 0),
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS rogue_trials_endless_rank_idx
|
|
ON rogue_trials_endless_records (highest_boss_kills DESC, updated_at ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS hockey_healing_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
highest_returns INTEGER NOT NULL DEFAULT 0 CHECK (highest_returns >= 0),
|
|
duration_seconds REAL NOT NULL DEFAULT 0 CHECK (duration_seconds >= 0),
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hockey_healing_rank_idx
|
|
ON hockey_healing_records (highest_returns DESC, duration_seconds DESC, updated_at ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS hockey_pvp_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
wins INTEGER NOT NULL DEFAULT 0 CHECK (wins >= 0),
|
|
losses INTEGER NOT NULL DEFAULT 0 CHECK (losses >= 0),
|
|
boss_kills INTEGER NOT NULL DEFAULT 0 CHECK (boss_kills >= 0),
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hockey_pvp_wins_rank_idx
|
|
ON hockey_pvp_records (wins DESC, losses ASC, updated_at ASC);
|
|
|
|
CREATE INDEX IF NOT EXISTS hockey_pvp_boss_kills_rank_idx
|
|
ON hockey_pvp_records (boss_kills DESC, wins DESC, updated_at ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS blockbreaker_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
highest_bricks INTEGER NOT NULL DEFAULT 0 CHECK (highest_bricks >= 0),
|
|
bricks_achieved_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
longest_seconds REAL NOT NULL DEFAULT 0 CHECK (longest_seconds >= 0),
|
|
time_achieved_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
highest_score INTEGER NOT NULL DEFAULT 0 CHECK (highest_score >= 0),
|
|
score_achieved_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS blockbreaker_bricks_rank_idx
|
|
ON blockbreaker_records (highest_bricks DESC, bricks_achieved_at ASC, account_id ASC, slot_id ASC);
|
|
|
|
CREATE INDEX IF NOT EXISTS blockbreaker_time_rank_idx
|
|
ON blockbreaker_records (longest_seconds DESC, time_achieved_at ASC, account_id ASC, slot_id ASC);
|
|
|
|
CREATE INDEX IF NOT EXISTS blockbreaker_score_rank_idx
|
|
ON blockbreaker_records (highest_score DESC, score_achieved_at ASC, account_id ASC, slot_id ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS aether_assault_records (
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
slot_id INTEGER NOT NULL CHECK (slot_id BETWEEN 1 AND 3),
|
|
highest_score INTEGER NOT NULL DEFAULT 0 CHECK (highest_score >= 0),
|
|
wave_at_best INTEGER NOT NULL DEFAULT 0 CHECK (wave_at_best >= 0),
|
|
duration_at_best REAL NOT NULL DEFAULT 0 CHECK (duration_at_best >= 0),
|
|
score_achieved_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (account_id, slot_id),
|
|
FOREIGN KEY (account_id, slot_id) REFERENCES hunter_saves(account_id, slot_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS aether_assault_rank_idx
|
|
ON aether_assault_records (
|
|
highest_score DESC,
|
|
wave_at_best DESC,
|
|
score_achieved_at ASC,
|
|
account_id ASC,
|
|
slot_id ASC
|
|
);
|