server/db/init.sql

-- ----------------------------
-- Table structure for sc_answer_card
-- ----------------------------
DROP TABLE IF EXISTS `sc_answer_card`;
CREATE TABLE `sc_answer_card`
(
    `exam_id`      bigint       NOT NULL,
    `number`       int          NOT NULL,
    `subject_code` varchar(64)  DEFAULT NULL,
    `subject_name` varchar(64)  DEFAULT NULL,
    `paper_count`  int          NOT NULL,
    `path`         varchar(255) NOT NULL,
    `md5`          varchar(32)  NOT NULL,
    `source`       varchar(16)  NOT NULL,
    `need_adapte`  bit(1)       NOT NULL,
    `single_page`  bit(1)       NOT NULL,
    `parameter`    text         DEFAULT NULL,
    `remark`       varchar(255) DEFAULT NULL,
    `creator_id`   bigint       DEFAULT NULL,
    `updater_id`   bigint       DEFAULT NULL,
    `create_time`  bigint       DEFAULT NULL,
    `update_time`  bigint       DEFAULT NULL,
    PRIMARY KEY (`exam_id`, `number`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for sc_exam
-- ----------------------------
DROP TABLE IF EXISTS `sc_exam`;
CREATE TABLE `sc_exam`
(
    `id`                         bigint       NOT NULL AUTO_INCREMENT,
    `school_id`                  bigint       NOT NULL,
    `school_name`                varchar(64)  NOT NULL,
    `name`                       varchar(128) NOT NULL,
    `enable`                     bit(1)       NOT NULL,
    `mode`                       varchar(16)  NOT NULL,
    `scan_by_package`            bit(1)       NOT NULL,
    `allow_unexist_paper`        bit(1)       NOT NULL,
    `answer_front_card_type`     int          NOT NULL,
    `enable_single_page_answer`  bit(1)       NOT NULL,
    `answer_paper_number_figure` int          NOT NULL,
    `enable_upload`              bit(1)       NOT NULL,
    `enable_sync_verify`         bit(1)       NOT NULL,
    `paper_type_barcode_content` text        DEFAULT NULL,
    `absent_barcode_content`     varchar(16) DEFAULT NULL,
    `creator_id`                 bigint      DEFAULT NULL,
    `updater_id`                 bigint      DEFAULT NULL,
    `create_time`                bigint      DEFAULT NULL,
    `update_time`                bigint      DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `school_enable` (`enable`, `school_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for sc_omr_task
-- ----------------------------
DROP TABLE IF EXISTS `sc_omr_task`;
CREATE TABLE `sc_omr_task`
(
    `id`           bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`      bigint      NOT NULL,
    `group_id`     bigint      NOT NULL,
    `student_id`   bigint      NOT NULL,
    `paper_number` int         NOT NULL,
    `paper_id`     int         NOT NULL,
    `card_number`  int         NOT NULL,
    `status`       varchar(16) NOT NULL,
    `device`       varchar(64) DEFAULT NULL,
    `pages`        text        NOT NULL,
    `creator_id`   bigint DEFAULT NULL,
    `updater_id`   bigint DEFAULT NULL,
    `create_time`  bigint DEFAULT NULL,
    `update_time`  bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `group_paper` (`group_id`, `paper_id`),
    KEY `exam_status` (`exam_id`, `status`, `student_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for sc_package_card
-- ----------------------------
DROP TABLE IF EXISTS `sc_package_card`;
CREATE TABLE `sc_package_card`
(
    `id`          bigint       NOT NULL AUTO_INCREMENT,
    `exam_id`     bigint       NOT NULL,
    `path`        varchar(255) NOT NULL,
    `md5`         varchar(32)  NOT NULL,
    `source`      varchar(16)  NOT NULL,
    `creator_id`  bigint DEFAULT NULL,
    `updater_id`  bigint DEFAULT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `exam` (`exam_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for sc_package_task
-- ----------------------------
DROP TABLE IF EXISTS `sc_package_task`;
CREATE TABLE `sc_package_task`
(
    `id`           bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`      bigint      NOT NULL,
    `package_code` varchar(64) NOT NULL,
    `status`       varchar(16) NOT NULL,
    `device`       varchar(64) NOT NULL,
    `creator_id`   bigint DEFAULT NULL,
    `updater_id`   bigint DEFAULT NULL,
    `create_time`  bigint DEFAULT NULL,
    `update_time`  bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `exam_package` (`exam_id`, `package_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for sc_scanner
-- ----------------------------
DROP TABLE IF EXISTS `sc_scanner`;
CREATE TABLE `sc_scanner`
(
    `device`          varchar(64) NOT NULL,
    `last_login_time` bigint DEFAULT NULL,
    `create_time`     bigint DEFAULT NULL,
    `update_time`     bigint DEFAULT NULL,
    PRIMARY KEY (`device`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- ----------------------------
-- Table structure for ss_system_config
-- ----------------------------
DROP TABLE IF EXISTS `sc_system_config`;
CREATE TABLE `sc_system_config`
(
    `id`                   bigint      NOT NULL AUTO_INCREMENT,
    `scanner_enable_login` bit(1)       DEFAULT NULL,
    `scanner_password`     varchar(16)  DEFAULT NULL,
    `image_transfer_mode`  varchar(16)  DEFAULT NULL,
    `system_mode`          varchar(16) NOT NULL,
    `client_version`       varchar(16)  DEFAULT NULL,
    `client_uri`           varchar(255) DEFAULT NULL,
    `client_md5`           varchar(32)  DEFAULT NULL,
    `client_update_time`   bigint       DEFAULT NULL,
    `creator_id`           bigint       DEFAULT NULL,
    `updater_id`           bigint       DEFAULT NULL,
    `create_time`          bigint       DEFAULT NULL,
    `update_time`          bigint       DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_package_result`;
CREATE TABLE `sc_package_result`
(
    `exam_id`      bigint      NOT NULL,
    `package_code` varchar(64) NOT NULL,
    `device`       varchar(64) NOT NULL,
    `path`         text        NOT NULL,
    `assigned`     bit(1)      NOT NULL,
    `creator_id`   bigint DEFAULT NULL,
    `updater_id`   bigint DEFAULT NULL,
    `create_time`  bigint DEFAULT NULL,
    `update_time`  bigint DEFAULT NULL,
    `upload_time`  bigint DEFAULT NULL,
    PRIMARY KEY (`exam_id`, `package_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_scanner_card`;
CREATE TABLE `sc_scanner_card`
(
    `exam_id`     bigint      NOT NULL,
    `device`      varchar(64) NOT NULL,
    `card_number` int         NOT NULL,
    `creator_id`  bigint DEFAULT NULL,
    `updater_id`  bigint DEFAULT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`exam_id`, `device`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_batch`;
CREATE TABLE `sc_batch`
(
    `id`             bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`        bigint      NOT NULL,
    `device`         varchar(64) NOT NULL,
    `package_code`   varchar(64) DEFAULT NULL,
    `scan_count`     int         NOT NULL,
    `assigned_count` int         NOT NULL,
    `status`         varchar(16) NOT NULL,
    `verify_status`  varchar(16) DEFAULT NULL,
    `creator_id`     bigint      DEFAULT NULL,
    `updater_id`     bigint      DEFAULT NULL,
    `create_time`    bigint      DEFAULT NULL,
    `update_time`    bigint      DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_paper`;
CREATE TABLE `sc_paper`
(
    `id`              bigint       NOT NULL AUTO_INCREMENT,
    `exam_id`         bigint       NOT NULL,
    `student_id`      bigint       NOT NULL,
    `batch_id`        bigint       NOT NULL,
    `card_number`     int          NOT NULL,
    `exam_number`     varchar(128) NOT NULL,
    `number`          int          NOT NULL,
    `page_count`      int          NOT NULL,
    `mismatch`        bit(1)       NOT NULL,
    `assigned`        bit(1)       NOT NULL,
    `question_filled` bit(1)       NOT NULL,
    `creator_id`      bigint DEFAULT NULL,
    `updater_id`      bigint DEFAULT NULL,
    `create_time`     bigint DEFAULT NULL,
    `update_time`     bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `batch_student_number` (`batch_id`, `student_id`, `number`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_paper_page`;
CREATE TABLE `sc_paper_page`
(
    `paper_id`    bigint       NOT NULL,
    `page_index`  int          NOT NULL,
    `batch_id`    bigint       NOT NULL,
    `student_id`  bigint       NOT NULL,
    `exam_id`     bigint       NOT NULL,
    `absent`      text     DEFAULT NULL,
    `breach`      text     DEFAULT NULL,
    `paper_type`  text     DEFAULT NULL,
    `question`    text     DEFAULT NULL,
    `selective`   text     DEFAULT NULL,
    `sheet_path`  varchar(255) NOT NULL,
    `slice_path`  text     DEFAULT NULL,
    `recog_data`  longtext DEFAULT NULL,
    `create_time` bigint   DEFAULT NULL,
    `update_time` bigint   DEFAULT NULL,
    `creator_id`  bigint   DEFAULT NULL,
    `updater_id`  bigint   DEFAULT NULL,
    PRIMARY KEY (`paper_id`, `page_index`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_paper_structure`;
CREATE TABLE `sc_paper_structure`
(
    `id`              bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`         bigint      NOT NULL,
    `card_number`     int         NOT NULL,
    `paper_number`    int         NOT NULL,
    `page_index`      int         NOT NULL,
    `field`           varchar(16) NOT NULL,
    `question_number` int         NOT NULL,
    `single`          bit(1)      NOT NULL,
    `creator_id`      bigint DEFAULT NULL,
    `updater_id`      bigint DEFAULT NULL,
    `create_time`     bigint DEFAULT NULL,
    `update_time`     bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `IDX_PAPER_STRUCTURE_001` (`exam_id`, `card_number`, `paper_number`, `page_index`, `field`,
                                          `question_number`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;


DROP TABLE IF EXISTS `sc_omr_group`;
CREATE TABLE `sc_omr_group`
(
    `id`          bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`     bigint      NOT NULL,
    `fixed`       bit(1)      NOT NULL,
    `stage`       varchar(16) NOT NULL,
    `conditions`  text        NOT NULL,
    `total_count` int         NOT NULL,
    `creator_id`  bigint DEFAULT NULL,
    `updater_id`  bigint DEFAULT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `exam_fixed` (`exam_id`, `fixed`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;


DROP TABLE IF EXISTS `sc_refix_batch`;
CREATE TABLE `sc_refix_batch`
(
    `id`           bigint NOT NULL AUTO_INCREMENT,
    `exam_id`      bigint NOT NULL,
    `card_number`  int    NOT NULL,
    `conditions`   text   NOT NULL,
    `update_slice` bit(1) NOT NULL,
    `total_count`  int    NOT NULL,
    `sucess_count` int    NOT NULL,
    `finished`     bit(1) NOT NULL,
    `creator_id`   bigint DEFAULT NULL,
    `updater_id`   bigint DEFAULT NULL,
    `create_time`  bigint DEFAULT NULL,
    `update_time`  bigint DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_student`;
CREATE TABLE `sc_student`
(
    `id`                 bigint      NOT NULL AUTO_INCREMENT,
    `exam_id`            bigint      NOT NULL,
    `exam_number`        varchar(64) NOT NULL,
    `student_code`       varchar(64) NOT NULL,
    `name`               varchar(64) NOT NULL,
    `subject_code`       varchar(64) NOT NULL,
    `package_code`       varchar(64) DEFAULT NULL,
    `exam_site`          varchar(64) DEFAULT NULL,
    `exam_room`          varchar(64) DEFAULT NULL,
    `seat_number`        varchar(16) DEFAULT NULL,
    `campus_name`        varchar(64) DEFAULT NULL,
    `status`             varchar(16) NOT NULL,
    `omr_absent`         bit(1)      NOT NULL,
    `question_filled`    bit(1)      NOT NULL,
    `assigned`           bit(1)      NOT NULL,
    `absent_suspect`     bit(1)      NOT NULL,
    `incomplete`         bit(1)      NOT NULL,
    `card_number`        int         DEFAULT NULL,
    `paper_type`         varchar(16) NOT NULL,
    `file_upload_status` varchar(16) DEFAULT NULL,
    `data_upload_status` varchar(16) DEFAULT NULL,
    `creator_id`         bigint      DEFAULT NULL,
    `updater_id`         bigint      DEFAULT NULL,
    `create_time`        bigint      DEFAULT NULL,
    `update_time`        bigint      DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `exam_number` (`exam_id`, `exam_number`),
    UNIQUE KEY `exam_student_subject` (`exam_id`, `student_code`, `subject_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_student_paper`;
CREATE TABLE `sc_student_paper`
(
    `student_id`   bigint NOT NULL,
    `paper_number` int    NOT NULL,
    `paper_id`     bigint NOT NULL,
    `creator_id`   bigint DEFAULT NULL,
    `updater_id`   bigint DEFAULT NULL,
    `create_time`  bigint DEFAULT NULL,
    `update_time`  bigint DEFAULT NULL,
    PRIMARY KEY (`student_id`, `paper_number`),
    UNIQUE KEY `paper_id` (`paper_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_subject`;
CREATE TABLE `sc_subject`
(
    `exam_id`     bigint      NOT NULL,
    `code`        varchar(64) NOT NULL,
    `name`        varchar(64) NOT NULL,
    `creator_id`  bigint DEFAULT NULL,
    `updater_id`  bigint DEFAULT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`exam_id`, `code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_file_property`;
CREATE TABLE `sc_file_property`
(
    `path`        varchar(255) NOT NULL,
    `md5`         varchar(32)  NOT NULL,
    `exam_id`     bigint       NOT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`path`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

DROP TABLE IF EXISTS `sc_user`;
CREATE TABLE `sc_user`
(
    `id`          int(11)     NOT NULL AUTO_INCREMENT,
    `login_name`  varchar(64) NOT NULL,
    `name`        varchar(64) NOT NULL,
    `password`    varchar(64) NOT NULL,
    `role`        varchar(16) NOT NULL,
    `enable`      tinyint(1)  NOT NULL,
    `school_id`   bigint      NOT NULL,
    `creator_id`  bigint DEFAULT NULL,
    `updater_id`  bigint DEFAULT NULL,
    `create_time` bigint DEFAULT NULL,
    `update_time` bigint DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `login_name` (`login_name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;