From 57c782546739fde08138b00e2d0b3ba5f18fb676 Mon Sep 17 00:00:00 2001
From: "Gabriel A. Giovanini" <mail@gabrielgio.me>
Date: Fri, 19 Apr 2024 18:22:50 +0200
Subject: ref: Better organize the files

---
 db/db.go | 198 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 198 insertions(+)
 create mode 100644 db/db.go

(limited to 'db/db.go')

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
+		})
+	})
+
+}
-- 
cgit v1.2.3