/*
============================================================
File name   : postgresql_db_structure.sql
Begin       : 2004-04-28
Last Update : 2006-10-01

Description : TCExam database structure.
Database    : PostgreSQL 8+

Author: Nicola Asuni
(c) Copyright:
              Tecnick.com S.r.l.
              Via Ugo Foscolo n.19
              09045 Quartu Sant'Elena (CA)
              ITALY
              www.tecnick.com
              info@tecnick.com

License: GNU GENERAL PUBLIC LICENSE v.2
         http://www.gnu.org/copyleft/gpl.html
============================================================
*/

/* Tables */

Create table "tce_sessions" (
	"cpsession_id" Varchar(32) NOT NULL,
	"cpsession_expiry" Timestamp NOT NULL,
	"cpsession_data" Text NOT NULL,
constraint "PK_tce_sessions_cpsession_id" primary key ("cpsession_id")
) Without Oids;


Create table "tce_users" (
	"user_id" BigSerial NOT NULL,
	"user_name" Varchar(255) NOT NULL,
	"user_password" Varchar(255) NOT NULL,
	"user_email" Varchar(255),
	"user_regdate" Timestamp NOT NULL,
	"user_ip" Varchar(15) 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 NOT NULL Default 1,
	"user_verifycode" Varchar(32) UNIQUE,
constraint "PK_tce_users_user_id" primary key ("user_id")
) Without Oids;


Create table "tce_subjects" (
	"subject_id" BigSerial NOT NULL,
	"subject_name" Varchar(255) NOT NULL,
	"subject_description" Text,
	"subject_enabled" Boolean NOT NULL Default '0',
	"subject_user_id" Bigint NOT NULL Default 1,
constraint "PK_tce_subjects_subject_id" primary key ("subject_id")
) Without Oids;


Create table "tce_questions" (
	"question_id" BigSerial NOT NULL,
	"question_subject_id" Bigint NOT NULL,
	"question_description" Text NOT NULL,
	"question_type" Smallint NOT NULL Default 1,
	"question_difficulty" Smallint NOT NULL Default 1,
	"question_enabled" Boolean NOT NULL Default '0',
constraint "PK_tce_questions_question_id" primary key ("question_id")
) Without Oids;


Create table "tce_answers" (
	"answer_id" BigSerial NOT NULL,
	"answer_question_id" Bigint NOT NULL,
	"answer_description" Text NOT NULL,
	"answer_isright" Boolean NOT NULL Default '0',
	"answer_enabled" Boolean NOT NULL Default '0',
constraint "PK_tce_answers_answer_id" primary key ("answer_id")
) Without Oids;


Create table "tce_tests" (
	"test_id" BigSerial NOT NULL,
	"test_name" Varchar(255) NOT NULL,
	"test_description" Text NOT NULL,
	"test_begin_time" Timestamp,
	"test_end_time" Timestamp,
	"test_duration_time" Smallint NOT NULL Default 0,
	"test_ip_range" Varchar(255) NOT NULL Default '*.*.*.*',
	"test_random_questions" Boolean NOT NULL Default '1',
	"test_results_to_users" Boolean NOT NULL Default '0',
	"test_score_right" Numeric(10,3) Default 0,
	"test_max_score" Numeric(10,3) NOT NULL Default 0,
	"test_user_id" Bigint NOT NULL Default 1,
constraint "PK_tce_tests_test_id" primary key ("test_id")
) Without Oids;


Create table "tce_test_subjects" (
	"subjset_tsubset_id" Bigint NOT NULL,
	"subjset_subject_id" Bigint NOT NULL,
constraint "pk_tce_test_subjects" primary key ("subjset_tsubset_id","subjset_subject_id")
) Without Oids;


Create table "tce_tests_users" (
	"testuser_id" BigSerial NOT NULL,
	"testuser_test_id" Bigint NOT NULL,
	"testuser_user_id" Bigint NOT NULL,
	"testuser_status" Smallint NOT NULL Default 0 Check (testuser_status IN (0,1,2,3,4)),
	"testuser_creation_time" Timestamp NOT NULL,
	"testuser_comment" Text,
constraint "pk_tce_tests_users" primary key ("testuser_id")
) Without Oids;


Create table "tce_tests_logs" (
	"testlog_id" BigSerial NOT NULL,
	"testlog_testuser_id" Bigint NOT NULL,
	"testlog_user_ip" Varchar(15),
	"testlog_question_id" Bigint NOT NULL,
	"testlog_answer_text" Text,
	"testlog_score" Numeric(10,3),
	"testlog_creation_time" Timestamp,
	"testlog_display_time" Timestamp,
	"testlog_change_time" Timestamp,
constraint "PK_tce_tests_logs_testlog_id" primary key ("testlog_id")
) Without Oids;


Create table "tce_tests_logs_answers" (
	"logansw_testlog_id" Bigint NOT NULL,
	"logansw_answer_id" Bigint NOT NULL,
	"logansw_selected" Boolean NOT NULL Default '0',
	"logansw_order" Smallint NOT NULL Default 1,
constraint "pk_tce_tests_logs_answers" primary key ("logansw_testlog_id","logansw_answer_id")
) Without Oids;


