1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

package main

import (
	"database/sql"
	"fmt"
)

// =============================================================================
// DB helpers
// =============================================================================

// Pragmas must travel as _pragma DSN parameters: the modernc driver applies
// those to every connection it opens, and silently ignores DSN parameters it
// does not recognise — the _journal_mode=/_busy_timeout= spelling used here
// previously looked correct but set nothing at all, leaving connections on the
// default journal mode with no busy timeout (fail immediately when locked).
func openDB(path string) (*sql.DB, error) {
	db, err := sql.Open("sqlite",
		"file:"+path+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)")
	if err != nil {
		return nil, err
	}
	return db, nil
}

// openImageFileDB opens a packed .hosty *file* read-only. Such files normally
// live on read-only media (the Nix store, a read-only mount, a CD) and are never
// written while we read them, so:
//
//   - do NOT request journal_mode(WAL): switching a database to WAL requires
//     creating a -shm sidecar next to it, which fails with SQLITE_CANTOPEN on
//     read-only media.
//   - immutable=1 tells SQLite the file cannot change, so it skips locking and
//     the -shm/-wal machinery entirely.
//
// Only use this for inert .hosty artifacts. For an installed data.hosty use
// openReadOnlyDB — see the warning there.
func openImageFileDB(path string) (*sql.DB, error) {
	db, err := sql.Open("sqlite",
		"file:"+path+"?mode=ro&immutable=1&_pragma=busy_timeout(5000)")
	if err != nil {
		return nil, err
	}
	return db, nil
}

// openReadOnlyDB opens a *live* database read-only, i.e. one that another
// process may be writing concurrently (an installed data.hosty is written by the
// running service and by the FUSE mount, which holds a read-write WAL connection
// to the same file).
//
// Deliberately no immutable=1 here: it makes SQLite ignore the -wal file and
// skip locking, which on a database that does change yields stale reads or
// spurious SQLITE_CORRUPT. journal_mode is likewise not set — the writer already
// put the file in WAL, and a read-only connection must not try to change it.
func openReadOnlyDB(path string) (*sql.DB, error) {
	db, err := sql.Open("sqlite",
		"file:"+path+"?mode=ro&_pragma=busy_timeout(5000)")
	if err != nil {
		return nil, err
	}
	return db, nil
}

func metaGet(db *sql.DB, key string) (string, error) {
	var val string
	err := db.QueryRow(`SELECT value FROM _hosty_meta WHERE key = ?`, key).Scan(&val)
	if err != nil {
		return "", fmt.Errorf("_hosty_meta[%q]: %w", key, err)
	}
	return val, nil
}

func metaSet(db *sql.DB, key, value string) error {
	_, err := db.Exec(`INSERT OR REPLACE INTO _hosty_meta (key, value) VALUES (?, ?)`, key, value)
	return err
}

// initHostyTables creates the spec tables in data.hosty.
// Port assignments and sensitive config values live in the environment
// database instead (see openEnvDB).
func initHostyTables(db *sql.DB) error {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS _hosty_meta (
			key   TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);
		CREATE TABLE IF NOT EXISTS _hosty_image_fs (
			path           TEXT    PRIMARY KEY,
			mode           INTEGER NOT NULL,
			mtime          INTEGER NOT NULL,
			symlink_target TEXT,
			content        BLOB,
			content_zstd   BLOB
		);
		CREATE TABLE IF NOT EXISTS _hosty_fs (
			path           TEXT    PRIMARY KEY,
			mode           INTEGER NOT NULL,
			mtime          INTEGER NOT NULL,
			symlink_target TEXT,
			content        BLOB
		);
		CREATE TABLE IF NOT EXISTS _hosty_config (
			key         TEXT PRIMARY KEY,
			description TEXT NOT NULL,
			required    INTEGER NOT NULL DEFAULT 1,
			sensitive   INTEGER NOT NULL DEFAULT 0,
			default_val TEXT,
			value       TEXT
		);
	`)
	return err
}