Skip to content

One self-hosted app for your shows, movies and music.

15.3 KB
Raw Blame
package database

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"io"
	"io/fs"
	"log"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/HolyArrs/holyarrs/app/backend/internal/core/files"
)

// backupTries is how many times the pre-update backup is tried.
const backupTries = 3

// SkipSpaceCheckEnv set to yes skips the free space check before an update.
const SkipSpaceCheckEnv = "HOLYARRS_SKIP_SPACE_CHECK"

// forbiddenStmt matches statements a migration must not run. BEGIN inside a
// trigger (BEGIN ... END) is allowed; a transaction BEGIN is not.
var forbiddenStmt = regexp.MustCompile(`(?im)(^|;)\s*(PRAGMA\b|VACUUM\b|COMMIT\b|END\s+TRANSACTION\b|ROLLBACK\b|BEGIN\s*(;|\z|TRANSACTION\b|DEFERRED\b|IMMEDIATE\b|EXCLUSIVE\b))`)

// Errors that stop an update of the data before (or right after) it runs.
// The data is left as it was, or the pinned backup is named.
var (
	ErrNoSpace     = errors.New("not enough free space")
	ErrBackup      = errors.New("the backup before the update failed")
	ErrCheckFailed = errors.New("the data check after the update failed")
	ErrMigration   = errors.New("the update of the data failed")
)

// Test hooks: free space on a folder's disk, whether two folders share a
// disk, and the pre-update backup.
var (
	freeSpace  = files.DiskFree
	sameVolume = files.OnSameVolume
	takeBackup = pinnedBackup
)

type migration struct {
	version int
	name    string
	sql     string
}

// NewerDataError means a database holds migrations this build does not know:
// a newer HolyArrs ran on this config folder. Nothing is changed.
type NewerDataError struct {
	Label      string
	Have, Know int
}

type querier interface {
	QueryContext(ctx context.Context, q string, args ...any) (*sql.Rows, error)
}

// Migration is one numbered SQL step of the built-in schema.
type Migration struct {
	Version int
	Name    string
	SQL     string
}

// SchemaVersion is the newest migration applied to db, read the way the
// migrator reads it (doctor uses it to tell a database still being set up).
func SchemaVersion(db *sql.DB) (int, error) { return version(db) }

func (e *NewerDataError) Error() string {
	return fmt.Sprintf("this data is from a newer HolyArrs (schema %d, this build knows %d). Run that version again, or restore a backup.", e.Have, e.Know)
}

// CheckBackupSum compares a pre-update backup with the sha256 file written
// next to it. A backup without that file (an older build's) passes.
func CheckBackupSum(p string) error {
	b, err := os.ReadFile(p + ".sha256")
	if errors.Is(err, os.ErrNotExist) {
		return nil
	}
	if err != nil {
		return err
	}
	want, _, _ := strings.Cut(string(b), " ")
	got, err := fileSHA256(p)
	if err != nil {
		return err
	}
	if got != want {
		return fmt.Errorf("%s does not match its sha256 file", filepath.Base(p))
	}
	return nil
}

// EmbeddedMigrations lists the built-in steps of one database in order. dir
// is "migrations/main" or "migrations/events". Tests of other packages use it
// to build older schemas.
func EmbeddedMigrations(dir string) ([]Migration, error) {
	src, err := fs.Sub(embedded, dir)
	if err != nil {
		return nil, err
	}
	ms, err := loadMigrations(src)
	if err != nil {
		return nil, err
	}
	out := make([]Migration, len(ms))
	for i, m := range ms {
		out[i] = Migration{Version: m.version, Name: m.name, SQL: m.sql}
	}
	return out, nil
}

func loadMigrations(src fs.FS) ([]migration, error) {
	entries, err := fs.ReadDir(src, ".")
	if err != nil {
		return nil, err
	}
	var out []migration
	seen := map[int]string{}
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		num, _, _ := strings.Cut(e.Name(), "_")
		v, err := strconv.Atoi(num)
		if err != nil || v <= 0 {
			return nil, fmt.Errorf("migration %q must start with a positive number and an underscore", e.Name())
		}
		if prev, dup := seen[v]; dup {
			return nil, fmt.Errorf("migrations %q and %q share number %d", prev, e.Name(), v)
		}
		seen[v] = e.Name()
		b, err := fs.ReadFile(src, e.Name())
		if err != nil {
			return nil, err
		}
		sql := string(b)
		if bad := forbiddenStmt.FindString(stripComments(sql)); bad != "" {
			return nil, fmt.Errorf("migration %q has %q: migrations run in a transaction on a managed connection and must not hold PRAGMA, VACUUM, BEGIN or COMMIT", e.Name(), strings.TrimSpace(bad))
		}
		out = append(out, migration{version: v, name: e.Name(), sql: sql})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].version < out[j].version })
	return out, nil
}

