st-pierre/sqlite.go

package main

import (
	"context"
	"database/sql"
	"embed"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"strings"

	"github.com/pressly/goose/v3"
	_ "modernc.org/sqlite"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS

type sqliteStore struct {
	db *sql.DB
}

func newSqliteStore(path string) (*sqliteStore, error) {
	dir := path
	if idx := strings.LastIndexByte(path, '/'); idx > 0 {
		dir = path[:idx]
		if fi, err := os.Stat(dir); err != nil {
			return nil, fmt.Errorf("sqlite dir %s: %w", dir, err)
		} else if fi.Mode().Perm()&0200 == 0 {
			return nil, fmt.Errorf("sqlite dir %s is not writable (mode %o)", dir, fi.Mode().Perm())
		}
	}

	db, err := sql.Open("sqlite", "file:"+path+"?_pragma=foreign_keys(1)")
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}

	db.SetMaxOpenConns(1)

	if _, err := db.Exec("SELECT 1"); err != nil {
		db.Close()
		return nil, fmt.Errorf("sqlite ping: %w", err)
	}

	if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
		db.Close()
		return nil, fmt.Errorf("enable foreign keys: %w", err)
	}

	goose.SetBaseFS(embedMigrations)

	if err := goose.SetDialect("sqlite3"); err != nil {
		db.Close()
		return nil, fmt.Errorf("set dialect: %w", err)
	}

	if err := goose.Up(db, "migrations"); err != nil {
		db.Close()
		return nil, fmt.Errorf("migrate: %w", err)
	}

	return &sqliteStore{db: db}, nil
}

