141 lines
5.9 KiB
SQL
141 lines
5.9 KiB
SQL
-- 添加检索结果项表和更新查询请求表
|
||
-- Migration script for search result items and inquiry request enhancements
|
||
|
||
USE medical_info_system;
|
||
|
||
-- 1. 更新查询请求表，添加新字段
|
||
-- 检查并添加 keywords_confirmed 字段
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND column_name = 'keywords_confirmed') = 0,
|
||
'ALTER TABLE inquiry_requests ADD COLUMN keywords_confirmed BOOLEAN DEFAULT FALSE COMMENT ''用户是否确认关键词''',
|
||
'SELECT ''Column keywords_confirmed already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 search_internal_data 字段
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND column_name = 'search_internal_data') = 0,
|
||
'ALTER TABLE inquiry_requests ADD COLUMN search_internal_data BOOLEAN DEFAULT FALSE COMMENT ''是否在内部数据中检索''',
|
||
'SELECT ''Column search_internal_data already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 search_knowledge_base 字段
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND column_name = 'search_knowledge_base') = 0,
|
||
'ALTER TABLE inquiry_requests ADD COLUMN search_knowledge_base BOOLEAN DEFAULT FALSE COMMENT ''是否在知识库中检索''',
|
||
'SELECT ''Column search_knowledge_base already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 search_cnki 字段
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND column_name = 'search_cnki') = 0,
|
||
'ALTER TABLE inquiry_requests ADD COLUMN search_cnki BOOLEAN DEFAULT FALSE COMMENT ''是否在知网中检索''',
|
||
'SELECT ''Column search_cnki already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 search_clinical_trials 字段
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND column_name = 'search_clinical_trials') = 0,
|
||
'ALTER TABLE inquiry_requests ADD COLUMN search_clinical_trials BOOLEAN DEFAULT FALSE COMMENT ''是否在ClinicalTrials中检索''',
|
||
'SELECT ''Column search_clinical_trials already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 2. 修改查询请求表，使customer_name为可选
|
||
ALTER TABLE inquiry_requests
|
||
MODIFY COLUMN customer_name VARCHAR(100) NULL COMMENT '客户姓名（非必填）';
|
||
|
||
-- 3. 修改查询请求表，使inquiry_content为必填
|
||
ALTER TABLE inquiry_requests
|
||
MODIFY COLUMN inquiry_content TEXT NOT NULL COMMENT '查询内容（必填）';
|
||
|
||
-- 4. 创建检索结果项表
|
||
CREATE TABLE IF NOT EXISTS search_result_items (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_request_id BIGINT NOT NULL COMMENT '查询请求ID',
|
||
title VARCHAR(500) NOT NULL COMMENT '标题',
|
||
summary TEXT COMMENT '摘要',
|
||
content TEXT COMMENT '内容',
|
||
authors VARCHAR(500) COMMENT '作者',
|
||
source VARCHAR(100) COMMENT '来源（知网、ClinicalTrials、知识库等）',
|
||
source_url VARCHAR(500) COMMENT '来源URL',
|
||
publication_date VARCHAR(50) COMMENT '发表日期',
|
||
doi VARCHAR(100) COMMENT 'DOI',
|
||
pmid VARCHAR(50) COMMENT 'PubMed ID',
|
||
nct_id VARCHAR(50) COMMENT 'ClinicalTrials NCT ID',
|
||
metadata TEXT COMMENT '其他元数据（JSON格式）',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'PENDING_REVIEW' COMMENT '结果状态: PENDING_REVIEW, APPROVED, REJECTED, DELETED',
|
||
include_in_response BOOLEAN DEFAULT FALSE COMMENT '是否纳入回复参考资料',
|
||
need_download BOOLEAN DEFAULT FALSE COMMENT '是否需要下载全文',
|
||
is_deleted BOOLEAN DEFAULT FALSE COMMENT '是否标记为错误并删除',
|
||
file_path VARCHAR(500) COMMENT '下载后的文件路径',
|
||
download_status VARCHAR(20) DEFAULT 'NOT_REQUIRED' COMMENT '下载状态: NOT_REQUIRED, PENDING, DOWNLOADING, COMPLETED, FAILED',
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
downloaded_at DATETIME,
|
||
INDEX idx_inquiry_request_id (inquiry_request_id),
|
||
INDEX idx_source (source),
|
||
INDEX idx_status (status),
|
||
INDEX idx_include_in_response (include_in_response),
|
||
INDEX idx_need_download (need_download),
|
||
INDEX idx_is_deleted (is_deleted),
|
||
INDEX idx_download_status (download_status),
|
||
FOREIGN KEY (inquiry_request_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='检索结果项表';
|
||
|
||
-- 5. 添加索引以提高查询性能
|
||
-- 检查并创建 idx_keywords_confirmed 索引
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND index_name = 'idx_keywords_confirmed') = 0,
|
||
'CREATE INDEX idx_keywords_confirmed ON inquiry_requests(keywords_confirmed)',
|
||
'SELECT ''Index idx_keywords_confirmed already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并创建 idx_search_flags 索引
|
||
SET @sql = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
||
WHERE table_name = 'inquiry_requests'
|
||
AND table_schema = DATABASE()
|
||
AND index_name = 'idx_search_flags') = 0,
|
||
'CREATE INDEX idx_search_flags ON inquiry_requests(search_internal_data, search_knowledge_base, search_cnki, search_clinical_trials)',
|
||
'SELECT ''Index idx_search_flags already exists'' as message'
|
||
));
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|