667 lines
22 KiB
PL/PgSQL
667 lines
22 KiB
PL/PgSQL
-- =====================================================================================
|
|
-- 商城系统 SEO 优化和安全策略脚本
|
|
-- 用途: 为商城系统添加 SEO 优化函数和 RLS 安全策略
|
|
-- 前置条件: 需要先执行 mall_migration.sql
|
|
-- =====================================================================================
|
|
|
|
-- =====================================================================================
|
|
-- 1. SEO 优化相关函数
|
|
-- =====================================================================================
|
|
|
|
-- 根据 cid 获取商品信息 (SEO 友好)
|
|
CREATE OR REPLACE FUNCTION public.get_product_by_cid(p_cid INTEGER)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
cid INTEGER,
|
|
name VARCHAR,
|
|
slug VARCHAR,
|
|
description TEXT,
|
|
main_image_url TEXT,
|
|
base_price DECIMAL,
|
|
rating_avg DECIMAL,
|
|
sale_count INTEGER,
|
|
category_name VARCHAR,
|
|
brand_name VARCHAR,
|
|
shop_name VARCHAR
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
p.id,
|
|
p.cid,
|
|
p.name,
|
|
p.slug,
|
|
p.description,
|
|
p.main_image_url,
|
|
p.base_price,
|
|
p.rating_avg,
|
|
p.sale_count,
|
|
c.name as category_name,
|
|
b.name as brand_name,
|
|
s.shop_name
|
|
FROM public.ml_products p
|
|
LEFT JOIN public.ml_categories c ON p.category_id = c.id
|
|
LEFT JOIN public.ml_brands b ON p.brand_id = b.id
|
|
LEFT JOIN public.ml_shops s ON p.merchant_id = s.merchant_id
|
|
WHERE p.cid = p_cid AND p.status = 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 根据 cid 获取分类信息 (SEO 友好)
|
|
CREATE OR REPLACE FUNCTION public.get_category_by_cid(p_cid INTEGER)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
cid INTEGER,
|
|
name VARCHAR,
|
|
slug VARCHAR,
|
|
description TEXT,
|
|
icon_url TEXT,
|
|
path TEXT[]
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
c.id,
|
|
c.cid,
|
|
c.name,
|
|
c.slug,
|
|
c.description,
|
|
c.icon_url,
|
|
c.path
|
|
FROM public.ml_categories c
|
|
WHERE c.cid = p_cid AND c.is_active = TRUE;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 根据 cid 获取品牌信息 (SEO 友好)
|
|
CREATE OR REPLACE FUNCTION public.get_brand_by_cid(p_cid INTEGER)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
cid INTEGER,
|
|
name VARCHAR,
|
|
logo_url TEXT,
|
|
description TEXT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
b.id,
|
|
b.cid,
|
|
b.name,
|
|
b.logo_url,
|
|
b.description
|
|
FROM public.ml_brands b
|
|
WHERE b.cid = p_cid AND b.is_active = TRUE;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 根据 cid 获取店铺信息 (SEO 友好)
|
|
CREATE OR REPLACE FUNCTION public.get_shop_by_cid(p_cid INTEGER)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
cid INTEGER,
|
|
shop_name VARCHAR,
|
|
description TEXT,
|
|
shop_logo TEXT,
|
|
rating_avg DECIMAL,
|
|
product_count INTEGER
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
s.id,
|
|
s.cid,
|
|
s.shop_name,
|
|
s.description,
|
|
s.shop_logo,
|
|
s.rating_avg,
|
|
s.product_count
|
|
FROM public.ml_shops s
|
|
WHERE s.cid = p_cid AND s.status = 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 生成 SEO 友好的 URL 路径
|
|
CREATE OR REPLACE FUNCTION public.generate_seo_url(
|
|
p_type VARCHAR, -- 'product', 'category', 'brand', 'shop'
|
|
p_cid INTEGER,
|
|
p_slug VARCHAR DEFAULT NULL
|
|
)
|
|
RETURNS TEXT AS $$
|
|
DECLARE
|
|
url_path TEXT;
|
|
BEGIN
|
|
CASE p_type
|
|
WHEN 'product' THEN
|
|
url_path := '/product/' || p_cid;
|
|
IF p_slug IS NOT NULL THEN
|
|
url_path := url_path || '/' || p_slug;
|
|
END IF;
|
|
WHEN 'category' THEN
|
|
url_path := '/category/' || p_cid;
|
|
IF p_slug IS NOT NULL THEN
|
|
url_path := url_path || '/' || p_slug;
|
|
END IF;
|
|
WHEN 'brand' THEN
|
|
url_path := '/brand/' || p_cid;
|
|
IF p_slug IS NOT NULL THEN
|
|
url_path := url_path || '/' || p_slug;
|
|
END IF;
|
|
WHEN 'shop' THEN
|
|
url_path := '/shop/' || p_cid;
|
|
IF p_slug IS NOT NULL THEN
|
|
url_path := url_path || '/' || p_slug;
|
|
END IF;
|
|
ELSE
|
|
url_path := '/' || p_type || '/' || p_cid;
|
|
END CASE;
|
|
|
|
RETURN url_path;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 批量更新 slug 字段（用于现有数据）
|
|
CREATE OR REPLACE FUNCTION public.update_seo_slugs()
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
-- 更新商品 slug
|
|
UPDATE public.ml_products
|
|
SET slug = LOWER(REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fa5]+', '-', 'g'))
|
|
WHERE slug IS NULL OR slug = '';
|
|
|
|
-- 更新分类 slug
|
|
UPDATE public.ml_categories
|
|
SET slug = LOWER(REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fa5]+', '-', 'g'))
|
|
WHERE slug IS NULL OR slug = '';
|
|
|
|
RAISE NOTICE 'SEO slugs updated successfully';
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- =====================================================================================
|
|
-- 2. 商业逻辑函数
|
|
-- =====================================================================================
|
|
|
|
-- 计算购物车总金额
|
|
CREATE OR REPLACE FUNCTION public.calculate_cart_total(p_user_id UUID)
|
|
RETURNS DECIMAL AS $$
|
|
DECLARE
|
|
total_amount DECIMAL := 0;
|
|
BEGIN
|
|
SELECT COALESCE(SUM(
|
|
CASE
|
|
WHEN s.id IS NOT NULL THEN s.price * c.quantity
|
|
ELSE p.base_price * c.quantity
|
|
END
|
|
), 0) INTO total_amount
|
|
FROM public.ml_shopping_cart c
|
|
LEFT JOIN public.ml_product_skus s ON c.sku_id = s.id
|
|
LEFT JOIN public.ml_products p ON c.product_id = p.id
|
|
WHERE c.user_id = p_user_id
|
|
AND c.selected = TRUE
|
|
AND p.status = 1
|
|
AND (s.id IS NULL OR s.status = 1);
|
|
|
|
RETURN total_amount;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 获取商品可用库存
|
|
CREATE OR REPLACE FUNCTION public.get_product_available_stock(p_product_id UUID, p_sku_id UUID DEFAULT NULL)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
stock_count INTEGER := 0;
|
|
BEGIN
|
|
IF p_sku_id IS NOT NULL THEN
|
|
-- 获取特定SKU库存
|
|
SELECT COALESCE(stock, 0) INTO stock_count
|
|
FROM public.ml_product_skus
|
|
WHERE id = p_sku_id AND product_id = p_product_id AND status = 1;
|
|
ELSE
|
|
-- 获取商品总库存
|
|
SELECT COALESCE(available_stock, 0) INTO stock_count
|
|
FROM public.ml_products
|
|
WHERE id = p_product_id AND status = 1;
|
|
END IF;
|
|
|
|
RETURN stock_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 商品库存更新触发器函数
|
|
CREATE OR REPLACE FUNCTION public.update_product_stock()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- 更新商品总库存
|
|
IF TG_OP = 'DELETE' THEN
|
|
UPDATE public.ml_products
|
|
SET
|
|
total_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = OLD.product_id AND status = 1
|
|
),
|
|
available_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = OLD.product_id AND status = 1
|
|
)
|
|
WHERE id = OLD.product_id;
|
|
RETURN OLD;
|
|
ELSE
|
|
UPDATE public.ml_products
|
|
SET
|
|
total_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = NEW.product_id AND status = 1
|
|
),
|
|
available_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = NEW.product_id AND status = 1
|
|
)
|
|
WHERE id = NEW.product_id;
|
|
RETURN NEW;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 订单状态变更时的处理
|
|
CREATE OR REPLACE FUNCTION public.handle_order_status_change()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- 如果订单状态变为已付款
|
|
IF NEW.order_status = 2 AND (OLD.order_status IS NULL OR OLD.order_status = 1) THEN
|
|
NEW.paid_at = NOW();
|
|
END IF;
|
|
|
|
-- 如果订单状态变为已发货
|
|
IF NEW.order_status = 3 AND OLD.order_status = 2 THEN
|
|
NEW.shipped_at = NOW();
|
|
END IF;
|
|
|
|
-- 如果订单状态变为已完成
|
|
IF NEW.order_status = 4 AND OLD.order_status = 3 THEN
|
|
NEW.delivered_at = NOW();
|
|
NEW.completed_at = NOW();
|
|
|
|
-- 更新商品销量
|
|
UPDATE public.ml_products
|
|
SET sale_count = sale_count + (
|
|
SELECT SUM(quantity)
|
|
FROM public.ml_order_items
|
|
WHERE order_id = NEW.id
|
|
)
|
|
WHERE id IN (
|
|
SELECT product_id
|
|
FROM public.ml_order_items
|
|
WHERE order_id = NEW.id
|
|
);
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 创建库存更新触发器
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trigger_ml_update_product_stock') THEN
|
|
CREATE TRIGGER trigger_ml_update_product_stock
|
|
AFTER INSERT OR UPDATE OR DELETE ON public.ml_product_skus
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_product_stock();
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 创建订单状态变更触发器
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trigger_ml_order_status_change') THEN
|
|
CREATE TRIGGER trigger_ml_order_status_change
|
|
BEFORE UPDATE ON public.ml_orders
|
|
FOR EACH ROW EXECUTE FUNCTION public.handle_order_status_change();
|
|
END IF;
|
|
END $$;
|
|
|
|
-- =====================================================================================
|
|
-- 3. 创建详细视图
|
|
-- =====================================================================================
|
|
|
|
-- 商品详情视图
|
|
CREATE OR REPLACE VIEW public.ml_products_detail_view AS
|
|
SELECT
|
|
p.*,
|
|
c.cid as category_cid,
|
|
c.name as category_name,
|
|
c.path as category_path,
|
|
b.cid as brand_cid,
|
|
b.name as brand_name,
|
|
s.cid as shop_cid,
|
|
s.shop_name,
|
|
u.username as merchant_name,
|
|
CASE
|
|
WHEN p.status = 1 THEN '上架'
|
|
WHEN p.status = 2 THEN '下架'
|
|
WHEN p.status = 3 THEN '草稿'
|
|
WHEN p.status = 4 THEN '删除'
|
|
ELSE '未知'
|
|
END as status_name
|
|
FROM public.ml_products p
|
|
LEFT JOIN public.ml_categories c ON p.category_id = c.id
|
|
LEFT JOIN public.ml_brands b ON p.brand_id = b.id
|
|
LEFT JOIN public.ml_shops s ON p.merchant_id = s.merchant_id
|
|
LEFT JOIN public.ak_users u ON p.merchant_id = u.id;
|
|
|
|
COMMENT ON VIEW public.ml_products_detail_view IS '商品详情视图';
|
|
|
|
-- 订单详情视图
|
|
CREATE OR REPLACE VIEW public.ml_orders_detail_view AS
|
|
SELECT
|
|
o.*,
|
|
u.username as customer_name,
|
|
u.phone as customer_phone,
|
|
m.username as merchant_name,
|
|
s.shop_name,
|
|
CASE
|
|
WHEN o.order_status = 1 THEN '待付款'
|
|
WHEN o.order_status = 2 THEN '待发货'
|
|
WHEN o.order_status = 3 THEN '待收货'
|
|
WHEN o.order_status = 4 THEN '已完成'
|
|
WHEN o.order_status = 5 THEN '已取消'
|
|
WHEN o.order_status = 6 THEN '退款中'
|
|
WHEN o.order_status = 7 THEN '已退款'
|
|
ELSE '未知'
|
|
END as order_status_name,
|
|
CASE
|
|
WHEN o.payment_status = 1 THEN '未付款'
|
|
WHEN o.payment_status = 2 THEN '已付款'
|
|
WHEN o.payment_status = 3 THEN '部分退款'
|
|
WHEN o.payment_status = 4 THEN '全额退款'
|
|
ELSE '未知'
|
|
END as payment_status_name
|
|
FROM public.ml_orders o
|
|
LEFT JOIN public.ak_users u ON o.user_id = u.id
|
|
LEFT JOIN public.ak_users m ON o.merchant_id = m.id
|
|
LEFT JOIN public.ml_shops s ON o.merchant_id = s.merchant_id;
|
|
|
|
COMMENT ON VIEW public.ml_orders_detail_view IS '订单详情视图';
|
|
|
|
-- =====================================================================================
|
|
-- 4. RLS (Row Level Security) 策略
|
|
-- =====================================================================================
|
|
|
|
-- 启用 RLS
|
|
ALTER TABLE public.ml_user_profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_user_addresses ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_shopping_cart ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_user_favorites ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_browse_history ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_user_coupons ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_orders ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.ml_products ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 用户档案策略：用户只能访问自己的数据
|
|
DO $$
|
|
BEGIN
|
|
-- 删除可能存在的策略
|
|
DROP POLICY IF EXISTS ml_user_profiles_select_policy ON public.ml_user_profiles;
|
|
DROP POLICY IF EXISTS ml_user_profiles_insert_policy ON public.ml_user_profiles;
|
|
DROP POLICY IF EXISTS ml_user_profiles_update_policy ON public.ml_user_profiles;
|
|
DROP POLICY IF EXISTS ml_user_profiles_delete_policy ON public.ml_user_profiles;
|
|
|
|
-- 创建新策略
|
|
CREATE POLICY ml_user_profiles_select_policy ON public.ml_user_profiles
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_profiles_insert_policy ON public.ml_user_profiles
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_profiles_update_policy ON public.ml_user_profiles
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_profiles_delete_policy ON public.ml_user_profiles
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 用户地址策略
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_user_addresses_select_policy ON public.ml_user_addresses;
|
|
DROP POLICY IF EXISTS ml_user_addresses_insert_policy ON public.ml_user_addresses;
|
|
DROP POLICY IF EXISTS ml_user_addresses_update_policy ON public.ml_user_addresses;
|
|
DROP POLICY IF EXISTS ml_user_addresses_delete_policy ON public.ml_user_addresses;
|
|
|
|
CREATE POLICY ml_user_addresses_select_policy ON public.ml_user_addresses
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_addresses_insert_policy ON public.ml_user_addresses
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_addresses_update_policy ON public.ml_user_addresses
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_addresses_delete_policy ON public.ml_user_addresses
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 购物车策略
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_shopping_cart_select_policy ON public.ml_shopping_cart;
|
|
DROP POLICY IF EXISTS ml_shopping_cart_insert_policy ON public.ml_shopping_cart;
|
|
DROP POLICY IF EXISTS ml_shopping_cart_update_policy ON public.ml_shopping_cart;
|
|
DROP POLICY IF EXISTS ml_shopping_cart_delete_policy ON public.ml_shopping_cart;
|
|
|
|
CREATE POLICY ml_shopping_cart_select_policy ON public.ml_shopping_cart
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_shopping_cart_insert_policy ON public.ml_shopping_cart
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_shopping_cart_update_policy ON public.ml_shopping_cart
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_shopping_cart_delete_policy ON public.ml_shopping_cart
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 订单策略：用户可以查看自己的订单，商家可以查看自己店铺的订单
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_orders_select_policy ON public.ml_orders;
|
|
DROP POLICY IF EXISTS ml_orders_insert_policy ON public.ml_orders;
|
|
DROP POLICY IF EXISTS ml_orders_update_policy ON public.ml_orders;
|
|
DROP POLICY IF EXISTS ml_orders_delete_policy ON public.ml_orders;
|
|
|
|
CREATE POLICY ml_orders_select_policy ON public.ml_orders
|
|
FOR SELECT USING (
|
|
auth.uid() IN (
|
|
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY ml_orders_insert_policy ON public.ml_orders
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() IN (
|
|
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY ml_orders_update_policy ON public.ml_orders
|
|
FOR UPDATE USING (
|
|
auth.uid() IN (
|
|
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY ml_orders_delete_policy ON public.ml_orders
|
|
FOR DELETE USING (
|
|
auth.uid() IN (
|
|
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
|
)
|
|
);
|
|
END $$;
|
|
|
|
-- 商品策略：所有人可以查看上架商品，商家只能管理自己的商品
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_products_select_policy ON public.ml_products;
|
|
DROP POLICY IF EXISTS ml_products_insert_policy ON public.ml_products;
|
|
DROP POLICY IF EXISTS ml_products_update_policy ON public.ml_products;
|
|
DROP POLICY IF EXISTS ml_products_delete_policy ON public.ml_products;
|
|
|
|
CREATE POLICY ml_products_select_policy ON public.ml_products
|
|
FOR SELECT USING (status = 1);
|
|
|
|
CREATE POLICY ml_products_insert_policy ON public.ml_products
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
|
);
|
|
|
|
CREATE POLICY ml_products_update_policy ON public.ml_products
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
|
);
|
|
|
|
CREATE POLICY ml_products_delete_policy ON public.ml_products
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 收藏策略
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_user_favorites_select_policy ON public.ml_user_favorites;
|
|
DROP POLICY IF EXISTS ml_user_favorites_insert_policy ON public.ml_user_favorites;
|
|
DROP POLICY IF EXISTS ml_user_favorites_update_policy ON public.ml_user_favorites;
|
|
DROP POLICY IF EXISTS ml_user_favorites_delete_policy ON public.ml_user_favorites;
|
|
|
|
CREATE POLICY ml_user_favorites_select_policy ON public.ml_user_favorites
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_favorites_insert_policy ON public.ml_user_favorites
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_favorites_update_policy ON public.ml_user_favorites
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_favorites_delete_policy ON public.ml_user_favorites
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 浏览历史策略
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_browse_history_select_policy ON public.ml_browse_history;
|
|
DROP POLICY IF EXISTS ml_browse_history_insert_policy ON public.ml_browse_history;
|
|
DROP POLICY IF EXISTS ml_browse_history_update_policy ON public.ml_browse_history;
|
|
DROP POLICY IF EXISTS ml_browse_history_delete_policy ON public.ml_browse_history;
|
|
|
|
CREATE POLICY ml_browse_history_select_policy ON public.ml_browse_history
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_browse_history_insert_policy ON public.ml_browse_history
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_browse_history_update_policy ON public.ml_browse_history
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_browse_history_delete_policy ON public.ml_browse_history
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- 优惠券策略
|
|
DO $$
|
|
BEGIN
|
|
DROP POLICY IF EXISTS ml_user_coupons_select_policy ON public.ml_user_coupons;
|
|
DROP POLICY IF EXISTS ml_user_coupons_insert_policy ON public.ml_user_coupons;
|
|
DROP POLICY IF EXISTS ml_user_coupons_update_policy ON public.ml_user_coupons;
|
|
DROP POLICY IF EXISTS ml_user_coupons_delete_policy ON public.ml_user_coupons;
|
|
|
|
CREATE POLICY ml_user_coupons_select_policy ON public.ml_user_coupons
|
|
FOR SELECT USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_coupons_insert_policy ON public.ml_user_coupons
|
|
FOR INSERT WITH CHECK (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_coupons_update_policy ON public.ml_user_coupons
|
|
FOR UPDATE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
|
|
CREATE POLICY ml_user_coupons_delete_policy ON public.ml_user_coupons
|
|
FOR DELETE USING (
|
|
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
|
);
|
|
END $$;
|
|
|
|
-- =====================================================================================
|
|
-- 5. 完成提示
|
|
-- =====================================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE 'SEO 优化和安全策略配置完成！';
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE '已创建 SEO 函数: 6 个';
|
|
RAISE NOTICE '已创建业务函数: 4 个';
|
|
RAISE NOTICE '已创建详细视图: 2 个';
|
|
RAISE NOTICE '已配置 RLS 策略: 8 个表';
|
|
RAISE NOTICE '已创建库存和订单触发器';
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE '功能说明:';
|
|
RAISE NOTICE '- SEO 友好的 URL 生成';
|
|
RAISE NOTICE '- CID 基础的数据查询';
|
|
RAISE NOTICE '- 自动库存管理';
|
|
RAISE NOTICE '- 订单状态自动更新';
|
|
RAISE NOTICE '- 用户数据安全隔离';
|
|
RAISE NOTICE '=======================================================';
|
|
END $$;
|