xonecas.io
60255
Zn

Zoea-Nova / files

Zoea-Nova / internal / store / store.go

3.0 KB
1// Package store provides SQLite-based persistence for Myses and memories.
2package store
3
4import (
5	"database/sql"
6	_ "embed"
7	"fmt"
8	"path/filepath"
9	"strings"
10
11	"github.com/xonecas/zoea-nova/internal/config"
12	_ "modernc.org/sqlite"
13)
14
15//go:embed schema.sql
16var schema string
17
18const currentSchemaVersion = 12
19
20// Store provides access to the SQLite database.
21type Store struct {
22	db *sql.DB
23}
24
25// New creates a new Store with the database at the default location.
26func New() (*Store, error) {
27	dir, err := config.EnsureDataDir()
28	if err != nil {
29		return nil, fmt.Errorf("ensure data dir: %w", err)
30	}
31
32	dbPath := filepath.Join(dir, "zoea.db")
33	return Open(dbPath)
34}
35
36// Open opens a database at the given path.
37func Open(path string) (*Store, error) {
38	// Add busy timeout to handle concurrent access
39	dsn := path
40	if !strings.Contains(path, "?") {
41		dsn += "?_busy_timeout=5000"
42	}
43
44	db, err := sql.Open("sqlite", dsn)
45	if err != nil {
46		return nil, fmt.Errorf("open database: %w", err)
47	}
48
49	// Limit to 1 connection to avoid "database is locked" errors with modernc.org/sqlite
50	// especially during tests with concurrent writes.
51	db.SetMaxOpenConns(1)
52
53	// Enable WAL mode for better concurrent access (though limited by MaxOpenConns=1)
54	if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {
55		db.Close()
56		return nil, fmt.Errorf("enable WAL: %w", err)
57	}
58
59	// Enable foreign keys
60	if _, err := db.Exec("PRAGMA foreign_keys=ON"); err != nil {
61		db.Close()
62		return nil, fmt.Errorf("enable foreign keys: %w", err)
63	}
64
65	s := &Store{db: db}
66	if err := s.migrate(); err != nil {
67		db.Close()
68		return nil, fmt.Errorf("migrate: %w", err)
69	}
70
71	return s, nil
72}
73
74// OpenMemory opens an in-memory database for testing.
75func OpenMemory() (*Store, error) {
76	return Open(":memory:")
77}
78
79// Close closes the database connection.
80func (s *Store) Close() error {
81	return s.db.Close()
82}
83
84// migrate runs schema migrations.
85func (s *Store) migrate() error {
86	// Check current version
87	var version int
88	err := s.db.QueryRow("SELECT version FROM schema_version LIMIT 1").Scan(&version)
89	if err != nil {
90		// Table doesn't exist or is empty, create fresh schema
91		if _, err := s.db.Exec(schema); err != nil {
92			return fmt.Errorf("create schema: %w", err)
93		}
94		return nil
95	}
96
97	// If version matches, nothing to do
98	if version == currentSchemaVersion {
99		return nil
100	}
101
102	// Per design: no data migrations. Schema changes require manual database deletion.
103	if version < currentSchemaVersion {
104		return fmt.Errorf(
105			"database schema version %d is outdated (current: %d)\n"+
106				"Reset the database to continue:\n"+
107				"  make db-reset-accounts",
108			version, currentSchemaVersion,
109		)
110	}
111
112	// Future schema version (downgrade not supported)
113	if version > currentSchemaVersion {
114		return fmt.Errorf(
115			"database schema version %d is newer than supported version %d\n"+
116				"Upgrade Zoea Nova or reset the database:\n"+
117				"  make db-reset-accounts",
118			version, currentSchemaVersion,
119		)
120	}
121
122	return nil
123}
124
125// DB returns the underlying database connection for advanced queries.
126func (s *Store) DB() *sql.DB {
127	return s.db
128}