aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/db.go198
1 files changed, 198 insertions, 0 deletions
diff --git a/db/db.go b/db/db.go
new file mode 100644
index 0000000..746c30d
--- /dev/null
+++ b/db/db.go
@@ -0,0 +1,198 @@
+package db
+
+import (
+ "context"
+ "database/sql"
+ "fmt"
+
+ "github.com/mattn/go-sqlite3"
+)
+
+type (
+ DB struct {
+ db *sql.DB
+ source string // for backup
+ }
+
+ Word struct {
+ Word string
+ Line string
+ }
+)
+
+func Open(filename string) (*DB, error) {
+ sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{
+ ConnectHook: func(conn *sqlite3.SQLiteConn) error {
+ return conn.LoadExtension("ext/libsqlite3ext", "sqlite3_spellfix_init")
+ },
+ })
+
+ db, err := sql.Open("sqlite3_with_extensions", filename)
+ if err != nil {
+ return nil, err
+ }
+
+ return &DB{
+ db: db,
+ source: filename,
+ }, nil
+}
+
+func (d *DB) Migrate(ctx context.Context) error {
+ _, err := d.db.ExecContext(
+ ctx,
+ `CREATE VIRTUAL TABLE IF NOT EXISTS words USING fts5 (word, line);
+ CREATE VIRTUAL TABLE IF NOT EXISTS words_terms USING fts4aux(words);
+ CREATE VIRTUAL TABLE IF NOT EXISTS spell USING spellfix1;
+ `,
+ )
+ return err
+}
+
+func (d *DB) SelectDict(ctx context.Context, query string, limit int) ([]*Word, error) {
+ rows, err := d.db.QueryContext(
+ ctx,
+ `SELECT
+ word, line
+ FROM words
+ WHERE word MATCH ?
+ ORDER BY rank;`,
+ query, limit,
+ )
+ if err != nil {
+ return nil, err
+ }
+
+ words := make([]*Word, 0)
+ for rows.Next() {
+ w := Word{}
+ err := rows.Scan(&w.Word, &w.Line)
+ if err != nil {
+ return nil, err
+ }
+ words = append(words, &w)
+ }
+
+ return words, err
+
+}
+
+func (d *DB) SelectSpell(ctx context.Context, query string) ([]string, error) {
+ rows, err := d.db.QueryContext(
+ ctx,
+ `SELECT
+ word
+ FROM spell
+ WHERE word MATCH ?;`,
+ query,
+ )
+ if err != nil {
+ return nil, err
+ }
+
+ words := make([]string, 0)
+ for rows.Next() {
+ w := ""
+ err := rows.Scan(&w)
+ if err != nil {
+ return nil, err
+ }
+ words = append(words, w)
+ }
+
+ return words, err
+
+}
+
+func (d *DB) InsertLine(ctx context.Context, word, line string) error {
+ _, err := d.db.ExecContext(
+ ctx,
+ `INSERT INTO words (WORD, LINE) VALUES(?, ?);`,
+ word, line,
+ )
+ if err != nil {
+ return err
+ }
+ return err
+}
+
+func (d *DB) Consolidade(ctx context.Context) error {
+ _, err := d.db.ExecContext(
+ ctx,
+ `INSERT INTO spell(word,rank)
+ SELECT term, documents FROM words_terms WHERE col='*'`,
+ )
+ if err != nil {
+ return err
+ }
+ return err
+}
+
+func (d *DB) Backup(ctx context.Context, name string) error {
+ destDb, err := sql.Open("sqlite3_with_extensions", name)
+ if err != nil {
+ return err
+ }
+ defer destDb.Close()
+
+ return Copy(ctx, d.db, destDb)
+}
+
+func (d *DB) Restore(ctx context.Context, name string) error {
+ srcDb, err := sql.Open("sqlite3_with_extensions", name)
+ if err != nil {
+ return err
+ }
+ defer srcDb.Close()
+
+ return Copy(ctx, srcDb, d.db)
+}
+
+func Copy(ctx context.Context, srcDb *sql.DB, destDb *sql.DB) error {
+ destConn, err := destDb.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer destConn.Close()
+
+ srcConn, err := srcDb.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer srcConn.Close()
+
+ return destConn.Raw(func(destConn interface{}) error {
+ return srcConn.Raw(func(srcConn interface{}) error {
+ destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
+ if !ok {
+ return fmt.Errorf("can't convert destination connection to SQLiteConn")
+ }
+
+ srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
+ if !ok {
+ return fmt.Errorf("can't convert source connection to SQLiteConn")
+ }
+
+ b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
+ if err != nil {
+ return fmt.Errorf("error initializing SQLite backup: %w", err)
+ }
+
+ done, err := b.Step(-1)
+ if !done {
+ return fmt.Errorf("step of -1, but not done")
+ }
+ if err != nil {
+ return fmt.Errorf("error in stepping backup: %w", err)
+ }
+
+ err = b.Finish()
+ if err != nil {
+ return fmt.Errorf("error finishing backup: %w", err)
+ }
+
+ return err
+ })
+ })
+
+}