examcloud-upgrade/upgrade-201909/basic.sql

----------------------------------------------------------------后端服务启动前执行-----------------------------------------------------------------------------------------
-- 新建表EC_B_STUDENT_CODE
CREATE TABLE `ec_b_student_code` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `identity_number` varchar(255) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `student_code` varchar(255) NOT NULL,
  `student_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_B_SC_005001` (`root_org_id`,`student_code`),
  KEY `IDX_B_SC_005002` (`student_id`),
  KEY `IDX_B_SC_005003` (`student_code`),
  KEY `IDX_B_SC_005004` (`identity_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 将表EC_B_STUDENT 的student_code字段割接到新表EC_B_STUDENT_CODE 
insert into ec_b_student_code(root_org_id,student_id,identity_number,student_code,creation_time,update_time)
SELECT root_org_id, id, identity_number, student_code, NOW(), NOW() from ec_b_student where student_code is not null ;

-- 删除表EC_B_STUDENT的student_code字段
ALTER  TABLE  ec_b_student   DROP  INDEX  IDX_B_S_002002;
alter table ec_b_student  drop column student_code;

-- 新建资源表
CREATE TABLE `ec_b_resource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `is_file` bit(1) NOT NULL,
  `name` varchar(255) NOT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `suffix` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_B_RESOURCE_000002` (`root_org_id`,`file_path`) USING BTREE,
  KEY `IDX_B_RESOURCE_000001` (`root_org_id`,`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新建顶级机构权限关联表
CREATE TABLE `ec_b_root_org_priv_relation` (
  `privilege_id` bigint(20) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `group_id` bigint(20) NOT NULL,
  PRIMARY KEY (`privilege_id`,`root_org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新建短信装配表
CREATE TABLE `ec_b_sms_assembly` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `destroyed` bit(1) NOT NULL,
  `enabled` bit(1) NOT NULL,
  `code` varchar(30) NOT NULL,
  `example` varchar(200) NOT NULL,
  `ext1` varchar(200) DEFAULT NULL,
  `ext10` varchar(200) DEFAULT NULL,
  `ext2` varchar(200) DEFAULT NULL,
  `ext3` varchar(200) DEFAULT NULL,
  `ext4` varchar(200) DEFAULT NULL,
  `ext5` varchar(200) DEFAULT NULL,
  `ext6` varchar(200) DEFAULT NULL,
  `ext7` varchar(200) DEFAULT NULL,
  `ext8` varchar(200) DEFAULT NULL,
  `ext9` varchar(200) DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `template` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_B_SMS_021000` (`code`),
  UNIQUE KEY `IDX_B_SMS_021001` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 修改app简称. 网考新简称 oe-face oe-student oe-websocket
update ec_b_app t  set t.simple_name = 'oe-face', t.`name`= 'EC-CORE-OE-FACE' where t.simple_name = 'oe-student-face';
update ec_b_app t  set t.simple_name = 'oe-student', t.`name`= 'EC-CORE-OE-STUDENT' where t.simple_name = 'oe-student';
update ec_b_app t  set t.simple_name = 'oe-websocket', t.`name`= 'EC-CORE-OE-WEBSOCKET' where t.simple_name = 'oe-student-websocket';






----------------------------------------------------------------后端服务启动后执行------------------------------------------------------------------------------------------