forked from tecnickcom/tcexam
-
Notifications
You must be signed in to change notification settings - Fork 0
/
mysql_db_structure.sql
300 lines (270 loc) · 13 KB
/
mysql_db_structure.sql
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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
/*
============================================================
File name : mysql_db_structure.sql
Begin : 2004-04-28
Last Update : 2013-07-02
Description : TCExam database structure.
Database : MySQL 4.1+
Author: Nicola Asuni
(c) Copyright:
Nicola Asuni
Tecnick.com LTD
www.tecnick.com
License:
Copyright (C) 2004-2018 Nicola Asuni - Tecnick.com LTD
See LICENSE.TXT file for more information.
//============================================================+
*/
/* Tables */
CREATE TABLE tce_sessions (
cpsession_id Varchar(32) NOT NULL,
cpsession_expiry Datetime NOT NULL,
cpsession_data Text NOT NULL,
Primary Key (cpsession_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_users (
user_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
user_name Varchar(255) NOT NULL,
user_password Varchar(255) NOT NULL,
user_email Varchar(255),
user_regdate Datetime NOT NULL,
user_ip Varchar(39) NOT NULL,
user_firstname Varchar(255),
user_lastname Varchar(255),
user_birthdate Date,
user_birthplace Varchar(255),
user_regnumber Varchar(255),
user_ssn Varchar(255),
user_level Smallint(3) UNSIGNED NOT NULL DEFAULT 1,
user_verifycode Varchar(32),
user_otpkey Varchar(255),
UNIQUE (user_verifycode),
Primary Key (user_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_modules (
module_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
module_name Varchar(255) NOT NULL,
module_enabled Bool NOT NULL DEFAULT '0',
module_user_id Bigint UNSIGNED NOT NULL DEFAULT 1,
Primary Key (module_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_subjects (
subject_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
subject_module_id Bigint UNSIGNED NOT NULL DEFAULT 1,
subject_name Varchar(255) NOT NULL,
subject_description Text,
subject_enabled Bool NOT NULL DEFAULT '0',
subject_user_id Bigint UNSIGNED NOT NULL DEFAULT 1,
Primary Key (subject_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_questions (
question_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
question_subject_id Bigint UNSIGNED NOT NULL,
question_description Text NOT NULL,
question_explanation Text NULL,
question_type Smallint(3) UNSIGNED NOT NULL DEFAULT 1,
question_difficulty Smallint NOT NULL DEFAULT 1,
question_enabled Bool NOT NULL DEFAULT '0',
question_position Bigint UNSIGNED NULL,
question_timer Smallint(10) NULL,
question_fullscreen Bool NOT NULL DEFAULT '0',
question_inline_answers Bool NOT NULL DEFAULT '0',
question_auto_next Bool NOT NULL DEFAULT '0',
Primary Key (question_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_answers (
answer_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
answer_question_id Bigint UNSIGNED NOT NULL,
answer_description Text NOT NULL,
answer_explanation Text NULL,
answer_isright Bool NOT NULL DEFAULT '0',
answer_enabled Bool NOT NULL DEFAULT '0',
answer_position Bigint UNSIGNED NULL,
answer_keyboard_key Smallint(10) UNSIGNED NULL,
Primary Key (answer_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_tests (
test_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
test_name Varchar(255) NOT NULL,
test_description Text NOT NULL,
test_begin_time Datetime,
test_end_time Datetime,
test_duration_time Smallint(10) UNSIGNED NOT NULL DEFAULT 0,
test_ip_range Varchar(255) NOT NULL DEFAULT '*.*.*.*',
test_results_to_users Bool NOT NULL DEFAULT '0',
test_report_to_users Bool NOT NULL DEFAULT '0',
test_score_right Decimal(10,3) DEFAULT 1,
test_score_wrong Decimal(10,3) DEFAULT 0,
test_score_unanswered Decimal(10,3) Default 0,
test_max_score Decimal(10,3) NOT NULL DEFAULT 0,
test_user_id Bigint UNSIGNED NOT NULL DEFAULT 1,
test_score_threshold Decimal(10,3) Default 0,
test_random_questions_select Bool NOT NULL Default '1',
test_random_questions_order Bool NOT NULL Default '1',
test_questions_order_mode Smallint(3) UNSIGNED NOT NULL DEFAULT 0,
test_random_answers_select Bool NOT NULL Default '1',
test_random_answers_order Bool NOT NULL Default '1',
test_answers_order_mode Smallint(3) UNSIGNED NOT NULL DEFAULT 0,
test_comment_enabled Bool NOT NULL Default '1',
test_menu_enabled Bool NOT NULL Default '1',
test_noanswer_enabled Bool NOT NULL Default '1',
test_mcma_radio Bool NOT NULL Default '1',
test_repeatable Bool NOT NULL Default '0',
test_mcma_partial_score Bool NOT NULL Default '1',
test_logout_on_timeout Bool NOT NULL Default '0',
test_password Varchar(255),
Primary Key (test_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_test_subjects (
subjset_tsubset_id Bigint UNSIGNED NOT NULL,
subjset_subject_id Bigint UNSIGNED NOT NULL,
Primary Key (subjset_tsubset_id,subjset_subject_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_tests_users (
testuser_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
testuser_test_id Bigint UNSIGNED NOT NULL,
testuser_user_id Bigint UNSIGNED NOT NULL,
testuser_status Smallint UNSIGNED NOT NULL DEFAULT 0,
testuser_creation_time Datetime NOT NULL,
testuser_comment Text,
Primary Key (testuser_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_tests_logs (
testlog_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
testlog_testuser_id Bigint UNSIGNED NOT NULL,
testlog_user_ip Varchar(39),
testlog_question_id Bigint UNSIGNED NOT NULL,
testlog_answer_text Text,
testlog_score Decimal(10,3),
testlog_creation_time Datetime,
testlog_display_time Datetime,
testlog_change_time Datetime,
testlog_reaction_time Bigint UNSIGNED NOT NULL DEFAULT 0,
testlog_order Smallint NOT NULL DEFAULT 1,
testlog_num_answers Smallint UNSIGNED NOT NULL DEFAULT 0,
testlog_comment Text,
Primary Key (testlog_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_tests_logs_answers (
logansw_testlog_id Bigint UNSIGNED NOT NULL,
logansw_answer_id Bigint UNSIGNED NOT NULL,
logansw_selected Smallint NOT NULL DEFAULT -1,
logansw_order Smallint NOT NULL DEFAULT 1,
logansw_position Bigint UNSIGNED NULL,
Primary Key (logansw_testlog_id,logansw_answer_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_user_groups (
group_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
group_name Varchar(255) NOT NULL,
UNIQUE (group_name),
Primary Key (group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_usrgroups (
usrgrp_user_id Bigint UNSIGNED NOT NULL,
usrgrp_group_id Bigint UNSIGNED NOT NULL,
Primary Key (usrgrp_user_id,usrgrp_group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_testgroups (
tstgrp_test_id Bigint UNSIGNED NOT NULL,
tstgrp_group_id Bigint UNSIGNED NOT NULL,
Primary Key (tstgrp_test_id,tstgrp_group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_test_subject_set (
tsubset_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
tsubset_test_id Bigint UNSIGNED NOT NULL,
tsubset_type Smallint NOT NULL DEFAULT 1,
tsubset_difficulty Smallint NOT NULL DEFAULT 1,
tsubset_quantity Smallint NOT NULL DEFAULT 1,
tsubset_answers Smallint NOT NULL DEFAULT 0,
Primary Key (tsubset_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_sslcerts (
ssl_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ssl_name VARCHAR(255) NOT NULL,
ssl_hash VARCHAR(32) NOT NULL,
ssl_end_date DATETIME NOT NULL,
ssl_enabled Bool NOT NULL DEFAULT '0',
ssl_user_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
Primary Key (ssl_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_testsslcerts (
tstssl_test_id BIGINT UNSIGNED NOT NULL,
tstssl_ssl_id BIGINT UNSIGNED NOT NULL,
Primary Key (tstssl_test_id, tstssl_ssl_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE tce_testuser_stat (
tus_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
tus_date Datetime NOT NULL,
PRIMARY KEY (tus_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
/* Alternate Keys */
ALTER TABLE tce_users ADD UNIQUE ak_user_name (user_name);
ALTER TABLE tce_users ADD UNIQUE ak_user_regnumber (user_regnumber);
ALTER TABLE tce_users ADD UNIQUE ak_user_ssn (user_ssn);
ALTER TABLE tce_modules ADD UNIQUE ak_module_name (module_name);
ALTER TABLE tce_subjects ADD UNIQUE ak_subject_name (subject_module_id,subject_name);
ALTER TABLE tce_tests ADD UNIQUE ak_test_name (test_name);
ALTER TABLE tce_tests_users ADD UNIQUE ak_testuser (testuser_test_id,testuser_user_id,testuser_status);
ALTER TABLE tce_tests_logs ADD UNIQUE ak_testuser_question (testlog_testuser_id,testlog_question_id);
/* Indexes */
ALTER TABLE tce_tests_users ADD INDEX p_testuser_user_id (testuser_user_id);
ALTER TABLE tce_tests ADD INDEX p_test_user_id (test_user_id);
ALTER TABLE tce_modules ADD INDEX p_module_user_id (module_user_id);
ALTER TABLE tce_subjects ADD INDEX p_subject_user_id (subject_user_id);
ALTER TABLE tce_usrgroups ADD INDEX p_usrgrp_user_id (usrgrp_user_id);
ALTER TABLE tce_questions ADD INDEX p_question_subject_id (question_subject_id);
ALTER TABLE tce_test_subjects ADD INDEX p_subjset_subject_id (subjset_subject_id);
ALTER TABLE tce_answers ADD INDEX p_answer_question_id (answer_question_id);
ALTER TABLE tce_tests_logs ADD INDEX p_testlog_question_id (testlog_question_id);
ALTER TABLE tce_tests_logs_answers ADD INDEX p_logansw_answer_id (logansw_answer_id);
ALTER TABLE tce_tests_users ADD INDEX p_testuser_test_id (testuser_test_id);
ALTER TABLE tce_testgroups ADD INDEX p_tstgrp_test_id (tstgrp_test_id);
ALTER TABLE tce_test_subject_set ADD INDEX p_tsubset_test_id (tsubset_test_id);
ALTER TABLE tce_tests_logs ADD INDEX p_testlog_testuser_id (testlog_testuser_id);
ALTER TABLE tce_tests_logs_answers ADD INDEX p_logansw_testlog_id (logansw_testlog_id);
ALTER TABLE tce_usrgroups ADD INDEX p_usrgrp_group_id (usrgrp_group_id);
ALTER TABLE tce_testgroups ADD INDEX p_tstgrp_group_id (tstgrp_group_id);
ALTER TABLE tce_test_subjects ADD INDEX p_subjset_tsubset_id (subjset_tsubset_id);
ALTER TABLE tce_testsslcerts ADD INDEX p_tstssl_test_id (tstssl_test_id);
ALTER TABLE tce_testsslcerts ADD INDEX p_tstssl_ssl_id (tstssl_ssl_id);
/* Foreign Keys */
ALTER TABLE tce_tests_users ADD Foreign Key (testuser_user_id) references tce_users (user_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_tests ADD Foreign Key (test_user_id) references tce_users (user_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_modules ADD Foreign Key (module_user_id) references tce_users (user_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_subjects ADD Foreign Key (subject_user_id) references tce_users (user_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_subjects ADD Foreign Key (subject_module_id) references tce_modules (module_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_usrgroups ADD Foreign Key (usrgrp_user_id) references tce_users (user_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_questions ADD Foreign Key (question_subject_id) references tce_subjects (subject_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_test_subjects ADD Foreign Key (subjset_subject_id) references tce_subjects (subject_id) ON DELETE restrict ON UPDATE no action;
ALTER TABLE tce_answers ADD Foreign Key (answer_question_id) references tce_questions (question_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_tests_logs ADD Foreign Key (testlog_question_id) references tce_questions (question_id) ON DELETE restrict ON UPDATE no action;
ALTER TABLE tce_tests_logs_answers ADD Foreign Key (logansw_answer_id) references tce_answers (answer_id) ON DELETE restrict ON UPDATE no action;
ALTER TABLE tce_tests_users ADD Foreign Key (testuser_test_id) references tce_tests (test_id) ON DELETE cascade ON UPDATE restrict;
ALTER TABLE tce_testgroups ADD Foreign Key (tstgrp_test_id) references tce_tests (test_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_test_subject_set ADD Foreign Key (tsubset_test_id) references tce_tests (test_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_tests_logs ADD Foreign Key (testlog_testuser_id) references tce_tests_users (testuser_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_tests_logs_answers ADD Foreign Key (logansw_testlog_id) references tce_tests_logs (testlog_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_usrgroups ADD Foreign Key (usrgrp_group_id) references tce_user_groups (group_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_testgroups ADD Foreign Key (tstgrp_group_id) references tce_user_groups (group_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_test_subjects ADD Foreign Key (subjset_tsubset_id) references tce_test_subject_set (tsubset_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_testsslcerts ADD Foreign Key (tstssl_test_id) references tce_tests (test_id) ON DELETE cascade ON UPDATE no action;
ALTER TABLE tce_testsslcerts ADD Foreign Key (tstssl_ssl_id) references tce_sslcerts (ssl_id) ON DELETE cascade ON UPDATE no action;