140 lines
6.6 KiB
Go
140 lines
6.6 KiB
Go
package logic
|
||
|
||
import (
|
||
"context"
|
||
|
||
"github.com/gogf/gf/v2/frame/g"
|
||
)
|
||
|
||
// ============================================================================
|
||
// 启动幂等迁移
|
||
//
|
||
// 为什么不用迁移工具：本项目部署方式是 `go run ./main.go`，仓库里也没有 goose/flyway，
|
||
// 而 manifest/config 被 gitignore 后运维手上常常只有一份二进制 + 数据库地址。
|
||
// 把「建表 + 加列」做成启动时的幂等 SQL，可以让升级只需要重启服务，不必再手工敲 DDL。
|
||
//
|
||
// 安全约定：
|
||
// - 全部语句必须可重复执行（CREATE TABLE IF NOT EXISTS / 先查 information_schema 再 ALTER）
|
||
// - 失败只告警不阻断启动：缺表会让相关接口报错，但不该连带整个服务起不来
|
||
// ============================================================================
|
||
|
||
type migrateTable struct {
|
||
name string
|
||
sql string
|
||
}
|
||
|
||
type migrateColumn struct {
|
||
table string
|
||
column string
|
||
sql string
|
||
}
|
||
|
||
// Migrate 启动时执行幂等迁移；失败仅告警。
|
||
func Migrate(ctx context.Context) {
|
||
for _, t := range migrateTables() {
|
||
if _, err := g.DB().Exec(ctx, t.sql); err != nil {
|
||
g.Log().Warningf(ctx, "[migrate] 建表 %s 失败: %v", t.name, err)
|
||
}
|
||
}
|
||
for _, c := range migrateColumns() {
|
||
exists, err := columnExists(ctx, c.table, c.column)
|
||
if err != nil {
|
||
g.Log().Warningf(ctx, "[migrate] 检查列 %s.%s 失败: %v", c.table, c.column, err)
|
||
continue
|
||
}
|
||
if exists {
|
||
continue
|
||
}
|
||
if _, err = g.DB().Exec(ctx, c.sql); err != nil {
|
||
g.Log().Warningf(ctx, "[migrate] 加列 %s.%s 失败: %v", c.table, c.column, err)
|
||
continue
|
||
}
|
||
g.Log().Infof(ctx, "[migrate] 已新增列 %s.%s", c.table, c.column)
|
||
}
|
||
}
|
||
|
||
// columnExists 用 information_schema 判断列是否已存在（DATABASE() 取当前库，避免写死库名）
|
||
func columnExists(ctx context.Context, table, column string) (bool, error) {
|
||
value, err := g.DB().GetValue(ctx,
|
||
"SELECT COUNT(*) FROM information_schema.COLUMNS "+
|
||
"WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?",
|
||
table, column)
|
||
if err != nil {
|
||
return false, err
|
||
}
|
||
return value.Int() > 0, nil
|
||
}
|
||
|
||
func migrateTables() []migrateTable {
|
||
return []migrateTable{
|
||
{"quota_packs", "CREATE TABLE IF NOT EXISTS `quota_packs` (" +
|
||
"`id` bigint unsigned NOT NULL AUTO_INCREMENT," +
|
||
"`pack_key` varchar(64) NOT NULL COMMENT '档位标识，跨端契约'," +
|
||
"`name` varchar(64) NOT NULL DEFAULT '' COMMENT '展示名'," +
|
||
"`times` int NOT NULL DEFAULT 0 COMMENT '可用次数'," +
|
||
"`price_cents` bigint NOT NULL DEFAULT 0 COMMENT '价格（分）'," +
|
||
"`product_id` varchar(64) NOT NULL DEFAULT '' COMMENT '微信虚拟支付道具 ID'," +
|
||
"`valid_days` int NOT NULL DEFAULT 365 COMMENT '有效期天数，默认一年'," +
|
||
"`tool_key` varchar(64) NOT NULL DEFAULT '' COMMENT '绑定工具，空=通用档位'," +
|
||
"`badge` varchar(32) NOT NULL DEFAULT '' COMMENT '角标文案'," +
|
||
"`sort` int NOT NULL DEFAULT 0," +
|
||
"`is_enabled` tinyint NOT NULL DEFAULT 1," +
|
||
"`remark` varchar(255) NOT NULL DEFAULT ''," +
|
||
"`created_at` datetime DEFAULT NULL," +
|
||
"`updated_at` datetime DEFAULT NULL," +
|
||
"PRIMARY KEY (`id`)," +
|
||
"UNIQUE KEY `uk_pack_key` (`pack_key`)" +
|
||
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='次数包档位（付费额度包）'"},
|
||
{"user_tool_quota", "CREATE TABLE IF NOT EXISTS `user_tool_quota` (" +
|
||
"`id` bigint unsigned NOT NULL AUTO_INCREMENT," +
|
||
"`user_id` bigint NOT NULL," +
|
||
"`tool_key` varchar(64) NOT NULL," +
|
||
"`times_left` int NOT NULL DEFAULT 0 COMMENT '剩余次数'," +
|
||
"`total_bought` int NOT NULL DEFAULT 0 COMMENT '累计购买次数'," +
|
||
"`expire_at` datetime DEFAULT NULL COMMENT '到期时间，NULL 视为已过期'," +
|
||
"`created_at` datetime DEFAULT NULL," +
|
||
"`updated_at` datetime DEFAULT NULL," +
|
||
"PRIMARY KEY (`id`)," +
|
||
"UNIQUE KEY `uk_user_tool` (`user_id`,`tool_key`)" +
|
||
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户工具付费额度余额'"},
|
||
{"user_quota_usage", "CREATE TABLE IF NOT EXISTS `user_quota_usage` (" +
|
||
"`id` bigint unsigned NOT NULL AUTO_INCREMENT," +
|
||
"`user_id` bigint NOT NULL," +
|
||
"`tool_key` varchar(64) NOT NULL," +
|
||
"`period_start` datetime NOT NULL COMMENT '当前周期起点'," +
|
||
"`period_end` datetime NOT NULL COMMENT '当前周期终点'," +
|
||
"`free_used` int NOT NULL DEFAULT 0 COMMENT '本周期已用免费额度'," +
|
||
"`member_used` int NOT NULL DEFAULT 0 COMMENT '本周期已用会员额度'," +
|
||
"`updated_at` datetime DEFAULT NULL," +
|
||
"PRIMARY KEY (`id`)," +
|
||
"UNIQUE KEY `uk_user_tool` (`user_id`,`tool_key`)" +
|
||
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户工具额度周期用量'"},
|
||
{"settings", "CREATE TABLE IF NOT EXISTS `settings` (" +
|
||
"`k` varchar(64) NOT NULL," +
|
||
"`v` varchar(255) NOT NULL DEFAULT ''," +
|
||
"`remark` varchar(255) NOT NULL DEFAULT ''," +
|
||
"`updated_at` datetime DEFAULT NULL," +
|
||
"PRIMARY KEY (`k`)" +
|
||
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台可改的全局配置'"},
|
||
}
|
||
}
|
||
|
||
func migrateColumns() []migrateColumn {
|
||
return []migrateColumn{
|
||
// 工具额度配置：默认值让老数据在升级后立刻可用（免费 3 次/周期、会员 100 次/周期、系数 1）
|
||
{"tools", "free_quota", "ALTER TABLE `tools` ADD COLUMN `free_quota` int NOT NULL DEFAULT 3 COMMENT '免费额度（每周期次数）'"},
|
||
{"tools", "member_quota", "ALTER TABLE `tools` ADD COLUMN `member_quota` int NOT NULL DEFAULT 100 COMMENT '会员额度（每周期次数）'"},
|
||
{"tools", "pay_cost", "ALTER TABLE `tools` ADD COLUMN `pay_cost` int NOT NULL DEFAULT 1 COMMENT '付费额度每次消耗系数'"},
|
||
|
||
// 额度周期：0 = 跟随全局设置
|
||
{"users", "quota_period_days", "ALTER TABLE `users` ADD COLUMN `quota_period_days` int NOT NULL DEFAULT 0 COMMENT '额度重置周期（天），0=跟随全局'"},
|
||
{"member_plans", "quota_period_days", "ALTER TABLE `member_plans` ADD COLUMN `quota_period_days` int NOT NULL DEFAULT 0 COMMENT '购买后生效的额度周期（天），0=不调整'"},
|
||
|
||
// 订单表承载两类商品
|
||
{"member_orders", "order_type", "ALTER TABLE `member_orders` ADD COLUMN `order_type` tinyint NOT NULL DEFAULT 1 COMMENT '1=会员套餐 2=次数包'"},
|
||
{"member_orders", "pack_key", "ALTER TABLE `member_orders` ADD COLUMN `pack_key` varchar(64) NOT NULL DEFAULT '' COMMENT '次数包档位'"},
|
||
{"member_orders", "tool_key", "ALTER TABLE `member_orders` ADD COLUMN `tool_key` varchar(64) NOT NULL DEFAULT '' COMMENT '次数包绑定工具'"},
|
||
{"member_orders", "times", "ALTER TABLE `member_orders` ADD COLUMN `times` int NOT NULL DEFAULT 0 COMMENT '次数包次数'"},
|
||
}
|
||
}
|