37 lines
1.2 KiB
SQL
37 lines
1.2 KiB
SQL
-- 入口模块地区管理 - 数据迁移脚本
|
|
-- 将 areas 字段从字符串数组转换为对象数组，支持启用/禁用状态
|
|
|
|
-- 1. 添加备份列
|
|
ALTER TABLE entry_modules ADD COLUMN IF NOT EXISTS areas_backup JSONB;
|
|
|
|
-- 2. 备份原数据
|
|
UPDATE entry_modules SET areas_backup = areas WHERE areas IS NOT NULL;
|
|
|
|
-- 3. 转换数据格式
|
|
UPDATE entry_modules
|
|
SET areas = (
|
|
SELECT jsonb_agg(
|
|
jsonb_build_object(
|
|
'area', area_name,
|
|
'enabled', true,
|
|
'sort_order', row_number
|
|
) ORDER BY row_number
|
|
)
|
|
FROM (
|
|
SELECT area_name, ROW_NUMBER() OVER () as row_number
|
|
FROM jsonb_array_elements_text(areas) AS area_name
|
|
) AS numbered_areas
|
|
)
|
|
WHERE areas IS NOT NULL AND jsonb_typeof(areas) = 'array';
|
|
|
|
-- 4. 添加注释
|
|
COMMENT ON COLUMN entry_modules.areas IS '地区配置: [{"area": "地区名", "enabled": true/false, "sort_order": 排序号}]';
|
|
|
|
-- 5. 查看迁移结果
|
|
SELECT id, name, areas_backup AS old_format, areas AS new_format
|
|
FROM entry_modules WHERE areas IS NOT NULL;
|
|
|
|
-- 回滚（如需要）：
|
|
-- UPDATE entry_modules SET areas = areas_backup WHERE areas_backup IS NOT NULL;
|
|
-- ALTER TABLE entry_modules DROP COLUMN areas_backup;
|