qrzk-jk/sql/qrzk_jk.sql

/*
SQLyog Community v11.41 (64 bit)
MySQL - 5.6.17 : Database - qrzk_jk
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `b_book` */

DROP TABLE IF EXISTS `b_book`;

CREATE TABLE `b_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `editor` varchar(100) NOT NULL,
  `press` varchar(100) NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `price` decimal(5,2) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL COMMENT '0停用 1启用',
  `remark` varchar(255) DEFAULT NULL,
  `chapter_size` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=760 DEFAULT CHARSET=utf8 COMMENT='教材';

/*Table structure for table `b_course` */

DROP TABLE IF EXISTS `b_course`;

CREATE TABLE `b_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `code_hubei` varchar(10) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `score` float DEFAULT NULL,
  `type` tinyint(1) DEFAULT NULL COMMENT '课程类型：0：省考；1：国考',
  `remark` varchar(255) DEFAULT NULL,
  `status` int(11) DEFAULT NULL COMMENT '0停考 1开考',
  `update_date` datetime DEFAULT NULL COMMENT '最近更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1242 DEFAULT CHARSET=utf8 COMMENT='课程';

/*Table structure for table `qr_code_book` */

DROP TABLE IF EXISTS `qr_code_book`;

CREATE TABLE `qr_code_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `random_code` varchar(100) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `qr_code_book_log` */

DROP TABLE IF EXISTS `qr_code_book_log`;

CREATE TABLE `qr_code_book_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_num` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `create_by` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper` */

DROP TABLE IF EXISTS `qr_paper`;

