dingtalk_api/doc/dingtalk.sql

-- ----------------------------
-- Table structure for app
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."app";
CREATE TABLE "dingtalk"."app" (
  "app_id" text COLLATE "pg_catalog"."default" NOT NULL,
  "app_secret" text COLLATE "pg_catalog"."default",
  "app_name" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "deleted" int4 DEFAULT 0
)
;
COMMENT ON COLUMN "dingtalk"."app"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."app"."app_secret" IS '应用密钥';
COMMENT ON COLUMN "dingtalk"."app"."app_name" IS '应用名称';
COMMENT ON COLUMN "dingtalk"."app"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."app"."update_time" IS '更新时间';
COMMENT ON COLUMN "dingtalk"."app"."deleted" IS '是否删除，0否1是';
COMMENT ON TABLE "dingtalk"."app" IS '应用';

-- ----------------------------
-- Table structure for app_robot
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."app_robot";
CREATE TABLE "dingtalk"."app_robot" (
  "app_id" text COLLATE "pg_catalog"."default" NOT NULL,
  "robot_code" text COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "dingtalk"."app_robot"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."app_robot"."robot_code" IS '机器人编码';
COMMENT ON TABLE "dingtalk"."app_robot" IS '应用机器人关联';

-- ----------------------------
-- Table structure for app_sftp
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."app_sftp";
CREATE TABLE "dingtalk"."app_sftp" (
  "sftp_id" text COLLATE "pg_catalog"."default" NOT NULL,
  "app_id" text COLLATE "pg_catalog"."default",
  "host" text COLLATE "pg_catalog"."default",
  "port" int4,
  "username" text COLLATE "pg_catalog"."default",
  "password" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "dingtalk"."app_sftp"."sftp_id" IS 'sftp id';
COMMENT ON COLUMN "dingtalk"."app_sftp"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."app_sftp"."host" IS '主机';
COMMENT ON COLUMN "dingtalk"."app_sftp"."port" IS '端口';
COMMENT ON COLUMN "dingtalk"."app_sftp"."username" IS '用户名';
COMMENT ON COLUMN "dingtalk"."app_sftp"."password" IS '密码';
COMMENT ON COLUMN "dingtalk"."app_sftp"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."app_sftp"."update_time" IS '更新时间';
COMMENT ON TABLE "dingtalk"."app_sftp" IS '应用sftp账号';

-- ----------------------------
-- Table structure for app_task
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."app_task";
CREATE TABLE "dingtalk"."app_task" (
  "task_id" text COLLATE "pg_catalog"."default" NOT NULL,
  "task_name" text COLLATE "pg_catalog"."default",
  "description" text COLLATE "pg_catalog"."default",
  "status" int4,
  "cron" text COLLATE "pg_catalog"."default",
  "app_id" text COLLATE "pg_catalog"."default",
  "robot_code" text COLLATE "pg_catalog"."default",
  "conversation_type" int4,
  "open_conversation_id" text COLLATE "pg_catalog"."default",
  "phones" text COLLATE "pg_catalog"."default",
  "user_ids" text COLLATE "pg_catalog"."default",
  "sftp_id" text COLLATE "pg_catalog"."default",
  "data_dir" text COLLATE "pg_catalog"."default",
  "file_pattern" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "dingtalk"."app_task"."task_id" IS '任务id';
COMMENT ON COLUMN "dingtalk"."app_task"."task_name" IS '任务名称';
COMMENT ON COLUMN "dingtalk"."app_task"."description" IS '任务描述';
COMMENT ON COLUMN "dingtalk"."app_task"."status" IS '任务状态，0已停止1运行中2已删除';
COMMENT ON COLUMN "dingtalk"."app_task"."cron" IS 'cron表达式';
COMMENT ON COLUMN "dingtalk"."app_task"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."app_task"."robot_code" IS '机器人编码';
COMMENT ON COLUMN "dingtalk"."app_task"."conversation_type" IS '会话类型：1：单聊，2：群聊';
COMMENT ON COLUMN "dingtalk"."app_task"."open_conversation_id" IS '群id';
COMMENT ON COLUMN "dingtalk"."app_task"."phones" IS '接收机器人消息的用户的手机号列表';
COMMENT ON COLUMN "dingtalk"."app_task"."user_ids" IS '接收机器人消息的用户的userId列表';
COMMENT ON COLUMN "dingtalk"."app_task"."sftp_id" IS 'sftp id';
COMMENT ON COLUMN "dingtalk"."app_task"."data_dir" IS '数据文件夹';
COMMENT ON COLUMN "dingtalk"."app_task"."file_pattern" IS '文件名匹配正则表达式';
COMMENT ON COLUMN "dingtalk"."app_task"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."app_task"."update_time" IS '更新时间';
COMMENT ON TABLE "dingtalk"."app_task" IS '应用定时任务';

-- ----------------------------
-- Table structure for app_task_log
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."app_task_log";
CREATE TABLE "dingtalk"."app_task_log" (
  "id" text COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "task_id" text COLLATE "pg_catalog"."default",
  "task_name" text COLLATE "pg_catalog"."default",
  "app_id" text COLLATE "pg_catalog"."default",
  "app_name" text COLLATE "pg_catalog"."default",
  "robot_code" text COLLATE "pg_catalog"."default",
  "robot_name" text COLLATE "pg_catalog"."default",
  "conversation_type" int4,
  "open_conversation_id" text COLLATE "pg_catalog"."default",
  "phones" text COLLATE "pg_catalog"."default",
  "user_ids" text COLLATE "pg_catalog"."default",
  "sftp_id" text COLLATE "pg_catalog"."default",
  "host" text COLLATE "pg_catalog"."default",
  "port" int4,
  "data_dir" text COLLATE "pg_catalog"."default",
  "file_pattern" text COLLATE "pg_catalog"."default",
  "file_path" text COLLATE "pg_catalog"."default",
  "status" int4,
  "detail" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "dingtalk"."app_task_log"."id" IS '日志id';
COMMENT ON COLUMN "dingtalk"."app_task_log"."create_time" IS '执行时间';
COMMENT ON COLUMN "dingtalk"."app_task_log"."task_id" IS '任务id';
COMMENT ON COLUMN "dingtalk"."app_task_log"."task_name" IS '任务名称';
COMMENT ON COLUMN "dingtalk"."app_task_log"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."app_task_log"."app_name" IS '应用名称';
COMMENT ON COLUMN "dingtalk"."app_task_log"."robot_code" IS '机器人编码';
COMMENT ON COLUMN "dingtalk"."app_task_log"."robot_name" IS '机器人名称';
COMMENT ON COLUMN "dingtalk"."app_task_log"."conversation_type" IS '会话类型：1：单聊，2：群聊';
COMMENT ON COLUMN "dingtalk"."app_task_log"."open_conversation_id" IS '群id';
COMMENT ON COLUMN "dingtalk"."app_task_log"."phones" IS '接收机器人消息的用户的手机号列表';
COMMENT ON COLUMN "dingtalk"."app_task_log"."user_ids" IS '接收机器人消息的用户的userId列表';
COMMENT ON COLUMN "dingtalk"."app_task_log"."sftp_id" IS 'sftp id';
COMMENT ON COLUMN "dingtalk"."app_task_log"."host" IS 'sftp ip';
COMMENT ON COLUMN "dingtalk"."app_task_log"."port" IS 'sftp端口';
COMMENT ON COLUMN "dingtalk"."app_task_log"."data_dir" IS '数据文件夹';
COMMENT ON COLUMN "dingtalk"."app_task_log"."file_pattern" IS '文件名匹配正则表达式';
COMMENT ON COLUMN "dingtalk"."app_task_log"."file_path" IS '文件路径';
COMMENT ON COLUMN "dingtalk"."app_task_log"."status" IS '任务执行状态，0失败1成功';
COMMENT ON COLUMN "dingtalk"."app_task_log"."detail" IS '详情';
COMMENT ON TABLE "dingtalk"."app_task_log" IS '应用定时任务日志';

-- ----------------------------
-- Table structure for receive_message_log
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."receive_message_log";
CREATE TABLE "dingtalk"."receive_message_log" (
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "robot_name" text COLLATE "pg_catalog"."default",
  "robot_code" text COLLATE "pg_catalog"."default",
  "sender_staff_id" text COLLATE "pg_catalog"."default",
  "sender_nick" text COLLATE "pg_catalog"."default",
  "content" text COLLATE "pg_catalog"."default",
  "msg_type" text COLLATE "pg_catalog"."default",
  "conversation_type" text COLLATE "pg_catalog"."default",
  "conversation_title" text COLLATE "pg_catalog"."default",
  "conversation_id" text COLLATE "pg_catalog"."default",
  "at_users" text COLLATE "pg_catalog"."default",
  "chatbot_corp_id" text COLLATE "pg_catalog"."default",
  "chatbot_user_id" text COLLATE "pg_catalog"."default",
  "msg_id" text COLLATE "pg_catalog"."default",
  "is_admin" text COLLATE "pg_catalog"."default",
  "session_webhook_expired_time" text COLLATE "pg_catalog"."default",
  "create_at" text COLLATE "pg_catalog"."default",
  "sender_corp_id" text COLLATE "pg_catalog"."default",
  "sender_id" text COLLATE "pg_catalog"."default",
  "is_in_at_list" text COLLATE "pg_catalog"."default",
  "session_webhook" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "dingtalk"."receive_message_log"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."robot_name" IS '机器人名称';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."robot_code" IS '机器人编码';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."sender_staff_id" IS '发送者 userId';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."sender_nick" IS '发送者昵称';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."content" IS '内容';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."msg_type" IS '消息类型';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."conversation_type" IS '会话类型：1：单聊2：群聊';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."conversation_title" IS '群聊时才有的会话标题';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."conversation_id" IS '会话 ID';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."at_users" IS '被@人的信息：dingtalkId：加密的发送者 ID。staffId：当前企业内部群中员工 userId 值。';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."chatbot_corp_id" IS '机器人所在的企业 corpId';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."chatbot_user_id" IS '加密的机器人 ID';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."msg_id" IS '加密的消息 ID';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."is_admin" IS '是否为管理员';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."session_webhook_expired_time" IS '当前会话的 Webhook 地址过期时间';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."create_at" IS '消息的时间戳，单位毫秒';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."sender_corp_id" IS '企业内部群有的发送者当前群的企业 corpId';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."sender_id" IS '加密的发送者 ID';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."is_in_at_list" IS '是否在@列表中';
COMMENT ON COLUMN "dingtalk"."receive_message_log"."session_webhook" IS '当前会话的 Webhook 地址';
COMMENT ON TABLE "dingtalk"."receive_message_log" IS '钉钉机器人接收消息日志';

-- ----------------------------
-- Table structure for request_log
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."request_log";
CREATE TABLE "dingtalk"."request_log" (
  "trace_id" text COLLATE "pg_catalog"."default",
  "request_time" timestamp(6),
  "ip" text COLLATE "pg_catalog"."default",
  "api" text COLLATE "pg_catalog"."default",
  "request_parameters" text COLLATE "pg_catalog"."default",
  "headers" text COLLATE "pg_catalog"."default",
  "open" int4,
  "username" text COLLATE "pg_catalog"."default",
  "app_name" text COLLATE "pg_catalog"."default",
  "app_id" text COLLATE "pg_catalog"."default",
  "time_stamp" timestamp(6),
  "token" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "dingtalk"."request_log"."trace_id" IS '请求序列号';
COMMENT ON COLUMN "dingtalk"."request_log"."request_time" IS '请求时间';
COMMENT ON COLUMN "dingtalk"."request_log"."ip" IS 'ip';
COMMENT ON COLUMN "dingtalk"."request_log"."api" IS '接口url';
COMMENT ON COLUMN "dingtalk"."request_log"."request_parameters" IS '请求参数';
COMMENT ON COLUMN "dingtalk"."request_log"."headers" IS '请求头';
COMMENT ON COLUMN "dingtalk"."request_log"."open" IS '是否开放接口，0否1是';
COMMENT ON COLUMN "dingtalk"."request_log"."username" IS '管理后台用户名';
COMMENT ON COLUMN "dingtalk"."request_log"."app_name" IS '应用名称';
COMMENT ON COLUMN "dingtalk"."request_log"."app_id" IS '应用id';
COMMENT ON COLUMN "dingtalk"."request_log"."time_stamp" IS '时间戳';
COMMENT ON COLUMN "dingtalk"."request_log"."token" IS '访问令牌';
COMMENT ON TABLE "dingtalk"."request_log" IS '访问日志';

-- ----------------------------
-- Table structure for robot
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."robot";
CREATE TABLE "dingtalk"."robot" (
  "robot_code" text COLLATE "pg_catalog"."default" NOT NULL,
  "robot_secret" text COLLATE "pg_catalog"."default",
  "robot_name" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "deleted" int4 DEFAULT 0
)
;
COMMENT ON COLUMN "dingtalk"."robot"."robot_code" IS '机器人编码';
COMMENT ON COLUMN "dingtalk"."robot"."robot_secret" IS '机器人密钥';
COMMENT ON COLUMN "dingtalk"."robot"."robot_name" IS '机器人名称';
COMMENT ON COLUMN "dingtalk"."robot"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."robot"."update_time" IS '更新时间';
COMMENT ON COLUMN "dingtalk"."robot"."deleted" IS '是否删除，0否1是';
COMMENT ON TABLE "dingtalk"."robot" IS '钉钉机器人';

-- ----------------------------
-- Table structure for robot_command
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."robot_command";
CREATE TABLE "dingtalk"."robot_command" (
  "app_id" text COLLATE "pg_catalog"."default" NOT NULL,
  "robot_code" text COLLATE "pg_catalog"."default" NOT NULL,
  "command" text COLLATE "pg_catalog"."default" NOT NULL,
  "url" text COLLATE "pg_catalog"."default",
  "description" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "dingtalk"."robot_command"."app_id" IS '应用编码';
COMMENT ON COLUMN "dingtalk"."robot_command"."robot_code" IS '机器人编码';
COMMENT ON COLUMN "dingtalk"."robot_command"."command" IS '指令';
COMMENT ON COLUMN "dingtalk"."robot_command"."url" IS '应用方接口';
COMMENT ON COLUMN "dingtalk"."robot_command"."description" IS '说明';
COMMENT ON COLUMN "dingtalk"."robot_command"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."robot_command"."update_time" IS '更新时间';
COMMENT ON TABLE "dingtalk"."robot_command" IS '机器人指令';

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS "dingtalk"."user";
CREATE TABLE "dingtalk"."user" (
  "username" text COLLATE "pg_catalog"."default" NOT NULL,
  "password" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "enable" int4 DEFAULT 1
)
;
COMMENT ON COLUMN "dingtalk"."user"."username" IS '用户名';
COMMENT ON COLUMN "dingtalk"."user"."password" IS '密码';
COMMENT ON COLUMN "dingtalk"."user"."create_time" IS '创建时间';
COMMENT ON COLUMN "dingtalk"."user"."update_time" IS '更新时间';
COMMENT ON COLUMN "dingtalk"."user"."enable" IS '是否启用，0否1是';
COMMENT ON TABLE "dingtalk"."user" IS '用户';

-- ----------------------------
-- Primary Key structure for table app
-- ----------------------------
ALTER TABLE "dingtalk"."app" ADD CONSTRAINT "app_pk" PRIMARY KEY ("app_id");

-- ----------------------------
-- Primary Key structure for table app_robot
-- ----------------------------
ALTER TABLE "dingtalk"."app_robot" ADD CONSTRAINT "app_robot_pk" PRIMARY KEY ("app_id", "robot_code");

-- ----------------------------
-- Indexes structure for table app_sftp
-- ----------------------------
CREATE INDEX "app_sftp_app_id_idx" ON "dingtalk"."app_sftp" USING btree (
  "app_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table app_sftp
-- ----------------------------
ALTER TABLE "dingtalk"."app_sftp" ADD CONSTRAINT "app_sftp_pk" PRIMARY KEY ("sftp_id");

-- ----------------------------
-- Indexes structure for table app_task
-- ----------------------------
CREATE INDEX "app_task_app_id_idx" ON "dingtalk"."app_task" USING btree (
  "app_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table app_task
-- ----------------------------
ALTER TABLE "dingtalk"."app_task" ADD CONSTRAINT "app_task_pk" PRIMARY KEY ("task_id");

-- ----------------------------
-- Indexes structure for table app_task_log
-- ----------------------------
CREATE INDEX "app_task_log_create_time_idx" ON "dingtalk"."app_task_log" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" DESC NULLS FIRST,
  "task_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Indexes structure for table receive_message_log
-- ----------------------------
CREATE INDEX "receive_message_log_create_time_idx" ON "dingtalk"."receive_message_log" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" DESC NULLS FIRST,
  "robot_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
  "sender_staff_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
  "sender_nick" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Indexes structure for table request_log
-- ----------------------------
CREATE INDEX "request_log_request_time_idx" ON "dingtalk"."request_log" USING btree (
  "request_time" "pg_catalog"."timestamp_ops" DESC NULLS FIRST,
  "app_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
  "app_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table robot
-- ----------------------------
ALTER TABLE "dingtalk"."robot" ADD CONSTRAINT "robot_pk" PRIMARY KEY ("robot_code");

-- ----------------------------
-- Primary Key structure for table robot_command
-- ----------------------------
ALTER TABLE "dingtalk"."robot_command" ADD CONSTRAINT "robot_command_pk" PRIMARY KEY ("app_id", "robot_code", "command");

-- ----------------------------
-- Primary Key structure for table user
-- ----------------------------
ALTER TABLE "dingtalk"."user" ADD CONSTRAINT "user_pk" PRIMARY KEY ("username");