func (s *sqliteStore) save(ctx context.Context, note, host, user, context_ string, repos, tags []string, publicID int64, version int) (int64, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	result, err := tx.ExecContext(ctx,
		`INSERT INTO flashcard (public_id, version, note, repos, host, "user", context)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		publicID, version, note, marshalStringSlice(repos), host, user, context_,
	)
	if err != nil {
		return 0, fmt.Errorf("insert: %w", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("last insert id: %w", err)
	}

	if publicID == 0 {
		if _, err := tx.ExecContext(ctx, `UPDATE flashcard SET public_id = ? WHERE _id = ?`, id, id); err != nil {
			return 0, fmt.Errorf("set public_id: %w", err)
		}
	}

	if err := insertTags(ctx, tx, id, tags); err != nil {
		return 0, err
	}

	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("commit: %w", err)
	}

	return id, nil
}

func insertTags(ctx context.Context, tx *sql.Tx, flashcardID int64, tags []string) error {
	for _, raw := range tags {
		name := strings.TrimSpace(raw)
		if name == "" {
			continue
		}

		if _, err := tx.ExecContext(ctx,
			`INSERT INTO tag(name) VALUES (?) ON CONFLICT(name) DO NOTHING`, name,
		); err != nil {
			return fmt.Errorf("upsert tag %q: %w", name, err)
		}

		var tagID int64
		if err := tx.QueryRowContext(ctx, `SELECT id FROM tag WHERE name = ?`, name).Scan(&tagID); err != nil {
			return fmt.Errorf("read tag %q: %w", name, err)
		}

		if _, err := tx.ExecContext(ctx,
			`INSERT OR IGNORE INTO flashcard_tag(flashcard_id, tag_id) VALUES (?, ?)`, flashcardID, tagID,
		); err != nil {
			return fmt.Errorf("link tag %q: %w", name, err)
		}
	}
	return nil
}

const tagsJSONSelect = `(SELECT json_group_array(t.name ORDER BY t.name) FROM flashcard_tag ft JOIN tag t ON t.id = ft.tag_id WHERE ft.flashcard_id = f._id)`

const selectFlashcard = `SELECT f._id, f.public_id, f.version, f.note, f.repos, f.host, ` + tagsJSONSelect + `,
	f."user", f.context, f.delete_timestamp, f.created
	FROM flashcard f`

func (s *sqliteStore) getByID(ctx context.Context, id int64) (*entry, error) {
	row := s.db.QueryRowContext(ctx, selectFlashcard+` WHERE f._id = ?`, id)

	var e entry
	var reposJSON, tagsJSON sql.NullString
	var host, user, context_, deleteTimestamp sql.NullString

	err := row.Scan(&e.ID, &e.PublicID, &e.Version, &e.Note, &reposJSON, &host, &tagsJSON, &user, &context_, &deleteTimestamp, &e.Created)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("query: %w", err)
	}

	if reposJSON.Valid {
		e.Repos = unmarshalStringSlice(reposJSON.String)
	}
	if tagsJSON.Valid {
		e.Tags = unmarshalStringSlice(tagsJSON.String)
	}
	if host.Valid {
		e.Host = host.String
	}
	if user.Valid {
		e.User = user.String
	}
	if context_.Valid {
		e.Context = context_.String
	}
	if deleteTimestamp.Valid {
		e.DeleteTimestamp = deleteTimestamp.String
	}

	return &e, nil
}

func (s *sqliteStore) getLatestByPublicID(ctx context.Context, publicID int64) (*entry, error) {
	row := s.db.QueryRowContext(ctx, selectFlashcard+` WHERE f.public_id = ? ORDER BY f.version DESC LIMIT 1`, publicID)

	var e entry
	var reposJSON, tagsJSON sql.NullString
	var host, user, context_, deleteTimestamp sql.NullString

	err := row.Scan(&e.ID, &e.PublicID, &e.Version, &e.Note, &reposJSON, &host, &tagsJSON, &user, &context_, &deleteTimestamp, &e.Created)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("query: %w", err)
	}

	if reposJSON.Valid {
		e.Repos = unmarshalStringSlice(reposJSON.String)
	}
	if tagsJSON.Valid {
		e.Tags = unmarshalStringSlice(tagsJSON.String)
	}
	if host.Valid {
		e.Host = host.String
	}
	if user.Valid {
		e.User = user.String
	}
	if context_.Valid {
		e.Context = context_.String
	}
	if deleteTimestamp.Valid {
		e.DeleteTimestamp = deleteTimestamp.String
	}

	return &e, nil
}

func (s *sqliteStore) listAll(ctx context.Context) ([]flashcardSummary, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT f.public_id, f.version, f.note, f.host, f.user, `+tagsJSONSelect+`, f.repos, f.created
		 FROM flashcard f
		 WHERE f.version = (SELECT MAX(f2.version) FROM flashcard f2 WHERE f2.public_id = f.public_id)
		 AND f.delete_timestamp IS NULL
		 AND f.public_id != 0
		 ORDER BY f.created DESC`,
	)
	if err != nil {
		return nil, fmt.Errorf("list: %w", err)
	}
	defer rows.Close()

	var summaries []flashcardSummary
	for rows.Next() {
		var s flashcardSummary
		var tagsJSON, reposJSON *string
		if err := rows.Scan(&s.PublicID, &s.Version, &s.Note, &s.Host, &s.User, &tagsJSON, &reposJSON, &s.Created); err != nil {
			return nil, fmt.Errorf("scan: %w", err)
		}
		if tagsJSON != nil {
			json.Unmarshal([]byte(*tagsJSON), &s.Tags)
		}
		if reposJSON != nil {
			json.Unmarshal([]byte(*reposJSON), &s.Repos)
		}
		s.URL = fmt.Sprintf("/api/v1/flashcards/%d", s.PublicID)
		summaries = append(summaries, s)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("rows: %w", err)
	}
	return summaries, nil
}

