AI_Engineering/ais_knowledge/knowledge/sql/knowledge.sql

----创建序列
CREATE SEQUENCE ai_knowledge_file_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE "public"."ai_knowledge_file" (
  "id" int4 NOT NULL DEFAULT nextval('ai_knowledge_file_seq'::regclass),
  "name" varchar(128) COLLATE "pg_catalog"."default",
  "url" varchar(256) COLLATE "pg_catalog"."default",
  "type" varchar(128) COLLATE "pg_catalog"."default",
  "size" "pg_catalog"."numeric",
  "parent_id" int4 DEFAULT 0,
  "vector_id" int4,
  "sort" int2,
  "creator" varchar(64) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  "updater" varchar(64) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6),
  "deleted" "pg_catalog"."numeric" DEFAULT 0,
  "type_name" varchar(128) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."ai_knowledge_file"."id" IS '主键ID';
COMMENT ON COLUMN "public"."ai_knowledge_file"."name" IS '文件名称';
COMMENT ON COLUMN "public"."ai_knowledge_file"."url" IS '文件URL';
COMMENT ON COLUMN "public"."ai_knowledge_file"."type" IS '文件类型';
COMMENT ON COLUMN "public"."ai_knowledge_file"."size" IS '文件大小';
COMMENT ON COLUMN "public"."ai_knowledge_file"."parent_id" IS '父ID';
COMMENT ON COLUMN "public"."ai_knowledge_file"."vector_id" IS '向量库ID';
COMMENT ON COLUMN "public"."ai_knowledge_file"."sort" IS '排序';
COMMENT ON COLUMN "public"."ai_knowledge_file"."type_name" IS '分类类型';

-- ----------------------------
-- Primary Key structure for table ai_knowledge_file
-- ----------------------------
ALTER TABLE "public"."ai_knowledge_file" ADD CONSTRAINT "ai_knowledage_file_pkey" PRIMARY KEY ("id");

----创建序列
CREATE SEQUENCE ai_knowledge_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
	
CREATE TABLE "public"."ai_knowledge_type" (
  "id" int4 NOT NULL DEFAULT nextval('ai_knowledge_type_seq'::regclass),
  "name" varchar(128) COLLATE "pg_catalog"."default",
  "sort" int2,
  "parent_id" int4 NOT NULL DEFAULT 0,
  "creator" varchar(64) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  "updater" varchar(64) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6),
  "deleted" "pg_catalog"."numeric" DEFAULT 0
)
;
COMMENT ON COLUMN "public"."ai_knowledge_type"."id" IS '主键ID';
COMMENT ON COLUMN "public"."ai_knowledge_type"."name" IS '分类名称';
COMMENT ON COLUMN "public"."ai_knowledge_type"."sort" IS '排序';
COMMENT ON COLUMN "public"."ai_knowledge_type"."parent_id" IS '父ID';

-- ----------------------------
-- Primary Key structure for table ai_knowledge_type
-- ----------------------------
ALTER TABLE "public"."ai_knowledge_type" ADD CONSTRAINT "ai_knowledge_type_pkey" PRIMARY KEY ("id");

