toncent/wecome-middleware
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
wecome-middleware/scripts/init-simple.sql
xudw 034d425b21 初始提交: WeCom Middleware项目基础结构 
包含以下内容:
1. Spring Boot后端项目结构
2. Vue.js前端项目结构
3. Docker Compose部署配置
4. MySQL数据库初始化脚本
5. Redis缓存配置
6. Nginx反向代理配置
7. 完整的项目文档

技术栈:
- 后端: Spring Boot 2.7.18 + Java 11 + MyBatis Plus
- 前端: Vue.js 3 + TypeScript + Element Plus
- 数据库: MySQL 8.0 + Redis 7
- 部署: Docker Compose + Nginx

已部署服务:
- 后端API: http://localhost:18080
- 前端界面: http://localhost:13000
- 数据库管理: http://localhost:18081
- MySQL: localhost:13306
- Redis: localhost:16379
2026-03-09 12:39:09 +08:00

311 lines
21 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ============================================================================
-- WeCom Middleware 数据库初始化脚本（简化版）
-- 版本: 1.0.0
-- 作者: WeCom Middleware Team
-- 创建时间: 2026-03-08
-- ============================================================================
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `wecom_middleware` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `wecom_middleware`;
-- ============================================================================
-- 用户表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密后）',
`nickname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '昵称',
`avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像URL',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
`status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：0-禁用，1-启用',
`last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
`last_login_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后登录IP',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_email` (`email`),
KEY `idx_phone` (`phone`),
KEY `idx_status` (`status`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
-- ============================================================================
-- 会话表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_session` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`session_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID',
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
`session_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话类型：wecom-企业微信，openclaw-OpenClaw',
`session_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '会话数据（JSON格式）',
`status` tinyint NOT NULL DEFAULT '1' COMMENT '会话状态：0-已结束，1-进行中，2-暂停',
`start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`expire_time` datetime DEFAULT NULL COMMENT '过期时间',
`last_active_time` datetime DEFAULT NULL COMMENT '最后活跃时间',
`message_count` int DEFAULT '0' COMMENT '消息数量',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_session_id` (`session_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_session_type` (`session_type`),
KEY `idx_status` (`status`),
KEY `idx_expire_time` (`expire_time`),
KEY `idx_last_active_time` (`last_active_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会话表';
-- ============================================================================
-- 消息表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_message` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`message_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息ID',
`session_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID',
`from_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发送方类型：wecom-企业微信，openclaw-OpenClaw',
`from_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发送方ID',
`to_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接收方类型：wecom-企业微信，openclaw-OpenClaw',
`to_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接收方ID',
`message_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息类型：text-文本，image-图片，file-文件，voice-语音，video-视频',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '消息内容',
`media_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '媒体文件URL',
`media_size` int DEFAULT NULL COMMENT '媒体文件大小（字节）',
`media_duration` int DEFAULT NULL COMMENT '媒体时长（秒，音频/视频）',
`status` tinyint NOT NULL DEFAULT '0' COMMENT '消息状态：0-发送中，1-已发送，2-已送达，3-已读，4-发送失败',
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '错误信息',
`retry_count` int DEFAULT '0' COMMENT '重试次数',
`send_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
`read_time` datetime DEFAULT NULL COMMENT '阅读时间',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_message_id` (`message_id`),
KEY `idx_session_id` (`session_id`),
KEY `idx_from_type_from_id` (`from_type`, `from_id`),
KEY `idx_to_type_to_id` (`to_type`, `to_id`),
KEY `idx_message_type` (`message_type`),
KEY `idx_status` (`status`),
KEY `idx_send_time` (`send_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息表';
-- ============================================================================
-- 系统配置表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_config` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`config_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置键',
`config_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '配置值',
`config_group` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'default' COMMENT '配置分组',
`config_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '配置类型：string-字符串，number-数字，boolean-布尔，json-JSON',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '描述信息',
`effective_time` datetime DEFAULT NULL COMMENT '生效时间',
`expire_time` datetime DEFAULT NULL COMMENT '过期时间',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_config_key_group` (`config_key`, `config_group`),
KEY `idx_config_group` (`config_group`),
KEY `idx_effective_time` (`effective_time`),
KEY `idx_expire_time` (`expire_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';
-- ============================================================================
-- 操作日志表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_operation_log` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`operation_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型',
`operation_target` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作目标',
`operation_detail` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '操作详情',
`operator_id` bigint DEFAULT NULL COMMENT '操作人ID',
`operator_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作人姓名',
`operator_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作人IP',
`operation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
`result` tinyint NOT NULL DEFAULT '1' COMMENT '操作结果：0-失败，1-成功',
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '错误信息',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
KEY `idx_operation_type` (`operation_type`),
KEY `idx_operation_target` (`operation_target`),
KEY `idx_operator_id` (`operator_id`),
KEY `idx_operation_time` (`operation_time`),
KEY `idx_result` (`result`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';
-- ============================================================================
-- 连接状态表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sys_connection_status` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`connection_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '连接类型：wecom-企业微信, openclaw-OpenClaw',
`connection_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '连接ID',
`status` tinyint NOT NULL DEFAULT '0' COMMENT '连接状态：0-断开, 1-连接中, 2-重连中, 3-错误',
`last_connect_time` datetime DEFAULT NULL COMMENT '最后连接时间',
`last_disconnect_time` datetime DEFAULT NULL COMMENT '最后断开时间',
`error_count` int NOT NULL DEFAULT '0' COMMENT '错误次数',
`last_error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '最后错误信息',
`heartbeat_time` datetime DEFAULT NULL COMMENT '心跳时间',
`config` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '连接配置（JSON格式）',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_connection_type` (`connection_type`),
KEY `idx_status` (`status`),
KEY `idx_last_connect_time` (`last_connect_time`),
KEY `idx_heartbeat_time` (`heartbeat_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='连接状态表';
-- ============================================================================
-- Bot配置表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `bot_config` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`bot_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Bot名称',
`wecom_bot_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '企业微信Bot ID',
`wecom_bot_secret` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '企业微信Bot Secret',
`openclaw_agent_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'OpenClaw代理Agent ID',
`openclaw_gateway_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'ws://localhost:18789' COMMENT 'OpenClaw网关URL',
`openclaw_gateway_token` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'OpenClaw网关令牌',
`wecom_websocket_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'wss://openws.work.weixin.qq.com' COMMENT '企业微信WebSocket URL',
`protocol_version` int DEFAULT '3' COMMENT '协议版本',
`bot_status` tinyint DEFAULT '0' COMMENT 'Bot状态：0-禁用，1-启用，2-连接中，3-已连接，4-错误',
`last_connect_time` datetime DEFAULT NULL COMMENT '最后连接时间',
`last_disconnect_time` datetime DEFAULT NULL COMMENT '最后断开时间',
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '错误信息',
`heartbeat_interval` int DEFAULT '30000' COMMENT '心跳间隔（毫秒）',
`reconnect_interval` int DEFAULT '5000' COMMENT '重连间隔（毫秒）',
`max_retry_count` int DEFAULT '3' COMMENT '最大重试次数',
`message_queue_size` int DEFAULT '1000' COMMENT '消息队列大小',
`config_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '配置JSON',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '描述信息',
`dm_policy` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'pairing' COMMENT '配对策略：pairing-需要配对批准，allowlist-仅允许列表中的用户，open-开放所有用户，disabled-禁用配对',
`allow_from` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '允许的用户列表（JSON数组格式）',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_wecom_bot_id` (`wecom_bot_id`),
UNIQUE KEY `uk_openclaw_agent_id` (`openclaw_agent_id`),
KEY `idx_bot_status` (`bot_status`),
KEY `idx_bot_name` (`bot_name`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bot配置表';
-- ============================================================================
-- OpenClaw配对请求表
-- ============================================================================
CREATE TABLE IF NOT EXISTS `pairing_request` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`request_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '请求ID',
`node_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '节点ID',
`node_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '节点名称',
`node_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '节点类型',
`node_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '节点描述',
`node_version` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '节点版本',
`operating_system` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作系统',
`hostname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '主机名',
`ip_address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
`silent` tinyint DEFAULT '0' COMMENT '是否静默配对：0-否，1-是',
`status` tinyint NOT NULL DEFAULT '0' COMMENT '请求状态：0-待处理，1-已批准，2-已拒绝，3-已过期，4-批准失败',
`auto_approve` tinyint DEFAULT '0' COMMENT '是否自动批准：0-否，1-是',
`approver` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '批准人',
`approve_time` datetime DEFAULT NULL COMMENT '批准时间',
`reject_reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '拒绝原因',
`remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '备注',
`request_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
`expire_time` datetime DEFAULT NULL COMMENT '过期时间',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '创建人',
`update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'system' COMMENT '更新人',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除：0-未删除，1-已删除',
`version` int NOT NULL DEFAULT '0' COMMENT '版本号（乐观锁）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_request_id` (`request_id`),
KEY `idx_node_id` (`node_id`),
KEY `idx_node_name` (`node_name`),
KEY `idx_status` (`status`),
KEY `idx_auto_approve` (`auto_approve`),
KEY `idx_approver` (`approver`),
KEY `idx_request_time` (`request_time`),
KEY `idx_expire_time` (`expire_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OpenClaw配对请求表';
-- ============================================================================
-- 初始化默认数据
-- ============================================================================
-- 初始化管理员用户
INSERT INTO `sys_user` (
`username`, `password`, `nickname`, `email`, `phone`, `status`, `create_by`, `update_by`
) VALUES (
'admin',
'$2a$10$YourEncryptedPasswordHere', -- 实际使用时需要替换为加密后的密码
'系统管理员',
'admin@wecom-middleware.com',
'13800138000',
1,
'system',
'system'
) ON DUPLICATE KEY UPDATE `update_time` = CURRENT_TIMESTAMP;
-- 初始化系统配置
INSERT INTO `sys_config` (
`config_key`, `config_value`, `config_group`, `config_type`, `description`
) VALUES
('system.name', 'WeCom Middleware', 'system', 'string', '系统名称'),
('system.version', '1.0.0', 'system', 'string', '系统版本'),
('system.description', '企业微信与OpenClaw中间件系统', 'system', 'string', '系统描述'),
('message.retry.max_count', '3', 'message', 'number', '消息最大重试次数'),
('message.retry.interval', '5000', 'message', 'number', '消息重试间隔（毫秒）'),
('session.timeout.minutes', '30', 'session', 'number', '会话超时时间（分钟）'),
('log.retention.days', '30', 'log', 'number', '日志保留天数')
ON DUPLICATE KEY UPDATE `update_time` = CURRENT_TIMESTAMP;
-- 显示表结构信息
SELECT '数据库初始化完成！' AS message;
SELECT
TABLE_NAME,
TABLE_COMMENT,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH,
CREATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wecom_middleware'
ORDER BY TABLE_NAME;
Reference in New Issue View Git Blame Copy Permalink