kyle
c9e74b236d
Go + SQLite web app, server-rendered, hackerman theme. Projects with descriptions, searchable notes, todo/doing/done task tracking. Bcrypt login sessions, hashed api keys, full /api/v1 crud. Deploys on straylight via configs/tracker.nix (prebuilt binary).
128 lines
3.3 KiB
Go
128 lines
3.3 KiB
Go
package store
|
|
|
|
import (
|
|
"database/sql"
|
|
"errors"
|
|
"strings"
|
|
)
|
|
|
|
var ErrConflict = errors.New("already exists")
|
|
|
|
type User struct {
|
|
ID int64
|
|
Username string
|
|
PasswordHash string
|
|
CreatedAt string
|
|
}
|
|
|
|
type APIKey struct {
|
|
ID int64 `json:"id"`
|
|
Name string `json:"name"`
|
|
KeyHash string `json:"-"`
|
|
CreatedAt string `json:"created_at"`
|
|
LastUsed sql.NullString `json:"-"`
|
|
}
|
|
|
|
// --- users ---
|
|
|
|
func (s *Store) CreateUser(username, passwordHash string) (int64, error) {
|
|
res, err := s.db.Exec(`INSERT INTO users (username, password_hash) VALUES (?, ?)`, username, passwordHash)
|
|
if err != nil {
|
|
if strings.Contains(err.Error(), "UNIQUE") {
|
|
return 0, ErrConflict
|
|
}
|
|
return 0, err
|
|
}
|
|
return res.LastInsertId()
|
|
}
|
|
|
|
func (s *Store) UserByName(username string) (User, error) {
|
|
var u User
|
|
err := s.db.QueryRow(`SELECT id, username, password_hash, created_at FROM users WHERE username = ?`, username).
|
|
Scan(&u.ID, &u.Username, &u.PasswordHash, &u.CreatedAt)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return u, ErrNotFound
|
|
}
|
|
return u, err
|
|
}
|
|
|
|
// --- api keys ---
|
|
|
|
func (s *Store) CreateAPIKey(name, keyHash string) (int64, error) {
|
|
res, err := s.db.Exec(`INSERT INTO api_keys (name, key_hash) VALUES (?, ?)`, name, keyHash)
|
|
if err != nil {
|
|
if strings.Contains(err.Error(), "UNIQUE") {
|
|
return 0, ErrConflict
|
|
}
|
|
return 0, err
|
|
}
|
|
return res.LastInsertId()
|
|
}
|
|
|
|
func (s *Store) APIKeys() ([]APIKey, error) {
|
|
rows, err := s.db.Query(`SELECT id, name, key_hash, created_at, last_used FROM api_keys ORDER BY id DESC`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var keys []APIKey
|
|
for rows.Next() {
|
|
var k APIKey
|
|
if err := rows.Scan(&k.ID, &k.Name, &k.KeyHash, &k.CreatedAt, &k.LastUsed); err != nil {
|
|
return nil, err
|
|
}
|
|
keys = append(keys, k)
|
|
}
|
|
return keys, rows.Err()
|
|
}
|
|
|
|
func (s *Store) KeyByHash(keyHash string) (APIKey, error) {
|
|
var k APIKey
|
|
err := s.db.QueryRow(`SELECT id, name, key_hash, created_at, last_used FROM api_keys WHERE key_hash = ?`, keyHash).
|
|
Scan(&k.ID, &k.Name, &k.KeyHash, &k.CreatedAt, &k.LastUsed)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return k, ErrNotFound
|
|
}
|
|
return k, err
|
|
}
|
|
|
|
func (s *Store) TouchKey(id int64) {
|
|
s.db.Exec(`UPDATE api_keys SET last_used = datetime('now','localtime') WHERE id = ?`, id)
|
|
}
|
|
|
|
func (s *Store) DeleteAPIKey(id int64) error {
|
|
res, err := s.db.Exec(`DELETE FROM api_keys WHERE id = ?`, id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return checkAffected(res)
|
|
}
|
|
|
|
// --- sessions ---
|
|
|
|
func (s *Store) CreateSession(tokenHash, username string, expiresAt string) error {
|
|
_, err := s.db.Exec(`INSERT INTO sessions (token_hash, username, expires_at) VALUES (?, ?, ?)`, tokenHash, username, expiresAt)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SessionByTokenHash(tokenHash string) (string, error) {
|
|
var username string
|
|
err := s.db.QueryRow(
|
|
`SELECT username FROM sessions WHERE token_hash = ? AND expires_at > datetime('now','localtime')`,
|
|
tokenHash,
|
|
).Scan(&username)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return "", ErrNotFound
|
|
}
|
|
return username, err
|
|
}
|
|
|
|
func (s *Store) DeleteSession(tokenHash string) error {
|
|
_, err := s.db.Exec(`DELETE FROM sessions WHERE token_hash = ?`, tokenHash)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteExpiredSessions() {
|
|
s.db.Exec(`DELETE FROM sessions WHERE expires_at <= datetime('now','localtime')`)
|
|
}
|