Database Documentation

01 Jan 0001

Table: `tce_sessions`

Description:

Columns:

Column	Data type	Attributes
`cpsession_id`	VARCHAR(32)	PRIMARY, Not null
`cpsession_expiry`	DATETIME	Not null
`cpsession_data`	TEXT	Not null

Indices:

Name	Columns	Type	Description
PRIMARY	`cpsession_id`	PRIMARY

Table: `tce_users`

Description:

Columns:

Column	Data type	Attributes	Default
`user_id`	BIGINT	PRIMARY, Auto increments, Not null
`user_name`	VARCHAR(255)	Not null, Unique
`user_password`	VARCHAR(255)	Not null
`user_email`	VARCHAR(255)		`NULL`
`user_regdate`	DATETIME	Not null
`user_ip`	VARCHAR(39)	Not null
`user_firstname`	VARCHAR(255)		`NULL`
`user_lastname`	VARCHAR(255)		`NULL`
`user_birthdate`	DATE		`NULL`
`user_birthplace`	VARCHAR(255)		`NULL`
`user_regnumber`	VARCHAR(255)	Unique	`NULL`
`user_ssn`	VARCHAR(255)	Unique	`NULL`
`user_level`	SMALLINT	Not null	`1`
`user_verifycode`	VARCHAR(32)	Unique	`NULL`
`user_otpkey`	VARCHAR(255)		`NULL`

Indices:

Name	Columns	Type
	`user_verifycode`	UNIQUE
PRIMARY	`user_id`	PRIMARY
ak_user_name	`user_name`	UNIQUE
ak_user_regnumber	`user_regnumber`	UNIQUE
ak_user_ssn	`user_ssn`	UNIQUE

Table: `tce_modules`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`module_id`	BIGINT	PRIMARY, Auto increments, Not null
`module_name`	VARCHAR(255)	Not null, Unique
`module_enabled`		Not null	`'0'`
`module_user_id`	BIGINT	Not null	`1`	foreign key to column `user_id` on table `tce_users`.

Indices:

Name	Columns	Type
PRIMARY	`module_id`	PRIMARY
ak_module_name	`module_name`	UNIQUE
p_module_user_id	`module_user_id`	INDEX

Table: `tce_subjects`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`subject_id`	BIGINT	PRIMARY, Auto increments, Not null
`subject_module_id`	BIGINT	Not null, Unique	`1`	foreign key to column `module_id` on table `tce_modules`.
`subject_name`	VARCHAR(255)	Not null, Unique
`subject_description`	TEXT		`NULL`
`subject_enabled`		Not null	`'0'`
`subject_user_id`	BIGINT	Not null	`1`	foreign key to column `user_id` on table `tce_users`.

Indices:

Name	Columns	Type
PRIMARY	`subject_id`	PRIMARY
ak_subject_name	`subject_module_id`, `subject_name`	UNIQUE
p_subject_user_id	`subject_user_id`	INDEX

Table: `tce_questions`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`question_id`	BIGINT	PRIMARY, Auto increments, Not null
`question_subject_id`	BIGINT	Not null		foreign key to column `subject_id` on table `tce_subjects`.
`question_description`	TEXT	Not null
`question_explanation`	TEXT		`NULL`
`question_type`	SMALLINT	Not null	`1`
`question_difficulty`	SMALLINT	Not null	`1`
`question_enabled`		PRIMARY, Not null	`'0'`
`question_position`	BIGINT		`NULL`
`question_timer`	SMALLINT		`NULL`
`question_fullscreen`		Not null	`'0'`
`question_inline_answers`		Not null	`'0'`
`question_auto_next`		Not null	`'0'`

Indices:

Name	Columns	Type	Description
PRIMARY	`question_id`, `question_enabled`	PRIMARY
p_question_subject_id	`question_subject_id`	INDEX

Table: `tce_answers`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`answer_id`	BIGINT	PRIMARY, Auto increments, Not null
`answer_question_id`	BIGINT	Not null		foreign key to column `question_id` on table `tce_questions`.
`answer_description`	TEXT	Not null
`answer_explanation`	TEXT		`NULL`
`answer_isright`		Not null	`'0'`
`answer_enabled`		Not null	`'0'`
`answer_position`	BIGINT		`NULL`
`answer_keyboard_key`	SMALLINT		`NULL`

Indices:

Name	Columns	Type	Description
PRIMARY	`answer_id`	PRIMARY
p_answer_question_id	`answer_question_id`	INDEX

