create_rating_db.sql 6.13 KB
Newer Older
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
/* 
MYSQL script

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

/* 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 answer;
DROP TABLE IF EXISTS answer_set;
DROP TABLE IF EXISTS question;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS experiment;

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)
);

/* New fields for updating embody tool to onni.utu.fi */
ALTER TABLE experiment ADD COLUMN (embody_enabled BOOLEAN DEFAULT 0);

/* 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)
);

58
59
60
61
ALTER TABLE answer_set ADD COLUMN (answer_type VARCHAR(120));

/* TODO: Update answer_set so it knows which part of the page the user is doing (embody/sliders/something else) */ 

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
/* 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)
);
INSERT INTO user VALUES(1,'Yngwie',NULL,'pbkdf2:sha256:50000$QioS5ICE$17a468394e72aef1243576aa80d29c296c6482ada48be9d25bd7c3b6e8129b40');

/* 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)
);

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

/* 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);