104 lines
2.7 KiB
Go
104 lines
2.7 KiB
Go
package cache
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"iter"
|
|
"time"
|
|
)
|
|
|
|
var _ Cache[any] = (*Sqlite[any])(nil)
|
|
|
|
const (
|
|
SqliteListAllKeys = `SELECT KEY_NAME, KEY_VALUE FROM %q WHERE LIMIT_TTL > ?;` // List all keys with value
|
|
SqliteDeleteOutdated = `DELETE FROM %q WHERE LIMIT_TTL < ?;` // Delete rows if outdate
|
|
SqliteDelete = `DELETE FROM %q WHERE KEY_NAME == ?;` // Delete with KEY_NAME
|
|
SqliteInsert = `INSERT OR REPLACE INTO %q (KEY_NAME, KEY_VALUE, LIMIT_TTL) VALUES (?,?,?);` // Create key or replace if exists
|
|
SqliteSelect = `SELECT KEY_VALUE FROM %q WHERE KEY_NAME == ? AND LIMIT_TTL > ?;` // Select value by KEY_NAME
|
|
|
|
// Create table if not exists to cache value
|
|
SqliteCreateTable = `CREATE TABLE IF NOT EXISTS %q (
|
|
ID INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
KEY_NAME TEXT UNIQUE NOT NULL,
|
|
KEY_VALUE TEXT NOT NULL,
|
|
LIMIT_TTL DATETIME NOT NULL
|
|
);`
|
|
)
|
|
|
|
type Sqlite[T any] struct {
|
|
DBName string
|
|
DB *sql.DB
|
|
}
|
|
|
|
func OpenSqlite[T any](dataSourceName, dbName string) (Cache[T], error) {
|
|
db, err := sql.Open("sqlite", dataSourceName)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
_, err = db.Exec(fmt.Sprintf(SqliteCreateTable, dbName))
|
|
return &Sqlite[T]{DBName: dbName, DB: db}, err
|
|
}
|
|
|
|
func (db Sqlite[T]) Flush() error {
|
|
_, err := db.DB.Exec(fmt.Sprintf(SqliteDeleteOutdated, db.DBName), time.Now())
|
|
if err == sql.ErrNoRows {
|
|
err = nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func (db Sqlite[T]) Delete(key string) error {
|
|
_, err := db.DB.Exec(fmt.Sprintf(SqliteDelete, db.DBName), key)
|
|
if err == sql.ErrNoRows {
|
|
err = ErrNotExist
|
|
}
|
|
return err
|
|
}
|
|
|
|
func (db Sqlite[T]) Set(ttl time.Duration, key string, value T) error {
|
|
data, err := ToString(value)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = db.DB.Exec(fmt.Sprintf(SqliteInsert, db.DBName), key, data, sql.NullTime{Time: time.Now().Add(ttl), Valid: true})
|
|
return err
|
|
}
|
|
|
|
func (db Sqlite[T]) Get(key string) (T, error) {
|
|
var value sql.NullString
|
|
if err := db.DB.QueryRow(fmt.Sprintf(SqliteSelect, db.DBName), key, time.Now()).Scan(&value); err != nil || !value.Valid {
|
|
if err == sql.ErrNoRows || !value.Valid {
|
|
err = ErrNotExist
|
|
}
|
|
return *new(T), err
|
|
}
|
|
|
|
return FromString[T](value.String)
|
|
}
|
|
|
|
func (db Sqlite[T]) Values() (iter.Seq2[string, T], error) {
|
|
rows, err := db.DB.Query(fmt.Sprintf(SqliteListAllKeys, db.DBName), time.Now())
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
return func(yield func(string, T) bool) {
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var key, value string
|
|
if err := rows.Scan(&key, &value); err != nil {
|
|
return
|
|
}
|
|
|
|
valueData, err := FromString[T](value)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
if !yield(key, valueData) {
|
|
return
|
|
}
|
|
}
|
|
}, nil
|
|
}
|