func (s *sqliteStore) getByPublicIDAndVersion(ctx context.Context, publicID int64, version int) (*entry, error) {
	row := s.db.QueryRowContext(ctx, selectFlashcard+` WHERE f.public_id = ? AND f.version = ?`, publicID, version)

	var e entry
	var reposJSON, tagsJSON sql.NullString
	var host, user, context_, deleteTimestamp sql.NullString

	err := row.Scan(&e.ID, &e.PublicID, &e.Version, &e.Note, &reposJSON, &host, &tagsJSON, &user, &context_, &deleteTimestamp, &e.Created)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("query: %w", err)
	}

	if reposJSON.Valid {
		e.Repos = unmarshalStringSlice(reposJSON.String)
	}
	if tagsJSON.Valid {
		e.Tags = unmarshalStringSlice(tagsJSON.String)
	}
	if host.Valid {
		e.Host = host.String
	}
	if user.Valid {
		e.User = user.String
	}
	if context_.Valid {
		e.Context = context_.String
	}
	if deleteTimestamp.Valid {
		e.DeleteTimestamp = deleteTimestamp.String
	}

	return &e, nil
}

func (s *sqliteStore) saveVote(ctx context.Context, flashcardID int64, value int, user, repo, message string) (*vote, error) {
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO vote (flashcard_id, value, "user", repo, message)
		 VALUES (?, ?, ?, ?, ?)
		 ON CONFLICT(flashcard_id, "user", repo)
		 DO UPDATE SET value = excluded.value, message = excluded.message`,
		flashcardID, value, user, repo, message,
	)
	if err != nil {
		return nil, fmt.Errorf("upsert vote: %w", err)
	}

	row := s.db.QueryRowContext(ctx,
		`SELECT _id, flashcard_id, value, "user", repo, message, created
		 FROM vote WHERE flashcard_id = ? AND "user" = ? AND repo = ?`,
		flashcardID, user, repo,
	)

	var v vote
	err = row.Scan(&v.ID, &v.FlashcardID, &v.Value, &v.User, &v.Repo, &v.Message, &v.Created)
	if err != nil {
		return nil, fmt.Errorf("read vote: %w", err)
	}

	return &v, nil
}

func (s *sqliteStore) tombstone(ctx context.Context, publicID int64, version int) (int64, error) {
	result, err := s.db.ExecContext(ctx,
		`INSERT INTO flashcard (public_id, version, note, delete_timestamp)
		 VALUES (?, ?, '', datetime('now'))`,
		publicID, version,
	)
	if err != nil {
		return 0, fmt.Errorf("tombstone: %w", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("last insert id: %w", err)
	}

	return id, nil
}

func (s *sqliteStore) indexFTS(ctx context.Context, id int64, note string) error {
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO flashcard_fts(rowid, note) VALUES (?, ?)`,
		id, note,
	)
	if err != nil {
		return fmt.Errorf("index fts: %w", err)
	}
	return nil
}

func (s *sqliteStore) deleteFTS(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx,
		`DELETE FROM flashcard_fts WHERE rowid = ?`,
		id,
	)
	if err != nil {
		return fmt.Errorf("delete fts: %w", err)
	}
	return nil
}

func (s *sqliteStore) searchFTS(ctx context.Context, query string, limit uint64) ([]searchResult, error) {
	if limit == 0 {
		limit = 5
	}

	rows, err := s.db.QueryContext(ctx,
		`SELECT rowid, rank FROM flashcard_fts WHERE note MATCH ? ORDER BY rank LIMIT ?`,
		query, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("fts query: %w", err)
	}

	type hit struct {
		id   int64
		rank float64
	}
	var hits []hit
	for rows.Next() {
		var h hit
		if err := rows.Scan(&h.id, &h.rank); err != nil {
			rows.Close()
			return nil, fmt.Errorf("scan: %w", err)
		}
		hits = append(hits, h)
	}
	rows.Close()

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("rows: %w", err)
	}

	results := make([]searchResult, 0, len(hits))
	for _, h := range hits {
		entry, err := s.getByID(ctx, h.id)
		if err != nil {
			return nil, fmt.Errorf("get entry: %w", err)
		}
		if entry == nil {
			continue
		}
		results = append(results, searchResult{
			Entry: *entry,
			Score: float32(-h.rank),
		})
	}

	return results, nil
}

