288 lines
8.5 KiB
PL/PgSQL
288 lines
8.5 KiB
PL/PgSQL
-- ====================================================================
|
||
-- 角色字段统一升级脚本 - Role Field Unification Upgrade
|
||
-- ====================================================================
|
||
-- 目的：将所有表的 user_type (INTEGER) 字段统一为 role (TEXT) 字段
|
||
-- 兼容性：Supabase + PostgreSQL 14+
|
||
-- 执行顺序：在现有数据库基础上执行
|
||
-- ====================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ====================================================================
|
||
-- 1. 统一 ml_user_profiles 表的角色字段
|
||
-- ====================================================================
|
||
|
||
-- 1.1 添加新的 role 字段
|
||
ALTER TABLE public.ml_user_profiles
|
||
ADD COLUMN IF NOT EXISTS role TEXT DEFAULT 'customer';
|
||
|
||
-- 1.2 将现有 user_type 数据迁移到 role 字段
|
||
UPDATE public.ml_user_profiles
|
||
SET role = CASE
|
||
WHEN user_type = 1 THEN 'customer' -- 消费者
|
||
WHEN user_type = 2 THEN 'merchant' -- 商家
|
||
WHEN user_type = 3 THEN 'delivery' -- 配送员
|
||
WHEN user_type = 4 THEN 'service' -- 客服
|
||
WHEN user_type = 5 THEN 'admin' -- 管理员
|
||
ELSE 'customer'
|
||
END
|
||
WHERE role IS NULL OR role = 'customer';
|
||
|
||
-- 1.3 设置 role 字段约束
|
||
ALTER TABLE public.ml_user_profiles
|
||
ALTER COLUMN role SET NOT NULL;
|
||
|
||
ALTER TABLE public.ml_user_profiles
|
||
ADD CONSTRAINT IF NOT EXISTS chk_ml_user_role
|
||
CHECK (role IN ('customer', 'merchant', 'delivery', 'service', 'admin'));
|
||
|
||
-- 1.4 更新索引
|
||
DROP INDEX IF EXISTS idx_ml_user_profiles_type;
|
||
CREATE INDEX IF NOT EXISTS idx_ml_user_profiles_role ON public.ml_user_profiles(role);
|
||
|
||
-- 1.5 删除旧的 user_type 字段和约束（可选，建议在测试确认后执行）
|
||
-- ALTER TABLE public.ml_user_profiles DROP CONSTRAINT IF EXISTS chk_ml_user_type;
|
||
-- ALTER TABLE public.ml_user_profiles DROP COLUMN IF EXISTS user_type;
|
||
|
||
-- ====================================================================
|
||
-- 2. 更新相关函数中的字段引用
|
||
-- ====================================================================
|
||
|
||
-- 2.1 更新商家验证函数
|
||
CREATE OR REPLACE FUNCTION public.is_verified_merchant(user_uuid UUID)
|
||
RETURNS BOOLEAN
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
result BOOLEAN := FALSE;
|
||
BEGIN
|
||
SELECT (role = 'merchant' AND verification_status = 1) INTO result
|
||
FROM public.ml_user_profiles
|
||
WHERE user_id = user_uuid;
|
||
|
||
RETURN COALESCE(result, FALSE);
|
||
END;
|
||
$$;
|
||
|
||
-- 2.2 更新用户信息视图
|
||
CREATE OR REPLACE VIEW public.vw_user_info AS
|
||
SELECT
|
||
u.id as user_id,
|
||
u.email,
|
||
u.username,
|
||
u.role as user_role,
|
||
u.status as user_status,
|
||
u.created_at as user_created_at,
|
||
p.cid as profile_cid,
|
||
p.role as profile_role,
|
||
p.status as profile_status,
|
||
p.real_name,
|
||
p.avatar_url,
|
||
p.phone,
|
||
p.credit_score,
|
||
p.verification_status,
|
||
p.created_at as profile_created_at,
|
||
CASE
|
||
WHEN p.role = 'customer' THEN '消费者'
|
||
WHEN p.role = 'merchant' THEN '商家'
|
||
WHEN p.role = 'delivery' THEN '配送员'
|
||
WHEN p.role = 'service' THEN '客服'
|
||
WHEN p.role = 'admin' THEN '管理员'
|
||
ELSE '未知'
|
||
END as role_name
|
||
FROM public.ak_users u
|
||
LEFT JOIN public.ml_user_profiles p ON u.id = p.user_id;
|
||
|
||
-- ====================================================================
|
||
-- 3. 更新 RLS 策略中的角色检查
|
||
-- ====================================================================
|
||
|
||
-- 3.1 更新商品相关策略
|
||
DROP POLICY IF EXISTS "商家管理自己的商品" ON public.ml_products;
|
||
CREATE POLICY "商家管理自己的商品"
|
||
ON public.ml_products
|
||
FOR ALL
|
||
TO authenticated
|
||
USING (
|
||
merchant_id = auth.uid()
|
||
OR EXISTS (
|
||
SELECT 1 FROM public.ml_user_profiles p
|
||
WHERE p.user_id = auth.uid()
|
||
AND p.role IN ('admin', 'service')
|
||
)
|
||
);
|
||
|
||
-- 3.2 更新订单相关策略
|
||
DROP POLICY IF EXISTS "配送员查看分配的订单" ON public.ml_orders;
|
||
CREATE POLICY "配送员查看分配的订单"
|
||
ON public.ml_orders
|
||
FOR SELECT
|
||
TO authenticated
|
||
USING (
|
||
delivery_id = auth.uid()
|
||
OR EXISTS (
|
||
SELECT 1 FROM public.ml_user_profiles p
|
||
WHERE p.user_id = auth.uid()
|
||
AND p.role IN ('admin', 'service')
|
||
)
|
||
);
|
||
|
||
-- 3.3 更新用户资料策略
|
||
DROP POLICY IF EXISTS "用户管理自己的资料" ON public.ml_user_profiles;
|
||
CREATE POLICY "用户管理自己的资料"
|
||
ON public.ml_user_profiles
|
||
FOR ALL
|
||
TO authenticated
|
||
USING (
|
||
user_id = auth.uid()
|
||
OR EXISTS (
|
||
SELECT 1 FROM public.ml_user_profiles p
|
||
WHERE p.user_id = auth.uid()
|
||
AND p.role IN ('admin', 'service')
|
||
)
|
||
);
|
||
|
||
-- ====================================================================
|
||
-- 4. 更新字段注释
|
||
-- ====================================================================
|
||
|
||
COMMENT ON COLUMN public.ml_user_profiles.role IS '用户角色：customer消费者, merchant商家, delivery配送员, service客服, admin管理员';
|
||
|
||
-- ====================================================================
|
||
-- 5. 创建角色辅助函数
|
||
-- ====================================================================
|
||
|
||
-- 5.1 获取用户角色函数
|
||
CREATE OR REPLACE FUNCTION public.get_user_role(user_uuid UUID)
|
||
RETURNS TEXT
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
user_role TEXT;
|
||
BEGIN
|
||
SELECT role INTO user_role
|
||
FROM public.ml_user_profiles
|
||
WHERE user_id = user_uuid;
|
||
|
||
RETURN COALESCE(user_role, 'customer');
|
||
END;
|
||
$$;
|
||
|
||
-- 5.2 检查用户权限函数
|
||
CREATE OR REPLACE FUNCTION public.check_user_permission(user_uuid UUID, required_roles TEXT[])
|
||
RETURNS BOOLEAN
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
user_role TEXT;
|
||
BEGIN
|
||
SELECT role INTO user_role
|
||
FROM public.ml_user_profiles
|
||
WHERE user_id = user_uuid;
|
||
|
||
RETURN user_role = ANY(required_roles);
|
||
END;
|
||
$$;
|
||
|
||
-- 5.3 角色升级函数（将用户提升为商家等）
|
||
CREATE OR REPLACE FUNCTION public.upgrade_user_role(user_uuid UUID, new_role TEXT)
|
||
RETURNS BOOLEAN
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
BEGIN
|
||
-- 检查新角色是否有效
|
||
IF new_role NOT IN ('customer', 'merchant', 'delivery', 'service', 'admin') THEN
|
||
RAISE EXCEPTION '无效的角色类型: %', new_role;
|
||
END IF;
|
||
|
||
-- 更新用户角色
|
||
UPDATE public.ml_user_profiles
|
||
SET role = new_role,
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE user_id = user_uuid;
|
||
|
||
-- 同步更新 ak_users 表的 role 字段
|
||
UPDATE public.ak_users
|
||
SET role = new_role,
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = user_uuid;
|
||
|
||
RETURN FOUND;
|
||
END;
|
||
$$;
|
||
|
||
-- ====================================================================
|
||
-- 6. 数据一致性检查
|
||
-- ====================================================================
|
||
|
||
-- 6.1 检查角色字段一致性
|
||
DO $$
|
||
DECLARE
|
||
inconsistent_count INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO inconsistent_count
|
||
FROM public.ak_users u
|
||
JOIN public.ml_user_profiles p ON u.id = p.user_id
|
||
WHERE u.role != p.role;
|
||
|
||
IF inconsistent_count > 0 THEN
|
||
RAISE NOTICE '发现 % 条记录的角色字段不一致，正在同步...', inconsistent_count;
|
||
|
||
-- 以 ml_user_profiles.role 为准同步到 ak_users.role
|
||
UPDATE public.ak_users
|
||
SET role = p.role,
|
||
updated_at = CURRENT_TIMESTAMP
|
||
FROM public.ml_user_profiles p
|
||
WHERE ak_users.id = p.user_id
|
||
AND ak_users.role != p.role;
|
||
|
||
RAISE NOTICE '角色字段同步完成';
|
||
ELSE
|
||
RAISE NOTICE '角色字段一致性检查通过';
|
||
END IF;
|
||
END;
|
||
$$;
|
||
|
||
-- ====================================================================
|
||
-- 7. 创建角色统计视图
|
||
-- ====================================================================
|
||
|
||
CREATE OR REPLACE VIEW public.vw_role_statistics AS
|
||
SELECT
|
||
role,
|
||
COUNT(*) as user_count,
|
||
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
|
||
FROM public.ml_user_profiles
|
||
GROUP BY role
|
||
ORDER BY user_count DESC;
|
||
|
||
COMMIT;
|
||
|
||
-- ====================================================================
|
||
-- 执行验证
|
||
-- ====================================================================
|
||
|
||
-- 检查角色分布
|
||
SELECT '角色分布统计:' as info;
|
||
SELECT * FROM public.vw_role_statistics;
|
||
|
||
-- 检查索引
|
||
SELECT '索引检查:' as info;
|
||
SELECT indexname, indexdef
|
||
FROM pg_indexes
|
||
WHERE tablename = 'ml_user_profiles'
|
||
AND indexname LIKE '%role%';
|
||
|
||
-- 检查约束
|
||
SELECT '约束检查:' as info;
|
||
SELECT conname, pg_get_constraintdef(oid) as definition
|
||
FROM pg_constraint
|
||
WHERE conrelid = 'public.ml_user_profiles'::regclass
|
||
AND conname LIKE '%role%';
|
||
|
||
SELECT '角色字段统一升级完成！' as result;
|