From 760155f8fdcfc98e9f3b793c6b5f9b6ac105dd1e Mon Sep 17 00:00:00 2001
From: ax_rd <ax_rd@aisim.cn>
Date: Thu, 03 Sep 2026 08:52:12 +0800
Subject: [PATCH] feat: schema v2 迁移（aliases/status/文件指纹/provenance/unresolved_links + FTS external-content）+ Makefile 固化 FTS5 构建标志

---
 internal/index/migrations_test.go |  131 +++++++++++++++++++++
 Makefile                          |   14 +
 internal/index/fts.go             |   47 +++++--
 cmd/review.go                     |    2 
 internal/index/migrations.go      |  125 ++++++++++++++++++++
 internal/index/sqlite.go          |    9 +
 6 files changed, 307 insertions(+), 21 deletions(-)

diff --git a/Makefile b/Makefile
index c772d23..f0daa47 100644
--- a/Makefile
+++ b/Makefile
@@ -1,20 +1,24 @@
-.PHONY: build test clean install
+# Makefile
+CGO_CFLAGS := -DSQLITE_ENABLE_FTS5
+CGO_LDFLAGS := -lm
+
+.PHONY: build test clean install test-v run-search run-index run-status
 
 # 构建
 build:
-	CGO_ENABLED=1 go build -tags fts5 -o bin/kb-cli .
+	CGO_CFLAGS="$(CGO_CFLAGS)" CGO_LDFLAGS="$(CGO_LDFLAGS)" go build -o bin/kb-cli .
 
 # 安装到 ~/go/bin
 install:
-	CGO_ENABLED=1 go install -tags fts5 .
+	CGO_CFLAGS="$(CGO_CFLAGS)" CGO_LDFLAGS="$(CGO_LDFLAGS)" go install .
 
 # 测试
 test:
-	CGO_ENABLED=1 go test -tags fts5 ./...
+	CGO_CFLAGS="$(CGO_CFLAGS)" CGO_LDFLAGS="$(CGO_LDFLAGS)" go test ./... 2>&1 | grep -v "no test files"
 
 # 详细测试
 test-v:
-	CGO_ENABLED=1 go test -tags fts5 -v ./...
+	CGO_CFLAGS="$(CGO_CFLAGS)" CGO_LDFLAGS="$(CGO_LDFLAGS)" go test -v ./... 2>&1 | grep -v "no test files"
 
 # 清理
 clean:
diff --git a/cmd/review.go b/cmd/review.go
index a84025b..bdc0561 100644
--- a/cmd/review.go
+++ b/cmd/review.go
@@ -74,7 +74,7 @@
 		if err == nil {
 			content := extractMainContent(string(draftContent))
 			if content != "" {
-				fmt.Println("### 草稿主要内容\n")
+				fmt.Println("### 草稿主要内容")
 				fmt.Println(content)
 			}
 		}
diff --git a/internal/index/fts.go b/internal/index/fts.go
index 07bc115..4323985 100644
--- a/internal/index/fts.go
+++ b/internal/index/fts.go
@@ -14,25 +14,46 @@
 	Rank    float64
 }
 
