schema.sql 7.18 KB
Newer Older
Ossi Laine's avatar
Ossi Laine committed
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
/* 
SQL initialization script

Run: mysql -u rating -p -D rating_db < create_rating_db.sql

This will create user 'admin' with password 'password'.

*/

CREATE DATABASE IF NOT EXISTS rating_db;
USE rating_db;
GRANT ALL PRIVILEGES ON rating_db.* TO 'rating'@'%' WITH GRANT OPTION;

/* Drop all tables (cascade removes foreign keys also) */

DROP TABLE IF EXISTS background_question_answer;
DROP TABLE IF EXISTS background_question_option;
DROP TABLE IF EXISTS background_question;
DROP TABLE IF EXISTS forced_id;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS trial_randomization;
DROP TABLE IF EXISTS embody_answer;
DROP TABLE IF EXISTS embody_question;
DROP TABLE IF EXISTS answer;
DROP TABLE IF EXISTS answer_set;
DROP TABLE IF EXISTS question;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS experiment;

/* Experiment set */
CREATE TABLE experiment (
	idexperiment INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(120), 
	instruction TEXT, 
	directoryname VARCHAR(120), 
	language VARCHAR(120), 
	status VARCHAR(120), 
	randomization VARCHAR(120), 
	short_instruction TEXT, 
	single_sentence_instruction TEXT, 
	is_archived VARCHAR(120), 
	creator_name VARCHAR(120), 
	research_notification_filename VARCHAR(120), 
	creation_time DATETIME, 
	stimulus_size VARCHAR(120), 
	consent_text TEXT, 
	use_forced_id VARCHAR(120), 
	PRIMARY KEY (idexperiment)
);

/* Answer set holds session information about users experiment */
CREATE TABLE answer_set (
	idanswer_set INTEGER NOT NULL AUTO_INCREMENT, 
	experiment_idexperiment INTEGER, 
	session VARCHAR(120), 
	agreement VARCHAR(120), 
	answer_counter INTEGER, 
	registration_time DATETIME, 
	last_answer_time DATETIME, 
	PRIMARY KEY (idanswer_set), 
	FOREIGN KEY(experiment_idexperiment) REFERENCES experiment (idexperiment)
);

/* Background questions are asked before the experiment begins */ 
CREATE TABLE background_question (
	idbackground_question INTEGER NOT NULL AUTO_INCREMENT,
	background_question VARCHAR(120), 
	experiment_idexperiment INTEGER, 
	PRIMARY KEY (idbackground_question)
);
CREATE TABLE background_question_option (
	idbackground_question_option INTEGER NOT NULL AUTO_INCREMENT,
	background_question_idbackground_question INTEGER, 
	`option` VARCHAR(120), 
	PRIMARY KEY (idbackground_question_option), 
	FOREIGN KEY(background_question_idbackground_question) REFERENCES background_question (idbackground_question)
);
CREATE TABLE background_question_answer (
	idbackground_question_answer INTEGER NOT NULL AUTO_INCREMENT,
	answer_set_idanswer_set INTEGER, 
	answer VARCHAR(120), 
	background_question_idbackground_question INTEGER, 
	PRIMARY KEY (idbackground_question_answer), 
	FOREIGN KEY(answer_set_idanswer_set) REFERENCES answer_set (idanswer_set), 
	FOREIGN KEY(background_question_idbackground_question) REFERENCES background_question (idbackground_question)
);

/* Randomize experiment page order  */
CREATE TABLE trial_randomization (
	idtrial_randomization INTEGER NOT NULL AUTO_INCREMENT, 
	page_idpage INTEGER, 
	randomized_idpage INTEGER, 
	answer_set_idanswer_set INTEGER, 
	experiment_idexperiment INTEGER, 
	PRIMARY KEY (idtrial_randomization)
);

CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	username VARCHAR(64), 
	email VARCHAR(120), 
	password_hash VARCHAR(128), 
	PRIMARY KEY (id)
);

/* By using forced ID login subjects can only participate to a rating task by logging in with a pregenerated ID */
CREATE TABLE forced_id (
	idforced_id INTEGER NOT NULL AUTO_INCREMENT,
	experiment_idexperiment INTEGER, 
	pregenerated_id VARCHAR(120), 
	PRIMARY KEY (idforced_id), 
	FOREIGN KEY(experiment_idexperiment) REFERENCES experiment (idexperiment)
);

