77 lines
3.1 KiB
SQL
77 lines
3.1 KiB
SQL
-- ===================================================================
|
|
-- 消息系统表结构验证和简单测试脚本
|
|
-- 用于验证修复后的 generate_message_data.sql 是否可以正常执行
|
|
-- ===================================================================
|
|
|
|
-- 检查消息类型表是否存在
|
|
SELECT 'ak_message_types表检查' as check_name,
|
|
CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables
|
|
WHERE table_name = 'ak_message_types')
|
|
THEN '✓ 表存在'
|
|
ELSE '✗ 表不存在'
|
|
END as result;
|
|
|
|
-- 检查消息主表是否存在
|
|
SELECT 'ak_messages表检查' as check_name,
|
|
CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables
|
|
WHERE table_name = 'ak_messages')
|
|
THEN '✓ 表存在'
|
|
ELSE '✗ 表不存在'
|
|
END as result;
|
|
|
|
-- 检查消息接收者表是否存在
|
|
SELECT 'ak_message_recipients表检查' as check_name,
|
|
CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables
|
|
WHERE table_name = 'ak_message_recipients')
|
|
THEN '✓ 表存在'
|
|
ELSE '✗ 表不存在'
|
|
END as result;
|
|
|
|
-- 检查消息类型数据
|
|
SELECT 'message_types数据检查' as check_name,
|
|
CASE WHEN EXISTS (SELECT 1 FROM ak_message_types WHERE code = 'system')
|
|
THEN '✓ 系统消息类型存在'
|
|
ELSE '✗ 系统消息类型不存在'
|
|
END as result;
|
|
|
|
-- 显示现有消息类型
|
|
SELECT '现有消息类型' as info, code, name FROM ak_message_types ORDER BY priority DESC;
|
|
|
|
-- 检查ak_message_recipients表的列结构
|
|
SELECT '接收者表列结构' as info, column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'ak_message_recipients'
|
|
AND column_name IN ('id', 'message_id', 'recipient_id', 'recipient_type', 'status', 'read_duration_sec', 'created_at', 'updated_at')
|
|
ORDER BY ordinal_position;
|
|
|
|
-- 检查ak_messages表的列结构
|
|
SELECT '消息表列结构' as info, column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'ak_messages'
|
|
AND column_name IN ('id', 'message_type_id', 'sender_id', 'sender_type', 'title', 'content', 'priority', 'created_at', 'updated_at', 'metadata')
|
|
ORDER BY ordinal_position;
|
|
|
|
-- 简单插入测试
|
|
INSERT INTO ak_messages (id, message_type_id, sender_id, sender_type, title, content, priority, created_at, updated_at, metadata)
|
|
VALUES (
|
|
gen_random_uuid(),
|
|
(SELECT id FROM ak_message_types WHERE code = 'system'),
|
|
'00000000-0000-0000-0000-000000000001'::uuid,
|
|
'system',
|
|
'测试消息',
|
|
'这是一条测试消息，用于验证表结构是否正确。',
|
|
90,
|
|
now(),
|
|
now(),
|
|
'{"test": true}'::jsonb
|
|
) RETURNING id, title, created_at;
|
|
|
|
-- 显示插入的测试消息
|
|
SELECT '插入测试结果' as info, m.title, mt.code as message_type, m.sender_type, m.priority, m.created_at
|
|
FROM ak_messages m
|
|
JOIN ak_message_types mt ON m.message_type_id = mt.id
|
|
WHERE m.title = '测试消息';
|
|
|
|
-- 清理测试数据
|
|
DELETE FROM ak_messages WHERE title = '测试消息';
|