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 LIMIT ?;`,
61 query, limit,
62 )
63 if err != nil {
64 return nil, err
65 }
66
67 words := make([]*Word, 0)
68 for rows.Next() {
69 w := Word{}
70 err := rows.Scan(&w.Word, &w.Line)
71 if err != nil {
72 return nil, err
73 }
74 words = append(words, &w)
75 }
76
77 return words, err
78
79}
80
81func (d *DB) SelectSpell(ctx context.Context, query string) ([]string, error) {
82 rows, err := d.db.QueryContext(
83 ctx,
84 `SELECT
85 word
86 FROM spell
87 WHERE word MATCH ?;`,
88 query,
89 )
90 if err != nil {
91 return nil, err
92 }
93
94 words := make([]string, 0)
95 for rows.Next() {
96 w := ""
97 err := rows.Scan(&w)
98 if err != nil {
99 return nil, err
100 }
101 words = append(words, w)
102 }
103
104 return words, nil
105
106}
107
108func (d *DB) InsertLine(ctx context.Context, word, line string) error {
109 _, err := d.db.ExecContext(
110 ctx,
111 `INSERT INTO words (WORD, LINE) VALUES(?, ?);`,
112 word, line,
113 )
114 if err != nil {
115 return err
116 }
117 return err
118}
119
120func (d *DB) Consolidade(ctx context.Context) error {
121 _, err := d.db.ExecContext(
122 ctx,
123 `INSERT INTO spell(word) SELECT term FROM words_vocab`,
124 )
125 if err != nil {
126 return err
127 }
128 return err
129}
130
131func (d *DB) Backup(ctx context.Context, name string) error {
132 destDb, err := sql.Open("sqlite3_with_extensions", name)
133 if err != nil {
134 return err
135 }
136 defer destDb.Close()
137
138 return Copy(ctx, d.db, destDb)
139}
140
141func (d *DB) Restore(ctx context.Context, name string) error {
142 if _, err := os.Stat(name); err != nil {
143 return err
144 }
145
146 srcDb, err := sql.Open("sqlite3_with_extensions", name)
147 if err != nil {
148 return err
149 }
150 defer srcDb.Close()
151
152 return Copy(ctx, srcDb, d.db)
153}
154
155func Copy(ctx context.Context, srcDb *sql.DB, destDb *sql.DB) error {
156 destConn, err := destDb.Conn(ctx)
157 if err != nil {
158 return err
159 }
160 defer destConn.Close()
161
162 srcConn, err := srcDb.Conn(ctx)
163 if err != nil {
164 return err
165 }
166 defer srcConn.Close()
167
168 return destConn.Raw(func(destConn interface{}) error {
169 return srcConn.Raw(func(srcConn interface{}) error {
170 destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
171 if !ok {
172 return fmt.Errorf("can't convert destination connection to SQLiteConn")
173 }
174
175 srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
176 if !ok {
177 return fmt.Errorf("can't convert source connection to SQLiteConn")
178 }
179
180 b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
181 if err != nil {
182 return fmt.Errorf("error initializing SQLite backup: %w", err)
183 }
184
185 done, err := b.Step(-1)
186 if !done {
187 return fmt.Errorf("step of -1, but not done")
188 }
189 if err != nil {
190 return fmt.Errorf("error in stepping backup: %w", err)
191 }
192
193 err = b.Finish()
194 if err != nil {
195 return fmt.Errorf("error finishing backup: %w", err)
196 }
197
198 return err
199 })
200 })
201
202}