schema.sql

PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS user (
	id            INTEGER PRIMARY KEY,
	user          TEXT NOT NULL UNIQUE,
	password_hash TEXT NOT NULL,
	first         TEXT NOT NULL,
	last          TEXT NOT NULL,
	registered    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS game (
	id     INTEGER PRIMARY KEY,
	front1 INTEGER REFERENCES user NOT NULL,
	back1  INTEGER REFERENCES user NOT NULL,
	score1 INTEGER NOT NULL,
	front2 INTEGER REFERENCES user NOT NULL,
	back2  INTEGER REFERENCES user NOT NULL,
	score2 INTEGER NOT NULL,
	time   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS signoff (
	id   INTEGER PRIMARY KEY,
	user INTEGER REFERENCES user NOT NULL,
	game INTEGER REFERENCES game NOT NULL,
	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE (user, game)
);

CREATE TABLE IF NOT EXISTS elo (
	id    INTEGER PRIMARY KEY,
	user  INTEGER REFERENCES user NOT NULL UNIQUE,
	elo   REAL NOT NULL,
	won   REAL NOT NULL,
	lost  REAL NOT NULL,
	games INT NOT NULL
);

CREATE TABLE IF NOT EXISTS webtoken (
	id        INTEGER PRIMARY KEY,
	token     TEXT NOT NULL UNIQUE,
	user      INTEGER REFERENCES user NOT NULL,
	timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)