Create table "tce_user_groups" (
	"group_id" BigSerial NOT NULL,
	"group_name" Varchar(255) NOT NULL UNIQUE,
constraint "pk_tce_user_groups" primary key ("group_id")
) With Oids;


Create table "tce_usrgroups" (
	"usrgrp_user_id" Bigint NOT NULL,
	"usrgrp_group_id" Bigint NOT NULL,
constraint "pk_tce_usrgroups" primary key ("usrgrp_user_id","usrgrp_group_id")
) With Oids;


Create table "tce_testgroups" (
	"tstgrp_test_id" Bigint NOT NULL,
	"tstgrp_group_id" Bigint NOT NULL,
constraint "pk_tce_testgroups" primary key ("tstgrp_test_id","tstgrp_group_id")
) With Oids;


Create table "tce_test_subject_set" (
	"tsubset_id" BigSerial NOT NULL,
	"tsubset_test_id" Bigint 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,
constraint "pk_tce_test_subject_set" primary key ("tsubset_id")
) With Oids;


/* Alternate Keys */

Alter Table "tce_users" add Constraint "ak_user_name" UNIQUE ("user_name");
Alter Table "tce_users" add Constraint "ak_user_regnumber" UNIQUE ("user_regnumber");
Alter Table "tce_users" add Constraint "ak_user_ssn" UNIQUE ("user_ssn");
Alter Table "tce_subjects" add Constraint "ak_subject_name" UNIQUE ("subject_name");
Alter Table "tce_questions" add Constraint "ak_question" UNIQUE ("question_subject_id","question_description");
Alter Table "tce_answers" add Constraint "ak_answer" UNIQUE ("answer_question_id","answer_description");
Alter Table "tce_tests" add Constraint "ak_test_name" UNIQUE ("test_name");
Alter Table "tce_tests_users" add Constraint "ak_testuser" UNIQUE ("testuser_test_id","testuser_user_id");
Alter Table "tce_tests_logs" add Constraint "ak_testuser_question" UNIQUE ("testlog_testuser_id","testlog_question_id");


/*  Foreign Keys */

Alter table "tce_tests_users" add Constraint "rel_user_tests" foreign key ("testuser_user_id") references "tce_users" ("user_id") on delete cascade;
Alter table "tce_tests" add Constraint "rel_test_author" foreign key ("test_user_id") references "tce_users" ("user_id") on delete cascade;
Alter table "tce_subjects" add Constraint "rel_subject_author" foreign key ("subject_user_id") references "tce_users" ("user_id") on delete cascade;
Alter table "tce_usrgroups" add Constraint "rel_user_group" foreign key ("usrgrp_user_id") references "tce_users" ("user_id") on delete cascade;
Alter table "tce_questions" add Constraint "rel_subject_questions" foreign key ("question_subject_id") references "tce_subjects" ("subject_id") on delete cascade;
Alter table "tce_test_subjects" add Constraint "rel_subject_set" foreign key ("subjset_subject_id") references "tce_subjects" ("subject_id") on delete restrict;
Alter table "tce_answers" add Constraint "rel_question_answers" foreign key ("answer_question_id") references "tce_questions" ("question_id") on delete cascade;
Alter table "tce_tests_logs" add Constraint "rel_question_logs" foreign key ("testlog_question_id") references "tce_questions" ("question_id") on delete restrict;
Alter table "tce_tests_logs_answers" add Constraint "rel_answer_logs" foreign key ("logansw_answer_id") references "tce_answers" ("answer_id") on delete restrict;
Alter table "tce_tests_users" add Constraint "rel_test_users" foreign key ("testuser_test_id") references "tce_tests" ("test_id") on update restrict on delete cascade;
Alter table "tce_testgroups" add Constraint "rel_test_group" foreign key ("tstgrp_test_id") references "tce_tests" ("test_id") on delete cascade;
Alter table "tce_test_subject_set" add Constraint "rel_test_subjset" foreign key ("tsubset_test_id") references "tce_tests" ("test_id") on delete cascade;
Alter table "tce_tests_logs" add Constraint "rel_testuser_logs" foreign key ("testlog_testuser_id") references "tce_tests_users" ("testuser_id") on delete cascade;
Alter table "tce_tests_logs_answers" add Constraint "rel_testlog_answers" foreign key ("logansw_testlog_id") references "tce_tests_logs" ("testlog_id") on delete cascade;
Alter table "tce_usrgroups" add Constraint "rel_group_user" foreign key ("usrgrp_group_id") references "tce_user_groups" ("group_id") on delete cascade;
Alter table "tce_testgroups" add Constraint "rel_group_test" foreign key ("tstgrp_group_id") references "tce_user_groups" ("group_id") on delete cascade;
Alter table "tce_test_subjects" add Constraint "rel_set_subjects" foreign key ("subjset_tsubset_id") references "tce_test_subject_set" ("tsubset_id") on delete cascade;
