80 lines
4.1 KiB
SQL
80 lines
4.1 KiB
SQL
-- =====================================================================================
|
||
-- Migration: 全量软删除 (Soft Delete) 基础设施补齐
|
||
-- 位置：docs/sql/10_schema/99_soft_delete_migration_v1.sql
|
||
-- 对象类型：ALTER TABLE
|
||
-- 说明：为所有核心业务表补齐 deleted_at, deleted_by, restored_at, restored_by 字段
|
||
-- 涵盖：权限、配置、内容、装修、物流、分销、财务、客服、营销、商品、用户、订单
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
DECLARE
|
||
-- 需补齐软删除字段的业务表全量清单
|
||
t_names TEXT[] := ARRAY[
|
||
-- 1. 系统与权限 (Auth/System)
|
||
'ak_roles', 'ak_permissions', 'ak_admin_roles', 'ak_role_permissions', 'ml_system_configs',
|
||
|
||
-- 2. 内容与装修 (CMS/Decoration)
|
||
'ml_articles', 'ml_article_categories', 'ak_diy_pages',
|
||
|
||
-- 3. 物流资源 (Delivery)
|
||
'ml_delivery_staff', 'ml_delivery_stations', 'ak_shipping_templates',
|
||
|
||
-- 4. 分销体系 (Distribution)
|
||
'ak_distribution_agents', 'ak_distribution_divisions',
|
||
'ak_distribution_agent_applications', 'ak_distribution_division_applications',
|
||
'ak_promoter_relations', 'ak_commission_logs', 'ak_distribution_level', 'ak_distribution_config',
|
||
|
||
-- 5. 财务管理 (Finance)
|
||
'ml_extract', 'ml_invoices', 'ml_user_bill', 'ml_user_recharge',
|
||
|
||
-- 6. 客服系统 (Kefu)
|
||
'ml_kefu_accounts', 'ml_kefu_words', 'ml_kefu_word_categories', 'ml_kefu_auto_replies', 'ml_kefu_feedbacks',
|
||
|
||
-- 7. 营销活动 (Marketing)
|
||
'ak_advanced_marketing', 'ak_bargain_groupbuy', 'ak_live_products', 'ak_lottery_live',
|
||
'ak_marketing_checkin_configs', 'ak_marketing_newcomer_config', 'ak_marketing_signin_logs',
|
||
'ak_member_management', 'ak_recharge_management', 'ak_signin_configs',
|
||
|
||
-- 8. 商品中心 (Product)
|
||
'ml_products', 'ml_product_skus', 'ml_categories',
|
||
'ak_product_labels', 'ak_product_member_prices', 'ak_product_protections', 'ak_product_templates',
|
||
|
||
-- 9. 用户管理 (User)
|
||
'ak_user_labels', 'ak_user_groups', 'ak_user_levels', 'ak_users',
|
||
|
||
-- 10. 订单中心 (Order)
|
||
'ml_orders'
|
||
];
|
||
t_name TEXT;
|
||
BEGIN
|
||
FOREACH t_name IN ARRAY t_names LOOP
|
||
-- 检查表是否存在
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = t_name) THEN
|
||
|
||
-- 1. 增加 deleted_at 字段
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'deleted_at') THEN
|
||
EXECUTE format('ALTER TABLE public.%I ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL', t_name);
|
||
END IF;
|
||
|
||
-- 2. 增加 deleted_by 字段
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'deleted_by') THEN
|
||
EXECUTE format('ALTER TABLE public.%I ADD COLUMN deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL', t_name);
|
||
END IF;
|
||
|
||
-- 3. 增加 restored_at 字段
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'restored_at') THEN
|
||
EXECUTE format('ALTER TABLE public.%I ADD COLUMN restored_at TIMESTAMPTZ DEFAULT NULL', t_name);
|
||
END IF;
|
||
|
||
-- 4. 增加 restored_by 字段
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'restored_by') THEN
|
||
EXECUTE format('ALTER TABLE public.%I ADD COLUMN restored_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL', t_name);
|
||
END IF;
|
||
|
||
-- 5. 建立软删除索引
|
||
EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON public.%I (deleted_at) WHERE deleted_at IS NULL', 'idx_' || t_name || '_soft_delete', t_name);
|
||
|
||
END IF;
|
||
END LOOP;
|
||
END $$;
|