6119
My
mysis / files
mysis / internal / store / store.go
9.4 KB
1// Package store provides SQLite persistence for conversation history. 2package store 3 4import ( 5 "database/sql" 6 "encoding/json" 7 "fmt" 8 "path/filepath" 9 "time" 10 11 _ "github.com/mattn/go-sqlite3" 12 "github.com/rs/zerolog/log" 13 "github.com/xonecas/mysis/internal/config" 14 "github.com/xonecas/mysis/internal/provider" 15) 16 17// Store handles database operations. 18type Store struct { 19 db *sql.DB 20} 21 22// Session represents a conversation session. 23type Session struct { 24 ID string 25 Name *string 26 Provider string 27 Model string 28 CreatedAt time.Time 29 LastActiveAt time.Time 30} 31 32// Message represents a stored message. 33type Message struct { 34 ID int64 35 SessionID string 36 Role string 37 Content string 38 ToolCallID *string 39 ToolCalls []provider.ToolCall 40 Reasoning *string 41 CreatedAt time.Time 42} 43 44// Open opens the database connection and ensures schema exists. 45func Open() (*Store, error) { 46 dataDir, err := config.EnsureDataDir() 47 if err != nil { 48 return nil, fmt.Errorf("ensure data dir: %w", err) 49 } 50 51 dbPath := filepath.Join(dataDir, "mysis.db") 52 db, err := sql.Open("sqlite3", dbPath+"?_journal_mode=WAL&_foreign_keys=1") 53 if err != nil { 54 return nil, fmt.Errorf("open database: %w", err) 55 } 56 57 store := &Store{db: db} 58 if err := store.initSchema(); err != nil { 59 _ = db.Close() 60 return nil, fmt.Errorf("init schema: %w", err) 61 } 62 63 return store, nil 64} 65 66// Close closes the database connection. 67func (s *Store) Close() error { 68 return s.db.Close() 69} 70 71// initSchema creates tables if they don't exist. 72func (s *Store) initSchema() error { 73 // Create tables 74 _, err := s.db.Exec(` 75 CREATE TABLE IF NOT EXISTS sessions ( 76 id TEXT PRIMARY KEY, 77 name TEXT UNIQUE, 78 provider TEXT NOT NULL, 79 model TEXT NOT NULL, 80 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 81 last_active_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 82 ); 83 84 CREATE TABLE IF NOT EXISTS messages ( 85 id INTEGER PRIMARY KEY AUTOINCREMENT, 86 session_id TEXT NOT NULL, 87 role TEXT NOT NULL, 88 content TEXT NOT NULL, 89 tool_call_id TEXT, 90 tool_calls TEXT, 91 reasoning TEXT, 92 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 93 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 94 ); 95 96 CREATE TABLE IF NOT EXISTS session_credentials ( 97 session_id TEXT PRIMARY KEY, 98 username TEXT NOT NULL, 99 password TEXT NOT NULL, 100 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 101 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 102 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 103 ); 104 105 CREATE INDEX IF NOT EXISTS idx_messages_session 106 ON messages(session_id, created_at); 107 `) 108 return err 109} 110 111// CreateSession creates a new session. 112func (s *Store) CreateSession(id, provider, model string, name *string) error { 113 query := ` 114 INSERT INTO sessions (id, name, provider, model, created_at, last_active_at) 115 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) 116 ` 117 _, err := s.db.Exec(query, id, name, provider, model) 118 if err != nil { 119 return fmt.Errorf("create session: %w", err) 120 } 121 return nil 122} 123 124// GetSession retrieves a session by ID. 125func (s *Store) GetSession(id string) (*Session, error) { 126 query := ` 127 SELECT id, name, provider, model, created_at, last_active_at 128 FROM sessions 129 WHERE id = ? 130 ` 131 132 var sess Session 133 var name sql.NullString 134 err := s.db.QueryRow(query, id).Scan( 135 &sess.ID, 136 &name, 137 &sess.Provider, 138 &sess.Model, 139 &sess.CreatedAt, 140 &sess.LastActiveAt, 141 ) 142 if err == sql.ErrNoRows { 143 return nil, nil 144 } 145 if err != nil { 146 return nil, fmt.Errorf("get session: %w", err) 147 } 148 149 if name.Valid { 150 sess.Name = &name.String 151 } 152 153 return &sess, nil 154} 155 156// GetSessionByName retrieves a session by name. 157func (s *Store) GetSessionByName(name string) (*Session, error) { 158 query := ` 159 SELECT id, name, provider, model, created_at, last_active_at 160 FROM sessions 161 WHERE name = ? 162 ` 163 164 var sess Session 165 var nameVal sql.NullString 166 err := s.db.QueryRow(query, name).Scan( 167 &sess.ID, 168 &nameVal, 169 &sess.Provider, 170 &sess.Model, 171 &sess.CreatedAt, 172 &sess.LastActiveAt, 173 ) 174 if err == sql.ErrNoRows { 175 return nil, nil 176 } 177 if err != nil { 178 return nil, fmt.Errorf("get session by name: %w", err) 179 } 180 181 if nameVal.Valid { 182 sess.Name = &nameVal.String 183 } 184 185 return &sess, nil 186} 187 188// ListSessions returns all sessions ordered by most recent. 189func (s *Store) ListSessions(limit int) ([]Session, error) { 190 query := ` 191 SELECT id, name, provider, model, created_at, last_active_at 192 FROM sessions 193 ORDER BY last_active_at DESC 194 LIMIT ? 195 ` 196 197 rows, err := s.db.Query(query, limit) 198 if err != nil { 199 return nil, fmt.Errorf("list sessions: %w", err) 200 } 201 defer func() { _ = rows.Close() }() 202 203 var sessions []Session 204 for rows.Next() { 205 var sess Session 206 var name sql.NullString 207 if err := rows.Scan( 208 &sess.ID, 209 &name, 210 &sess.Provider, 211 &sess.Model, 212 &sess.CreatedAt, 213 &sess.LastActiveAt, 214 ); err != nil { 215 return nil, fmt.Errorf("scan session: %w", err) 216 } 217 218 if name.Valid { 219 sess.Name = &name.String 220 } 221 222 sessions = append(sessions, sess) 223 } 224 225 return sessions, rows.Err() 226} 227 228// TouchSession updates the last_active_at timestamp. 229func (s *Store) TouchSession(id string) error { 230 query := `UPDATE sessions SET last_active_at = CURRENT_TIMESTAMP WHERE id = ?` 231 _, err := s.db.Exec(query, id) 232 return err 233} 234 235// SaveMessage stores a message in the database. 236func (s *Store) SaveMessage(sessionID string, msg provider.Message) error { 237 // Marshal tool calls to JSON if present 238 var toolCallsJSON *string 239 if len(msg.ToolCalls) > 0 { 240 data, err := json.Marshal(msg.ToolCalls) 241 if err != nil { 242 return fmt.Errorf("marshal tool calls: %w", err) 243 } 244 jsonStr := string(data) 245 toolCallsJSON = &jsonStr 246 } 247 248 // Handle optional fields 249 var toolCallID *string 250 if msg.ToolCallID != "" { 251 toolCallID = &msg.ToolCallID 252 } 253 254 // Reasoning: NULL if empty, otherwise the value 255 var reasoning interface{} 256 if msg.Reasoning == "" { 257 reasoning = nil 258 } else { 259 reasoning = msg.Reasoning 260 } 261 262 query := ` 263 INSERT INTO messages (session_id, role, content, tool_call_id, tool_calls, reasoning) 264 VALUES (?, ?, ?, ?, ?, ?) 265 ` 266 _, err := s.db.Exec(query, sessionID, msg.Role, msg.Content, toolCallID, toolCallsJSON, reasoning) 267 if err != nil { 268 return fmt.Errorf("save message: %w", err) 269 } 270 271 // Touch session to update last_active_at 272 return s.TouchSession(sessionID) 273} 274 275// LoadMessages retrieves all messages for a session. 276func (s *Store) LoadMessages(sessionID string) ([]provider.Message, error) { 277 query := ` 278 SELECT role, content, tool_call_id, tool_calls, reasoning, created_at 279 FROM messages 280 WHERE session_id = ? 281 ORDER BY created_at ASC 282 ` 283 284 rows, err := s.db.Query(query, sessionID) 285 if err != nil { 286 return nil, fmt.Errorf("load messages: %w", err) 287 } 288 defer func() { _ = rows.Close() }() 289 290 var messages []provider.Message 291 for rows.Next() { 292 var msg provider.Message 293 var toolCallID sql.NullString 294 var toolCallsJSON sql.NullString 295 var reasoning sql.NullString 296 var createdAt string 297 298 if err := rows.Scan(&msg.Role, &msg.Content, &toolCallID, &toolCallsJSON, &reasoning, &createdAt); err != nil { 299 return nil, fmt.Errorf("scan message: %w", err) 300 } 301 302 // Parse timestamp (SQLite CURRENT_TIMESTAMP uses ISO 8601 / RFC3339) 303 if t, err := time.Parse(time.RFC3339, createdAt); err == nil { 304 msg.CreatedAt = t 305 } else { 306 log.Warn().Err(err).Str("timestamp", createdAt).Msg("Failed to parse message timestamp") 307 } 308 309 if toolCallID.Valid { 310 msg.ToolCallID = toolCallID.String 311 } 312 313 if toolCallsJSON.Valid { 314 if err := json.Unmarshal([]byte(toolCallsJSON.String), &msg.ToolCalls); err != nil { 315 return nil, fmt.Errorf("unmarshal tool calls: %w", err) 316 } 317 } 318 319 if reasoning.Valid { 320 msg.Reasoning = reasoning.String 321 } 322 323 messages = append(messages, msg) 324 } 325 326 return messages, rows.Err() 327} 328 329// DeleteSession deletes a session and all its messages. 330func (s *Store) DeleteSession(id string) error { 331 query := `DELETE FROM sessions WHERE id = ?` 332 _, err := s.db.Exec(query, id) 333 return err 334} 335 336// DeleteSessionByName deletes a session by name and all its messages. 337func (s *Store) DeleteSessionByName(name string) error { 338 query := `DELETE FROM sessions WHERE name = ?` 339 result, err := s.db.Exec(query, name) 340 if err != nil { 341 return fmt.Errorf("delete session by name: %w", err) 342 } 343 344 rowsAffected, err := result.RowsAffected() 345 if err != nil { 346 return fmt.Errorf("get rows affected: %w", err) 347 } 348 349 if rowsAffected == 0 { 350 return fmt.Errorf("session '%s' not found", name) 351 } 352 353 return nil 354} 355 356// SaveCredentials stores game credentials for a session. 357func (s *Store) SaveCredentials(sessionID, username, password string) error { 358 query := ` 359 INSERT INTO session_credentials (session_id, username, password, created_at, updated_at) 360 VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) 361 ON CONFLICT(session_id) DO UPDATE SET 362 username = excluded.username, 363 password = excluded.password, 364 updated_at = CURRENT_TIMESTAMP 365 ` 366 _, err := s.db.Exec(query, sessionID, username, password) 367 if err != nil { 368 return fmt.Errorf("save credentials: %w", err) 369 } 370 return nil 371} 372 373// GetCredentials retrieves game credentials for a session. 374func (s *Store) GetCredentials(sessionID string) (username, password string, err error) { 375 query := `SELECT username, password FROM session_credentials WHERE session_id = ?` 376 err = s.db.QueryRow(query, sessionID).Scan(&username, &password) 377 if err == sql.ErrNoRows { 378 return "", "", nil 379 } 380 if err != nil { 381 return "", "", fmt.Errorf("get credentials: %w", err) 382 } 383 return username, password, nil 384}