package index
|
|
import (
|
"database/sql"
|
"encoding/json"
|
"sort"
|
)
|
// FTSResult 全文搜索结果
|
type FTSResult struct {
|
ID int64
|
Path string
|
Title string
|
Section string
|
Rank float64
|
}
|
|
// CreateFTS 创建 FTS5 虚拟表（external-content 模式，由触发器增量维护）
|
// 注意：external-content 模式下 FTS 列名必须与 content 表列名一致，
|
// 故 content 列对应 nodes 表的 content_fts 列
|
func (s *Store) CreateFTS() error {
|
_, err := s.db.Exec(`
|
CREATE VIRTUAL TABLE IF NOT EXISTS nodes_fts USING fts5(
|
title, content_fts, tags, aliases,
|
content='nodes', content_rowid='id'
|
)`)
|
if err != nil {
|
return err
|
}
|
// 触发器：nodes 的增删改同步维护 FTS
|
triggers := []string{
|
`CREATE TRIGGER IF NOT EXISTS nodes_fts_ai AFTER INSERT ON nodes BEGIN
|
INSERT INTO nodes_fts(rowid, title, content_fts, tags, aliases)
|
VALUES (new.id, new.title, new.content_fts, new.tags, new.aliases);
|
END`,
|
`CREATE TRIGGER IF NOT EXISTS nodes_fts_ad AFTER DELETE ON nodes BEGIN
|
INSERT INTO nodes_fts(nodes_fts, rowid, title, content_fts, tags, aliases)
|
VALUES ('delete', old.id, old.title, old.content_fts, old.tags, old.aliases);
|
END`,
|
`CREATE TRIGGER IF NOT EXISTS nodes_fts_au AFTER UPDATE ON nodes BEGIN
|
INSERT INTO nodes_fts(nodes_fts, rowid, title, content_fts, tags, aliases)
|
VALUES ('delete', old.id, old.title, old.content_fts, old.tags, old.aliases);
|
INSERT INTO nodes_fts(rowid, title, content_fts, tags, aliases)
|
VALUES (new.id, new.title, new.content_fts, new.tags, new.aliases);
|
END`,
|
}
|
for _, t := range triggers {
|
if _, err := s.db.Exec(t); err != nil {
|
return err
|
}
|
}
|
return nil
|
}
|
|
// PopulateFTS 全量重灌 FTS（external-content 模式专用语法）
|
func (s *Store) PopulateFTS() error {
|
_, err := s.db.Exec(`INSERT INTO nodes_fts(nodes_fts) VALUES('rebuild')`)
|
return err
|
}
|
|
// FTSSearch 全文搜索
|
func (s *Store) FTSSearch(keywords []string, limit int) ([]FTSResult, error) {
|
if len(keywords) == 0 {
|
return nil, nil
|
}
|
|
// 对每个关键词单独搜索，然后合并结果（去重）
|
// 这样即使某个关键词匹配不到，其他关键词也能找到结果
|
seen := make(map[int64]bool)
|
var allResults []FTSResult
|
|
for _, kw := range keywords {
|
rows, err := s.db.Query(`
|
SELECT n.id, n.path, n.title, n.section, fts.rank
|
FROM nodes_fts fts
|
JOIN nodes n ON n.id = fts.rowid
|
WHERE nodes_fts MATCH ?
|
ORDER BY fts.rank
|
LIMIT ?
|
`, kw, limit)
|
if err != nil {
|
// 单个关键词搜索失败，跳过继续
|
continue
|
}
|
|
for rows.Next() {
|
var r FTSResult
|
if err := rows.Scan(&r.ID, &r.Path, &r.Title, &r.Section, &r.Rank); err != nil {
|
rows.Close()
|
continue
|
}
|
// 去重
|
if !seen[r.ID] {
|
seen[r.ID] = true
|
allResults = append(allResults, r)
|
}
|
}
|
rows.Close()
|
}
|
|
// 按 rank 排序（FTS5 的 rank 越小越好）
|
sort.Slice(allResults, func(i, j int) bool {
|
return allResults[i].Rank < allResults[j].Rank
|
})
|
|
// 限制返回数量
|
if limit > 0 && len(allResults) > limit {
|
allResults = allResults[:limit]
|
}
|
|
return allResults, nil
|
}
|
|
// GetNodeContent 获取节点内容
|
func (s *Store) GetNodeContent(id int64) (string, []string, []string, error) {
|
var content, tagsJSON, entitiesJSON string
|
err := s.db.QueryRow("SELECT content_fts, tags, entities FROM nodes WHERE id = ?", id).
|
Scan(&content, &tagsJSON, &entitiesJSON)
|
if err == sql.ErrNoRows {
|
return "", nil, nil, nil
|
}
|
if err != nil {
|
return "", nil, nil, err
|
}
|
|
// 解析 JSON 数组
|
var tags, entities []string
|
if tagsJSON != "" {
|
if err := json.Unmarshal([]byte(tagsJSON), &tags); err != nil {
|
tags = nil
|
}
|
}
|
if entitiesJSON != "" {
|
if err := json.Unmarshal([]byte(entitiesJSON), &entities); err != nil {
|
entities = nil
|
}
|
}
|
|
return content, tags, entities, nil
|
}
|
