examcloud-upgrade/upgrade-归档/upgrade-201909/oe_sync.sql

-- 1.ec_oe_exam_audit表删除旧索引,并添加新索引
drop index idx_exam_record_data_id on ec_oe_exam_audit;
alter table ec_oe_exam_audit add UNIQUE INDEX IDX_E_O_E_A_001 (`exam_record_data_id`) USING BTREE;

-- 2.1.ec_oe_exam_capture字段更新
alter table ec_oe_exam_capture MODIFY `face_compare_result` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人脸比较返回信息';
alter table ec_oe_exam_capture MODIFY `faceliveness_result` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '百度在线活体检测结果-3.0新增';
alter table ec_oe_exam_capture MODIFY `ext_msg` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '其他信息';
-- 2.2.ec_oe_exam_capture表删除旧索引，并添加新索引
drop index idx_001 on ec_oe_exam_capture;
drop index idx_002 on ec_oe_exam_capture;
alter table ec_oe_exam_capture ADD UNIQUE INDEX IDX_E_O_E_C_001 (`exam_record_data_id`, `file_name` ) USING BTREE;
alter table ec_oe_exam_capture add index IDX_E_O_E_C_002 (`exam_record_data_id` ) USING BTREE;

-- 3.ec_oe_exam_capture_queue表直接删除重建
DROP TABLE IF EXISTS `ec_oe_exam_capture_queue`;
CREATE TABLE `ec_oe_exam_capture_queue`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) NOT NULL COMMENT '学生ID',
  `exam_record_data_id` bigint(20) NOT NULL,
  `base_face_token` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '底照token',
  `file_url` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '照片的url',
  `file_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '照片名称',
  `status` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '待处理,处理中,处理失败',
  `error_msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `error_num` int(11) NOT NULL DEFAULT 0,
  `has_virtual_camera` bit(1) NULL DEFAULT NULL,
  `camera_infos` varchar(800) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `ext_msg` varchar(800) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `creation_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `process_batch_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `face_compare_result` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `priority` int(11) NULL DEFAULT 0,
  `face_compare_start_time` bigint(20) NULL DEFAULT NULL,
  `faceliveness_result` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `is_pass` bit(1) NULL DEFAULT NULL,
  `is_stranger` bit(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `IDX_E_O_E_C_Q_001`(`exam_record_data_id`, `file_name`) USING BTREE,
  INDEX `IDX_E_O_E_C_Q_002`(`status`, `error_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- 4.ec_oe_exam_face_liveness_verify表删除再重建索引
drop index idx_001 on ec_oe_exam_face_liveness_verify;
drop index idx_002 on ec_oe_exam_face_liveness_verify;
alter table ec_oe_exam_face_liveness_verify add index IDX_E_O_E_F_L_V_001 (`exam_record_data_id`, `is_error` ) USING BTREE;

-- 5.添加新表ec_oe_exam_file_answer_temp
CREATE TABLE `ec_oe_exam_file_answer_temp`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime(0) NOT NULL,
  `update_time` datetime(0) NOT NULL,
  `exam_record_data_id` bigint(20) NOT NULL,
  `exam_student_id` bigint(20) NOT NULL,
  `file_path` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `question_order` int(11) NOT NULL,
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `transfer_file_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `IDX_E_O_E_F_A_T_001`(`exam_record_data_id`, `exam_student_id`, `question_order`, `file_path`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 6.1.ec_oe_exam_org_score_handle表字段更新
alter table ec_oe_exam_org_score_handle MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table ec_oe_exam_org_score_handle MODIFY `root_org_id` bigint(20) NOT NULL;
-- 6.2.ec_oe_exam_org_score_handle表删除旧索引，清加新索引
drop index idx_001 on ec_oe_exam_org_score_handle;
alter table ec_oe_exam_org_score_handle ADD UNIQUE INDEX `IDX_E_O_E_O_S_H_001`(`root_org_id` ) USING BTREE;

-- 7.1.ec_oe_exam_record表字段更新
alter table ec_oe_exam_record MODIFY `course_id` bigint(20) NOT NULL COMMENT '课程id';
alter table ec_oe_exam_record MODIFY `root_org_id` bigint(20) NOT NULL COMMENT '顶级机构ID';
alter table ec_oe_exam_record MODIFY `org_id` bigint(20) NOT NULL COMMENT '学习中心ID';
alter table ec_oe_exam_record MODIFY `paper_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
alter table ec_oe_exam_record MODIFY `info_collector` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '采集人';
-- 7.2.ec_oe_exam_record表，删除旧索引，添加新索引
drop index idx_001 on ec_oe_exam_record;
drop index idx_002 on ec_oe_exam_record;
drop index idx_003 on ec_oe_exam_record;
drop index idx_004 on ec_oe_exam_record;
drop index idx_005 on ec_oe_exam_record;
drop index idx_006 on ec_oe_exam_record;
alter table ec_oe_exam_record add index IDX_E_O_E_R_001 (`exam_student_id`) USING BTREE;
alter table ec_oe_exam_record add index IDX_E_O_E_R_002 (`student_code`) USING BTREE;
alter table ec_oe_exam_record add index IDX_E_O_E_R_003 (`student_id`, `exam_type`) USING BTREE;

-- 8.1.ec_oe_exam_record_4_marking表字段更新
alter table ec_oe_exam_record_4_marking MODIFY `course_id` bigint(20) NOT NULL;
-- 8.2.ec_oe_exam_record_4_marking表删除旧索引，添加新索引
drop index uniq_001 on ec_oe_exam_record_4_marking;
drop index idx_002 on ec_oe_exam_record_4_marking;
drop index idx_003 on ec_oe_exam_record_4_marking;
alter table ec_oe_exam_record_4_marking ADD UNIQUE INDEX IDX_E_O_E_R_4_M_001 (`exam_record_data_id`) USING BTREE;
alter table ec_oe_exam_record_4_marking add INDEX IDX_E_O_E_R_4_M_002 (`exam_id`) USING BTREE;
alter table ec_oe_exam_record_4_marking add INDEX IDX_E_O_E_R_4_M_003 (`exam_student_id`) USING BTREE;

-- 9.1.ec_oe_exam_record_data表字段更新
alter table ec_oe_exam_record_data MODIFY `is_warn` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是异常数据';
alter table ec_oe_exam_record_data MODIFY `is_audit` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被审核过';
alter table ec_oe_exam_record_data MODIFY `is_illegality` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否违纪';
alter table ec_oe_exam_record_data MODIFY `is_reexamine` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否为重考';
alter table ec_oe_exam_record_data MODIFY `is_continued` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否存在断点续考';
alter table ec_oe_exam_record_data MODIFY `is_all_objective_paper` bit(1) NULL DEFAULT NULL COMMENT '本次的试卷是否是全客观题卷';
alter table ec_oe_exam_record_data ADD COLUMN `exam_record_questions_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
-- 9.2.ec_oe_exam_record_data表删除外键约束
alter table ec_oe_exam_record_data drop foreign key FKj4vu1t2fb85iefm7pwymi2evo;
alter table ec_oe_exam_record_data drop foreign key fk_exam_record_id;
-- 9.3.ec_oe_exam_record_data表删除旧索引，添加新索引
drop index uniq_01 on ec_oe_exam_record_data;
drop index idx_02 on ec_oe_exam_record_data;
drop index idx_03 on ec_oe_exam_record_data;
alter table ec_oe_exam_record_data ADD UNIQUE INDEX IDX_E_O_E_R_D_001 (`exam_record_id`) USING BTREE;
alter table ec_oe_exam_record_data add INDEX IDX_E_O_E_R_D_002 (`exam_record_status`) USING BTREE;


-- 10.ec_oe_exam_score表删除旧索引，添加新索引
drop index idx_exam_record_data_id on ec_oe_exam_score;
alter table ec_oe_exam_score ADD UNIQUE INDEX IDX_E_O_E_S_001 (`exam_record_data_id`) USING BTREE;

-- 11.1.ec_oe_exam_score_obtain_queue表字段更新
alter table ec_oe_exam_score_obtain_queue MODIFY `exam_id` bigint(20) NOT NULL;
alter table ec_oe_exam_score_obtain_queue MODIFY `is_valid` bit(1) NOT NULL COMMENT '是否有效';
-- 11.2.ec_oe_exam_score_obtain_queue表删除旧索引，添加新索引
drop index idx_001 on ec_oe_exam_score_obtain_queue;
drop index idx_002 on ec_oe_exam_score_obtain_queue;
drop index idx_003 on ec_oe_exam_score_obtain_queue;
alter table ec_oe_exam_score_obtain_queue add INDEX `IDX_E_O_E_S_O_Q_001`(`exam_record_data_id`) USING BTREE;
alter table ec_oe_exam_score_obtain_queue add INDEX IDX_E_O_E_S_O_Q_002 (`root_org_id`, `is_valid`) USING BTREE;
alter table ec_oe_exam_score_obtain_queue add INDEX IDX_E_O_E_S_O_Q_003(`exam_id`) USING BTREE;

-- 12.1.ec_oe_exam_score_push_queue表字段更新
alter table ec_oe_exam_score_push_queue MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table ec_oe_exam_score_push_queue MODIFY `exam_id` bigint(20) NOT NULL COMMENT '考试ID';
alter table ec_oe_exam_score_push_queue MODIFY `exam_record_id` bigint(20) NOT NULL COMMENT '考试记录ID';
alter table ec_oe_exam_score_push_queue MODIFY `exam_score_id` bigint(20) NOT NULL COMMENT '考试分数ID';
alter table ec_oe_exam_score_push_queue MODIFY `root_org_id` bigint(20) NOT NULL COMMENT '学校ID';
alter table ec_oe_exam_score_push_queue MODIFY `exam_id` bigint(20) NOT NULL COMMENT '考试ID';
-- 12.2.ec_oe_exam_score_push_queue表删除旧索引，添加新索引
drop index idx_01 on ec_oe_exam_score_push_queue;
drop index uniq_002 on ec_oe_exam_score_push_queue;
drop index uniq_003 on ec_oe_exam_score_push_queue;
alter table ec_oe_exam_score_push_queue add INDEX `IDX_E_O_E_S_P_Q_001`(`status`) USING BTREE;
alter table ec_oe_exam_score_push_queue ADD UNIQUE INDEX `IDX_E_O_E_S_P_Q_002`(`exam_record_id`) USING BTREE;
alter table ec_oe_exam_score_push_queue ADD UNIQUE INDEX `IDX_E_O_E_S_P_Q_003`(`exam_score_id`) USING BTREE;

-- 13.1.ec_oe_exam_student表字段更新
alter table ec_oe_exam_student MODIFY `info_collector` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '采集人';
alter table ec_oe_exam_student MODIFY `grade` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
-- 13.2.ec_oe_exam_student表删除旧索引，添加新索引
drop index idx_001 on ec_oe_exam_student;
drop index idx_002 on ec_oe_exam_student;
drop index idx_003 on ec_oe_exam_student;
drop index idx_004 on ec_oe_exam_student;
drop index idx_005 on ec_oe_exam_student;
alter table ec_oe_exam_student ADD UNIQUE INDEX `IDX_E_O_E_S_001`(`exam_student_id`) USING BTREE;
alter table ec_oe_exam_student add INDEX `IDX_E_O_E_S_002`(`student_id`) USING BTREE;
alter table ec_oe_exam_student add INDEX `IDX_E_O_E_S_003`(`identity_number`) USING BTREE;
alter table ec_oe_exam_student add INDEX `IDX_E_O_E_S_004`(`student_code`) USING BTREE;
alter table ec_oe_exam_student add INDEX `IDX_E_O_E_S_005`(`exam_id`) USING BTREE;

-- 14.添加新表ec_oe_examing_record
CREATE TABLE `ec_oe_examing_record`  (
  `id` bigint(20) NOT NULL,
  `creation_time` datetime(0) NOT NULL,
  `update_time` datetime(0) NOT NULL,
  `exam_record_data_id` bigint(20) NULL DEFAULT NULL,
  `student_id` bigint(20) NULL DEFAULT NULL,
  `is_exceed` bit(1) NULL DEFAULT NULL,
  `continued_count` int(11) NULL DEFAULT NULL,
  `exam_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_continued` bit(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `IDX_E_O_E_R_001`(`exam_record_data_id`) USING BTREE,
  INDEX `IDX_E_O_E_R_002`(`student_id`) USING BTREE,
  INDEX `IDX_E_O_E_R_003`(`exam_type`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


-- 15.添加新表ec_oe_hand_in_exam_record
CREATE TABLE `ec_oe_hand_in_exam_record`  (
  `id` bigint(20) NOT NULL,
  `creation_time` datetime(0) NOT NULL,
  `update_time` datetime(0) NOT NULL,
  `exam_record_data_id` bigint(20) NOT NULL,
  `student_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `IDX_E_O_H_I_E_R_001`(`exam_record_data_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;