pansy/internal/store/sqlite.go

// Package store provides pansy's durable persistence layer: a pure-Go SQLite
// database (modernc.org/sqlite — no cgo, so the binary stays static) with a
// tiny embedded migration runner. Entity query methods are added per feature
// issue; this file only handles opening and the connection pragmas.
package store

import (
	"context"
	"database/sql"
	"fmt"
	"net/url"
	"strings"

	_ "modernc.org/sqlite" // pure-Go driver, registered as "sqlite"
)

// requiredPragmas are applied to every connection (they are per-connection in
// SQLite): wait rather than fail on a contended write, use WAL journaling, and
// enforce foreign keys. They are attached to the DSN so the whole pool inherits
// them.
var requiredPragmas = []string{
	"busy_timeout(5000)",
	"journal_mode(WAL)",
	"foreign_keys(1)",
}

// DB is a handle to the SQLite database backing pansy. Safe for concurrent use:
// WAL mode allows many readers with one writer, and busy_timeout makes a
// contended writer wait rather than fail with SQLITE_BUSY.
type DB struct {
	sql *sql.DB
}

// Open opens (creating if absent) the SQLite database at path and returns a DB.
// The pragmas — busy_timeout, WAL journal, and foreign-key enforcement — are
// applied per connection via the DSN so every pooled connection inherits them,
// regardless of whether path is a bare filename, a path with query parameters,
// or a full file: URI. A path of ":memory:" yields an ephemeral in-memory
// database (useful in tests); the pool is pinned to a single connection so all
// queries see the same DB. The caller owns the returned DB and must Close it.
func Open(path string) (*DB, error) {
	dsn, memory := buildDSN(path)
	sqldb, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("store: open %q: %w", path, err)
	}
	if memory {
		// Each new connection to a bare ":memory:" DSN gets its OWN empty database,
		// so a pooled second connection wouldn't see the migrated schema. Pin the
		// pool to one connection to keep an in-memory DB coherent.
		sqldb.SetMaxOpenConns(1)
	}
	if err := sqldb.Ping(); err != nil {
		sqldb.Close()
		return nil, fmt.Errorf("store: ping %q: %w", path, err)
	}
	return &DB{sql: sqldb}, nil
}

// buildDSN converts the configured path into a modernc DSN that always carries
// the required pragmas, and reports whether it is an in-memory database. It
// accepts a bare filename, a path with existing query parameters, or a full
// file: URI, and merges the pragmas without clobbering any the user supplied.
func buildDSN(path string) (dsn string, memory bool) {
	body := strings.TrimPrefix(path, "file:")

	filePart, rawQuery, _ := strings.Cut(body, "?")
	query := url.Values{}
	if q, err := url.ParseQuery(rawQuery); err == nil {
		query = q
	}

	// Detect in-memory precisely: the exact ":memory:" token, or an explicit
	// mode=memory parameter. A substring match would misfire on real file paths.
	memory = filePart == ":memory:" || query.Get("mode") == "memory"

	have := make(map[string]bool, len(query["_pragma"]))
	for _, p := range query["_pragma"] {
		have[pragmaName(p)] = true
	}
	for _, p := range requiredPragmas {
		if !have[pragmaName(p)] {
			query.Add("_pragma", p)
		}
	}

	// A '#' in a filesystem path would otherwise start a URI fragment and drop
	// the query string that carries the pragmas.
	filePart = strings.ReplaceAll(filePart, "#", "%23")
	return "file:" + filePart + "?" + query.Encode(), memory
}

// pragmaName extracts the pragma's name from a "_pragma" value such as
// "busy_timeout(5000)" or "foreign_keys=1", lowercased for case-insensitive
// de-duplication against a user-supplied value.
func pragmaName(p string) string {
	if i := strings.IndexAny(p, "(="); i >= 0 {
		p = p[:i]
	}
	return strings.ToLower(strings.TrimSpace(p))
}

// queryer is the read surface shared by *sql.DB and *sql.Tx, so a list helper can
// serve both a standalone read and one inside a transaction. (Its single-row
// counterpart is `scanner`, in users.go.)
type queryer interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

// qualifyColumns prefixes each comma-separated column in cols with "alias." so a
// shared column list can be used in a JOIN — e.g. qualifyColumns("pl", "id, x")
// → "pl.id, pl.x". Whitespace/newlines in the list are trimmed.
func qualifyColumns(alias, cols string) string {
	parts := strings.Split(cols, ",")
	for i, p := range parts {
		parts[i] = alias + "." + strings.TrimSpace(p)
	}
	return strings.Join(parts, ", ")
}

// Close closes the underlying database.
func (d *DB) Close() error { return d.sql.Close() }

// SQL exposes the underlying *sql.DB for the per-entity query files.
func (d *DB) SQL() *sql.DB { return d.sql }