mirror of
https://github.com/tiennm99/goclaw.git
synced 2026-06-13 22:10:56 +00:00
viettranx
b088144fd9
Root cause: pool.go applied busy_timeout PRAGMA via db.Exec() which only affects one connection in the pool. Other connections had no busy_timeout, causing immediate SQLITE_BUSY errors during concurrent startup operations (agent creation, WebSocket connect, health checks). This silently aborted context file seeding — BOOTSTRAP.md, USER.md, AGENTS.md all missing from system prompt on first interaction. Fix (3 layers): 1. pragmaConnector: wraps sql.Driver to apply PRAGMAs (busy_timeout, WAL, etc.) on every new connection, not just one. All SQLite queries benefit. 2. CacheInvalidateFunc: clears ContextFileInterceptor cache after seeding so LoadContextFiles sees newly seeded files on the first turn. 3. fallbackBootstrap: if DB seed still fails, injects embedded templates in-memory so the first turn still gets onboarding. Clears after use.
107 lines
3.2 KiB
Go
107 lines
3.2 KiB
Go
//go:build sqlite || sqliteonly
|
|
|
|
package sqlitestore
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"database/sql/driver"
|
|
"fmt"
|
|
"log/slog"
|
|
|
|
_ "modernc.org/sqlite" // pure-Go SQLite driver
|
|
)
|
|
|
|
// connectionPragmas are applied to EVERY new SQLite connection.
|
|
// Critical: PRAGMA settings are per-connection in SQLite. Using db.Exec()
|
|
// only applies to ONE connection in the pool — other connections won't have
|
|
// busy_timeout, causing immediate SQLITE_BUSY errors under concurrency.
|
|
var connectionPragmas = []string{
|
|
"PRAGMA journal_mode = WAL",
|
|
"PRAGMA busy_timeout = 15000",
|
|
"PRAGMA synchronous = NORMAL",
|
|
"PRAGMA cache_size = -8000", // 8MB cache
|
|
"PRAGMA foreign_keys = ON",
|
|
}
|
|
|
|
// pragmaConnector wraps a sql.Driver to apply PRAGMAs on every new connection.
|
|
// This ensures ALL connections in the pool have busy_timeout, WAL mode, etc.
|
|
type pragmaConnector struct {
|
|
driver driver.Driver
|
|
dsn string
|
|
pragmas []string
|
|
}
|
|
|
|
func (c *pragmaConnector) Connect(ctx context.Context) (driver.Conn, error) {
|
|
conn, err := c.driver.Open(c.dsn)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Apply PRAGMAs to this specific connection.
|
|
for _, p := range c.pragmas {
|
|
if execer, ok := conn.(driver.ExecerContext); ok {
|
|
if _, execErr := execer.ExecContext(ctx, p, nil); execErr != nil {
|
|
slog.Warn("sqlite pragma failed on new conn", "pragma", p, "error", execErr)
|
|
}
|
|
} else if execer, ok := conn.(driver.Execer); ok { //nolint:staticcheck
|
|
if _, execErr := execer.Exec(p, nil); execErr != nil {
|
|
slog.Warn("sqlite pragma failed on new conn", "pragma", p, "error", execErr)
|
|
}
|
|
}
|
|
}
|
|
return conn, nil
|
|
}
|
|
|
|
func (c *pragmaConnector) Driver() driver.Driver { return c.driver }
|
|
|
|
// OpenDB opens a SQLite database at the given path with WAL mode and recommended pragmas.
|
|
// Uses modernc.org/sqlite (pure Go, zero CGo).
|
|
//
|
|
// Desktop app concurrency model:
|
|
// - WAL mode: allows concurrent readers alongside a single writer
|
|
// - busy_timeout=15000: ALL connections wait up to 15s before SQLITE_BUSY
|
|
// - _txlock=immediate: write transactions acquire lock immediately (fail-fast on contention)
|
|
//
|
|
// PRAGMAs are applied per-connection via pragmaConnector, ensuring every
|
|
// connection in the pool has consistent settings (busy_timeout, WAL, etc.).
|
|
func OpenDB(path string) (*sql.DB, error) {
|
|
dsn := fmt.Sprintf("file:%s?_txlock=immediate", path)
|
|
|
|
// Get the registered driver to wrap with pragmaConnector.
|
|
drv, err := getSQLiteDriver()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
db := sql.OpenDB(&pragmaConnector{
|
|
driver: drv,
|
|
dsn: dsn,
|
|
pragmas: connectionPragmas,
|
|
})
|
|
|
|
// SQLite is single-writer; WAL allows concurrent readers.
|
|
// 4 connections: up to 3 readers + 1 writer can proceed in parallel,
|
|
// reducing connection pool starvation during concurrent operations.
|
|
db.SetMaxOpenConns(4)
|
|
|
|
// Verify connection works (also triggers first pragma application).
|
|
if err := db.Ping(); err != nil {
|
|
db.Close()
|
|
return nil, fmt.Errorf("ping sqlite: %w", err)
|
|
}
|
|
|
|
return db, nil
|
|
}
|
|
|
|
// getSQLiteDriver retrieves the registered "sqlite" driver instance.
|
|
func getSQLiteDriver() (driver.Driver, error) {
|
|
db, err := sql.Open("sqlite", ":memory:")
|
|
if err != nil {
|
|
return nil, fmt.Errorf("get sqlite driver: %w", err)
|
|
}
|
|
drv := db.Driver()
|
|
db.Close()
|
|
return drv, nil
|
|
}
|