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