62159
Sy
symb / files
symb / internal / store / store.go
10.0 KB
1// Package store provides a SQLite-backed cache for web fetch and search results. 2package store 3 4import ( 5 "database/sql" 6 "fmt" 7 "strings" 8 "sync" 9 "time" 10 11 "github.com/rs/zerolog/log" 12 _ "modernc.org/sqlite" // register sqlite driver 13) 14 15const schema = ` 16CREATE TABLE IF NOT EXISTS fetch_cache ( 17 url TEXT PRIMARY KEY, 18 result TEXT NOT NULL, 19 created INTEGER NOT NULL 20); 21 22CREATE TABLE IF NOT EXISTS search_cache ( 23 query TEXT PRIMARY KEY, 24 result TEXT NOT NULL, 25 created INTEGER NOT NULL 26); 27 28CREATE INDEX IF NOT EXISTS idx_fetch_created ON fetch_cache(created); 29CREATE INDEX IF NOT EXISTS idx_search_created ON search_cache(created); 30 31CREATE TABLE IF NOT EXISTS sessions ( 32 id TEXT PRIMARY KEY, 33 title TEXT NOT NULL DEFAULT '', 34 created INTEGER NOT NULL, 35 updated INTEGER NOT NULL 36); 37 38CREATE TABLE IF NOT EXISTS messages ( 39 id INTEGER PRIMARY KEY AUTOINCREMENT, 40 session_id TEXT NOT NULL REFERENCES sessions(id), 41 role TEXT NOT NULL, 42 content TEXT NOT NULL DEFAULT '', 43 reasoning TEXT NOT NULL DEFAULT '', 44 tool_calls TEXT NOT NULL DEFAULT '[]', 45 tool_call_id TEXT NOT NULL DEFAULT '', 46 created INTEGER NOT NULL 47); 48 49CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, id); 50 51CREATE TABLE IF NOT EXISTS file_deltas ( 52 id INTEGER PRIMARY KEY AUTOINCREMENT, 53 session_id TEXT NOT NULL, 54 turn_id INTEGER NOT NULL, 55 file_path TEXT NOT NULL, 56 op TEXT NOT NULL, 57 old_content BLOB, 58 created INTEGER NOT NULL 59); 60 61CREATE INDEX IF NOT EXISTS idx_deltas_turn ON file_deltas(session_id, turn_id); 62` 63 64// Cache is a SQLite-backed cache for web results and session storage. 65type Cache struct { 66 mu sync.Mutex 67 db *sql.DB 68 ttl time.Duration 69} 70 71// Open creates or opens a cache database at the given path. 72// ttl controls how long entries remain fresh. 73func Open(dbPath string, ttl time.Duration) (*Cache, error) { 74 db, err := sql.Open("sqlite", dbPath) 75 if err != nil { 76 return nil, fmt.Errorf("open cache db: %w", err) 77 } 78 79 // SQLite pragmas for performance. 80 for _, pragma := range []string{ 81 "PRAGMA journal_mode = WAL", 82 "PRAGMA synchronous = NORMAL", 83 "PRAGMA busy_timeout = 5000", 84 "PRAGMA foreign_keys = ON", 85 } { 86 if _, err := db.Exec(pragma); err != nil { 87 db.Close() 88 return nil, fmt.Errorf("pragma %q: %w", pragma, err) 89 } 90 } 91 92 // Migrate: drop old search_cache with keywords column and recreate. 93 // This is a cache, so losing data is acceptable. 94 if hasColumn(db, "search_cache", "keywords") { 95 if _, err := db.Exec("DROP TABLE search_cache"); err != nil { 96 log.Warn().Err(err).Msg("failed to drop search_cache for migration") 97 } 98 } 99 100 if _, err := db.Exec(schema); err != nil { 101 db.Close() 102 return nil, fmt.Errorf("create schema: %w", err) 103 } 104 105 // Migrate: add token count columns to messages table. 106 if !hasColumn(db, "messages", "input_tokens") { 107 if _, err := db.Exec("ALTER TABLE messages ADD COLUMN input_tokens INTEGER NOT NULL DEFAULT 0"); err != nil { 108 db.Close() 109 return nil, fmt.Errorf("add messages.input_tokens: %w", err) 110 } 111 if _, err := db.Exec("ALTER TABLE messages ADD COLUMN output_tokens INTEGER NOT NULL DEFAULT 0"); err != nil { 112 db.Close() 113 return nil, fmt.Errorf("add messages.output_tokens: %w", err) 114 } 115 } 116 117 c := &Cache{ 118 db: db, 119 ttl: ttl, 120 } 121 c.purgeStale() 122 return c, nil 123} 124 125// DB returns the underlying *sql.DB for shared access (e.g. delta tracker). 126func (c *Cache) DB() *sql.DB { 127 if c == nil { 128 return nil 129 } 130 return c.db 131} 132 133// Close closes the database. 134func (c *Cache) Close() error { 135 if c == nil { 136 return nil 137 } 138 return c.db.Close() 139} 140 141// --- Fetch cache --- 142 143// GetFetch returns a cached fetch result for the given URL, or "" if miss/stale. 144// Safe to call on a nil receiver (returns miss). 145func (c *Cache) GetFetch(url string) (string, bool) { 146 if c == nil { 147 return "", false 148 } 149 c.mu.Lock() 150 defer c.mu.Unlock() 151 152 cutoff := time.Now().Add(-c.ttl).Unix() 153 var result string 154 err := c.db.QueryRow( 155 "SELECT result FROM fetch_cache WHERE url = ? AND created > ?", 156 url, cutoff, 157 ).Scan(&result) 158 if err != nil { 159 return "", false 160 } 161 return result, true 162} 163 164// SetFetch stores a fetch result. No-op on nil receiver. 165func (c *Cache) SetFetch(url, result string) { 166 if c == nil { 167 return 168 } 169 c.mu.Lock() 170 defer c.mu.Unlock() 171 172 _, err := c.db.Exec( 173 "INSERT OR REPLACE INTO fetch_cache (url, result, created) VALUES (?, ?, ?)", 174 url, result, time.Now().Unix(), 175 ) 176 if err != nil { 177 log.Warn().Err(err).Str("url", url).Msg("failed to cache fetch result") 178 } 179} 180 181// --- Search cache --- 182 183// GetSearch returns a cached search result for the exact query, or "" if miss/stale. 184// Safe to call on a nil receiver (returns miss). 185func (c *Cache) GetSearch(query string) (string, bool) { 186 if c == nil { 187 return "", false 188 } 189 c.mu.Lock() 190 defer c.mu.Unlock() 191 192 normalized := normalizeQuery(query) 193 cutoff := time.Now().Add(-c.ttl).Unix() 194 var result string 195 err := c.db.QueryRow( 196 "SELECT result FROM search_cache WHERE query = ? AND created > ?", 197 normalized, cutoff, 198 ).Scan(&result) 199 if err != nil { 200 return "", false 201 } 202 return result, true 203} 204 205// SearchCachedContent looks for a cached result whose text content contains 206// enough of the query keywords. This searches the actual cached results, not 207// the original queries — so if the answer already exists in any cached result, 208// we return it instead of making a new API call. 209func (c *Cache) SearchCachedContent(query string) (string, bool) { 210 if c == nil { 211 return "", false 212 } 213 c.mu.Lock() 214 defer c.mu.Unlock() 215 216 queryKw := tokenize(query) 217 if len(queryKw) < 2 { 218 return "", false 219 } 220 221 cutoff := time.Now().Add(-c.ttl).Unix() 222 rows, err := c.db.Query( 223 "SELECT result FROM search_cache WHERE created > ?", 224 cutoff, 225 ) 226 if err != nil { 227 return "", false 228 } 229 defer rows.Close() 230 231 var bestResult string 232 var bestScore float64 233 var bestHits int 234 235 for rows.Next() { 236 var result string 237 if err := rows.Scan(&result); err != nil { 238 continue 239 } 240 resultLower := strings.ToLower(result) 241 score, hits := contentOverlap(queryKw, resultLower) 242 if score > bestScore { 243 bestScore = score 244 bestHits = hits 245 bestResult = result 246 } 247 } 248 249 // Require at least 75% of query keywords found in content AND at least 3 hits. 250 if bestScore >= 0.75 && bestHits >= 3 { 251 return bestResult, true 252 } 253 return "", false 254} 255 256// SetSearch stores a search result. No-op on nil receiver. 257func (c *Cache) SetSearch(query, result string) { 258 if c == nil { 259 return 260 } 261 c.mu.Lock() 262 defer c.mu.Unlock() 263 264 normalized := normalizeQuery(query) 265 _, err := c.db.Exec( 266 "INSERT OR REPLACE INTO search_cache (query, result, created) VALUES (?, ?, ?)", 267 normalized, result, time.Now().Unix(), 268 ) 269 if err != nil { 270 log.Warn().Err(err).Str("query", query).Msg("failed to cache search result") 271 } 272} 273 274// hasColumn checks if a table has a specific column. 275func hasColumn(db *sql.DB, table, column string) bool { 276 rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table)) //nolint:gosec // table name is hardcoded by caller 277 if err != nil { 278 return false 279 } 280 defer rows.Close() 281 for rows.Next() { 282 var cid int 283 var name, typ string 284 var notNull, pk int 285 var dflt sql.NullString 286 if err := rows.Scan(&cid, &name, &typ, ¬Null, &dflt, &pk); err != nil { 287 continue 288 } 289 if name == column { 290 return true 291 } 292 } 293 return false 294} 295 296// --- Helpers --- 297 298// purgeStale removes entries older than the TTL. 299func (c *Cache) purgeStale() { 300 cutoff := time.Now().Add(-c.ttl).Unix() 301 for _, table := range []string{"fetch_cache", "search_cache"} { 302 res, err := c.db.Exec( 303 fmt.Sprintf("DELETE FROM %s WHERE created <= ?", table), //nolint:gosec // table name is hardcoded 304 cutoff, 305 ) 306 if err != nil { 307 log.Warn().Err(err).Str("table", table).Msg("failed to purge stale cache") 308 continue 309 } 310 if n, _ := res.RowsAffected(); n > 0 { 311 log.Info().Int64("deleted", n).Str("table", table).Msg("purged stale cache entries") 312 } 313 } 314} 315 316// normalizeQuery lowercases and trims a query string. 317func normalizeQuery(q string) string { 318 return strings.ToLower(strings.TrimSpace(q)) 319} 320 321// stopWords are common words filtered out during tokenization. 322var stopWords = map[string]bool{ 323 "a": true, "an": true, "the": true, "is": true, "are": true, 324 "was": true, "were": true, "be": true, "been": true, "being": true, 325 "have": true, "has": true, "had": true, "do": true, "does": true, 326 "did": true, "will": true, "would": true, "could": true, "should": true, 327 "may": true, "might": true, "shall": true, "can": true, 328 "for": true, "and": true, "but": true, "or": true, "nor": true, 329 "not": true, "so": true, "yet": true, "to": true, "of": true, 330 "in": true, "on": true, "at": true, "by": true, "with": true, 331 "from": true, "as": true, "into": true, "about": true, "between": true, 332 "through": true, "during": true, "before": true, "after": true, 333 "above": true, "below": true, "up": true, "down": true, "out": true, 334 "off": true, "over": true, "under": true, "again": true, "then": true, 335 "once": true, "here": true, "there": true, "when": true, "where": true, 336 "why": true, "how": true, "what": true, "which": true, "who": true, 337 "whom": true, "this": true, "that": true, "these": true, "those": true, 338 "i": true, "me": true, "my": true, "we": true, "our": true, 339 "you": true, "your": true, "he": true, "him": true, "his": true, 340 "she": true, "her": true, "it": true, "its": true, "they": true, 341 "them": true, "their": true, 342} 343 344// tokenize splits a query into lowercase keywords, filtering stop words and short tokens. 345func tokenize(query string) []string { 346 words := strings.Fields(strings.ToLower(strings.TrimSpace(query))) 347 var out []string 348 for _, w := range words { 349 w = strings.Trim(w, ".,;:!?\"'()-[]{}") // strip punctuation 350 if len(w) < 2 || stopWords[w] { 351 continue 352 } 353 out = append(out, w) 354 } 355 return out 356} 357 358// contentOverlap checks how many of the query keywords appear anywhere in the 359// lowercased result text. Returns fraction and count. 360func contentOverlap(queryKw []string, resultLower string) (float64, int) { 361 if len(queryKw) == 0 { 362 return 0, 0 363 } 364 hits := 0 365 for _, kw := range queryKw { 366 if strings.Contains(resultLower, kw) { 367 hits++ 368 } 369 } 370 return float64(hits) / float64(len(queryKw)), hits 371}