1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "os"
8
9 "github.com/mattn/go-sqlite3"
10)
11
12var LibPath = "ext/libsqlite3ext"
13
14type (
15 DB struct {
16 db *sql.DB
17 source string // for backup
18 }
19
20 Word struct {
21 Word string
22 Line string
23 }
24)
25
26func Open(filename string) (*DB, error) {
27 sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{
28 ConnectHook: func(conn *sqlite3.SQLiteConn) error {
29 return conn.LoadExtension(LibPath, "sqlite3_spellfix_init")
30 },
31 })
32
33 db, err := sql.Open("sqlite3_with_extensions", filename)
34 if err != nil {
35 return nil, err
36 }
37
38 return &DB{
39 db: db,
40 source: filename,
41 }, nil
42}
43
44func (d *DB) Migrate(ctx context.Context) error {
45 _, err := d.db.ExecContext(
46 ctx,
47 `CREATE VIRTUAL TABLE IF NOT EXISTS words USING fts5 (word, line);
48 CREATE VIRTUAL TABLE IF NOT EXISTS words_vocab USING fts5vocab('words', 'row');
49 CREATE VIRTUAL TABLE IF NOT EXISTS spell USING spellfix1;`,
50 )
51 return err
52}
53
54func (d *DB) SelectDict(ctx context.Context, query string, limit int) ([]*Word, error) {
55 rows, err := d.db.QueryContext(
56 ctx,
57 `SELECT
58 word, line
59 FROM words
60 WHERE words MATCH ?
61 ORDER BY rank
62 LIMIT ?;`,
63 query, limit,
64 )
65 if err != nil {
66 return nil, err
67 }
68
69 words := make([]*Word, 0)
70 for rows.Next() {
71 w := Word{}
72 err := rows.Scan(&w.Word, &w.Line)
73 if err != nil {
74 return nil, err
75 }
76 words = append(words, &w)
77 }
78
79 return words, err
80
81}
82
83func (d *DB) SelectSpell(ctx context.Context, query string) ([]string, error) {
84 rows, err := d.db.QueryContext(
85 ctx,
86 `SELECT
87 word
88 FROM spell
89 WHERE word MATCH ?;`,
90 query,
91 )
92 if err != nil {
93 return nil, err
94 }
95
96 words := make([]string, 0)
97 for rows.Next() {
98 w := ""
99 err := rows.Scan(&w)
100 if err != nil {
101 return nil, err
102 }
103 words = append(words, w)
104 }
105
106 return words, nil
107
108}
109
110func (d *DB) InsertLine(ctx context.Context, word, line string) error {
111 _, err := d.db.ExecContext(
112 ctx,
113 `INSERT INTO words (WORD, LINE) VALUES(?, ?);`,
114 word, line,
115 )
116 if err != nil {
117 return err
118 }
119 return err
120}
121
122func (d *DB) Consolidade(ctx context.Context) error {
123 _, err := d.db.ExecContext(
124 ctx,
125 `INSERT INTO spell(word) SELECT term FROM words_vocab`,
126 )
127 if err != nil {
128 return err
129 }
130 return err
131}
132
133func (d *DB) Backup(ctx context.Context, name string) error {
134 destDb, err := sql.Open("sqlite3_with_extensions", name)
135 if err != nil {
136 return err
137 }
138 defer destDb.Close()
139
140 return Copy(ctx, d.db, destDb)
141}
142
143func (d *DB) Restore(ctx context.Context, name string) error {
144 if _, err := os.Stat(name); err != nil {
145 return err
146 }
147
148 srcDb, err := sql.Open("sqlite3_with_extensions", name)
149 if err != nil {
150 return err
151 }
152 defer srcDb.Close()
153
154 return Copy(ctx, srcDb, d.db)
155}
156
157func Copy(ctx context.Context, srcDb *sql.DB, destDb *sql.DB) error {
158 destConn, err := destDb.Conn(ctx)
159 if err != nil {
160 return err
161 }
162 defer destConn.Close()
163
164 srcConn, err := srcDb.Conn(ctx)
165 if err != nil {
166 return err
167 }
168 defer srcConn.Close()
169
170 return destConn.Raw(func(destConn interface{}) error {
171 return srcConn.Raw(func(srcConn interface{}) error {
172 destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
173 if !ok {
174 return fmt.Errorf("can't convert destination connection to SQLiteConn")
175 }
176
177 srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
178 if !ok {
179 return fmt.Errorf("can't convert source connection to SQLiteConn")
180 }
181
182 b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
183 if err != nil {
184 return fmt.Errorf("error initializing SQLite backup: %w", err)
185 }
186
187 done, err := b.Step(-1)
188 if !done {
189 return fmt.Errorf("step of -1, but not done")
190 }
191 if err != nil {
192 return fmt.Errorf("error in stepping backup: %w", err)
193 }
194
195 err = b.Finish()
196 if err != nil {
197 return fmt.Errorf("error finishing backup: %w", err)
198 }
199
200 return err
201 })
202 })
203
204}