GHSCPartyBuild/lib/考试模块建表_mysql.txt

create table test_question_libary
(
    test_question_libary_id varchar(36) not null comment '主键',
    name                 varchar(300) comment '题库名称',
    note                 varchar(500) comment '备注',
    parent_id            varchar(36) comment '父级题库',
    is_vaild             int comment '是否有效',
    createTime           datetime,
    createUserId         varchar(36),
    createUserName       varchar(300),
    updateTime           datetime,
    updateUserId         varchar(36),
    updateUserName       varchar(300),
    isPublic             int,
    orderby              int not null comment '排序',
    primary key (test_question_libary_id)
);
create table test_paper_libary
(
    test_paper_libary_id varchar(36) not null comment '主键',
    test_paper_id        varchar(36) comment '关联试卷',
    test_question_libary_id varchar(36) comment '关联题库',
    primary key (test_paper_libary_id)
);

alter table test_paper_libary add constraint FK_Reference_10 foreign key (test_question_libary_id)
    references test_question_libary (test_question_libary_id) on delete restrict on update restrict;

alter table test_paper_libary add constraint FK_Reference_11 foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;


create table test_base_question_type
(
    base_question_type_id int not null auto_increment comment '主键',
    name                 varchar(300) comment '类型名',
    diplay_order         int comment '排序',
    score                int comment '分数',
    objective_type       int comment '1、主观题
            2、客观题',
    primary key (base_question_type_id)
);

alter table test_base_question_type comment '试题类型';

create table test_question
(
    test_question_Id     varchar(36) not null comment '主键',
    base_question_type_id int comment '试题类型',
    content              varchar(1000) comment '题干',
    note                 varchar(300) comment '备注',
    is_vaild             int comment '是否有效',
    createTime           datetime,
    createUserId         varchar(36),
    createUserName       varchar(300),
    updateTime           datetime,
    updateUserId         varchar(36),
    updateUserName       varchar(300),
    difficulty           int comment '试题难度字典（QuestionDifficulty）',
    primary key (test_question_Id)
);

alter table test_question comment '试题表';

alter table test_question add constraint fk_testquestions2questiontype foreign key (base_question_type_id)
    references test_base_question_type (base_question_type_id) on delete restrict on update restrict;

create table test_question_libary_question
(
    libary_question_id   varchar(36) not null comment '主键',
    test_question_Id     varchar(36) comment '关联题目',
    test_question_libary_id varchar(36) comment '关联题库',
    primary key (libary_question_id)
);

alter table test_question_libary_question add constraint fk_question_libary_2question foreign key (test_question_Id)
    references test_question (test_question_Id) on delete restrict on update restrict;

alter table test_question_libary_question add constraint fk_question_libary_question_2quetionlibary foreign key (test_question_libary_id)
    references test_question_libary (test_question_libary_id) on delete restrict on update restrict;


create table test_question_provid_answer
(
    provid_answer_id     varchar(36) not null comment '主键',
    answer_name          varchar(4000) comment '答案内容',
    orderby              int comment '排序',
    test_question_Id     varchar(36) comment '试题',
    isright              int comment '是否正确答案',
    primary key (provid_answer_id)
);

alter table test_question_provid_answer comment '试题答案';

alter table test_question_provid_answer add constraint FK_TEST_QUE_REFERENCE_TEST_QUE foreign key (test_question_Id)
    references test_question (test_question_Id) on delete restrict on update restrict;

create table test_paper
(
    test_paper_id        varchar(36) not null comment '主键',
    PaperName            varchar(200) comment '试卷名称',
    note                 varchar(500) comment '备注',
    is_vaild             int comment '是否有效',
    paper_score          decimal(8,2) comment '试卷分数',
    createTime           datetime,
    createUserId         varchar(36),
    createUserName       varchar(300),
    updateTime           datetime,
    updateUserId         varchar(36),
    updateUserName       varchar(300),
    paperType            int comment '试卷类型：1、常规考试 2、新入职考试',
    limited_minutes      int comment '考试时长',
    partyCode            varchar(36) comment '所在组织',
    partyName            varchar(300) comment '所在组织名称',
    primary key (test_paper_id)
);

create table test_paper_basetype
(
    test_paper_basetype_id varchar(36) not null comment '主键',
    test_paper_id        varchar(36) comment '试卷',
    paperType            int comment '试卷类型：1、常规考试 2、新任专职党支部书记3、新任组织助理员4、新任宣传助理员',
    primary key (test_paper_basetype_id)
);

alter table test_paper_basetype comment '试卷类型';

alter table test_paper_basetype add constraint FK_test_paper_basetype_ref_paper foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;


