58 lines
3.2 KiB
SQL
58 lines
3.2 KiB
SQL
-- 修复脚本：重建被误删的 sd_deploy_envs + 新建微信通道两张表
|
||
-- 注意：原 mysql.ddl.sql 有 xls2ui 生成 bug（DEFAULT ''N'' 双重引号），不能整文件执行
|
||
|
||
CREATE TABLE IF NOT EXISTS sd_deploy_envs (
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
|
||
`env_type` VARCHAR(20) NOT NULL comment '环境类型',
|
||
`host` VARCHAR(200) NOT NULL comment 'SSH主机地址',
|
||
`port` int NOT NULL DEFAULT 22 comment 'SSH端口',
|
||
`user` VARCHAR(100) NOT NULL comment 'SSH用户',
|
||
`ssh_key_path` VARCHAR(500) comment 'SSH密钥路径',
|
||
`sudo_enabled` VARCHAR(1) NOT NULL DEFAULT 'N' comment '免密Sudo',
|
||
`deploy_path` VARCHAR(500) NOT NULL comment '部署目录',
|
||
`python_path` VARCHAR(500) comment 'Python路径',
|
||
`db_host` VARCHAR(200) comment '数据库地址',
|
||
`db_port` int DEFAULT 3306 comment '数据库端口',
|
||
`db_name` VARCHAR(100) comment '数据库名',
|
||
`db_user` VARCHAR(100) comment '数据库用户',
|
||
`db_password` VARCHAR(500) comment '数据库密码(加密)',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'configured' comment '环境状态',
|
||
`verified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '最近验证时间',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间',
|
||
primary key(id)
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci engine=innodb comment '部署环境表';
|
||
|
||
CREATE INDEX idx_sd_deploy_envs_project ON sd_deploy_envs(project_id);
|
||
CREATE INDEX idx_sd_deploy_envs_type ON sd_deploy_envs(env_type);
|
||
CREATE UNIQUE INDEX idx_sd_deploy_envs_unique ON sd_deploy_envs(project_id, env_type);
|
||
|
||
CREATE TABLE IF NOT EXISTS wechat_channel_config (
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`org_id` VARCHAR(32) NOT NULL comment '机构ID',
|
||
`name` VARCHAR(200) NOT NULL comment '公众号名称',
|
||
`appid` VARCHAR(100) NOT NULL comment '公众号AppID',
|
||
`appsecret` VARCHAR(500) NOT NULL comment '公众号AppSecret(加密)',
|
||
`token` VARCHAR(200) NOT NULL comment '消息校验Token',
|
||
`encoding_aes_key` VARCHAR(200) comment '消息加密Key(可选)',
|
||
`enabled` VARCHAR(1) NOT NULL DEFAULT '1' comment '是否启用(1/0)',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间',
|
||
primary key(id)
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci engine=innodb comment '微信通道配置(机构级公众号)';
|
||
|
||
CREATE UNIQUE INDEX wechat_channel_config_idx_wcc_org ON wechat_channel_config(org_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS wechat_user_binding (
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`openid` VARCHAR(100) NOT NULL comment '微信OpenID',
|
||
`user_id` VARCHAR(32) NOT NULL comment '系统用户ID',
|
||
`org_id` VARCHAR(32) NOT NULL comment '机构ID',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
primary key(id)
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci engine=innodb comment '微信用户绑定(openid→系统用户)';
|
||
|
||
CREATE UNIQUE INDEX wechat_user_binding_idx_wub_openid ON wechat_user_binding(openid);
|
||
CREATE INDEX wechat_user_binding_idx_wub_user ON wechat_user_binding(user_id);
|