----创建序列
CREATE SEQUENCE ai_question_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE "public"."ai_question" (
                                        "id" int8 NOT NULL DEFAULT nextval('ai_question_seq'::regclass),
                                        "question" text COLLATE "pg_catalog"."default",
                                        "answer" text COLLATE "pg_catalog"."default",
                                        "question_type" varchar(4) COLLATE "pg_catalog"."default" DEFAULT '知识库'::character varying,
                                        "feedback" int4 DEFAULT 0,
                                        "creator" varchar(64) COLLATE "pg_catalog"."default",
                                        "create_time" timestamp(6),
                                        "updater" varchar(64) COLLATE "pg_catalog"."default",
                                        "update_time" timestamp(6),
                                        "deleted" "pg_catalog"."numeric" DEFAULT 0,
                                        "keywords" text COLLATE "pg_catalog"."default",
                                        "net_answer" int4 DEFAULT 0,
                                        "net_answer_manual" int4 DEFAULT 0,
                                        "answer_sources" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."ai_question"."question" IS '问题';
COMMENT ON COLUMN "public"."ai_question"."answer" IS '回答';
COMMENT ON COLUMN "public"."ai_question"."question_type" IS '问题类型';
COMMENT ON COLUMN "public"."ai_question"."feedback" IS '反馈类型 - 0 :无反馈，1: 赞， 2: 踩';
COMMENT ON COLUMN "public"."ai_question"."keywords" IS '问题关键词';
COMMENT ON COLUMN "public"."ai_question"."net_answer" IS '是否全网回答 0 - 否 1 - 是';
COMMENT ON COLUMN "public"."ai_question"."net_answer_manual" IS '是否手动切换全网 0 - 否 1 - 是';
COMMENT ON COLUMN "public"."ai_question"."answer_sources" IS '回答来源';

-- ----------------------------
-- Primary Key structure for table ai_question
-- ----------------------------
ALTER TABLE "public"."ai_question" ADD CONSTRAINT "ai_question_pkey" PRIMARY KEY ("id");



----创建序列
CREATE SEQUENCE infra_file_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE "public"."infra_file" (
                                       "id" "pg_catalog"."numeric" NOT NULL,
                                       "config_id" "pg_catalog"."numeric",
                                       "path" varchar(512) COLLATE "pg_catalog"."default",
                                       "url" text COLLATE "pg_catalog"."default",
                                       "type" varchar(128) COLLATE "pg_catalog"."default",
                                       "size" "pg_catalog"."numeric",
                                       "creator" varchar(64) COLLATE "pg_catalog"."default",
                                       "create_time" timestamp(6),
                                       "updater" varchar(64) COLLATE "pg_catalog"."default",
                                       "update_time" timestamp(6),
                                       "deleted" "pg_catalog"."numeric" DEFAULT 0,
                                       "name" varchar(512) COLLATE "pg_catalog"."default",
                                       "project_id" varchar(60) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Primary Key structure for table infra_file
-- ----------------------------
ALTER TABLE "public"."infra_file" ADD CONSTRAINT "infra_file_pkey" PRIMARY KEY ("id");

----创建序列
CREATE SEQUENCE infra_file_config_seq
    START WITH 2
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE "public"."infra_file_config" (
                                              "id" "pg_catalog"."numeric" NOT NULL,
                                              "name" varchar(63) COLLATE "pg_catalog"."default",
                                              "storage" "pg_catalog"."numeric",
                                              "remark" varchar(255) COLLATE "pg_catalog"."default",
                                              "master" "pg_catalog"."numeric",
                                              "config" text COLLATE "pg_catalog"."default",
                                              "creator" varchar(64) COLLATE "pg_catalog"."default",
                                              "create_time" timestamp(6),
                                              "updater" varchar(64) COLLATE "pg_catalog"."default",
                                              "update_time" timestamp(6),
                                              "deleted" "pg_catalog"."numeric" DEFAULT 0
)
;

-- ----------------------------
-- Records of infra_file_config
-- ----------------------------
INSERT INTO "public"."infra_file_config" VALUES (0, '数据库', 1, '我是数据库', 1, '{"@class":"com.zjugis.ai.knowledge.file.client.db.DBFileClientConfig","domain":"http://127.0.0.1:48080"}', '1', '2022-03-15 23:56:24', '1', '2022-03-26 21:39:26', 0);

-- ----------------------------
-- Primary Key structure for table infra_file_config
-- ----------------------------
ALTER TABLE "public"."infra_file_config" ADD CONSTRAINT "infra_file_config_pkey" PRIMARY KEY ("id");

----创建序列
CREATE SEQUENCE infra_file_content_seq
    START WITH 2
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE "public"."infra_file_content" (
                                               "id" "pg_catalog"."numeric" NOT NULL,
                                               "config_id" "pg_catalog"."numeric",
                                               "path" varchar(512) COLLATE "pg_catalog"."default",
                                               "content" bytea,
                                               "creator" varchar(64) COLLATE "pg_catalog"."default",
                                               "create_time" timestamp(6),
                                               "updater" varchar(64) COLLATE "pg_catalog"."default",
                                               "update_time" timestamp(6),
                                               "deleted" "pg_catalog"."numeric" DEFAULT 0
)
;

-- ----------------------------
-- Primary Key structure for table infra_file_content
-- ----------------------------
ALTER TABLE "public"."infra_file_content" ADD CONSTRAINT "infra_file_content_pkey" PRIMARY KEY ("id");


---知识库类型表添加向量库ID字段
ALTER TABLE "public"."ai_knowledge_type"
    ADD COLUMN "vector_id" varchar(32);

COMMENT ON COLUMN "public"."ai_knowledge_type"."vector_id" IS '向量库ID';

ALTER TABLE "public"."ai_knowledge_file"
ALTER COLUMN "vector_id" TYPE varchar(32) USING "vector_id"::varchar(32);

--知识库向量库表
CREATE TABLE "public"."ai_knowledge_vector" (
                                                "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
                                                "user_id" varchar(64) COLLATE "pg_catalog"."default",
                                                "creator" varchar(64) COLLATE "pg_catalog"."default",
                                                "create_time" timestamp(6),
                                                "updater" varchar(64) COLLATE "pg_catalog"."default",
                                                "update_time" timestamp(6),
                                                "deleted" "pg_catalog"."numeric" DEFAULT 0
)
;
COMMENT ON COLUMN "public"."ai_knowledge_vector"."id" IS '主键ID(向量库collection)';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."user_id" IS '用户ID';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."creator" IS '创建人';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."updater" IS '更新人';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."update_time" IS '更新时间';
COMMENT ON COLUMN "public"."ai_knowledge_vector"."deleted" IS '是否删除';

-- ----------------------------
-- Primary Key structure for table ai_knowledge_vector
-- ----------------------------
ALTER TABLE "public"."ai_knowledge_vector" ADD CONSTRAINT "ai_knowledge_vector_pkey" PRIMARY KEY ("id");


ALTER TABLE "public"."ai_question"
    ADD COLUMN "chat_id" varchar(32);

COMMENT ON COLUMN "public"."ai_question"."chat_id" IS '聊天ID';

--AI聊天表
CREATE TABLE "public"."ai_chat" (
                                    "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
                                    "tittle" varchar(255) COLLATE "pg_catalog"."default",
                                    "creator" varchar(64) COLLATE "pg_catalog"."default",
                                    "create_time" timestamp(6),
                                    "updater" varchar(64) COLLATE "pg_catalog"."default",
                                    "update_time" timestamp(6),
                                    "deleted" "pg_catalog"."numeric" DEFAULT 0
)
;
COMMENT ON COLUMN "public"."ai_chat"."id" IS '主键ID';
COMMENT ON COLUMN "public"."ai_chat"."tittle" IS '聊天标题';

-- ----------------------------
-- Primary Key structure for table ai_chat
-- ----------------------------
ALTER TABLE "public"."ai_chat" ADD CONSTRAINT "ai_chat_pkey" PRIMARY KEY ("id");

ALTER TABLE "public"."ai_question"
    ADD COLUMN "think_time" int4;

COMMENT ON COLUMN "public"."ai_question"."think_time" IS '思考时间';

CREATE TABLE "public"."ai_knowledge_oa_file" (
                                                 "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
                                                 "file_id" int4,
                                                 "publish_unit" varchar(128) COLLATE "pg_catalog"."default",
                                                 "publish_date" timestamp(0),
                                                 "effect_level" varchar(36) COLLATE "pg_catalog"."default",
                                                 "publish_area" varchar(64) COLLATE "pg_catalog"."default",
                                                 "document_num" varchar(64) COLLATE "pg_catalog"."default",
                                                 "create_oa_user_id" varchar(36) COLLATE "pg_catalog"."default",
                                                 "creator" varchar(64) COLLATE "pg_catalog"."default",
                                                 "create_time" timestamp(6),
                                                 "updater" varchar(64) COLLATE "pg_catalog"."default",
                                                 "update_time" timestamp(6),
                                                 "deleted" "pg_catalog"."numeric" DEFAULT 0,
                                                 "is_vectorized" bool DEFAULT false
)
;
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."id" IS '主键ID';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."file_id" IS '文件ID';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."publish_unit" IS '发布单位';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."publish_date" IS '发布日期';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."effect_level" IS '效力级别';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."publish_area" IS '发布区域';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."document_num" IS '文号';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."create_oa_user_id" IS '创建人OAID';
COMMENT ON COLUMN "public"."ai_knowledge_oa_file"."is_vectorized" IS '是否向量化';

-- ----------------------------
-- Primary Key structure for table ai_knowledge_oa_file
-- ----------------------------
ALTER TABLE "public"."ai_knowledge_oa_file" ADD CONSTRAINT "ai_knowledge_oa_file_pkey" PRIMARY KEY ("id");