func stripComments(s string) string {
	lines := strings.Split(s, "\n")
	for i, l := range lines {
		if j := strings.Index(l, "--"); j >= 0 {
			lines[i] = l[:j]
		}
	}
	return strings.Join(lines, "\n")
}

func version(db *sql.DB) (int, error) {
	var v sql.NullInt64
	if err := db.QueryRow("SELECT max(version) FROM schema_migrations").Scan(&v); err != nil {
		return 0, err
	}
	return int(v.Int64), nil
}

// headroom is the free space kept on top of what a backup or update
// needs: 10% of the database, at least 50 MB.
func headroom(size int64) uint64 {
	return max(uint64(50<<20), uint64(size)/10)
}

func skipSpaceCheck() bool {
	switch strings.ToLower(strings.TrimSpace(os.Getenv(SkipSpaceCheckEnv))) {
	case "yes", "1", "true", "on":
		return true
	}
	return false
}

// plan makes sure schema_migrations exists and returns the pending
// migrations and the version applied. Data newer than this build is refused.
func plan(w *writer, src fs.FS, label string) ([]migration, int, error) {
	ms, err := loadMigrations(src)
	if err != nil {
		return nil, 0, err
	}
	err = w.doUncapped(context.Background(), func(tx *sql.Tx) error {
		_, err := tx.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations(
			version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL)`)
		return err
	})
	if err != nil {
		return nil, 0, err
	}
	cur, err := version(w.db)
	if err != nil {
		return nil, 0, err
	}
	know := 0
	if len(ms) > 0 {
		know = ms[len(ms)-1].version
	}
	if cur > know {
		return nil, 0, &NewerDataError{Label: label, Have: cur, Know: know}
	}
	var out []migration
	for _, m := range ms {
		if m.version > cur {
			out = append(out, m)
		}
	}
	return out, cur, nil
}

// migrate applies pending migrations in order. When the database already
// held data it first takes one checked backup (pinned: the way back from
// this update), after checking there is room for it and the update.
// Migrations run with synchronous=FULL and the result is checked.
// onUpdating, when set, is called before existing data is updated.
func migrate(w *writer, path string, ms []migration, cur int, backupDir, label string, onUpdating func()) error {
	if len(ms) == 0 {
		return nil
	}
	pinned := ""
	if cur > 0 {
		if onUpdating != nil {
			onUpdating()
		}
		if err := os.MkdirAll(backupDir, 0o750); err != nil {
			return fmt.Errorf("%w (%s): the backups folder %s cannot be made: %v; the data was not changed", ErrBackup, label, backupDir, err)
		}
		stale, _ := filepath.Glob(filepath.Join(backupDir, label+"-pre-update-*.db.partial"))
		for _, p := range stale {
			_ = os.Remove(p)
		}
		if skipSpaceCheck() {
			log.Printf("store: %s=yes, so the free space check before the %s update is skipped", SkipSpaceCheckEnv, label)
		} else if err := checkSpace(path, filepath.Dir(path), backupDir); err != nil {
			return err
		}
		to := ms[len(ms)-1].version
		var err error
		if kept, from := cutOffBackup(backupDir, label); kept != "" {
			// A raw, checked copy is only left when an update was cut off
			// (a good start adopts it), so resume with that backup.
			log.Printf("store: resuming an update that was cut off; keeping the backup from %d (%s)", from, kept)
			pinned = kept
		}
		for try := 1; pinned == "" && try <= backupTries; try++ {
			if pinned, err = takeBackup(w, backupDir, label, cur, to); err == nil {
				break
			}
			log.Printf("store: backup before the %s update (try %d of %d): %v", label, try, backupTries, err)
		}
		if err != nil {
			return fmt.Errorf("%w (%s, %d tries): %v; the data was not changed", ErrBackup, label, backupTries, err)
		}
	}
	// Every PRAGMA and migration runs on one connection, so a setting can
	// never land on a different (or recycled) one.
	ctx := context.Background()
	conn, err := w.db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()
	// foreign_keys can only change outside a transaction; migrations that
	// rebuild tables need it off, and each one is checked before commit.
	if _, err := conn.ExecContext(ctx, "PRAGMA foreign_keys=OFF"); err != nil {
		return err
	}
	defer func() { _, _ = conn.ExecContext(ctx, "PRAGMA foreign_keys=ON") }()
	if _, err := conn.ExecContext(ctx, "PRAGMA synchronous=FULL"); err != nil {
		return err
	}
	defer func() { _, _ = conn.ExecContext(ctx, "PRAGMA synchronous=NORMAL") }()
	for _, m := range ms {
		err := func() error {
			tx, err := conn.BeginTx(ctx, nil)
			if err != nil {
				return err
			}
			defer func() { _ = tx.Rollback() }()
			if _, err := tx.Exec(m.sql); err != nil {
				return err
			}
			if _, err := tx.Exec("INSERT INTO schema_migrations(version, name, applied_at) VALUES (?, ?, ?)",
				m.version, m.name, time.Now().UTC().Format(time.RFC3339Nano)); err != nil {
				return err
			}
			if bad, err := foreignKeyCheck(tx); err != nil {
				return err
			} else if bad != "" {
				return fmt.Errorf("foreign key check: %s", bad)
			}
			return tx.Commit()
		}()
		if err != nil {
			return fmt.Errorf("%w: migration %s (%s): %v; this step was undone", ErrMigration, m.name, label, err)
		}
	}
	res, err := integrityCheck(conn)
	if err == nil && res == "ok" {
		var bad string
		if bad, err = foreignKeyCheck(conn); err == nil && bad != "" {
			res = "foreign key check: " + bad
		}
	}
	if err != nil || res != "ok" {
		if err != nil {
			res = err.Error()
		}
		where := "this was new data, so there is no backup"
		if pinned != "" {
			where = "restore the backup " + pinned
		}
		return fmt.Errorf("%w (%s): %s; %s", ErrCheckFailed, label, res, where)
	}
	return nil
}

// integrityCheck runs the full PRAGMA integrity_check; "ok" means sound.
func integrityCheck(q querier) (string, error) {
	rows, err := q.QueryContext(context.Background(), "PRAGMA integrity_check")
	if err != nil {
		return "", err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			return "", err
		}
		out = append(out, s)
	}
	if err := rows.Err(); err != nil {
		return "", err
	}
	return strings.Join(out, "; "), nil
}

// foreignKeyCheck returns "" when no row breaks a foreign key, otherwise
// the first broken row (table, rowid, parent).
func foreignKeyCheck(q querier) (string, error) {
	rows, err := q.QueryContext(context.Background(), "PRAGMA foreign_key_check")
	if err != nil {
		return "", err
	}
	defer rows.Close()
	if !rows.Next() {
		return "", rows.Err()
	}
	var table, parent string
	var rowid sql.NullInt64
	var fkid int
	if err := rows.Scan(&table, &rowid, &parent, &fkid); err != nil {
		return "", err
	}
	return fmt.Sprintf("%s row %d points to a missing %s", table, rowid.Int64, parent), nil
}

func quickCheck(db *sql.DB) (string, error) {
	var res string
	err := db.QueryRow("PRAGMA quick_check").Scan(&res)
	return res, err
}

// checkSpace refuses when the backups disk cannot hold a copy of the
// database (plus headroom), or the data disk cannot hold one and a half
// copies (the update's own growth, plus headroom). One disk needs both.
// When free space cannot be measured (some filesystems) it warns and lets
// the update go on: only a measured shortage refuses.
func checkSpace(dbPath, dataDir, backupDir string) error {
	size := int64(0)
	for _, p := range []string{dbPath, dbPath + "-wal"} {
		if fi, err := os.Stat(p); err == nil {
			size += fi.Size()
		}
	}
	needBackup, needData := uint64(size)+headroom(size), uint64(size)*3/2+headroom(size)
	same, err := sameVolume(dataDir, backupDir)
	if err != nil {
		log.Printf("store: warning: cannot tell whether %s and %s share a disk (%v); checking each on its own", dataDir, backupDir, err)
		same = false
	}
	type need struct {
		dir string
		n   uint64
	}
	needs := []need{{backupDir, needBackup}, {dataDir, needData}}
	if same {
		needs = []need{{dataDir, needBackup + needData}}
	}
	for _, n := range needs {
		have, err := freeSpace(n.dir)
		if err != nil {
			log.Printf("store: warning: cannot measure free space on %s (%v); updating anyway (needs about %s)", n.dir, err, humanSize(n.n))
			continue
		}
		if have < n.n {
			return fmt.Errorf("%w: need %s, have %s on %s", ErrNoSpace, humanSize(n.n), humanSize(have), n.dir)
		}
	}
	return nil
}

// humanSize writes a byte count as 1.5 GB, 200.0 MB or 12 B.
func humanSize(n uint64) string {
	if n < 1024 {
		return fmt.Sprintf("%d B", n)
	}
	f, i := float64(n)/1024, 0
	for f >= 1024 && i < 4 {
		f /= 1024
		i++
	}
	return fmt.Sprintf("%.1f %cB", f, "KMGTP"[i])
}

// cutOffBackup returns the newest raw pre-update copy for label that has a
// sha256 file and matches it, and the schema it was taken at.
func cutOffBackup(backupDir, label string) (string, int) {
	ps, _ := filepath.Glob(filepath.Join(backupDir, label+"-pre-update-*.db"))
	sort.Sort(sort.Reverse(sort.StringSlice(ps)))
	for _, p := range ps {
		if _, err := os.Stat(p + ".sha256"); err != nil {
			continue
		}
		if err := CheckBackupSum(p); err != nil {
			log.Printf("store: not resuming with %s: %v", filepath.Base(p), err)
			continue
		}
		var from, to int
		rest := strings.TrimPrefix(filepath.Base(p), label+"-pre-update-")
		if _, err := fmt.Sscanf(rest, "%04d-to-%04d-", &from, &to); err != nil {
			continue
		}
		return p, from
	}
	return "", 0
}

// pinnedBackup copies the database with VACUUM INTO to a .partial file,
// checks the copy (quick_check, read-only), writes its sha256 next to it
// and only then names it .db. It returns the backup's path.
func pinnedBackup(w *writer, backupDir, label string, from, to int) (string, error) {
	if _, err := w.db.Exec("PRAGMA wal_checkpoint(TRUNCATE)"); err != nil {
		return "", fmt.Errorf("checkpoint: %w", err)
	}
	name := fmt.Sprintf("%s-pre-update-%04d-to-%04d-%s.db", label, from, to, time.Now().UTC().Format("20060102T150405.000Z"))
	final := filepath.Join(backupDir, name)
	part := final + ".partial"
	fail := func(err error) (string, error) {
		_ = os.Remove(part)
		return "", err
	}
	if _, err := w.db.Exec("VACUUM INTO ?", part); err != nil {
		return fail(fmt.Errorf("copy: %w", err))
	}
	ro, err := sql.Open("sqlite", fileURI(part)+"?mode=ro")
	if err != nil {
		return fail(err)
	}
	res, err := quickCheck(ro)
	_ = ro.Close()
	if err != nil {
		return fail(fmt.Errorf("check: %w", err))
	}
	if res != "ok" {
		return fail(fmt.Errorf("check: %s", res))
	}
	sum, err := fileSHA256(part)
	if err != nil {
		return fail(err)
	}
	if err := files.SyncFile(part); err != nil {
		return fail(fmt.Errorf("sync: %w", err))
	}
	if err := files.WriteFileSynced(final+".sha256", []byte(sum+"  "+name+"\n"), 0o600); err != nil {
		return fail(err)
	}
	if err := os.Rename(part, final); err != nil {
		_ = os.Remove(final + ".sha256")
		return fail(err)
	}
	if err := files.SyncDir(backupDir); err != nil {
		return "", fmt.Errorf("sync %s: %w", backupDir, err)
	}
	log.Printf("store: backup before the %s update %04d -> %04d: %s (sha256 %s)", label, from, to, final, sum)
	return final, nil
}

func fileSHA256(p string) (string, error) {
	f, err := os.Open(p)
	if err != nil {
		return "", err
	}
	defer f.Close()
	h := sha256.New()
	if _, err := io.Copy(h, f); err != nil {
		return "", err
	}
	return hex.EncodeToString(h.Sum(nil)), nil
}