建表语句
<h1>表结构设计</h1>
<h2>basic_questionnaire_template</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_template;
CREATE TABLE basic_questionnaire_template(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(50) NOT NULL,
"templateJson" TEXT NOT NULL,
remark TEXT,
"createUser" VARCHAR(36) NOT NULL,
"createUserName" VARCHAR(50) NOT NULL,
"updateUser" VARCHAR(36),
"updateUserName" VARCHAR(50),
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_template.name IS '模板名称';
COMMENT ON COLUMN basic_questionnaire_template."templateJson" IS '问卷调查设计JSON';
</code></pre>
<h2>basic_questionnaire_template_history</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_template_history;
CREATE TABLE basic_questionnaire_template_history(
uuid VARCHAR(36) NOT NULL,
"foreignUuid" VARCHAR(36) NOT NULL,
"version" int4 NOT NULL,
name VARCHAR(50) NOT NULL,
"templateJson" TEXT NOT NULL,
remark TEXT,
"createUser" VARCHAR(36) NOT NULL,
"createUserName" VARCHAR(50) NOT NULL,
"updateUser" VARCHAR(36),
"updateUserName" VARCHAR(50),
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_template_history."foreignUuid" IS 'basic_questionnaire_template的uuid';
COMMENT ON COLUMN basic_questionnaire_template_history."version" IS '版本号';
COMMENT ON COLUMN basic_questionnaire_template_history.name IS '模板名称';
COMMENT ON COLUMN basic_questionnaire_template_history."templateJson" IS '问卷调查设计JSON';
</code></pre>
<h2>basic_questionnaire_publish</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_publish;
CREATE TABLE basic_questionnaire_publish(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(50),
code VARCHAR(50) NOT NULL,
title VARCHAR(255) NOT NULL,
"templateUuid" VARCHAR(36) NOT NULL,
"templateName" TEXT NOT NULL,
"startTime" VARCHAR(20) NOT NULL,
"endTime" VARCHAR(20) NOT NULL,
status VARCHAR(5) NOT NULL DEFAULT '未开始',
pattern varchar(10) NOT NULL,
participants TEXT NOT NULL,
"createUser" VARCHAR(36) NOT NULL,
"createUserName" VARCHAR(50) NOT NULL,
"updateUser" VARCHAR(36),
"updateUserName" VARCHAR(50),
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_publish.code IS '编号';
COMMENT ON COLUMN basic_questionnaire_publish.title IS '问卷标题';
COMMENT ON COLUMN basic_questionnaire_publish."templateUuid" IS '模板uuid';
COMMENT ON COLUMN basic_questionnaire_publish."templateName" IS '模板名称';
COMMENT ON COLUMN basic_questionnaire_publish."startTime" IS '开始时间';
COMMENT ON COLUMN basic_questionnaire_publish."endTime" IS '结束时间';
COMMENT ON COLUMN basic_questionnaire_publish.status IS '状态，未开始、进行中、已结束';
COMMENT ON COLUMN basic_questionnaire_publish.pattern IS '模式，按部门、按人员';
COMMENT ON COLUMN basic_questionnaire_publish.participants IS '参与人，部门或人员';</code></pre>
<h2>basic_questionnaire_record</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_record;
CREATE TABLE basic_questionnaire_record(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(50),
title VARCHAR(255) NOT NULL,
"publishUuid" VARCHAR(36) NOT NULL,
"questionnaireData" TEXT NOT NULL,
pattern VARCHAR(10) NOT NULL,
participant VARCHAR(36) NOT NULL,
"participantName" VARCHAR(100) NOT NULL,
submitter VARCHAR(36) NOT NULL,
"submitterName" VARCHAR(36) NOT NULL,
status VARCHAR(5) NOT NULL DEFAULT '未提交',
circulate TEXT,
"circulateName" TEXT,
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
CREATE INDEX publishUuid_idx ON basic_questionnaire_record ("publishUuid");
COMMENT ON COLUMN basic_questionnaire_record."publishUuid" IS 'basic_questionnaire_publish的问卷uuid';
COMMENT ON COLUMN basic_questionnaire_record."questionnaireData" IS '问卷填写数据';
COMMENT ON COLUMN basic_questionnaire_record.pattern IS '模式';
COMMENT ON COLUMN basic_questionnaire_record.participant IS '参与人，部门或人员';
COMMENT ON COLUMN basic_questionnaire_record."participantName" IS '参与人名称，部门或人员';
COMMENT ON COLUMN basic_questionnaire_record.submitter IS '实际提交人';
COMMENT ON COLUMN basic_questionnaire_record."submitterName" IS '实际提交人名称';
COMMENT ON COLUMN basic_questionnaire_record.status IS '填写状态，未提交、已提交';
COMMENT ON COLUMN basic_questionnaire_record."circulate" IS '传阅人';
COMMENT ON COLUMN basic_questionnaire_record."circulateName" IS '传阅人姓名';
COMMENT ON COLUMN basic_questionnaire_record."updateTime" IS '填写时间';</code></pre>
<h2>basic_questionnaire_field</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_field;
CREATE TABLE basic_questionnaire_field(
uuid VARCHAR(36) NOT NULL,
"templateUuid" VARCHAR(36) NOT NULL,
name TEXT NOT NULL,
model VARCHAR(50),
"preModel" VARCHAR(50),
"type" VARCHAR(50) NOT NULL,
"typeName" VARCHAR(50) NOT NULL,
"options" TEXT,
"option" TEXT,
"rowOption" TEXT,
"order" int4,
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
CREATE INDEX templateUuid_idx ON basic_questionnaire_field("templateUuid");
COMMENT ON COLUMN basic_questionnaire_field."templateUuid" IS 'basic_questionnaire_template的模板uuid';
COMMENT ON COLUMN basic_questionnaire_field.name IS '名称';
COMMENT ON COLUMN basic_questionnaire_field."type" IS '类型';
COMMENT ON COLUMN basic_questionnaire_field."typeName" IS '类型';
COMMENT ON COLUMN basic_questionnaire_field."order" IS '排序';
COMMENT ON COLUMN basic_questionnaire_field."option" IS '下拉数据';
COMMENT ON COLUMN basic_questionnaire_field."rowOption" IS '单选框组或多选框组的行数据';</code></pre>
<h2>basic_questionnaire_field_history</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_field_history;
CREATE TABLE basic_questionnaire_field_history(
uuid VARCHAR(36) NOT NULL,
"version" int4 NOT NULL,
"templateUuid" VARCHAR(36) NOT NULL,
name TEXT NOT NULL,
model VARCHAR(50),
"preModel" VARCHAR(50),
"type" VARCHAR(50) NOT NULL,
"typeName" VARCHAR(50) NOT NULL,
"options" TEXT,
"option" TEXT,
"rowOption" TEXT,
"order" int4,
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
CREATE INDEX templateHistoryUuid_idx ON basic_questionnaire_field_history("templateUuid");
COMMENT ON COLUMN basic_questionnaire_field_history."version" IS '版本号';
COMMENT ON COLUMN basic_questionnaire_field_history."templateUuid" IS 'basic_questionnaire_template_history的模板uuid';
COMMENT ON COLUMN basic_questionnaire_field_history.name IS '名称';
COMMENT ON COLUMN basic_questionnaire_field_history."type" IS '类型';
COMMENT ON COLUMN basic_questionnaire_field_history."typeName" IS '类型';
COMMENT ON COLUMN basic_questionnaire_field_history."order" IS '排序';
COMMENT ON COLUMN basic_questionnaire_field_history."option" IS '下拉数据';
COMMENT ON COLUMN basic_questionnaire_field_history."rowOption" IS '单选框组或多选框组的行数据';</code></pre>
<h2>basic_questionnaire_answer</h2>
<pre><code>--DROP TABLE IF EXISTS basic_questionnaire_answer;
CREATE TABLE basic_questionnaire_answer(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(50),
answer TEXT,
"answerJson" TEXT,
"recordUuid" VARCHAR(36) NOT NULL,
"fieldUuid" VARCHAR(36) NOT NULL,
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_answer."answer" IS '答案';
COMMENT ON COLUMN basic_questionnaire_answer."recordUuid" IS 'basic_questionnaire_record的uuid';
COMMENT ON COLUMN basic_questionnaire_answer."fieldUuid" IS 'basic_questionnaire_field的uuid';</code></pre>
<h2>basic_questionnaire_template_file</h2>
<pre><code>DROP TABLE IF EXISTS basic_questionnaire_template_file;
CREATE TABLE basic_questionnaire_template_file(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(255) NOT NULL,
"filePath" TEXT NOT NULL,
"fileSize" VARCHAR(20) NOT NULL,
"fileType" VARCHAR(20),
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_template_file.name IS '文件名称';
COMMENT ON COLUMN basic_questionnaire_template_file."filePath" IS '服务器保存路径';</code></pre>
<h2>basic_questionnaire_answer_file</h2>
<pre><code>DROP TABLE IF EXISTS basic_questionnaire_answer_file;
CREATE TABLE basic_questionnaire_answer_file(
uuid VARCHAR(36) NOT NULL,
name VARCHAR(255) NOT NULL,
"recordUuid" VARCHAR(36) NOT NULL,
"filePath" TEXT NOT NULL,
"fileSize" VARCHAR(20) NOT NULL,
"fileType" VARCHAR(20),
"createTime" VARCHAR(20) NOT NULL,
"updateTime" VARCHAR(20) NOT NULL,
"state" INT NOT NULL DEFAULT 0,
PRIMARY KEY(uuid)
);
COMMENT ON COLUMN basic_questionnaire_answer_file.name IS '文件名称';
COMMENT ON COLUMN basic_questionnaire_answer_file."recordUuid" IS 'basic_questionnaire_record的uuid';
COMMENT ON COLUMN basic_questionnaire_answer_file."filePath" IS '服务器保存路径';</code></pre>