138 lines
2.8 KiB
Go
138 lines
2.8 KiB
Go
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"time"
|
|
|
|
_ "github.com/mattn/go-sqlite3"
|
|
)
|
|
|
|
// region DB
|
|
type DB struct {
|
|
path string
|
|
readConn *sql.DB
|
|
writeConn *sql.DB
|
|
}
|
|
|
|
func (db *DB) Open() error {
|
|
if db.path == "" {
|
|
return fmt.Errorf("database path not set")
|
|
}
|
|
|
|
writeConn, err := sql.Open("sqlite3", db.path+"?_journal=WAL&_synchronous=NORMAL")
|
|
if err != nil {
|
|
Error.Printf("%++v", err)
|
|
return err
|
|
}
|
|
writeConn.SetMaxOpenConns(1)
|
|
writeConn.SetConnMaxIdleTime(30 * time.Second)
|
|
writeConn.SetConnMaxLifetime(30 * time.Second)
|
|
db.writeConn = writeConn
|
|
|
|
readConn, err := sql.Open("sqlite3", db.path+"?mode=ro&_journal=WAL&_synchronous=NORMAL&_mode=ro")
|
|
if err != nil {
|
|
Error.Printf("%++v", err)
|
|
return err
|
|
}
|
|
readConn.SetMaxOpenConns(4)
|
|
readConn.SetConnMaxIdleTime(30 * time.Second)
|
|
readConn.SetConnMaxLifetime(30 * time.Second)
|
|
db.readConn = readConn
|
|
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) Close() error {
|
|
err := db.writeConn.Close()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
err = db.readConn.Close()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
type DBCache interface {
|
|
Get(key string) (any, error)
|
|
Set(key string, value any) error
|
|
Tidy() error
|
|
Init() error
|
|
}
|
|
|
|
// region Cache
|
|
type Cache struct {
|
|
db *DB
|
|
hits int
|
|
misses int
|
|
}
|
|
type CacheEntry struct {
|
|
Key string
|
|
Value string
|
|
Date time.Time
|
|
}
|
|
|
|
func (cache *Cache) Get(key string) (CacheEntry, error) {
|
|
ret := CacheEntry{}
|
|
var rkey, rvalue string
|
|
var date time.Time
|
|
err := cache.db.readConn.QueryRow("SELECT key, value, date FROM cache WHERE key = ?", key).Scan(&rkey, &rvalue, &date)
|
|
if err != nil {
|
|
log.Printf("Cache miss for %s: %v", key, err)
|
|
cache.misses++
|
|
return ret, err
|
|
}
|
|
|
|
ret.Key = rkey
|
|
ret.Value = rvalue
|
|
ret.Date = date
|
|
|
|
cache.hits++
|
|
log.Printf("Cache hit for %s", key)
|
|
return ret, nil
|
|
}
|
|
|
|
func (cache *Cache) Set(key string, value string) error {
|
|
_, err := cache.db.writeConn.Exec("INSERT INTO cache (key, value) VALUES (?, ?)", key, value)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (cache *Cache) Init() error {
|
|
_, err := cache.db.writeConn.Exec("CREATE TABLE IF NOT EXISTS cache (key TEXT, value TEXT, date DATE DEFAULT CURRENT_TIMESTAMP)")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = cache.db.writeConn.Exec("CREATE INDEX IF NOT EXISTS idx_cache_key ON cache (key)")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (cache *Cache) Tidy() error {
|
|
_, err := cache.db.writeConn.Exec("DELETE FROM cache WHERE date < datetime('now', '-1 day')")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (cache *Cache) Rate() float32 {
|
|
return float32(cache.hits) / float32(cache.hits+cache.misses)
|
|
}
|
|
func (cache *Cache) RatePerc() string {
|
|
return fmt.Sprintf("%.2f%%", cache.Rate()*100)
|
|
}
|