-// CreateFTS 创建 FTS5 虚拟表
+// 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,
-			tags,
-			entities
-		)
-	`)
-	return err
+			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 索引
+// PopulateFTS 全量重灌 FTS（external-content 模式专用语法）
 func (s *Store) PopulateFTS() error {
-	_, err := s.db.Exec(`
-		INSERT INTO nodes_fts(rowid, title, content, tags, entities)
-		SELECT id, title, content_fts, tags, entities FROM nodes
-	`)
+	_, err := s.db.Exec(`INSERT INTO nodes_fts(nodes_fts) VALUES('rebuild')`)
 	return err
 }
 
diff --git a/internal/index/migrations.go b/internal/index/migrations.go
new file mode 100644
index 0000000..25955b0
--- /dev/null
+++ b/internal/index/migrations.go
@@ -0,0 +1,125 @@
+package index
+
+import (
+	"fmt"
+	"os"
+)
+
+const currentSchemaVersion = 2
+
+// migrate 幂等的 schema 升级：v1 → v2。
+// v2 变更：nodes 加 aliases/status/size/mtime/content_hash 列；
+// edges 加 provenance 列；新建 unresolved_links、schema_versions 表；
+// nodes_fts 改 external-content + 触发器。迁移前自动备份 .db 文件。
+func (s *Store) migrate() error {
+	// 读当前版本
+	var version int
+	err := s.db.QueryRow(`SELECT value FROM schema_versions WHERE key='version'`).Scan(&version)
+	if err != nil && version == 0 {
+		// 首次：schema_versions 表可能不存在
+		version = 1
+	}
+	if version >= currentSchemaVersion {
+		return nil
+	}
+
+	// 备份
+	if err := s.backupDB(); err != nil {
+		return fmt.Errorf("备份失败: %w", err)
+	}
+
+	// v1 → v2
+	if err := s.migrateV1toV2(); err != nil {
+		return fmt.Errorf("v1→v2 迁移失败: %w", err)
+	}
+	return nil
+}
+
+// backupDB 迁移前备份 .db 文件（已有备份则跳过）
+func (s *Store) backupDB() error {
+	if s.dbPath == "" {
+		return nil // 无路径信息时跳过备份（测试场景）
+	}
+	dst := s.dbPath + ".bak"
+	if _, err := os.Stat(dst); err == nil {
+		return nil // 已有备份
+	}
+	data, err := os.ReadFile(s.dbPath)
+	if err != nil {
+		return err
+	}
+	return os.WriteFile(dst, data, 0644)
+}
+
+// migrateV1toV2 执行 v1 → v2 的全部变更
+func (s *Store) migrateV1toV2() error {
+	// 1. schema_versions 表
+	if _, err := s.db.Exec(`CREATE TABLE IF NOT EXISTS schema_versions (
+		key TEXT PRIMARY KEY, value TEXT NOT NULL)`); err != nil {
+		return err
+	}
+	if _, err := s.db.Exec(`INSERT OR REPLACE INTO schema_versions VALUES ('version', '2')`); err != nil {
+		return err
+	}
+
+	// 2. nodes 加列（逐列检查，幂等）
+	for _, col := range []struct{ name, def string }{
+		{"aliases", "ALTER TABLE nodes ADD COLUMN aliases TEXT"},
+		{"status", "ALTER TABLE nodes ADD COLUMN status TEXT"},
+		{"size", "ALTER TABLE nodes ADD COLUMN size INTEGER"},
+		{"mtime", "ALTER TABLE nodes ADD COLUMN mtime INTEGER"},
+		{"content_hash", "ALTER TABLE nodes ADD COLUMN content_hash TEXT"},
+	} {
+		if !s.nodeColumnExists(col.name) {
+			if _, err := s.db.Exec(col.def); err != nil {
+				return fmt.Errorf("加列 %s 失败: %w", col.name, err)
+			}
+		}
+	}
+
+	// 3. edges 加 provenance 列
+	if !s.edgeColumnExists("provenance") {
+		if _, err := s.db.Exec(`ALTER TABLE edges ADD COLUMN provenance TEXT`); err != nil {
+			return fmt.Errorf("edges 加 provenance 失败: %w", err)
+		}
+	}
+
+	// 4. unresolved_links 表
+	if _, err := s.db.Exec(`CREATE TABLE IF NOT EXISTS unresolved_links (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		from_node INTEGER NOT NULL,
+		link_text TEXT NOT NULL,
+		name_tail TEXT NOT NULL,
+		status TEXT NOT NULL DEFAULT 'pending',
+		created_at TEXT DEFAULT (datetime('now')),
+		last_attempt TEXT)`); err != nil {
+		return err
+	}
+	if _, err := s.db.Exec(`CREATE INDEX IF NOT EXISTS idx_unresolved_tail
+		ON unresolved_links(name_tail) WHERE status='failed'`); err != nil {
+		return err
+	}
+
+	// 5. FTS 改 external-content：删旧表重建 + 触发器 + 重灌
+	if _, err := s.db.Exec(`DROP TABLE IF EXISTS nodes_fts`); err != nil {
+		return err
+	}
+	if err := s.CreateFTS(); err != nil {
+		return err
+	}
+	return s.PopulateFTS()
+}
+
+// nodeColumnExists 检查 nodes 表是否已有指定列
+func (s *Store) nodeColumnExists(name string) bool {
+	var n int
+	s.db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('nodes') WHERE name=?`, name).Scan(&n)
+	return n > 0
+}
+
+// edgeColumnExists 检查 edges 表是否已有指定列
+func (s *Store) edgeColumnExists(name string) bool {
+	var n int
+	s.db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('edges') WHERE name=?`, name).Scan(&n)
+	return n > 0
+}
diff --git a/internal/index/migrations_test.go b/internal/index/migrations_test.go
new file mode 100644
index 0000000..2dd4328
--- /dev/null
+++ b/internal/index/migrations_test.go
@@ -0,0 +1,131 @@
+package index
+
+import (
+	"database/sql"
+	"os"
+	"path/filepath"
+	"testing"
+)
+
+// newV1Store 手工构建一个 v1 schema 的老库（模拟迁移前状态）
+func newV1Store(t *testing.T) (*Store, string) {
+	t.Helper()
+	dbPath := filepath.Join(t.TempDir(), "kb-v1.db")
+	db, err := sql.Open("sqlite3", dbPath+"?_journal_mode=WAL")
+	if err != nil {
+		t.Fatal(err)
+	}
+	schema := `
+	CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
+	CREATE TABLE nodes (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		path TEXT NOT NULL UNIQUE,
+		title TEXT, section TEXT, tags TEXT, entities TEXT, wikilinks TEXT,
+		content_fts TEXT,
+		created_at TEXT DEFAULT (datetime('now')),
+		updated_at TEXT DEFAULT (datetime('now')));
+	CREATE TABLE edges (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		from_node INTEGER NOT NULL, to_node INTEGER NOT NULL,
+		relation TEXT NOT NULL, label TEXT,
+		UNIQUE(from_node, to_node, relation, label));
+	`
+	if _, err := db.Exec(schema); err != nil {
+		t.Fatal(err)
+	}
+	if _, err := db.Exec(`INSERT INTO nodes (path, title, tags, entities, wikilinks, content_fts)
+		VALUES ('FAQ/001-测试.md', '测试', '["a"]', '[]', '[]', '内容')`); err != nil {
+		t.Fatal(err)
+	}
+	return &Store{db: db, dbPath: dbPath}, dbPath
+}
+
+func TestMigrateV1ToV2(t *testing.T) {
+	store, dbPath := newV1Store(t)
+	defer store.Close()
+
+	// 迁移前备份文件必须存在
+	if err := store.migrate(); err != nil {
+		t.Fatalf("migrate: %v", err)
+	}
+	if _, err := os.Stat(dbPath + ".bak"); err != nil {
+		t.Fatalf("备份文件不存在: %v", err)
+	}
+
+	// v2 列必须存在
+	cols := store.nodeColumns(t)
+	for _, want := range []string{"aliases", "status", "size", "mtime", "content_hash"} {
+		if !cols[want] {
+			t.Errorf("nodes 缺列 %s", want)
+		}
+	}
+	if !store.edgeHasProvenance(t) {
+		t.Error("edges 缺 provenance 列")
+	}
+	if !store.tableExists(t, "unresolved_links") {
+		t.Error("缺 unresolved_links 表")
+	}
+
+	// 数据保留
+	var title string
+	if err := store.db.QueryRow(`SELECT title FROM nodes WHERE path='FAQ/001-测试.md'`).Scan(&title); err != nil {
+		t.Fatal(err)
+	}
+	if title != "测试" {
+		t.Errorf("迁移丢数据: %s", title)
+	}
+}
+
+func TestMigrateIdempotent(t *testing.T) {
+	store, _ := newV1Store(t)
+	defer store.Close()
+	if err := store.migrate(); err != nil {
+		t.Fatalf("第一次: %v", err)
+	}
+	if err := store.migrate(); err != nil {
+		t.Fatalf("第二次(幂等): %v", err)
+	}
+}
+
+func TestNewDBIsV2(t *testing.T) {
+	dbPath := filepath.Join(t.TempDir(), "kb-v2.db")
+	store, err := Open(dbPath)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer store.Close()
+	if !store.edgeHasProvenance(t) {
+		t.Error("新建库应为 v2 schema")
+	}
+}
+
+// 测试辅助：列检查
+func (s *Store) nodeColumns(t *testing.T) map[string]bool {
+	t.Helper()
+	rows, err := s.db.Query(`SELECT name FROM pragma_table_info('nodes')`)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer rows.Close()
+	m := map[string]bool{}
+	for rows.Next() {
+		var name string
+		rows.Scan(&name)
+		m[name] = true
+	}
+	return m
+}
+
+func (s *Store) edgeHasProvenance(t *testing.T) bool {
+	t.Helper()
+	var n int
+	s.db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('edges') WHERE name='provenance'`).Scan(&n)
+	return n > 0
+}
+
+func (s *Store) tableExists(t *testing.T, name string) bool {
+	t.Helper()
+	var n int
+	s.db.QueryRow(`SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?`, name).Scan(&n)
+	return n > 0
+}
diff --git a/internal/index/sqlite.go b/internal/index/sqlite.go
index 1501020..a70b7d1 100644
--- a/internal/index/sqlite.go
+++ b/internal/index/sqlite.go
@@ -15,7 +15,8 @@
 
 // Store SQLite 存储层
 type Store struct {
-	db *sql.DB
+	db     *sql.DB
+	dbPath string
 }
 
 // Open 打开或创建数据库
@@ -31,11 +32,15 @@
 		return nil, fmt.Errorf("打开数据库失败: %w", err)
 	}
 
-	s := &Store{db: db}
+	s := &Store{db: db, dbPath: dbPath}
 	if err := s.initTables(); err != nil {
 		db.Close()
 		return nil, err
 	}
+	if err := s.migrate(); err != nil {
+		db.Close()
+		return nil, err
+	}
 	return s, nil
 }
 

--
Gitblit v1.10.0