yggdrasil/migrations/0_yggdrasil.sql

CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    language TEXT NOT NULL,
    country TEXT NOT NULL,
    selected_profile INTEGER,
    FOREIGN KEY(selected_profile) REFERENCES profiles(id)
);

CREATE TABLE IF NOT EXISTS blocked_servers (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    pattern TEXT NOT NULL UNIQUE,
    sha1 TEXT NOT NULL UNIQUE,
    reason TEXT
);

CREATE TABLE IF NOT EXISTS capes (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    friendly_id TEXT NOT NULL UNIQUE,
    alias TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS profiles (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    uuid TEXT NOT NULL UNIQUE,
    created INTEGER NOT NULL,
    owner INTEGER NOT NULL,
    name TEXT NOT NULL UNIQUE,
    name_history TEXT NOT NULL,
    skin_variant TEXT NOT NULL,
    capes TEXT,
    active_cape INTEGER,
    attributes TEXT NOT NULL,
    FOREIGN KEY(owner) REFERENCES accounts(id),
    FOREIGN KEY(active_cape) REFERENCES capes(id)
);

CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    profile INTEGER NOT NULL,
    server_id TEXT NOT NULL,
    ip_addr TEXT NOT NULL,
    FOREIGN KEY(profile) REFERENCES profiles(id)
);

CREATE TABLE IF NOT EXISTS tokens (
    id INTEGER PRIMARY KEY NOT NULL UNIQUE,
    access TEXT NOT NULL UNIQUE,
    client TEXT NOT NULL,
    account INTEGER NOT NULL,
    issued INTEGER NOT NULL,
    expires INTEGER NOT NULL,
    FOREIGN KEY(account) REFERENCES accounts(id)
);