1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7
8 "github.com/mattn/go-sqlite3"
9)
10
11type (
12 DB struct {
13 db *sql.DB
14 source string // for backup
15 }
16
17 Word struct {
18 Word string
19 Line string
20 }
21)
22
23func Open(filename string) (*DB, error) {
24 sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{
25 ConnectHook: func(conn *sqlite3.SQLiteConn) error {
26 return conn.LoadExtension("ext/libsqlite3ext", "sqlite3_spellfix_init")
27 },
28 })
29
30 db, err := sql.Open("sqlite3_with_extensions", filename)
31 if err != nil {
32 return nil, err
33 }
34
35 return &DB{
36 db: db,
37 source: filename,
38 }, nil
39}
40
41func (d *DB) Migrate(ctx context.Context) error {
42 _, err := d.db.ExecContext(
43 ctx,
44 `CREATE VIRTUAL TABLE IF NOT EXISTS words USING fts5 (word, line);
45 CREATE VIRTUAL TABLE IF NOT EXISTS words_terms USING fts4aux(words);
46 CREATE VIRTUAL TABLE IF NOT EXISTS spell USING spellfix1;
47 `,
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, err
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,rank)
123 SELECT term, documents FROM words_terms WHERE col='*'`,
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 srcDb, err := sql.Open("sqlite3_with_extensions", name)
143 if err != nil {
144 return err
145 }
146 defer srcDb.Close()
147
148 return Copy(ctx, srcDb, d.db)
149}
150
151func Copy(ctx context.Context, srcDb *sql.DB, destDb *sql.DB) error {
152 destConn, err := destDb.Conn(ctx)
153 if err != nil {
154 return err
155 }
156 defer destConn.Close()
157
158 srcConn, err := srcDb.Conn(ctx)
159 if err != nil {
160 return err
161 }
162 defer srcConn.Close()
163
164 return destConn.Raw(func(destConn interface{}) error {
165 return srcConn.Raw(func(srcConn interface{}) error {
166 destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
167 if !ok {
168 return fmt.Errorf("can't convert destination connection to SQLiteConn")
169 }
170
171 srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
172 if !ok {
173 return fmt.Errorf("can't convert source connection to SQLiteConn")
174 }
175
176 b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
177 if err != nil {
178 return fmt.Errorf("error initializing SQLite backup: %w", err)
179 }
180
181 done, err := b.Step(-1)
182 if !done {
183 return fmt.Errorf("step of -1, but not done")
184 }
185 if err != nil {
186 return fmt.Errorf("error in stepping backup: %w", err)
187 }
188
189 err = b.Finish()
190 if err != nil {
191 return fmt.Errorf("error finishing backup: %w", err)
192 }
193
194 return err
195 })
196 })
197
198}