CREATE TABLE `qr_paper` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL COMMENT '试卷编号',
  `course_id` int(11) DEFAULT NULL COMMENT '课程id',
  `course_code` varchar(50) DEFAULT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL COMMENT '教材id',
  `book_name` varchar(100) DEFAULT NULL COMMENT '教材名称',
  `book_author` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '教材编者',
  `book_press` varchar(100) DEFAULT NULL COMMENT '教材出版社',
  `book_press_year` varchar(10) DEFAULT NULL COMMENT '教材版次',
  `school` varchar(100) DEFAULT NULL COMMENT '试卷出题学校',
  `status` varchar(20) DEFAULT NULL COMMENT '状态',
  `is_special_answer_sheet` tinyint(1) DEFAULT NULL,
  `paper_path` varchar(200) DEFAULT NULL COMMENT '电子试卷地址',
  `standard_answer_path` varchar(200) DEFAULT NULL COMMENT '标答地址',
  `answer_sheet_path` varchar(100) DEFAULT NULL COMMENT '提卡地址',
  `attachments_path` varchar(500) DEFAULT NULL COMMENT '附件地址，多个附件逗号隔开',
  `pictures_path` varchar(500) DEFAULT NULL COMMENT '图片地址，多个图片逗号隔开',
  `remark` varchar(200) DEFAULT NULL,
  `reserved` varchar(100) DEFAULT NULL COMMENT '保留字段',
  `created_dt` datetime DEFAULT NULL COMMENT '创建时间',
  `created_by` int(11) DEFAULT NULL COMMENT '创建人',
  `updated_dt` datetime DEFAULT NULL COMMENT '更新时间',
  `updated_by` int(11) DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`id`),
  KEY `course_id` (`course_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `qr_paper_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `b_course` (`id`),
  CONSTRAINT `qr_paper_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `b_book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2012 DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper_batch` */

DROP TABLE IF EXISTS `qr_paper_batch`;

CREATE TABLE `qr_paper_batch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(30) DEFAULT NULL COMMENT '批次代码',
  `type` varchar(30) DEFAULT NULL COMMENT '批次类型：0:毛坯 1：清样 2：启用',
  `status` varchar(30) DEFAULT NULL COMMENT '批次状态：0：未启动 1：启动 2：完成 3：取消',
  `start_date` date DEFAULT NULL COMMENT '批次开始日期',
  `end_date` date DEFAULT NULL COMMENT '批次结束日期',
  `admin` varchar(100) DEFAULT NULL COMMENT '负责人',
  `created_by` int(11) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  `remark` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper_dbf_paper_info` */

DROP TABLE IF EXISTS `qr_paper_dbf_paper_info`;

CREATE TABLE `qr_paper_dbf_paper_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paper_batch_id` int(11) DEFAULT NULL,
  `course_code` varchar(11) DEFAULT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `book_name` varchar(100) DEFAULT NULL,
  `book_author` varchar(30) DEFAULT NULL,
  `book_press` varchar(100) DEFAULT NULL,
  `book_press_year` varchar(10) DEFAULT NULL,
  `related_paper_ids` varchar(100) DEFAULT NULL,
  `search_keyword` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1107 DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper_file_suffix` */

DROP TABLE IF EXISTS `qr_paper_file_suffix`;

CREATE TABLE `qr_paper_file_suffix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `suffix` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper_paper_batch` */

DROP TABLE IF EXISTS `qr_paper_paper_batch`;

CREATE TABLE `qr_paper_paper_batch` (
  `paper_batch_id` int(11) NOT NULL COMMENT '批次id',
  `paper_id` int(11) DEFAULT NULL COMMENT '试卷id',
  `is_outbound_scanned` tinyint(1) DEFAULT NULL COMMENT '是否出库扫描',
  `is_storage_scanned` tinyint(1) DEFAULT NULL COMMENT '是否入库扫描',
  `is_paper_return` tinyint(1) DEFAULT NULL,
  `is_standard_answer_return` tinyint(1) DEFAULT NULL,
  `is_answer_sheet_return` tinyint(1) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL COMMENT '教材id',
  `book_name` varchar(100) DEFAULT NULL,
  `book_author` varchar(100) DEFAULT NULL,
  `book_press` varchar(100) DEFAULT NULL,
  `book_press_year` varchar(10) DEFAULT NULL,
  `school` varchar(100) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  KEY `paper_batch_id` (`paper_batch_id`),
  KEY `paper_id` (`paper_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `qr_paper_paper_batch_ibfk_1` FOREIGN KEY (`paper_batch_id`) REFERENCES `qr_paper_batch` (`id`),
  CONSTRAINT `qr_paper_paper_batch_ibfk_2` FOREIGN KEY (`paper_id`) REFERENCES `qr_paper` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `qr_paper_rel_dbf_paper_info` */

DROP TABLE IF EXISTS `qr_paper_rel_dbf_paper_info`;

CREATE TABLE `qr_paper_rel_dbf_paper_info` (
  `dbf_paper_info_id` int(11) NOT NULL,
  `paper_id` int(11) NOT NULL,
  PRIMARY KEY (`dbf_paper_info_id`,`paper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `s_role` */

DROP TABLE IF EXISTS `s_role`;

CREATE TABLE `s_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `is_enabled` int(1) DEFAULT NULL,
  `user_type` int(11) DEFAULT NULL,
  `role_type` int(11) DEFAULT NULL,
  `update_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

/*Table structure for table `s_user` */

DROP TABLE IF EXISTS `s_user`;

CREATE TABLE `s_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `is_enabled` int(11) DEFAULT NULL,
  `last_login_date` datetime DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `password` varchar(200) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `login_name` varchar(50) NOT NULL,
  `certino` varchar(18) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `position` varchar(50) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `institute_id` int(11) DEFAULT NULL,
  `user_type` int(11) DEFAULT '1',
  `school` varchar(100) DEFAULT NULL COMMENT '单位',
  `gender` varchar(5) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8;

/*Table structure for table `s_user_role` */

DROP TABLE IF EXISTS `s_user_role`;

CREATE TABLE `s_user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `FK9E393B9ED604833C` (`role_id`),
  KEY `FK9E393B9E7B2F471C` (`user_id`),
  CONSTRAINT `s_user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `s_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;