333 lines
14 KiB
SQL
333 lines
14 KiB
SQL
-- =====================================================================================
|
||
-- 商城系统数据库状态检查脚本
|
||
-- 分析现有数据库结构，生成个性化ALTER建议
|
||
-- =====================================================================================
|
||
|
||
-- =====================================================================================
|
||
-- 1. 检查现有表结构
|
||
-- =====================================================================================
|
||
|
||
-- 检查 ak_users 表字段情况
|
||
DO $$
|
||
DECLARE
|
||
missing_fields TEXT[] := ARRAY[]::TEXT[];
|
||
existing_fields TEXT[] := ARRAY[]::TEXT[];
|
||
field_name TEXT;
|
||
field_names TEXT[] := ARRAY['mall_status', 'mall_type', 'last_login_ip', 'total_orders', 'total_spent', 'user_level', 'points', 'verified_status'];
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '检查 ak_users 表字段状态';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
FOREACH field_name IN ARRAY field_names LOOP
|
||
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = field_name) THEN
|
||
existing_fields := array_append(existing_fields, field_name);
|
||
RAISE NOTICE '✓ 字段已存在: %', field_name;
|
||
ELSE
|
||
missing_fields := array_append(missing_fields, field_name);
|
||
RAISE NOTICE '✗ 字段缺失: %', field_name;
|
||
END IF;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '-------------------------------------------------------';
|
||
RAISE NOTICE '已存在字段数量: %', array_length(existing_fields, 1);
|
||
RAISE NOTICE '缺失字段数量: %', array_length(missing_fields, 1);
|
||
|
||
IF array_length(missing_fields, 1) > 0 THEN
|
||
RAISE NOTICE '需要添加的字段: %', array_to_string(missing_fields, ', ');
|
||
ELSE
|
||
RAISE NOTICE 'ak_users 表所有商城字段均已存在';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 检查商城表存在情况
|
||
DO $$
|
||
DECLARE
|
||
table_name TEXT;
|
||
table_names TEXT[] := ARRAY['ml_user_profiles', 'ml_user_addresses', 'ml_categories', 'ml_brands', 'ml_products', 'ml_product_skus', 'ml_shops', 'ml_orders', 'ml_shopping_cart'];
|
||
existing_tables TEXT[] := ARRAY[]::TEXT[];
|
||
missing_tables TEXT[] := ARRAY[]::TEXT[];
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '检查商城核心表存在情况';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
FOREACH table_name IN ARRAY table_names LOOP
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = table_name) THEN
|
||
existing_tables := array_append(existing_tables, table_name);
|
||
RAISE NOTICE '✓ 表已存在: %', table_name;
|
||
ELSE
|
||
missing_tables := array_append(missing_tables, table_name);
|
||
RAISE NOTICE '✗ 表缺失: %', table_name;
|
||
END IF;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '-------------------------------------------------------';
|
||
RAISE NOTICE '已存在表数量: %', array_length(existing_tables, 1);
|
||
RAISE NOTICE '缺失表数量: %', array_length(missing_tables, 1);
|
||
|
||
IF array_length(missing_tables, 1) > 0 THEN
|
||
RAISE NOTICE '需要创建的表: %', array_to_string(missing_tables, ', ');
|
||
ELSE
|
||
RAISE NOTICE '所有商城核心表均已存在';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 2. 检查现有索引情况
|
||
-- =====================================================================================
|
||
|
||
-- 检查重要索引存在情况
|
||
DO $$
|
||
DECLARE
|
||
index_info RECORD;
|
||
missing_indexes TEXT[] := ARRAY[]::TEXT[];
|
||
existing_indexes TEXT[] := ARRAY[]::TEXT[];
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '检查重要索引存在情况';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
-- 定义重要索引列表
|
||
FOR index_info IN
|
||
SELECT * FROM (VALUES
|
||
('idx_ak_users_mall_status', 'ak_users', 'mall_status'),
|
||
('idx_ak_users_mall_type', 'ak_users', 'mall_type'),
|
||
('idx_ak_users_total_orders', 'ak_users', 'total_orders'),
|
||
('idx_ml_products_cid', 'ml_products', 'cid'),
|
||
('idx_ml_products_slug', 'ml_products', 'slug'),
|
||
('idx_ml_categories_cid', 'ml_categories', 'cid'),
|
||
('idx_ml_orders_cid', 'ml_orders', 'cid'),
|
||
('idx_ml_shops_cid', 'ml_shops', 'cid')
|
||
) AS t(index_name, table_name, column_name)
|
||
LOOP
|
||
-- 检查表是否存在
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = index_info.table_name) THEN
|
||
-- 检查索引是否存在
|
||
IF EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = index_info.index_name) THEN
|
||
existing_indexes := array_append(existing_indexes, index_info.index_name);
|
||
RAISE NOTICE '✓ 索引已存在: % (表: %)', index_info.index_name, index_info.table_name;
|
||
ELSE
|
||
missing_indexes := array_append(missing_indexes, index_info.index_name);
|
||
RAISE NOTICE '✗ 索引缺失: % (表: %)', index_info.index_name, index_info.table_name;
|
||
END IF;
|
||
ELSE
|
||
RAISE NOTICE '○ 表不存在，跳过索引检查: % (表: %)', index_info.index_name, index_info.table_name;
|
||
END IF;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '-------------------------------------------------------';
|
||
RAISE NOTICE '已存在索引数量: %', array_length(existing_indexes, 1);
|
||
RAISE NOTICE '缺失索引数量: %', array_length(missing_indexes, 1);
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 3. 检查扩展和函数
|
||
-- =====================================================================================
|
||
|
||
-- 检查必要的PostgreSQL扩展
|
||
DO $$
|
||
DECLARE
|
||
ext_name TEXT;
|
||
extensions TEXT[] := ARRAY['uuid-ossp', 'btree_gin'];
|
||
existing_ext TEXT[] := ARRAY[]::TEXT[];
|
||
missing_ext TEXT[] := ARRAY[]::TEXT[];
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '检查PostgreSQL扩展';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
FOREACH ext_name IN ARRAY extensions LOOP
|
||
IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = ext_name) THEN
|
||
existing_ext := array_append(existing_ext, ext_name);
|
||
RAISE NOTICE '✓ 扩展已安装: %', ext_name;
|
||
ELSE
|
||
missing_ext := array_append(missing_ext, ext_name);
|
||
RAISE NOTICE '✗ 扩展缺失: %', ext_name;
|
||
END IF;
|
||
END LOOP;
|
||
|
||
IF array_length(missing_ext, 1) > 0 THEN
|
||
RAISE NOTICE '需要安装的扩展: %', array_to_string(missing_ext, ', ');
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 检查商城相关函数
|
||
DO $$
|
||
DECLARE
|
||
func_name TEXT;
|
||
functions TEXT[] := ARRAY['generate_order_no', 'calculate_cart_total', 'update_user_mall_stats'];
|
||
existing_funcs TEXT[] := ARRAY[]::TEXT[];
|
||
missing_funcs TEXT[] := ARRAY[]::TEXT[];
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '检查商城相关函数';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
FOREACH func_name IN ARRAY functions LOOP
|
||
IF EXISTS (SELECT 1 FROM pg_proc WHERE proname = func_name) THEN
|
||
existing_funcs := array_append(existing_funcs, func_name);
|
||
RAISE NOTICE '✓ 函数已存在: %', func_name;
|
||
ELSE
|
||
missing_funcs := array_append(missing_funcs, func_name);
|
||
RAISE NOTICE '✗ 函数缺失: %', func_name;
|
||
END IF;
|
||
END LOOP;
|
||
|
||
IF array_length(missing_funcs, 1) > 0 THEN
|
||
RAISE NOTICE '需要创建的函数: %', array_to_string(missing_funcs, ', ');
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 4. 生成个性化建议
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
DECLARE
|
||
ak_users_missing INTEGER := 0;
|
||
mall_tables_missing INTEGER := 0;
|
||
suggestion TEXT := '';
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '个性化升级建议';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
-- 统计ak_users缺失字段
|
||
SELECT COUNT(*) INTO ak_users_missing
|
||
FROM (VALUES ('mall_status'), ('mall_type'), ('total_orders'), ('total_spent')) AS t(field)
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'ak_users' AND column_name = t.field
|
||
);
|
||
|
||
-- 统计商城表缺失情况
|
||
SELECT COUNT(*) INTO mall_tables_missing
|
||
FROM (VALUES ('ml_products'), ('ml_categories'), ('ml_orders'), ('ml_shops')) AS t(table_name)
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM information_schema.tables
|
||
WHERE table_name = t.table_name
|
||
);
|
||
|
||
-- 生成建议
|
||
IF ak_users_missing > 0 AND mall_tables_missing > 0 THEN
|
||
suggestion := '建议使用 mall_alter_upgrade.sql（完整升级脚本）';
|
||
ELSIF ak_users_missing > 0 AND mall_tables_missing = 0 THEN
|
||
suggestion := '建议使用 mall_fields_only_upgrade.sql（仅字段升级脚本）';
|
||
ELSIF ak_users_missing = 0 AND mall_tables_missing > 0 THEN
|
||
suggestion := '建议使用 mall_migration.sql（表结构创建脚本）';
|
||
ELSE
|
||
suggestion := '数据库结构已完整，建议检查数据完整性和权限配置';
|
||
END IF;
|
||
|
||
RAISE NOTICE '根据您的数据库状态分析：';
|
||
RAISE NOTICE '• ak_users 表缺失字段数: %', ak_users_missing;
|
||
RAISE NOTICE '• 缺失商城核心表数: %', mall_tables_missing;
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '推荐执行方案: %', suggestion;
|
||
|
||
-- 详细建议
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '详细执行步骤：';
|
||
IF ak_users_missing > 0 THEN
|
||
RAISE NOTICE '1. 先执行字段升级脚本为ak_users表添加商城字段';
|
||
END IF;
|
||
IF mall_tables_missing > 0 THEN
|
||
RAISE NOTICE '2. 执行表结构创建脚本建立商城核心表';
|
||
END IF;
|
||
RAISE NOTICE '3. 执行SEO和安全策略脚本（mall_seo_security.sql）';
|
||
RAISE NOTICE '4. 根据需要执行模拟数据插入脚本进行测试';
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 5. 生成具体的ALTER语句（可选）
|
||
-- =====================================================================================
|
||
|
||
-- 生成ak_users表缺失字段的ALTER语句
|
||
DO $$
|
||
DECLARE
|
||
alter_statements TEXT := '';
|
||
field_name TEXT;
|
||
field_configs TEXT[] := ARRAY[
|
||
'mall_status INTEGER DEFAULT 1 CHECK (mall_status IN (1,2))',
|
||
'mall_type INTEGER DEFAULT 1 CHECK (mall_type IN (1,2,3))',
|
||
'total_orders INTEGER DEFAULT 0 CHECK (total_orders >= 0)',
|
||
'total_spent DECIMAL(12,2) DEFAULT 0.00 CHECK (total_spent >= 0)',
|
||
'user_level INTEGER DEFAULT 1 CHECK (user_level >= 1 AND user_level <= 10)',
|
||
'points INTEGER DEFAULT 0 CHECK (points >= 0)',
|
||
'verified_status INTEGER DEFAULT 0 CHECK (verified_status IN (0,1,2))'
|
||
];
|
||
field_names TEXT[] := ARRAY['mall_status', 'mall_type', 'total_orders', 'total_spent', 'user_level', 'points', 'verified_status'];
|
||
i INTEGER;
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '生成ak_users表ALTER语句';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
FOR i IN 1..array_length(field_names, 1) LOOP
|
||
field_name := field_names[i];
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = field_name) THEN
|
||
alter_statements := alter_statements || format('ALTER TABLE public.ak_users ADD COLUMN %s;' || chr(10), field_configs[i]);
|
||
RAISE NOTICE '需要执行: ALTER TABLE public.ak_users ADD COLUMN %;', field_configs[i];
|
||
END IF;
|
||
END LOOP;
|
||
|
||
IF alter_statements = '' THEN
|
||
RAISE NOTICE 'ak_users表无需添加字段';
|
||
ELSE
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '完整ALTER脚本：';
|
||
RAISE NOTICE '%', alter_statements;
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 6. 数据完整性检查
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
DECLARE
|
||
users_count INTEGER;
|
||
profiles_count INTEGER;
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '数据完整性检查';
|
||
RAISE NOTICE '=======================================================';
|
||
|
||
-- 检查用户表数据
|
||
SELECT COUNT(*) INTO users_count FROM public.ak_users;
|
||
RAISE NOTICE 'ak_users 表用户数量: %', users_count;
|
||
|
||
-- 检查用户档案表（如果存在）
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ml_user_profiles') THEN
|
||
SELECT COUNT(*) INTO profiles_count FROM public.ml_user_profiles;
|
||
RAISE NOTICE 'ml_user_profiles 表档案数量: %', profiles_count;
|
||
|
||
IF users_count > profiles_count THEN
|
||
RAISE NOTICE '注意: 有 % 个用户缺少商城档案，建议执行档案补充脚本', users_count - profiles_count;
|
||
END IF;
|
||
ELSE
|
||
RAISE NOTICE 'ml_user_profiles 表不存在';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 完成提示
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '数据库状态检查完成！';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '请根据上述分析结果选择合适的升级脚本：';
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '• mall_alter_upgrade.sql - 完整升级（表+字段+索引+函数）';
|
||
RAISE NOTICE '• mall_fields_only_upgrade.sql - 仅字段升级（最小化修改）';
|
||
RAISE NOTICE '• mall_migration.sql - 完整建表（全新部署）';
|
||
RAISE NOTICE '• mall_seo_security.sql - SEO优化和安全策略';
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '建议在生产环境执行前先在测试环境验证！';
|
||
RAISE NOTICE '=======================================================';
|
||
END $$;
|