qwer-xyz
68 lines
3.2 KiB
SQL
68 lines
3.2 KiB
SQL
-- 配额系统重构迁移
|
|
-- 用户配额从 CPU/内存/磁盘/端口/快照/备份 改为 宿主机数量/实例数量/好友数量
|
|
-- 套餐新增 snapshotLimit 和 backupLimit 字段
|
|
|
|
-- =====================================================
|
|
-- 1. 修改 user_quotas 表
|
|
-- =====================================================
|
|
|
|
-- 添加新字段
|
|
ALTER TABLE "user_quotas" ADD COLUMN IF NOT EXISTS "host_limit" INTEGER NOT NULL DEFAULT 5;
|
|
ALTER TABLE "user_quotas" ADD COLUMN IF NOT EXISTS "host_used" INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE "user_quotas" ADD COLUMN IF NOT EXISTS "friend_limit" INTEGER NOT NULL DEFAULT 20;
|
|
ALTER TABLE "user_quotas" ADD COLUMN IF NOT EXISTS "friend_used" INTEGER NOT NULL DEFAULT 0;
|
|
|
|
-- 更新 instance_limit 默认值为 50（可选，不影响现有数据）
|
|
-- ALTER TABLE "user_quotas" ALTER COLUMN "instance_limit" SET DEFAULT 50;
|
|
|
|
-- 统计并更新现有用户的 host_used
|
|
UPDATE "user_quotas" q SET "host_used" = (
|
|
SELECT COUNT(*) FROM "hosts" h WHERE h."user_id" = q."user_id"
|
|
);
|
|
|
|
-- 统计并更新现有用户的 instance_used
|
|
UPDATE "user_quotas" q SET "instance_used" = (
|
|
SELECT COUNT(*) FROM "instances" i WHERE i."user_id" = q."user_id" AND i."status" != 'deleted'
|
|
);
|
|
|
|
-- 统计并更新现有用户的 friend_used（已接受的好友数）
|
|
UPDATE "user_quotas" q SET "friend_used" = (
|
|
SELECT COUNT(*) FROM "friendships" f
|
|
WHERE (f."user_id" = q."user_id" OR f."friend_id" = q."user_id")
|
|
AND f."status" = 'accepted'
|
|
);
|
|
|
|
-- 删除旧字段（可选，建议先保留一段时间后再删除）
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "cpu_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "cpu_used";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "memory_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "memory_used";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "disk_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "disk_used";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "port_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "port_used";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "snapshot_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "snapshot_used";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "backup_limit";
|
|
-- ALTER TABLE "user_quotas" DROP COLUMN IF EXISTS "backup_used";
|
|
|
|
-- =====================================================
|
|
-- 2. 修改 packages 表
|
|
-- =====================================================
|
|
|
|
-- 添加新字段
|
|
ALTER TABLE "packages" ADD COLUMN IF NOT EXISTS "snapshot_limit" INTEGER NOT NULL DEFAULT 5;
|
|
ALTER TABLE "packages" ADD COLUMN IF NOT EXISTS "backup_limit" INTEGER NOT NULL DEFAULT 3;
|
|
|
|
-- =====================================================
|
|
-- 3. 更新实例配额（从套餐继承）
|
|
-- =====================================================
|
|
|
|
-- 对于有套餐的实例，如果 snapshot_limit 或 backup_limit 为 NULL，则从套餐继承
|
|
UPDATE "instances" i SET
|
|
"snapshot_limit" = COALESCE(i."snapshot_limit", p."snapshot_limit"),
|
|
"backup_limit" = COALESCE(i."backup_limit", p."backup_limit")
|
|
FROM "packages" p
|
|
WHERE i."package_id" = p."id"
|
|
AND (i."snapshot_limit" IS NULL OR i."backup_limit" IS NULL);
|