89 lines
3.7 KiB
SQL
89 lines
3.7 KiB
SQL
-- ===================================================================
|
||
-- ON CONFLICT 修复验证脚本
|
||
-- ===================================================================
|
||
-- 此脚本用于验证所有ON CONFLICT语句是否能正确执行
|
||
|
||
\echo '开始验证ON CONFLICT修复...'
|
||
|
||
-- 1. 测试 ak_content_categories 的 name_key 唯一约束
|
||
\echo '测试 ak_content_categories ON CONFLICT (name_key)...'
|
||
INSERT INTO public.ak_content_categories (name_key, ai_keywords, sort_order)
|
||
VALUES ('test.conflict.category', ARRAY['测试冲突'], 999)
|
||
ON CONFLICT (name_key) DO NOTHING;
|
||
|
||
-- 再次插入相同的name_key，应该被忽略
|
||
INSERT INTO public.ak_content_categories (name_key, ai_keywords, sort_order)
|
||
VALUES ('test.conflict.category', ARRAY['测试冲突重复'], 1000)
|
||
ON CONFLICT (name_key) DO NOTHING;
|
||
|
||
-- 2. 测试 ak_ai_usage_stats 的复合唯一约束
|
||
\echo '测试 ak_ai_usage_stats ON CONFLICT (service_type, ai_provider, date_bucket)...'
|
||
INSERT INTO public.ak_ai_usage_stats (
|
||
service_type, ai_provider, date_bucket, tokens_used, requests_count,
|
||
cost_usd, processing_time_ms, success_count, error_count
|
||
) VALUES (
|
||
'test_service', 'test_provider', CURRENT_DATE, 100, 1, 0.01, 500, 1, 0
|
||
)
|
||
ON CONFLICT (service_type, ai_provider, date_bucket)
|
||
DO UPDATE SET
|
||
tokens_used = ak_ai_usage_stats.tokens_used + EXCLUDED.tokens_used,
|
||
requests_count = ak_ai_usage_stats.requests_count + 1,
|
||
cost_usd = ak_ai_usage_stats.cost_usd + EXCLUDED.cost_usd;
|
||
|
||
-- 再次插入相同的组合，应该更新而不是插入新记录
|
||
INSERT INTO public.ak_ai_usage_stats (
|
||
service_type, ai_provider, date_bucket, tokens_used, requests_count,
|
||
cost_usd, processing_time_ms, success_count, error_count
|
||
) VALUES (
|
||
'test_service', 'test_provider', CURRENT_DATE, 200, 1, 0.02, 600, 1, 0
|
||
)
|
||
ON CONFLICT (service_type, ai_provider, date_bucket)
|
||
DO UPDATE SET
|
||
tokens_used = ak_ai_usage_stats.tokens_used + EXCLUDED.tokens_used,
|
||
requests_count = ak_ai_usage_stats.requests_count + 1,
|
||
cost_usd = ak_ai_usage_stats.cost_usd + EXCLUDED.cost_usd;
|
||
|
||
-- 3. 测试 ak_user_profiles 的 user_id 唯一约束
|
||
\echo '测试 ak_user_profiles ON CONFLICT (user_id)...'
|
||
-- 注意：这个测试需要一个有效的user_id，所以我们跳过实际插入
|
||
-- 但约束已经存在于表定义中
|
||
|
||
-- 4. 测试 ak_database_versions 的 version 唯一约束
|
||
\echo '测试 ak_database_versions ON CONFLICT (version)...'
|
||
INSERT INTO public.ak_database_versions (version, description)
|
||
VALUES ('test.version.1.0', '测试版本冲突处理')
|
||
ON CONFLICT (version) DO NOTHING;
|
||
|
||
-- 再次插入相同版本，应该被忽略
|
||
INSERT INTO public.ak_database_versions (version, description)
|
||
VALUES ('test.version.1.0', '测试版本冲突处理 - 重复')
|
||
ON CONFLICT (version) DO NOTHING;
|
||
|
||
-- 5. 验证结果查询
|
||
\echo '验证测试结果...'
|
||
|
||
SELECT 'ak_content_categories' as table_name, count(*) as test_records
|
||
FROM public.ak_content_categories
|
||
WHERE name_key = 'test.conflict.category'
|
||
|
||
UNION ALL
|
||
|
||
SELECT 'ak_ai_usage_stats' as table_name,
|
||
CASE WHEN count(*) = 1 AND sum(tokens_used) = 300 THEN 1 ELSE 0 END as test_records
|
||
FROM public.ak_ai_usage_stats
|
||
WHERE service_type = 'test_service' AND ai_provider = 'test_provider'
|
||
|
||
UNION ALL
|
||
|
||
SELECT 'ak_database_versions' as table_name, count(*) as test_records
|
||
FROM public.ak_database_versions
|
||
WHERE version = 'test.version.1.0';
|
||
|
||
-- 清理测试数据
|
||
\echo '清理测试数据...'
|
||
DELETE FROM public.ak_content_categories WHERE name_key = 'test.conflict.category';
|
||
DELETE FROM public.ak_ai_usage_stats WHERE service_type = 'test_service' AND ai_provider = 'test_provider';
|
||
DELETE FROM public.ak_database_versions WHERE version = 'test.version.1.0';
|
||
|
||
\echo 'ON CONFLICT 验证完成！'
|