Table: `tce_tests`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`test_id`	BIGINT	PRIMARY, Auto increments, Not null
`test_name`	VARCHAR(255)	Not null, Unique
`test_description`	TEXT	Not null
`test_begin_time`	DATETIME		`NULL`
`test_end_time`	DATETIME		`NULL`
`test_duration_time`	SMALLINT	Not null	`0`
`test_ip_range`	VARCHAR(255)	Not null	`'...'`
`test_results_to_users`		Not null	`'0'`
`test_report_to_users`		Not null	`'0'`
`test_score_right`	DECIMAL		`1`
`test_score_wrong`	DECIMAL		`0`
`test_score_unanswered`	DECIMAL		`0`
`test_max_score`	DECIMAL	Not null	`0`
`test_user_id`	BIGINT	Not null	`1`	foreign key to column `user_id` on table `tce_users`.
`test_score_threshold`	DECIMAL		`0`
`test_random_questions_select`		Not null	`'1'`
`test_random_questions_order`		Not null	`'1'`
`test_questions_order_mode`	SMALLINT	Not null	`0`
`test_random_answers_select`		Not null	`'1'`
`test_random_answers_order`		Not null	`'1'`
`test_answers_order_mode`	SMALLINT	Not null	`0`
`test_comment_enabled`		Not null	`'1'`
`test_menu_enabled`		Not null	`'1'`
`test_noanswer_enabled`		Not null	`'1'`
`test_mcma_radio`		Not null	`'1'`
`test_repeatable`	TINYINT	Not null	`0`
`test_mcma_partial_score`		Not null	`'1'`
`test_logout_on_timeout`		Not null	`'0'`
`test_password`	VARCHAR(255)		`NULL`

Indices:

Name	Columns	Type
PRIMARY	`test_id`	PRIMARY
ak_test_name	`test_name`	UNIQUE
p_test_user_id	`test_user_id`	INDEX

Table: `tce_test_subjects`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`subjset_tsubset_id`	BIGINT	PRIMARY, Not null		foreign key to column `tsubset_id` on table `tce_test_subject_set`.
`subjset_subject_id`	BIGINT	PRIMARY, Not null		foreign key to column `subject_id` on table `tce_subjects`.

Indices:

Name	Columns	Type
PRIMARY	`subjset_tsubset_id`, `subjset_subject_id`	PRIMARY
p_subjset_subject_id	`subjset_subject_id`	INDEX
p_subjset_tsubset_id	`subjset_tsubset_id`	INDEX

Table: `tce_tests_users`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`testuser_id`	BIGINT	PRIMARY, Auto increments, Not null
`testuser_test_id`	BIGINT	Not null, Unique		foreign key to column `test_id` on table `tce_tests`.
`testuser_user_id`	BIGINT	Not null, Unique		foreign key to column `user_id` on table `tce_users`.
`testuser_status`	SMALLINT	Not null, Unique	`0`
`testuser_creation_time`	DATETIME	Not null
`testuser_comment`	TEXT		`NULL`

Indices:

Name	Columns	Type
PRIMARY	`testuser_id`	PRIMARY
ak_testuser	`testuser_test_id`, `testuser_user_id`, `testuser_status`	UNIQUE
p_testuser_user_id	`testuser_user_id`	INDEX
p_testuser_test_id	`testuser_test_id`	INDEX

Table: `tce_tests_logs`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`testlog_id`	BIGINT	PRIMARY, Auto increments, Not null
`testlog_testuser_id`	BIGINT	Not null, Unique		foreign key to column `testuser_id` on table `tce_tests_users`.
`testlog_user_ip`	VARCHAR(39)		`NULL`
`testlog_question_id`	BIGINT	Not null, Unique		foreign key to column `question_id` on table `tce_questions`.
`testlog_answer_text`	TEXT		`NULL`
`testlog_score`	DECIMAL		`NULL`
`testlog_creation_time`	DATETIME		`NULL`
`testlog_display_time`	DATETIME		`NULL`
`testlog_change_time`	DATETIME		`NULL`
`testlog_reaction_time`	BIGINT	Not null	`0`
`testlog_order`	SMALLINT	Not null	`1`
`testlog_num_answers`	SMALLINT	Not null	`0`
`testlog_comment`	TEXT		`NULL`

Indices:

Name	Columns	Type
PRIMARY	`testlog_id`	PRIMARY
ak_testuser_question	`testlog_testuser_id`, `testlog_question_id`	UNIQUE
p_testlog_question_id	`testlog_question_id`	INDEX
p_testlog_testuser_id	`testlog_testuser_id`	INDEX

Table: `tce_tests_logs_answers`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`logansw_testlog_id`	BIGINT	PRIMARY, Not null		foreign key to column `testlog_id` on table `tce_tests_logs`.
`logansw_answer_id`	BIGINT	PRIMARY, Not null		foreign key to column `answer_id` on table `tce_answers`.
`logansw_selected`	SMALLINT	Not null	`-1`
`logansw_order`	SMALLINT	Not null	`1`
`logansw_position`	BIGINT		`NULL`

Indices:

Name	Columns	Type
PRIMARY	`logansw_testlog_id`, `logansw_answer_id`	PRIMARY
p_logansw_answer_id	`logansw_answer_id`	INDEX
p_logansw_testlog_id	`logansw_testlog_id`	INDEX

Table: `tce_user_groups`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`group_id`	BIGINT	PRIMARY, Auto increments, Not null
`group_name`	VARCHAR(255)	Not null, Unique

Indices:

Name	Columns	Type	Description
	`group_name`	UNIQUE
PRIMARY	`group_id`	PRIMARY

