-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit.sql
More file actions
78 lines (71 loc) · 2.04 KB
/
init.sql
File metadata and controls
78 lines (71 loc) · 2.04 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
CREATE TABLE IF NOT EXISTS Participants (
pid SERIAL PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
age INT NOT NULL,
gender TEXT NOT NULL,
phone TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS Experiments (
exp_id SERIAL PRIMARY KEY,
pid1 INT,
pid2 INT,
date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (pid1) REFERENCES Participants(pid),
FOREIGN KEY (pid2) REFERENCES Participants(pid)
);
CREATE TABLE IF NOT EXISTS ExperimentsFeedback (
exp_id INT,
pid INT,
question TEXT,
answer TEXT NOT NULL,
PRIMARY KEY (exp_id, pid, question),
FOREIGN KEY (pid) REFERENCES Participants(pid),
FOREIGN KEY (exp_id) REFERENCES Experiments(exp_id)
);
CREATE TABLE IF NOT EXISTS Sessions (
session_id SERIAL PRIMARY KEY,
exp_id INT,
duration INT NOT NULL,
session_type TEXT NOT NULL,
session_order INT NOT NULL,
tolerance INT NOT NULL,
window_length INT NOT NULL,
state TEXT NOT NULL,
FOREIGN KEY (exp_id) REFERENCES Experiments(exp_id)
);
CREATE TABLE IF NOT EXISTS SessionsFeedback (
session_id INT,
pid INT,
question TEXT,
answer TEXT NOT NULL,
PRIMARY KEY (session_id, pid, question),
FOREIGN KEY (pid) REFERENCES Participants(pid),
FOREIGN KEY (session_id) REFERENCES Sessions(session_id)
);
CREATE TABLE IF NOT EXISTS SessionEvents (
event_id SERIAL PRIMARY KEY,
session_id INT,
type TEXT NOT NULL,
subtype TEXT NOT NULL,
timestamp BIGINT NOT NULL,
actor TEXT NOT NULL,
data TEXT,
FOREIGN KEY (session_id) REFERENCES Sessions(session_id)
);
CREATE TABLE IF NOT EXISTS Credentials (
user_id TEXT PRIMARY KEY,
password TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS Presets (
preset_name text,
idx integer,
duration integer NOT NULL,
game_type text NOT NULL,
sync_tolerance integer NOT NULL,
sync_window_length integer NOT NULL,
is_warmup boolean NOT NULL,
countdown_timer integer NOT NULL,
PRIMARY KEY (preset_name, idx)
);