67 lines
4.1 KiB
SQL
67 lines
4.1 KiB
SQL
-- =====================================================================
|
||
-- 问卷应用 question-center R1 建表脚本（库：ysch_question）
|
||
-- 表：questionnaire / pull_cursor / answer_projection
|
||
-- 与实体（ddl-auto=update 开发期自动建表）保持同构；生产用本脚本 + ddl-auto=none
|
||
-- =====================================================================
|
||
|
||
CREATE DATABASE IF NOT EXISTS ysch_question DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
||
USE ysch_question;
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 问卷主表：一份问卷 = 一个 FC 表单（1:1）
|
||
-- ---------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `questionnaire` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
`sch_id` BIGINT NOT NULL COMMENT '学校域',
|
||
`form_uuid` VARCHAR(40) NOT NULL COMMENT 'FC form.uuid（uk 兜底防重复绑定）',
|
||
`form_code` VARCHAR(64) NULL COMMENT 'FC form.code：填写链接 #/f/{code} 入口',
|
||
`title_snapshot` VARCHAR(128) NULL COMMENT 'FC form.name 快照（以 FC 为准可刷新）',
|
||
`form_version` INT NULL COMMENT '发布核验时钉版的版本号；NULL=从未发布',
|
||
`status` INT NOT NULL DEFAULT 0 COMMENT '0=草稿 1=收集中 2=已结束（问卷投放态）',
|
||
`description` LONGTEXT NULL COMMENT 'JSON：{description,cover,thanks}',
|
||
`anonymous` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '受众含 ANONYMOUS',
|
||
`schema_snapshot` LONGTEXT NULL COMMENT '发布时钉版的 FC schema 快照（统计渲染用）',
|
||
`publish_time` DATETIME NULL,
|
||
`finish_time` DATETIME NULL,
|
||
`create_by` VARCHAR(64) NULL,
|
||
`create_time` DATETIME NULL,
|
||
`update_by` VARCHAR(64) NULL,
|
||
`update_time` DATETIME NULL,
|
||
`is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_questionnaire_form_uuid` (`form_uuid`),
|
||
KEY `idx_questionnaire_sch_status` (`sch_id`, `status`, `is_deleted`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '问卷主表';
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 投影拉取游标：每表单一行（冻结表单 append-only answer_uuid 游标）
|
||
-- ---------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `pull_cursor` (
|
||
`form_uuid` VARCHAR(40) NOT NULL COMMENT 'FC form.uuid',
|
||
`cursor` VARCHAR(40) NULL COMMENT '已拉到的最后一条 answer_uuid（FC nextCursor）',
|
||
`seen_count` BIGINT NOT NULL DEFAULT 0 COMMENT '对账：累计首见行数（含墓碑）',
|
||
`last_pull_at` DATETIME NULL,
|
||
`last_status` VARCHAR(255) NULL COMMENT 'OK / FAILED:原因',
|
||
PRIMARY KEY (`form_uuid`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '答卷投影拉取游标';
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 答卷投影行：uk answer_uuid 幂等 upsert；墓碑翻 is_deleted 不删行
|
||
-- ---------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `answer_projection` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
`answer_uuid` VARCHAR(40) NOT NULL COMMENT 'FC answer_uuid（uk）',
|
||
`form_uuid` VARCHAR(40) NOT NULL,
|
||
`values_json` LONGTEXT NOT NULL COMMENT 'FC 行 values 原文 JSON（不解析不校验）',
|
||
`submitter_id` INT NULL COMMENT 'FC userId；0=匿名行',
|
||
`subject_id` INT NULL COMMENT '被代填人；自填=本人、匿名=NULL',
|
||
`status` VARCHAR(32) NULL COMMENT 'FC 答卷状态',
|
||
`is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=FC 侧墓碑（软删）',
|
||
`fc_create_time` VARCHAR(32) NULL COMMENT 'FC 行 create_time 原文',
|
||
`fc_update_time` VARCHAR(32) NULL,
|
||
`pulled_at` DATETIME NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_answer_projection_uuid` (`answer_uuid`),
|
||
KEY `idx_answer_projection_form` (`form_uuid`, `is_deleted`, `fc_create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '答卷投影行';
|