Update schema

[gruntmaster-data.git] / db.sql

CREATE TYPE ULEVEL    AS ENUM ('Highschool', 'Undergraduate', 'Master', 'Doctorate', 'Other');
CREATE TYPE PLEVEL    AS ENUM ('beginner', 'easy', 'medium', 'hard');
CREATE TYPE GENERATOR AS ENUM ('File', 'Run', 'Undef');
CREATE TYPE RUNNER    AS ENUM ('File', 'Verifier', 'Interactive');
CREATE TYPE JUDGE     AS ENUM ('Absolute', 'Points');

CREATE TABLE users (
	id         TEXT  PRIMARY KEY,
	name       TEXT,  -- NOT NULL,
	email      TEXT,  -- NOT NULL,
	town       TEXT,  -- NOT NULL,
	university TEXT,  -- NOT NULL,
	level      TEXT,  --ULEVEL NOT NULL,
	lastjob    BIGINT
);

CREATE TABLE contests (
	id    TEXT PRIMARY KEY,
	name  TEXT NOT NULL,
	start INT  NOT NULL,
	stop  INT  NOT NULL,
	owner TEXT NOT NULL REFERENCES users,
	CONSTRAINT positive_duration CHECK (stop > start)
);

CREATE TABLE problems (
	id        TEXT      PRIMARY KEY,
	author    TEXT      NOT NULL,
	generator GENERATOR NOT NULL,
	judge     JUDGE     NOT NULL,
	level     PLEVEL    NOT NULL,
	name      TEXT      NOT NULL,
	olimit    INT,
	owner     TEXT      NOT NULL REFERENCES users,
	private   BOOLEAN   NOT NULL DEFAULT FALSE,
	runner    RUNNER    NOT NULL,
	statement TEXT      NOT NULL,
	testcnt   INT       NOT NULL,
	tests     TEXT,
	timeout   REAL      NOT NULL,
	value     INT,
	verformat TEXT,
	versource TEXT
);

CREATE TABLE contest_problems (
	contest TEXT REFERENCES contests,
	problem TEXT NOT NULL REFERENCES problems,
	PRIMARY KEY (contest, problem)
);

CREATE TABLE jobs (
	id          SERIAL  PRIMARY KEY,
	contest     TEXT    REFERENCES contests,
	daemon      TEXT,
	date        BIGINT  NOT NULL,
	errors      TEXT,
	extension   TEXT    NOT NULL,
	format      TEXT    NOT NULL,
	private     BOOLEAN NOT NULL DEFAULT FALSE,
	problem     TEXT    NOT NULL REFERENCES problems,
	result      INT,
	result_text TEXT,
	results     JSON,
	source      TEXT    NOT NULL,
	owner       TEXT    NOT NULL REFERENCES users
);

CREATE TABLE opens (
	contest TEXT   NOT NULL REFERENCES contests,
	problem TEXT   NOT NULL REFERENCES problems,
	owner   TEXT   NOT NULL REFERENCES users,
	time    BIGINT NOT NULL,
	PRIMARY KEY (contest, problem, owner)
);