create table test_paper_question_set
(
    test_paper_question_set_id varchar(36) not null comment '主键',
    test_paper_id        varchar(36) comment '试卷',
    test_question_Id     varchar(36) comment '试题',
    score                numeric(8,2) comment '分数',
    orderby              int comment '排序',
    primary key (test_paper_question_set_id)
);

alter table test_paper_question_set add constraint fk_paperquestion2question foreign key (test_question_Id)
    references test_question (test_question_Id) on delete restrict on update restrict;

alter table test_paper_question_set add constraint fk_paperquestion_2Paper foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;

create table test_paper_type
(
    test_paper_type_id   varchar(36) not null comment '主键',
    test_paper_id        varchar(36) comment '关联试卷',
    base_question_type_id int comment '试题类型',
    question_num         int comment '取题数',
    primary key (test_paper_type_id)
);

alter table test_paper_type comment '试卷题型设置';

alter table test_paper_type add constraint FK_Reference_12 foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;

alter table test_paper_type add constraint FK_test_paper_type_ref_question_type foreign key (base_question_type_id)
    references test_base_question_type (base_question_type_id) on delete restrict on update restrict;

create table test_onlinetest
(
    onlinetest_id        varchar(36) not null comment '主键',
    test_paper_id        varchar(36) comment '试卷',
    test_name            varchar(500) comment '考试名称',
    test_begin_date      datetime comment '考试开始时间',
    test_end_date        datetime comment '考试结束时间',
    limited_minutes      int comment '考试时长',
    passingscore         numeric(8,2) comment '及格分数',
    examiner             varchar(36) comment '阅卷人',
    recordStatus         numeric(2,0) comment '状态0：已删除',
    createTime           datetime,
    createUserId         varchar(36),
    createUserName       varchar(300),
    updateTime           datetime,
    updateUserId         varchar(36),
    updateUserName       varchar(300),
    publicParty          varchar(300) comment '组织单位',
    note                 varchar(2000) comment '备注',
    partyCode            varchar(36) comment '所在组织',
    partyName            varchar(300) comment '所在组织名称',
    primary key (onlinetest_id)
);

alter table test_onlinetest comment '考试设置';

alter table test_onlinetest add constraint FK_onlinetest_ref_test_paper foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;

create table test_onlinetest_man
(
    online_testman_id    varchar(36) not null,
    onlinetest_id        varchar(36) comment '考试',
    userId               varchar(36) comment '参试人',
    userName             varchar(36),
    score                numeric(8,2) comment '分数',
    modified_score       numeric(8,2) comment '修改后分数',
    test_begin_date      datetime comment '考试开始时间',
    test_end_date        datetime comment '考试结束时间',
    ranking              numeric(3,0) comment '排名',
    recordStatus         numeric(3,0) comment '1、待考试
            2、考试中
            3、已交卷
            4、缺考
            5、已公布成绩',
    primary key (online_testman_id)
);

alter table test_onlinetest_man comment '考生信息';

alter table test_onlinetest_man add constraint FK_test_onlinetest_man_ref_onlinetest foreign key (onlinetest_id)
    references test_onlinetest (onlinetest_id) on delete restrict on update restrict;

create table test_paper_difficulty
(
    paper_difficulty_id  varchar(36) not null,
    test_paper_id        varchar(36) comment '关联试卷',
    difficulty           int comment '试题难度(QuestionDifficulty)',
    primary key (paper_difficulty_id)
);

alter table test_paper_difficulty comment '试卷试题难度设置';

alter table test_paper_difficulty add constraint FK_test_paper_difficulty_ref_test_paper foreign key (test_paper_id)
    references test_paper (test_paper_id) on delete restrict on update restrict;

create table test_user_testinstance
(
    user_test_instance_id varchar(36) not null comment '主键',
    online_testman_id    varchar(36) comment '考生',
    test_question_Id     varchar(36) not null comment '试题',
    score                numeric(8,2) comment '分数',
    modified_score       numeric(8,2) comment '修改后分数',
    primary key (user_test_instance_id)
);

alter table test_user_testinstance comment '题目得分';

alter table test_user_testinstance add constraint FK_testinstance_ref_online_testman foreign key (online_testman_id)
    references test_onlinetest_man (online_testman_id) on delete restrict on update restrict;


create table test_user_testinstance_answer
(
    user_testinstance_answer_id varchar(36) not null comment '主键',
    user_test_instance_id varchar(36) comment '关联题目得分表',
    answers              varchar(4000) comment '答案',
    ordber_by            numeric(8,0) not null comment '排序',
    primary key (user_testinstance_answer_id)
);

alter table test_user_testinstance_answer comment '考生题目答案';


alter table test_user_testinstance_answer add constraint FK_testinstance_answer_ref_test_instanc foreign key (user_test_instance_id)
    references test_user_testinstance (user_test_instance_id) on delete restrict on update restrict;