]> iEval git - gruntmaster-data.git/blob - db.sql
d06521796abc8adeb0df656d78490520dc21e9f7
[gruntmaster-data.git] / db.sql
1 CREATE TABLE users (
2 id TEXT PRIMARY KEY,
3 passphrase TEXT, -- NOT NULL,
4 admin BOOLEAN NOT NULL DEFAULT FALSE,
5 name TEXT, -- NOT NULL,
6 email TEXT, -- NOT NULL,
7 phone TEXT, -- NOT NULL,
8 town TEXT, -- NOT NULL,
9 university TEXT, -- NOT NULL,
10 level TEXT, -- NOT NULL,
11 lastjob BIGINT
12 );
13
14 CREATE TABLE contests (
15 id TEXT PRIMARY KEY,
16 name TEXT NOT NULL,
17 start INT NOT NULL,
18 stop INT NOT NULL,
19 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
20 CONSTRAINT positive_duration CHECK (stop > start)
21 );
22
23 CREATE TABLE problems (
24 id TEXT PRIMARY KEY,
25 author TEXT,
26 writer TEXT,
27 generator TEXT NOT NULL,
28 judge TEXT NOT NULL,
29 level TEXT NOT NULL,
30 name TEXT NOT NULL,
31 olimit INT,
32 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
33 private BOOLEAN NOT NULL DEFAULT FALSE,
34 runner TEXT NOT NULL,
35 statement TEXT NOT NULL,
36 testcnt INT NOT NULL,
37 tests TEXT,
38 timeout REAL NOT NULL,
39 value INT,
40 genformat TEXT,
41 gensource TEXT,
42 verformat TEXT,
43 versource TEXT
44 );
45
46 CREATE TABLE contest_problems (
47 contest TEXT REFERENCES contests ON DELETE CASCADE,
48 problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
49 PRIMARY KEY (contest, problem)
50 );
51
52 CREATE TABLE jobs (
53 id SERIAL PRIMARY KEY,
54 contest TEXT REFERENCES contests ON DELETE CASCADE,
55 daemon TEXT,
56 date BIGINT NOT NULL,
57 errors TEXT,
58 extension TEXT NOT NULL,
59 format TEXT NOT NULL,
60 private BOOLEAN NOT NULL DEFAULT FALSE,
61 problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
62 result INT,
63 result_text TEXT,
64 results TEXT,
65 source TEXT NOT NULL,
66 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE
67 );
68
69 CREATE TABLE opens (
70 contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
71 problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
72 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
73 time BIGINT NOT NULL,
74 PRIMARY KEY (contest, problem, owner)
75 );
This page took 0.049907 seconds and 3 git commands to generate.