marking-paper-struct/db/init.sql

DROP TABLE IF EXISTS `mps_wxapp_info`;
CREATE TABLE `mps_wxapp_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `openid` varchar(255) COLLATE utf8_bin NOT NULL,
  `phone` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_WXAPP_INFO_01` (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `mps_wxapp_access_token`;
CREATE TABLE `mps_wxapp_access_token` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `access_token` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `expires_time` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Table structure for mps_course
-- ----------------------------
DROP TABLE IF EXISTS `mps_course`;
CREATE TABLE `mps_course` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `school_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_COURSE_01` (`school_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_exam
-- ----------------------------
DROP TABLE IF EXISTS `mps_exam`;
CREATE TABLE `mps_exam` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `exam_status` varchar(255) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `school_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_EXAM_01` (`school_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_paper
-- ----------------------------
DROP TABLE IF EXISTS `mps_paper`;
CREATE TABLE `mps_paper` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `course_id` bigint NOT NULL,
  `exam_id` bigint NOT NULL,
  `group_finish` bit(1) NOT NULL,
  `struct_finish` bit(1) NOT NULL,
  `objective_score` double NOT NULL,
  `paper_type` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `school_id` bigint NOT NULL,
  `subjective_score` double NOT NULL,
  `total_score` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PAPER_01` (`school_id`,`exam_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_paper_detail
-- ----------------------------
DROP TABLE IF EXISTS `mps_paper_detail`;
CREATE TABLE `mps_paper_detail` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `number` int NOT NULL,
  `paper_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PAPER_DETAIL_01` (`paper_id`,`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_paper_detail_unit
-- ----------------------------
DROP TABLE IF EXISTS `mps_paper_detail_unit`;
CREATE TABLE `mps_paper_detail_unit` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `detail_id` bigint NOT NULL,
  `detail_number` int NOT NULL,
  `number` int NOT NULL,
  `objective` bit(1) NOT NULL,
  `paper_id` bigint NOT NULL,
  `score` double NOT NULL,
  `score_step` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PAPER_DETAIL_UNIT_01` (`paper_id`,`detail_id`,`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_paper_group
-- ----------------------------
DROP TABLE IF EXISTS `mps_paper_group`;
CREATE TABLE `mps_paper_group` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `number` int NOT NULL,
  `paper_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PAPER_GROUP_01` (`paper_id`,`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_paper_group_unit
-- ----------------------------
DROP TABLE IF EXISTS `mps_paper_group_unit`;
CREATE TABLE `mps_paper_group_unit` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `group_id` bigint NOT NULL,
  `paper_id` bigint NOT NULL,
  `detail_number` int NOT NULL,
  `detail_unit_number` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PAPER_GROUP_UNIT_01` (`paper_id`,`detail_number`,`detail_unit_number`),
  KEY `IDX_PAPER_GROUP_UNIT_02` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_privilege
-- ----------------------------
DROP TABLE IF EXISTS `mps_privilege`;
CREATE TABLE `mps_privilege` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ext1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ext2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ext3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ext4` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ext5` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `parent_id` bigint DEFAULT NULL,
  `seq` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PRIVILEGE_01` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_role
-- ----------------------------
DROP TABLE IF EXISTS `mps_role`;
CREATE TABLE `mps_role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `school_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_ROLE_01` (`school_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_role_privilege_relation
-- ----------------------------
DROP TABLE IF EXISTS `mps_role_privilege_relation`;
CREATE TABLE `mps_role_privilege_relation` (
  `school_id` bigint NOT NULL,
  `role_id` bigint NOT NULL,
  `privilege_id` bigint NOT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  PRIMARY KEY (`school_id`,`role_id`,`privilege_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_school
-- ----------------------------
DROP TABLE IF EXISTS `mps_school`;
CREATE TABLE `mps_school` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `contacts` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `enable` bit(1) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `code` varchar(255) COLLATE utf8_bin NOT NULL,
  `access_key` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `access_secret` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `region` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `telephone` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_SCHOOL_01` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_user
-- ----------------------------
DROP TABLE IF EXISTS `mps_user`;
CREATE TABLE `mps_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  `enable` bit(1) NOT NULL,
  `login_name` varchar(255) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `password` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `role_id` bigint NOT NULL,
  `school_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_USER_01` (`login_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for mps_user_course_relation
-- ----------------------------
DROP TABLE IF EXISTS `mps_user_course_relation`;
CREATE TABLE `mps_user_course_relation` (
  `user_id` bigint NOT NULL,
  `course_id` bigint NOT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `creator_id` bigint DEFAULT NULL,
  `updater_id` bigint DEFAULT NULL,
  PRIMARY KEY (`user_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `mps_wxapp_access_token` (`id`) VALUES (1);
INSERT INTO `mps_school` (`id`,`code`, `create_time`, `update_time`, `enable`, `name`) VALUES (1,'qmth', now(), now(), 1, '启明泰和');


INSERT INTO `mps_privilege` (`id`, `create_time`, `update_time`, `code`, `description`, `ext1`, `ext2`, `ext3`, `ext4`, `ext5`, `name`, `parent_id`, `seq`) VALUES (1, now(), now(), 'index_school', NULL, 'menu', NULL, NULL, NULL, '/struct/school', '学校管理', NULL, 1);
INSERT INTO `mps_privilege` (`id`, `create_time`, `update_time`, `code`, `description`, `ext1`, `ext2`, `ext3`, `ext4`, `ext5`, `name`, `parent_id`, `seq`) VALUES (2, now(), now(), 'exam_manage', '', 'menu', '', '', '', '', '考试管理', NULL, 2);
INSERT INTO `mps_privilege` (`id`, `create_time`, `update_time`, `code`, `description`, `ext1`, `ext2`, `ext3`, `ext4`, `ext5`, `name`, `parent_id`, `seq`) VALUES (3, now(), now(), 'index_exam', '', 'menu', '', '', '', '/struct/exam', '考试批次管理', 2, 1);
INSERT INTO `mps_privilege` (`id`, `create_time`, `update_time`, `code`, `description`, `ext1`, `ext2`, `ext3`, `ext4`, `ext5`, `name`, `parent_id`, `seq`) VALUES (4, now(), now(), 'index_paper', '', 'menu', '', '', '', '/struct/paper', '科目试卷结构', 2, 2);
INSERT INTO `mps_privilege` (`id`, `create_time`, `update_time`, `code`, `description`, `ext1`, `ext2`, `ext3`, `ext4`, `ext5`, `name`, `parent_id`, `seq`) VALUES (5, now(), now(), 'index_user', '', 'menu', '', '', '', '/struct/user', '用户管理', NULL, 3);


INSERT INTO `mps_role` (`id`, `create_time`, `update_time`, `code`, `name`, `school_id`) VALUES (1, now(), now(), 'SUPER_ADMIN', '超级管理员', -1);
INSERT INTO `mps_role` (`id`, `create_time`, `update_time`, `code`, `name`, `school_id`) VALUES (2, now(), now(), 'SCHOOL_ADMIN', '机构管理员', -1);
INSERT INTO `mps_role` (`id`, `create_time`, `update_time`, `code`, `name`, `school_id`) VALUES (3, now(), now(), 'SECTION_LEADER', '科组长', -1);

INSERT INTO `mps_role_privilege_relation` (`privilege_id`, `role_id`, `school_id`, `create_time`, `update_time`) VALUES (1, 2, -1, now(), now());
INSERT INTO `mps_role_privilege_relation` (`privilege_id`, `role_id`, `school_id`, `create_time`, `update_time`) VALUES (2, 2, -1, now(), now());
INSERT INTO `mps_role_privilege_relation` (`privilege_id`, `role_id`, `school_id`, `create_time`, `update_time`) VALUES (3, 2, -1, now(), now());
INSERT INTO `mps_role_privilege_relation` (`privilege_id`, `role_id`, `school_id`, `create_time`, `update_time`) VALUES (4, 2, -1, now(), now());
INSERT INTO `mps_role_privilege_relation` (`privilege_id`, `role_id`, `school_id`, `create_time`, `update_time`) VALUES (5, 2, -1, now(), now());


INSERT INTO `mps_user` (`id`, `create_time`, `update_time`,  `enable`,  `login_name`, `name`,  `password`,  `school_id`,  `role_id`) VALUES (1, now(), now(),  1,  'admin1', '超管1',  UPPER(SHA2('0',256)), 1, 1);
INSERT INTO `mps_user` (`id`, `create_time`, `update_time`,  `enable`,  `login_name`, `name`,  `password`,  `school_id`,  `role_id`) VALUES (2, now(), now(),  1,  'admin2', '超管2',  UPPER(SHA2('0',256)), 1, 1);
INSERT INTO `mps_user` (`id`, `create_time`, `update_time`,  `enable`,  `login_name`, `name`,  `password`,  `school_id`,  `role_id`) VALUES (3, now(), now(),  1,  'admin3', '超管3',  UPPER(SHA2('0',256)), 1, 1);
INSERT INTO `mps_user` (`id`, `create_time`, `update_time`,  `enable`,  `login_name`, `name`,  `password`,  `school_id`,  `role_id`) VALUES (4, now(), now(),  1,  'admin4', '超管4',  UPPER(SHA2('0',256)), 1, 1);
INSERT INTO `mps_user` (`id`, `create_time`, `update_time`,  `enable`,  `login_name`, `name`,  `password`,  `school_id`,  `role_id`) VALUES (5, now(), now(),  1,  'admin5', '超管5',  UPPER(SHA2('0',256)), 1, 1);