examcloud-upgrade/upgrade-归档/upgrade-201911/oe.sql

/*
脚本执行说明：
1.第一步执行 updatePushScore.sql，用于更新旧表中对examRecordId的引用
2.第二步，将ec_oe_exam_record和ec_oe_exam_record_data表，分别更新为ec_oe_exam_record_bak和ec_oe_exam_record_data_bak
3.第三步，执行ec_oe_exam_record_data.sql
4.第四步，执行切数据的程序

*/
-- 1.新加字段exam_record_data_id
alter table ec_oe_exam_score_push_queue ADD COLUMN `exam_record_data_id` bigint(20) Not NULL DEFAULT 0;
-- 2.更新exam_record_data_id的数据
update ec_oe_exam_score_push_queue t1 set t1.exam_record_data_id=(select id from ec_oe_exam_record_data where exam_record_id=t1.exam_record_id);
-- 3.删除无用的exam_record_id
ALTER table ec_oe_exam_score_push_queue drop `exam_record_id`;


/*
脚本执行说明：
1.第一步执行 updatePushScore.sql，用于更新旧表中对examRecordId的引用
2.第二步，将ec_oe_exam_record和ec_oe_exam_record_data表，分别更新为ec_oe_exam_record_bak和ec_oe_exam_record_data_bak
3.第三步，执行ec_oe_exam_record_data.sql
4.第四步，执行切数据的程序

*/

CREATE TABLE `ec_oe_exam_record_data`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `root_org_id` bigint(20) NULL DEFAULT NULL,
  `org_id` bigint(20) NULL DEFAULT NULL,  
  `exam_id` bigint(20) NULL DEFAULT NULL,
  `exam_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `course_id` bigint(20) NULL DEFAULT NULL,  
  `course_level` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `exam_student_id` bigint(20) NULL DEFAULT NULL,  
  `student_id` bigint(20) NULL DEFAULT NULL,
  `student_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `identity_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `base_paper_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `paper_struct_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `paper_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `exam_record_questions_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `exam_record_status` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `start_time` datetime(0) NULL DEFAULT NULL,  
  `used_exam_time` bigint(20) NULL DEFAULT NULL,   
  `is_illegality` bit(1) NULL DEFAULT NULL,
  `is_warn` bit(1) NULL DEFAULT NULL,
  `is_audit` bit(1) NULL DEFAULT NULL,
  `is_reexamine` bit(1) NULL DEFAULT NULL,
  `is_continued` bit(1) NULL DEFAULT NULL,
  `continued_count` int(11) NULL DEFAULT NULL,
  `is_exceed` bit(1) NULL DEFAULT NULL,     
  `is_all_objective_paper` bit(1) NULL DEFAULT NULL,
  `clean_time` datetime(0) NULL DEFAULT NULL,  
  `end_time` datetime(0) NULL DEFAULT NULL,  
  `exam_order` int(11) NULL DEFAULT NULL,
  `baidu_face_liveness_success_percent` double NULL DEFAULT NULL,       
  `face_failed_count` int(11) NULL DEFAULT NULL,
  `face_landmark_val` double NULL DEFAULT NULL,
  `face_stranger_count` int(11) NULL DEFAULT NULL,
  `face_success_count` int(11) NULL DEFAULT NULL,
  `face_success_percent` double NULL DEFAULT NULL,
  `face_total_count` int(11) NULL DEFAULT NULL,
  `face_verify_result` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,  
  `info_collector` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,  
  `creation_time` datetime(0) NOT NULL,
  `update_time` datetime(0) NOT NULL,  
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_001`(`exam_student_id`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_002`(`student_code`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_003`(`student_id`, `exam_type`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_004`(`exam_id`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_005`(`course_id`) USING BTREE,
  INDEX `IDX_E_O_E_R_D_006`(`exam_record_status`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;