-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.go
170 lines (139 loc) · 3.88 KB
/
database.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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
package main
import (
"database/sql"
"errors"
"fmt"
)
var (
db *sql.DB
)
type Note struct {
id int
title string
body string
created string
modified string
}
type SearchResult struct {
id int
title string
snippet string
}
func CreateDB(dbPath string) error {
OpenDB(dbPath)
_, err := db.Exec("CREATE VIRTUAL TABLE IF NOT EXISTS Notes USING fts5(title, body, created, modified, tokenize=trigram)")
if err != nil {
return err
}
return nil
}
func OpenDB(dbPath string) error {
var err error
db, err = sql.Open("sqlite", dbPath)
if err != nil {
return err
}
return nil
}
func FindAllNotes() ([]Note, error) {
LogOnVerbose("Finding all notes...")
results, err := db.Query("select rowid, title, created, modified from Notes")
if err != nil {
return nil, err
}
notes := make([]Note, 0, 10)
for results.Next() {
var rowid int
var title, created, modified string
if err = results.Scan(&rowid, &title, &created, &modified); err != nil {
return nil, err
}
notes = append(notes, Note{id: rowid, title: title, created: created, modified: modified})
}
return notes, nil
}
func FindNoteById(id int) (*Note, error) {
LogOnVerbose(fmt.Sprint("Finding note with id", id, "..."))
query := fmt.Sprintf("SELECT title, body FROM Notes WHERE rowid = %v", id)
result, err := db.Query(query)
if err != nil {
return nil, err
}
var title string
var body string
if succ := result.Next(); !succ {
return nil, errors.New("could not find note by Id")
}
defer result.Close()
if err = result.Scan(&title, &body); err != nil {
return nil, err
}
return &Note{id: id, title: title, body: body}, nil
}
func FindNotesBySubstring(substring string) ([]Note, error) {
LogOnVerbose(fmt.Sprint("Finding notes with substring ", substring, "..."))
results, err := db.Query("SELECT rowid, title, body, created, modified FROM Notes WHERE title MATCH ?", substring)
if err != nil {
return nil, err
}
notes := make([]Note, 0, 10)
for results.Next() {
var rowid int
var title, body, created, modified string
if err = results.Scan(&rowid, &title, &body, &created, &modified); err != nil {
return nil, err
}
notes = append(notes, Note{id: rowid, title: title, body: body, created: created, modified: modified})
}
return notes, nil
}
func InsertNote(note Note) error {
LogOnVerbose(fmt.Sprintf("Inserting note '%v'...", note.title))
_, err := db.Exec("INSERT INTO Notes VALUES (?, ?, ?, ?)", note.title, note.body, note.created, note.modified)
return err
}
func UpdateNote(note *Note) error {
_, err := db.Exec("UPDATE Notes SET title = ?, body = ?, modified = ? WHERE rowid = ?", note.title, note.body, note.modified, note.id)
return err
}
func DeleteNoteByTitle(title string) (int, error) {
LogOnVerbose(fmt.Sprint("Deleting note titled", title, "..."))
res, err := db.Exec("DELETE FROM Notes WHERE title MATCH ?", title)
if err != nil {
return -1, err
}
rows, err := res.RowsAffected()
if err != nil {
return -1, err
}
return int(rows), nil
}
func DeleteNoteById(id int) error {
LogOnVerbose(fmt.Sprint("Deleting note with id", id, "..."))
res, err := db.Exec("DELETE FROM Notes WHERE rowid = ?", id)
if err != nil {
return err
}
n, err := res.RowsAffected()
if err == nil && n == 0 {
return errors.New("id not found")
}
return err
}
func FindMatchesOnAllNotes(term string, matchStart string, matchEnd string, trailing string) ([]SearchResult, error) {
results, err := db.Query("SELECT rowid, title, snippet(Notes, 1, ?, ?, ?, 200) FROM Notes WHERE body MATCH ?", matchStart, matchEnd, trailing, term)
if err != nil {
return nil, err
}
matches := make([]SearchResult, 0, 10)
for results.Next() {
var rowid int
var title string
var snippet string
if err = results.Scan(&rowid, &title, &snippet); err != nil {
return nil, err
}
matches = append(matches, SearchResult{id: rowid, title: title, snippet: snippet})
}
return matches, nil
}