/* Information about stimulus type and content on a page */
CREATE TABLE page (
	idpage INTEGER NOT NULL AUTO_INCREMENT,
	experiment_idexperiment INTEGER, 
	type VARCHAR(120), 
	text VARCHAR(120), 
	media VARCHAR(120), 
	PRIMARY KEY (idpage), 
	FOREIGN KEY(experiment_idexperiment) REFERENCES experiment (idexperiment)
);

/* Slider question */
CREATE TABLE question (
	idquestion INTEGER NOT NULL AUTO_INCREMENT,
	experiment_idexperiment INTEGER, 
	question VARCHAR(120), 
	`left` VARCHAR(120), 
	`right` VARCHAR(120), 
	PRIMARY KEY (idquestion), 
	FOREIGN KEY(experiment_idexperiment) REFERENCES experiment (idexperiment)
);

/* Slider answer */
CREATE TABLE answer (
	idanswer INTEGER NOT NULL AUTO_INCREMENT,
	question_idquestion INTEGER, 
	answer_set_idanswer_set INTEGER, 
	answer VARCHAR(120), 
	page_idpage INTEGER, 
	PRIMARY KEY (idanswer), 
	FOREIGN KEY(answer_set_idanswer_set) REFERENCES answer_set (idanswer_set), 
	FOREIGN KEY(page_idpage) REFERENCES page (idpage), 
	FOREIGN KEY(question_idquestion) REFERENCES question (idquestion)
);

/* Create indexes for faster operations */
CREATE INDEX ix_experiment_consent_text ON experiment (consent_text(255));
CREATE INDEX ix_experiment_creation_time ON experiment (creation_time);
CREATE UNIQUE INDEX ix_experiment_directoryname ON experiment (directoryname);
CREATE INDEX ix_experiment_instruction ON experiment (instruction(255));
CREATE INDEX ix_experiment_name ON experiment (name);
CREATE INDEX ix_experiment_short_instruction ON experiment (short_instruction(255));
CREATE INDEX ix_experiment_single_sentence_instruction ON experiment (single_sentence_instruction(255));
CREATE UNIQUE INDEX ix_user_email ON user (email);
CREATE UNIQUE INDEX ix_user_username ON user (username);
CREATE INDEX ix_answer_set_last_answer_time ON answer_set (last_answer_time);
CREATE INDEX ix_answer_set_registration_time ON answer_set (registration_time);
CREATE INDEX ix_page_media ON page (media);
CREATE INDEX ix_page_text ON page (text);
CREATE INDEX ix_page_type ON page (type);


/* New fields for updating embody tool to onni.utu.fi */

/* Embody picture/question information */
CREATE TABLE embody_question (
	idembody INTEGER NOT NULL AUTO_INCREMENT,
	experiment_idexperiment INTEGER, 
	picture TEXT, 
	question TEXT, 
	PRIMARY KEY (idembody), 
	FOREIGN KEY(experiment_idexperiment) REFERENCES experiment (idexperiment)
);

/* Embody answer (coordinates). Answer is saved as a json object: 
	{x:[1,2,100,..], y:[3,4,101,..], r:[13,13,8,...]} */
CREATE TABLE embody_answer (
	idanswer INTEGER NOT NULL AUTO_INCREMENT,
	answer_set_idanswer_set INTEGER, 
	page_idpage INTEGER, 
    embody_question_idembody INTEGER DEFAULT 0,
	coordinates TEXT, 
	PRIMARY KEY (idanswer), 
	FOREIGN KEY(answer_set_idanswer_set) REFERENCES answer_set (idanswer_set), 
	FOREIGN KEY(page_idpage) REFERENCES page (idpage) ,
	FOREIGN KEY(embody_question_idembody) REFERENCES embody_question (idembody) 
);


/* Set flag if embody tool is enabled -> this is not the most modular solution, but works for now */
ALTER TABLE experiment ADD COLUMN (embody_enabled BOOLEAN DEFAULT 0);

/* Set current answer type (embody/slider/etc..) so returning users are routed to correct question */ 
ALTER TABLE answer_set ADD COLUMN (answer_type VARCHAR(120));

Ossi Laine's avatar
Ossi Laine committed
200
ALTER TABLE experiment ADD COLUMN (group_id INTEGER), ADD FOREIGN KEY(group_id) REFERENCES research_group(id);
Ossi Laine's avatar
Ossi Laine committed
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218


CREATE TABLE research_group (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name TEXT, 
	tag TEXT, 
	description TEXT,
	PRIMARY KEY (id)
);

CREATE TABLE user_in_group (
	idgroup INTEGER,
	iduser INTEGER,
	role TEXT,
	FOREIGN KEY(idgroup) REFERENCES research_group (id), 
	FOREIGN KEY(iduser) REFERENCES user (id)
);