package db import ( "context" "database/sql" "fmt" "os" "github.com/mattn/go-sqlite3" ) var LibPath = "ext/libsqlite3ext" 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(LibPath, "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_vocab USING fts5vocab('words', 'row'); 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 words MATCH ? ORDER BY rank LIMIT ?;`, 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, nil } 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) SELECT term FROM words_vocab`, ) 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 { if _, err := os.Stat(name); err != nil { return err } 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 }) }) }