1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
CREATE TABLE players(id INTEGER PRIMARY KEY, name TEXT NOT NULL, token TEXT NOT NULL);
CREATE TABLE jobs(id INTEGER PRIMARY KEY, player INTEGER, instruction INTEGER, status INTEGER, time TEXT DEFAULT NULL, FOREIGN KEY(player) REFERENCES players(id), FOREIGN KEY(instruction) REFERENCES instructions(id));
CREATE TABLE tasks(job INTEGER, task INTEGER, status INTEGER, FOREIGN KEY(job) REFERENCES jobs(id));
CREATE TABLE instructions(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
CREATE TABLE available(instruction INTEGER, player INTEGER, FOREIGN KEY(instruction) REFERENCES instructions(id), FOREIGN KEY(player) REFERENCES players(id), UNIQUE(instruction, player));
CREATE TABLE groups(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
CREATE TABLE group_instructions(group_id INTEGER, instruction INTEGER, player INTEGER, FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE);
CREATE TABLE checkin(player INTEGER PRIMARY KEY, year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minute INTEGER, second INTEGER, os TEXT DEFAULT NULL, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE);
|
