/
sqlite.go
117 lines (91 loc) · 2.48 KB
/
sqlite.go
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
114
115
116
117
package datastore
import (
"context"
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
"argc.in/archivo/internal/model"
)
func NewSQLiteStore(path string) (WebStore, error) {
dsn := fmt.Sprintf("file:%s?_journal=WAL", path)
conn, err := sql.Open("sqlite3", dsn)
if err != nil {
return nil, err
}
i := &sqliteImpl{conn: conn}
if err := i.initialize(); err != nil {
conn.Close()
return nil, err
}
return i, nil
}
type sqliteImpl struct {
conn *sql.DB
}
func (i *sqliteImpl) Close() error {
return i.conn.Close()
}
func (i *sqliteImpl) initialize() error {
query := `CREATE TABLE IF NOT EXISTS pages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT,
title TEXT
);`
if _, err := i.conn.ExecContext(context.Background(), query); err != nil {
return err
}
query = `CREATE VIRTUAL TABLE IF NOT EXISTS fts
USING fts5(pageid, content);`
if _, err := i.conn.ExecContext(context.Background(), query); err != nil {
return err
}
return nil
}
func (i *sqliteImpl) Save(ctx context.Context, p *model.Page) (err error) {
// Is it required to split them up?
query := `BEGIN TRANSACTION;
INSERT INTO pages (url, title) VALUES(?, ?);
INSERT INTO fts VALUES(last_insert_rowid(), ?);
COMMIT TRANSACTION;`
if _, err := i.conn.ExecContext(ctx, query, p.URL, p.Title, p.HTMLContent); err != nil {
return err
}
return nil
}
func (i *sqliteImpl) Get(ctx context.Context, p *model.Page) error {
query := `SELECT
pages.url,
pages.title,
fts.content,
FROM pages
JOIN fts ON pages.id = fts.pageid
WHERE pages.id = ?`
if err := i.conn.QueryRowContext(ctx, query, p.ID).Scan(&p.URL, &p.Title, &p.HTMLContent); err != nil {
return err
}
return nil
}
func (i *sqliteImpl) Search(ctx context.Context, search string) ([]model.Page, error) {
query := `SELECT
pages.id,
pages.url,
pages.title,
snippet(fts, -1, "[ ", " ]", "...", 10)
FROM pages
JOIN fts ON pages.id = fts.pageid
WHERE fts MATCH ?;`
rows, err := i.conn.QueryContext(ctx, query, search)
if err != nil {
return nil, err
}
defer rows.Close()
var pages []model.Page
for rows.Next() {
var p model.Page
if err := rows.Scan(&p.ID, &p.URL, &p.Title, &p.HTMLContent); err != nil {
return nil, err
}
pages = append(pages, p)
}
return pages, nil
}