1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
package db
import (
"database/sql"
_ "nebbet.no/internal/sqlitedrv"
)
type SearchDB struct {
db *sql.DB
}
type SearchPage struct {
Path string
Title string
Content string
}
type SearchResult struct {
Path string
Title string
Snippet string
}
func OpenSearch(path string) (*SearchDB, error) {
db, err := sql.Open("sqlite", path)
if err != nil {
return nil, err
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS indexed_pages (
path TEXT NOT NULL PRIMARY KEY,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
path UNINDEXED,
title,
content,
tokenize = 'porter unicode61'
);
`)
if err != nil {
return nil, err
}
return &SearchDB{db: db}, nil
}
func (s *SearchDB) Close() error { return s.db.Close() }
func (s *SearchDB) IndexPage(p SearchPage) error {
tx, err := s.db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
if _, err = tx.Exec(`DELETE FROM pages_fts WHERE path = ?`, p.Path); err != nil {
return err
}
if _, err = tx.Exec(
`INSERT INTO pages_fts (path, title, content) VALUES (?, ?, ?)`,
p.Path, p.Title, p.Content,
); err != nil {
return err
}
if _, err = tx.Exec(`
INSERT INTO indexed_pages (path, updated_at) VALUES (?, CURRENT_TIMESTAMP)
ON CONFLICT(path) DO UPDATE SET updated_at = CURRENT_TIMESTAMP
`, p.Path); err != nil {
return err
}
return tx.Commit()
}
func (s *SearchDB) DeletePage(path string) error {
tx, err := s.db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
if _, err = tx.Exec(`DELETE FROM pages_fts WHERE path = ?`, path); err != nil {
return err
}
if _, err = tx.Exec(`DELETE FROM indexed_pages WHERE path = ?`, path); err != nil {
return err
}
return tx.Commit()
}
// Search runs a full-text query and returns up to 20 results with snippets.
func (s *SearchDB) Search(query string) ([]SearchResult, error) {
rows, err := s.db.Query(`
SELECT path, title,
snippet(pages_fts, 2, '<mark>', '</mark>', '...', 20)
FROM pages_fts
WHERE pages_fts MATCH ?
ORDER BY rank
LIMIT 20
`, query)
if err != nil {
return nil, err
}
defer rows.Close()
var results []SearchResult
for rows.Next() {
var r SearchResult
if err := rows.Scan(&r.Path, &r.Title, &r.Snippet); err != nil {
return nil, err
}
results = append(results, r)
}
return results, rows.Err()
}
|
