examcloud-upgrade/upgrade-201909/examwork.sql

----------------------------------------------------------------后端服务启动前执行-----------------------------------------------------------------------------------------
-- 新增通知相关表
CREATE TABLE `ec_e_notice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `content` longtext,
  `notice_status` varchar(255) NOT NULL,
  `publish_time` datetime DEFAULT NULL,
  `publisher` varchar(100) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_E_NOTICE_001001` (`root_org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ec_e_notice_publish_schedule` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `notice_id` bigint(20) DEFAULT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `publish_status` varchar(255) NOT NULL,
  `max_common_user_id` bigint(20) DEFAULT NULL,
  `max_student_id` bigint(20) DEFAULT NULL,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_E_NOTICE_PUBLISH_SCHEDULE_001002` (`root_org_id`,`notice_id`),
  KEY `IDX_E_NOTICE_PUBLISH_SCHEDULE_001001` (`root_org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ec_e_notice_receiver_rule` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `notice_id` bigint(20) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `rule_type` varchar(255) NOT NULL,
  `rule_value` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_E_N_RULE_001001` (`root_org_id`),
  KEY `IDX_E_N_RULE_001002` (`notice_id`),
  KEY `IDX_E_N_RULE_001003` (`notice_id`,`rule_type`,`rule_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ec_e_notice_rule_publish_progress` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `max_common_user_id` bigint(20) DEFAULT NULL,
  `max_student_id` bigint(20) DEFAULT NULL,
  `notice_id` bigint(20) DEFAULT NULL,
  `notice_receiver_rule_type` varchar(255) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_E_NOTICE_R_P_R_001001` (`notice_id`,`notice_receiver_rule_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ec_e_user_notice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `has_read` bit(1) NOT NULL,
  `notice_id` bigint(20) NOT NULL,
  `root_org_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `user_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_E_U_NOTICE_001002` (`notice_id`,`user_type`,`user_id`),
  KEY `IDX_E_U_NOTICE_001001` (`notice_id`),
  KEY `IDX_E_U_NOTICE_001003` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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