create_rating_db.txt 4.45 KB
Newer Older
Timo Heikkilä's avatar
Timo Heikkilä committed
1
2
3
4
5
6
7
8
9
CREATE TABLE background_question (
	idbackground_question INTEGER NOT NULL AUTO_INCREMENT,
	background_question VARCHAR(120), 
	experiment_idexperiment INTEGER, 
	PRIMARY KEY (idbackground_question)
);
CREATE TABLE experiment (
	idexperiment INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(120), 
10
	instruction MEDIUMTEXT, 
Timo Heikkilä's avatar
Timo Heikkilä committed
11
12
13
14
	directoryname VARCHAR(120), 
	language VARCHAR(120), 
	status VARCHAR(120), 
	randomization VARCHAR(120), 
15
16
	short_instruction MEDIUMTEXT, 
	single_sentence_instruction MEDIUMTEXT, 
Timo Heikkilä's avatar
Timo Heikkilä committed
17
18
19
20
21
	is_archived VARCHAR(120), 
	creator_name VARCHAR(120), 
	research_notification_filename VARCHAR(120), 
	creation_time DATETIME, 
	stimulus_size VARCHAR(120), 
22
	consent_text MEDIUMTEXT, 
Timo Heikkilä's avatar
Timo Heikkilä committed
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
	use_forced_id VARCHAR(120), 
	PRIMARY KEY (idexperiment)
);
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');
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)
);
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 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)
);
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)
);
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)
);
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 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)
);
CREATE INDEX ix_experiment_consent_text ON experiment (consent_text);
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);
CREATE INDEX ix_experiment_name ON experiment (name);
CREATE INDEX ix_experiment_short_instruction ON experiment (short_instruction);
CREATE INDEX ix_experiment_single_sentence_instruction ON experiment (single_sentence_instruction);
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);