1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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);
CREATE TABLE schedule(instruction INTEGER NOT NULL, player INTEGER NOT NULL, day INTEGER DEFAULT 0, hour INTEGER DEFAULT 0, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE );
|