func (s *sqliteStore) listRecent(ctx context.Context, tag, repo, host string, limit uint64) ([]flashcardSummary, error) {
	if limit == 0 {
		limit = 20
	}

	q := `SELECT f.public_id, f.version, f.note, f.host, f.user, ` + tagsJSONSelect + `, f.repos, f.created
		 FROM flashcard f
		 WHERE f.version = (SELECT MAX(f2.version) FROM flashcard f2 WHERE f2.public_id = f.public_id)
		 AND f.delete_timestamp IS NULL
		 AND f.public_id != 0`
	var args []any

	if tag != "" {
		q += ` AND EXISTS (SELECT 1 FROM flashcard_tag ft JOIN tag t ON t.id = ft.tag_id WHERE ft.flashcard_id = f._id AND t.name = ?)`
		args = append(args, tag)
	}
	if repo != "" {
		q += ` AND f.repos LIKE ?`
		args = append(args, `%"`+repo+`"%`)
	}
	if host != "" {
		q += ` AND f.host = ?`
		args = append(args, host)
	}

	q += ` ORDER BY f.created DESC LIMIT ?`
	args = append(args, limit)

	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("list recent: %w", err)
	}
	defer rows.Close()

	var summaries []flashcardSummary
	for rows.Next() {
		var s flashcardSummary
		var tagsJSON, reposJSON *string
		if err := rows.Scan(&s.PublicID, &s.Version, &s.Note, &s.Host, &s.User, &tagsJSON, &reposJSON, &s.Created); err != nil {
			return nil, fmt.Errorf("scan: %w", err)
		}
		if tagsJSON != nil {
			json.Unmarshal([]byte(*tagsJSON), &s.Tags)
		}
		if reposJSON != nil {
			json.Unmarshal([]byte(*reposJSON), &s.Repos)
		}
		s.URL = fmt.Sprintf("/api/v1/flashcards/%d", s.PublicID)
		summaries = append(summaries, s)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("rows: %w", err)
	}
	return summaries, nil
}

const tagCountExpr = `(SELECT COUNT(*) FROM flashcard_tag ft
	JOIN flashcard f ON f._id = ft.flashcard_id
	WHERE ft.tag_id = t.id
	AND f.delete_timestamp IS NULL
	AND f.public_id != 0
	AND f.version = (SELECT MAX(f2.version) FROM flashcard f2 WHERE f2.public_id = f.public_id))`

var (
	errTagNotFound = errors.New("tag not found")
	errTagExists   = errors.New("tag already exists")
)

