114 lines
4.2 KiB
SQL
114 lines
4.2 KiB
SQL
-- 商城数据库脚本验证测试
|
|
-- 这个脚本用于验证数据库创建和模拟数据插入是否正常工作
|
|
|
|
-- 1. 检查必要的扩展是否可用
|
|
DO $$
|
|
BEGIN
|
|
-- 检查 uuid-ossp 扩展
|
|
IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'uuid-ossp') THEN
|
|
RAISE NOTICE 'uuid-ossp 扩展未安装，请先执行: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";';
|
|
ELSE
|
|
RAISE NOTICE 'uuid-ossp 扩展已安装 ✓';
|
|
END IF;
|
|
|
|
-- 检查 pgcrypto 扩展
|
|
IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgcrypto') THEN
|
|
RAISE NOTICE 'pgcrypto 扩展未安装，请先执行: CREATE EXTENSION IF NOT EXISTS "pgcrypto";';
|
|
ELSE
|
|
RAISE NOTICE 'pgcrypto 扩展已安装 ✓';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 2. 检查 ak_users 表是否存在
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_users') THEN
|
|
RAISE NOTICE 'ak_users 表已存在 ✓';
|
|
|
|
-- 检查 ak_users 表结构
|
|
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'ak_users' AND column_name = 'auth_id' AND data_type = 'uuid') THEN
|
|
RAISE NOTICE 'ak_users.auth_id 字段类型正确 (uuid) ✓';
|
|
ELSE
|
|
RAISE NOTICE 'ak_users.auth_id 字段类型可能不正确，应为 uuid 类型';
|
|
END IF;
|
|
ELSE
|
|
RAISE NOTICE 'ak_users 表不存在，需要先创建或从现有系统迁移';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 3. 语法验证 - 测试典型的 RLS 策略语法
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE '开始验证 RLS 策略语法...';
|
|
|
|
-- 测试 UUID 比较语法
|
|
BEGIN
|
|
-- 这个查询应该能正常解析
|
|
PERFORM 1 WHERE '00000000-0000-0000-0000-000000000000'::uuid = '00000000-0000-0000-0000-000000000000'::uuid;
|
|
RAISE NOTICE 'UUID 比较语法正确 ✓';
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RAISE NOTICE 'UUID 比较语法错误: %', SQLERRM;
|
|
END;
|
|
|
|
RAISE NOTICE 'RLS 策略语法验证完成 ✓';
|
|
END $$;
|
|
|
|
-- 4. 检查商城表是否已存在
|
|
DO $$
|
|
DECLARE
|
|
table_count INTEGER;
|
|
mall_tables TEXT[] := ARRAY[
|
|
'ml_user_profiles', 'ml_user_addresses', 'ml_shopping_cart',
|
|
'ml_merchants', 'ml_categories', 'ml_products', 'ml_product_images',
|
|
'ml_product_variants', 'ml_inventory', 'ml_orders', 'ml_order_items',
|
|
'ml_reviews', 'ml_user_behavior', 'ml_promotions', 'ml_coupons',
|
|
'ml_user_coupons', 'ml_delivery_info', 'ml_system_config'
|
|
];
|
|
tbl TEXT;
|
|
BEGIN
|
|
table_count := 0;
|
|
|
|
FOREACH tbl IN ARRAY mall_tables
|
|
LOOP
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = tbl) THEN
|
|
table_count := table_count + 1;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '商城表检查: %/% 个表已存在', table_count, array_length(mall_tables, 1);
|
|
|
|
IF table_count = 0 THEN
|
|
RAISE NOTICE '商城表尚未创建，可以执行 complete_mall_database.sql';
|
|
ELSIF table_count = array_length(mall_tables, 1) THEN
|
|
RAISE NOTICE '所有商城表已存在 ✓';
|
|
ELSE
|
|
RAISE NOTICE '部分商城表已存在，建议检查现有表结构';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 5. 模拟数据检查
|
|
DO $$
|
|
DECLARE
|
|
user_count INTEGER;
|
|
profile_count INTEGER;
|
|
product_count INTEGER;
|
|
BEGIN
|
|
-- 检查用户数据
|
|
SELECT COUNT(*) INTO user_count FROM public.ak_users WHERE username IN ('admin', 'merchant1', 'merchant2', 'customer1', 'customer2', 'customer3', 'driver1', 'driver2');
|
|
RAISE NOTICE '测试用户数量: %', user_count;
|
|
|
|
-- 检查商城相关数据（如果表存在）
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ml_user_profiles') THEN
|
|
SELECT COUNT(*) INTO profile_count FROM public.ml_user_profiles;
|
|
RAISE NOTICE '用户档案数量: %', profile_count;
|
|
END IF;
|
|
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ml_products') THEN
|
|
SELECT COUNT(*) INTO product_count FROM public.ml_products;
|
|
RAISE NOTICE '商品数量: %', product_count;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 验证完成
|
|
SELECT '数据库验证测试完成' AS status;
|