Table: `tce_usrgroups`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`usrgrp_user_id`	BIGINT	PRIMARY, Not null		foreign key to column `user_id` on table `tce_users`.
`usrgrp_group_id`	BIGINT	PRIMARY, Not null		foreign key to column `group_id` on table `tce_user_groups`.

Indices:

Name	Columns	Type
PRIMARY	`usrgrp_user_id`, `usrgrp_group_id`	PRIMARY
p_usrgrp_user_id	`usrgrp_user_id`	INDEX
p_usrgrp_group_id	`usrgrp_group_id`	INDEX

Table: `tce_testgroups`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`tstgrp_test_id`	BIGINT	PRIMARY, Not null		foreign key to column `test_id` on table `tce_tests`.
`tstgrp_group_id`	BIGINT	PRIMARY, Not null		foreign key to column `group_id` on table `tce_user_groups`.

Indices:

Name	Columns	Type
PRIMARY	`tstgrp_test_id`, `tstgrp_group_id`	PRIMARY
p_tstgrp_test_id	`tstgrp_test_id`	INDEX
p_tstgrp_group_id	`tstgrp_group_id`	INDEX

Table: `tce_test_subject_set`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`tsubset_id`	BIGINT	PRIMARY, Auto increments, Not null
`tsubset_test_id`	BIGINT	Not null		foreign key to column `test_id` on table `tce_tests`.
`tsubset_type`	SMALLINT	Not null	`1`
`tsubset_difficulty`	SMALLINT	Not null	`1`
`tsubset_quantity`	SMALLINT	Not null	`1`
`tsubset_answers`	SMALLINT	Not null	`0`

Indices:

Name	Columns	Type	Description
PRIMARY	`tsubset_id`	PRIMARY
p_tsubset_test_id	`tsubset_test_id`	INDEX

Table: `tce_testuser_stat`

Description:

Columns:

Column	Data type	Attributes	Default	Description
`tus_id`	BIGINT	PRIMARY, Auto increments, Not null
`tus_date`	DATETIME	Not null

Indices:

Name	Columns	Type	Description
PRIMARY	`tus_id`	PRIMARY

Table: `tce_sslcerts`

Description:

store hash codes of SSL certificates

Columns:

Column	Data type	Attributes	Default	Description
`ssl_id`	BIGINT	PRIMARY, Auto increments, Not null
`ssl_name`	VARCHAR(255)	Not null		certificate name
`ssl_hash`	VARCHAR(32)	Not null		hash of the string composed by concatenating some certificate data
`ssl_end_date`	DATETIME	Not null		certificate expiration date
`ssl_user_id`	BIGINT	Not null	`1`

Indices:

Name	Columns	Type	Description
PRIMARY	`ssl_id`	PRIMARY

Table: `tce_testsslcerts`

Description:

map the enabled certificated for tehe selected test

Columns:

Column	Data type	Attributes	Default	Description
`tstssl_test_id`	BIGINT	PRIMARY, Not null		foreign key to column `test_id` on table `tce_tests`.
`tstssl_ssl_id`	BIGINT	PRIMARY, Not null		foreign key to column `ssl_id` on table `tce_sslcerts`.

Indices:

Name	Columns	Type
PRIMARY	`tstssl_test_id`, `tstssl_ssl_id`	PRIMARY
fk_tce_testsslcerts_tce_sslcerts1_idx	`tstssl_ssl_id`	INDEX
fk_tce_testsslcerts_tce_tests1_idx	`tstssl_test_id`	INDEX

Database Documentation

Table: tce_sessions

Description:

Columns:

Indices:

Table: tce_users

Description:

Columns:

Indices:

Table: tce_modules

Description:

Columns:

Indices:

Table: tce_subjects

Description:

Columns:

Indices:

Table: tce_questions

Description:

Columns:

Indices:

Table: tce_answers

Description:

Columns:

Indices:

Table: tce_tests

Description:

Columns:

Indices:

Table: tce_test_subjects

Description:

Columns:

Indices:

Table: tce_tests_users

Description:

Columns:

Indices:

Table: tce_tests_logs

Description:

Columns:

Indices:

Table: tce_tests_logs_answers

Description:

Columns:

Indices:

Table: tce_user_groups

Description:

Columns:

Indices:

Table: tce_usrgroups

Description:

Columns:

Indices:

Table: tce_testgroups

Description:

Columns:

Indices:

Table: tce_test_subject_set

Description:

Columns:

Indices:

Table: tce_testuser_stat

Description:

Columns:

Indices:

Table: tce_sslcerts

Description:

Columns:

Indices:

Table: tce_testsslcerts

Description:

Columns:

Indices:

Table: `tce_sessions`

Table: `tce_users`

Table: `tce_modules`

Table: `tce_subjects`

Table: `tce_questions`

Table: `tce_answers`

Table: `tce_tests`

Table: `tce_test_subjects`

Table: `tce_tests_users`

Table: `tce_tests_logs`

Table: `tce_tests_logs_answers`

Table: `tce_user_groups`

Table: `tce_usrgroups`

Table: `tce_testgroups`

Table: `tce_test_subject_set`

Table: `tce_testuser_stat`

Table: `tce_sslcerts`

Table: `tce_testsslcerts`