func (s *sqliteStore) getTagByName(ctx context.Context, name string) (*tagCount, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT t.name, t.description, `+tagCountExpr+` FROM tag t WHERE t.name = ?`, name,
	)

	var t tagCount
	err := row.Scan(&t.Tag, &t.Description, &t.Count)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("query tag: %w", err)
	}
	return &t, nil
}

func (s *sqliteStore) createTag(ctx context.Context, name, description string) (*tagCount, error) {
	result, err := s.db.ExecContext(ctx,
		`INSERT INTO tag(name, description) VALUES (?, ?) ON CONFLICT(name) DO NOTHING`,
		name, description,
	)
	if err != nil {
		return nil, fmt.Errorf("insert tag: %w", err)
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return nil, fmt.Errorf("rows affected: %w", err)
	}
	if affected == 0 {
		return nil, errTagExists
	}

	return s.getTagByName(ctx, name)
}

func (s *sqliteStore) updateTag(ctx context.Context, name, newName, description string, setDescription bool) (*tagCount, error) {
	existing, err := s.getTagByName(ctx, name)
	if err != nil {
		return nil, err
	}
	if existing == nil {
		return nil, errTagNotFound
	}

	if newName == "" {
		newName = name
	}
	if newName != name {
		clash, err := s.getTagByName(ctx, newName)
		if err != nil {
			return nil, err
		}
		if clash != nil {
			return nil, errTagExists
		}
	}
	if !setDescription {
		description = existing.Description
	}

	if _, err := s.db.ExecContext(ctx,
		`UPDATE tag SET name = ?, description = ? WHERE name = ?`, newName, description, name,
	); err != nil {
		return nil, fmt.Errorf("update tag: %w", err)
	}

	return s.getTagByName(ctx, newName)
}

func (s *sqliteStore) mergeTag(ctx context.Context, from, into, description string, setDescription bool) (*tagCount, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	fromID, err := tagIDByName(ctx, tx, from)
	if err != nil {
		return nil, err
	}
	intoID, err := tagIDByName(ctx, tx, into)
	if err != nil {
		return nil, err
	}
	if fromID == intoID {
		return nil, fmt.Errorf("cannot merge tag %q into itself", from)
	}

	if _, err := tx.ExecContext(ctx,
		`INSERT OR IGNORE INTO flashcard_tag(flashcard_id, tag_id)
		 SELECT flashcard_id, ? FROM flashcard_tag WHERE tag_id = ?`,
		intoID, fromID,
	); err != nil {
		return nil, fmt.Errorf("repoint links: %w", err)
	}

	if _, err := tx.ExecContext(ctx, `DELETE FROM flashcard_tag WHERE tag_id = ?`, fromID); err != nil {
		return nil, fmt.Errorf("delete source links: %w", err)
	}

	if _, err := tx.ExecContext(ctx, `DELETE FROM tag WHERE id = ?`, fromID); err != nil {
		return nil, fmt.Errorf("delete source tag: %w", err)
	}

	if setDescription {
		if _, err := tx.ExecContext(ctx, `UPDATE tag SET description = ? WHERE id = ?`, description, intoID); err != nil {
			return nil, fmt.Errorf("update description: %w", err)
		}
	}

	if err := tx.Commit(); err != nil {
		return nil, fmt.Errorf("commit: %w", err)
	}

	return s.getTagByName(ctx, into)
}

func tagIDByName(ctx context.Context, tx *sql.Tx, name string) (int64, error) {
	var id int64
	err := tx.QueryRowContext(ctx, `SELECT id FROM tag WHERE name = ?`, name).Scan(&id)
	if err == sql.ErrNoRows {
		return 0, fmt.Errorf("%w: %s", errTagNotFound, name)
	}
	if err != nil {
		return 0, fmt.Errorf("query tag %q: %w", name, err)
	}
	return id, nil
}

func (s *sqliteStore) listTags(ctx context.Context) ([]tagCount, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT t.name, t.description, `+tagCountExpr+` AS count
		 FROM tag t
		 ORDER BY count DESC, t.name ASC`,
	)
	if err != nil {
		return nil, fmt.Errorf("list tags: %w", err)
	}
	defer rows.Close()

	var tags []tagCount
	for rows.Next() {
		var t tagCount
		if err := rows.Scan(&t.Tag, &t.Description, &t.Count); err != nil {
			return nil, fmt.Errorf("scan: %w", err)
		}
		tags = append(tags, t)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("rows: %w", err)
	}
	return tags, nil
}

func (s *sqliteStore) close() error {
	return s.db.Close()
}

func marshalStringSlice(ss []string) string {
	if len(ss) == 0 {
		return "[]"
	}
	b, _ := json.Marshal(ss)
	return string(b)
}

func unmarshalStringSlice(raw string) []string {
	if raw == "" || raw == "[]" || raw == "null" {
		return nil
	}
	var ss []string
	if err := json.Unmarshal([]byte(raw), &ss); err != nil {
		return nil
	}
	return ss
}

func parseStringSlice(raw any) []string {
	if raw == nil {
		return nil
	}
	arr, ok := raw.([]any)
	if !ok {
		return nil
	}
	result := make([]string, 0, len(arr))
	for _, v := range arr {
		if s, ok := v.(string); ok {
			result = append(result, s)
		}
	}
	return result
}

func parseOptionalString(raw any) string {
	if s, ok := raw.(string); ok && strings.TrimSpace(s) != "" {
		return s
	}
	return ""
}

func parseInt64(raw any) int64 {
	if raw == nil {
		return 0
	}
	switch v := raw.(type) {
	case float64:
		return int64(v)
	case int64:
		return v
	case int:
		return int64(v)
	}
	return 0
}