summaryrefslogtreecommitdiff
path: root/database
diff options
context:
space:
mode:
Diffstat (limited to 'database')
-rw-r--r--database/canvas.go80
-rw-r--r--database/chat.go55
-rw-r--r--database/conn.go17
-rw-r--r--database/connections.go105
-rw-r--r--database/connections_test.go63
-rw-r--r--database/migrate.go143
-rw-r--r--database/minecraft.go28
-rw-r--r--database/sessions.go44
-rw-r--r--database/users.go101
9 files changed, 636 insertions, 0 deletions
diff --git a/database/canvas.go b/database/canvas.go
new file mode 100644
index 0000000..57823d4
--- /dev/null
+++ b/database/canvas.go
@@ -0,0 +1,80 @@
+package database
+
+import (
+ "database/sql"
+ "errors"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type Pixel struct {
+ ID int64
+ X int
+ Y int
+ Color int
+}
+
+// CanvasSnapshot is a materialized canvas (RGB bytes) plus the id of the last
+// pixel baked into it, so replaying just needs pixels with a greater id.
+type CanvasSnapshot struct {
+ Data []byte
+ ThroughID int64
+}
+
+func SavePixel(db *sql.DB, userID string, x, y, color int) (int64, error) {
+ res, err := db.Exec(
+ "INSERT INTO canvas_pixels (user_id, x, y, color, created_at) VALUES (?, ?, ?, ?, ?)",
+ userID, x, y, color, time.Now(),
+ )
+ if err != nil {
+ return 0, err
+ }
+ return res.LastInsertId()
+}
+
+func LatestCanvasSnapshot(db *sql.DB) (*CanvasSnapshot, error) {
+ var s CanvasSnapshot
+ err := db.QueryRow("SELECT data, through_id FROM canvas_snapshots ORDER BY id DESC LIMIT 1").Scan(&s.Data, &s.ThroughID)
+ if errors.Is(err, sql.ErrNoRows) {
+ return nil, nil
+ }
+ if err != nil {
+ return nil, err
+ }
+ return &s, nil
+}
+
+func PixelsAfter(db *sql.DB, throughID int64) ([]Pixel, error) {
+ rows, err := db.Query("SELECT id, x, y, color FROM canvas_pixels WHERE id > ? ORDER BY id", throughID)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ pixels := []Pixel{}
+ for rows.Next() {
+ var p Pixel
+ if err := rows.Scan(&p.ID, &p.X, &p.Y, &p.Color); err != nil {
+ return nil, err
+ }
+ pixels = append(pixels, p)
+ }
+ return pixels, rows.Err()
+}
+
+func CountPixelsAfter(db *sql.DB, throughID int64) (int, error) {
+ var n int
+ err := db.QueryRow("SELECT COUNT(*) FROM canvas_pixels WHERE id > ?", throughID).Scan(&n)
+ return n, err
+}
+
+func SaveCanvasSnapshot(db *sql.DB, data []byte, throughID int64) error {
+ _, err := db.Exec("INSERT INTO canvas_snapshots (data, through_id, created_at) VALUES (?, ?, ?)", data, throughID, time.Now())
+ return err
+}
+
+func DeleteOldCanvasSnapshots(db *sql.DB) error {
+ _, err := db.Exec("DELETE FROM canvas_snapshots WHERE id NOT IN (SELECT id FROM canvas_snapshots ORDER BY id DESC LIMIT 1)")
+ return err
+}
diff --git a/database/chat.go b/database/chat.go
new file mode 100644
index 0000000..66550cd
--- /dev/null
+++ b/database/chat.go
@@ -0,0 +1,55 @@
+package database
+
+import (
+ "database/sql"
+ "log"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type ChatMessage struct {
+ ID string `json:"id"`
+ UserID string `json:"user_id"`
+ Nick string `json:"nick"`
+ Body string `json:"body"`
+ CreatedAt time.Time `json:"created_at"`
+}
+
+func GetRecentChat(db *sql.DB, limit int) ([]ChatMessage, error) {
+ rows, err := db.Query(
+ `SELECT c.id, c.user_id, u.username, c.body, c.created_at
+ FROM chat c JOIN users u ON u.id = c.user_id
+ ORDER BY c.created_at DESC LIMIT ?`,
+ limit,
+ )
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ messages := []ChatMessage{}
+ for rows.Next() {
+ var msg ChatMessage
+ if err := rows.Scan(&msg.ID, &msg.UserID, &msg.Nick, &msg.Body, &msg.CreatedAt); err != nil {
+ return nil, err
+ }
+ messages = append([]ChatMessage{msg}, messages...)
+ }
+
+ return messages, rows.Err()
+}
+
+func SaveChatMessage(db *sql.DB, msg *ChatMessage) (*ChatMessage, error) {
+ log.Println("saving chat message", msg.ID)
+
+ msg.CreatedAt = time.Now()
+ _, err := db.Exec(
+ "INSERT INTO chat (id, user_id, body, created_at) VALUES (?, ?, ?, ?)",
+ msg.ID, msg.UserID, msg.Body, msg.CreatedAt,
+ )
+ if err != nil {
+ return nil, err
+ }
+ return msg, nil
+}
diff --git a/database/conn.go b/database/conn.go
new file mode 100644
index 0000000..4c41a9b
--- /dev/null
+++ b/database/conn.go
@@ -0,0 +1,17 @@
+package database
+
+import (
+ "database/sql"
+ "log"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+func MakeConn(databasePath *string) *sql.DB {
+ log.Println("opening database at", *databasePath, "with foreign keys enabled")
+ dbConn, err := sql.Open("sqlite3", *databasePath+"?_foreign_keys=on")
+ if err != nil {
+ panic(err)
+ }
+ return dbConn
+}
diff --git a/database/connections.go b/database/connections.go
new file mode 100644
index 0000000..e16a17e
--- /dev/null
+++ b/database/connections.go
@@ -0,0 +1,105 @@
+package database
+
+import (
+ "database/sql"
+ "errors"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+// connectionFreshness is how long a device counts as present after we last saw
+// it in the ARP table. It smooths over the kernel briefly aging an entry out.
+const connectionFreshness = 60 * time.Second
+
+type Connection struct {
+ IP string
+ Mac string
+ UpdatedAt time.Time
+}
+
+// RefreshConnections upserts the current ARP snapshot, bumping updated_at for
+// devices still present, and prunes ones not seen within the freshness window.
+func RefreshConnections(db *sql.DB, ipToMac map[string]string) error {
+ tx, err := db.Begin()
+ if err != nil {
+ return err
+ }
+ defer tx.Rollback()
+
+ stmt, err := tx.Prepare(`INSERT INTO connections (ip, mac, updated_at) VALUES (?, ?, ?)
+ ON CONFLICT(ip) DO UPDATE SET mac = excluded.mac, updated_at = excluded.updated_at`)
+ if err != nil {
+ return err
+ }
+ defer stmt.Close()
+
+ now := time.Now()
+ for ip, mac := range ipToMac {
+ if _, err := stmt.Exec(ip, mac, now); err != nil {
+ return err
+ }
+ }
+
+ if _, err := tx.Exec("DELETE FROM connections WHERE updated_at < ?", now.Add(-connectionFreshness)); err != nil {
+ return err
+ }
+
+ return tx.Commit()
+}
+
+func MacForIP(db *sql.DB, ip string) (string, error) {
+ var mac string
+ err := db.QueryRow(
+ "SELECT mac FROM connections WHERE ip = ? AND updated_at > ?",
+ ip, time.Now().Add(-connectionFreshness),
+ ).Scan(&mac)
+ if errors.Is(err, sql.ErrNoRows) {
+ return "", nil
+ }
+ return mac, err
+}
+
+// NetworkUserIDs returns the set of users whose MAC was seen on the LAN within
+// the freshness window, whether or not they're on the chat.
+func NetworkUserIDs(db *sql.DB) (map[string]bool, error) {
+ rows, err := db.Query(
+ "SELECT DISTINCT m.user_id FROM macs m JOIN connections c ON c.mac = m.mac WHERE c.updated_at > ?",
+ time.Now().Add(-connectionFreshness),
+ )
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ ids := map[string]bool{}
+ for rows.Next() {
+ var id string
+ if err := rows.Scan(&id); err != nil {
+ return nil, err
+ }
+ ids[id] = true
+ }
+ return ids, rows.Err()
+}
+
+func ListAliveConnections(db *sql.DB) ([]Connection, error) {
+ rows, err := db.Query(
+ "SELECT ip, mac, updated_at FROM connections WHERE updated_at > ? ORDER BY ip",
+ time.Now().Add(-connectionFreshness),
+ )
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ conns := []Connection{}
+ for rows.Next() {
+ var c Connection
+ if err := rows.Scan(&c.IP, &c.Mac, &c.UpdatedAt); err != nil {
+ return nil, err
+ }
+ conns = append(conns, c)
+ }
+ return conns, rows.Err()
+}
diff --git a/database/connections_test.go b/database/connections_test.go
new file mode 100644
index 0000000..b4ea57b
--- /dev/null
+++ b/database/connections_test.go
@@ -0,0 +1,63 @@
+package database
+
+import (
+ "database/sql"
+ "path/filepath"
+ "testing"
+ "time"
+)
+
+func testDB(t *testing.T) *sql.DB {
+ db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "test.db"))
+ if err != nil {
+ t.Fatal(err)
+ }
+ t.Cleanup(func() { db.Close() })
+ if _, err := MigrateConnections(db); err != nil {
+ t.Fatal(err)
+ }
+ return db
+}
+
+func TestMacForIPFreshness(t *testing.T) {
+ db := testDB(t)
+
+ if err := RefreshConnections(db, map[string]string{"192.168.1.5": "aa:bb:cc:dd:ee:ff"}); err != nil {
+ t.Fatal(err)
+ }
+ if mac, _ := MacForIP(db, "192.168.1.5"); mac != "aa:bb:cc:dd:ee:ff" {
+ t.Fatalf("fresh entry = %q, want the mac", mac)
+ }
+
+ // age the entry past the window: it should stop resolving
+ db.Exec("UPDATE connections SET updated_at = ? WHERE ip = ?", time.Now().Add(-2*connectionFreshness), "192.168.1.5")
+ if mac, _ := MacForIP(db, "192.168.1.5"); mac != "" {
+ t.Errorf("stale entry resolved to %q, want empty", mac)
+ }
+
+ // seeing it again re-activates it
+ if err := RefreshConnections(db, map[string]string{"192.168.1.5": "aa:bb:cc:dd:ee:ff"}); err != nil {
+ t.Fatal(err)
+ }
+ if mac, _ := MacForIP(db, "192.168.1.5"); mac != "aa:bb:cc:dd:ee:ff" {
+ t.Errorf("re-seen entry = %q, want the mac", mac)
+ }
+}
+
+func TestRefreshConnectionsPrunesStale(t *testing.T) {
+ db := testDB(t)
+
+ RefreshConnections(db, map[string]string{"10.0.0.1": "aa:aa:aa:aa:aa:aa"})
+ db.Exec("UPDATE connections SET updated_at = ? WHERE ip = ?", time.Now().Add(-2*connectionFreshness), "10.0.0.1")
+
+ // a refresh with a different device prunes the aged-out one
+ RefreshConnections(db, map[string]string{"10.0.0.2": "bb:bb:bb:bb:bb:bb"})
+
+ conns, err := ListAliveConnections(db)
+ if err != nil {
+ t.Fatal(err)
+ }
+ if len(conns) != 1 || conns[0].IP != "10.0.0.2" {
+ t.Errorf("after prune = %+v, want only 10.0.0.2", conns)
+ }
+}
diff --git a/database/migrate.go b/database/migrate.go
new file mode 100644
index 0000000..24d47b6
--- /dev/null
+++ b/database/migrate.go
@@ -0,0 +1,143 @@
+package database
+
+import (
+ "database/sql"
+ "log"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type Migrator func(*sql.DB) (*sql.DB, error)
+
+func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating users table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS users (
+ id TEXT PRIMARY KEY,
+ username TEXT NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ );`)
+ if err != nil {
+ return dbConn, err
+ }
+
+ _, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users (username);`)
+ return dbConn, err
+}
+
+// MigrateMacs maps hardware addresses to users. One user can own many MACs —
+// that's how a privacy-rotated device "logs back in" to an existing account.
+func MigrateMacs(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating macs table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS macs (
+ mac TEXT PRIMARY KEY,
+ user_id TEXT NOT NULL,
+ first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+ );`)
+ return dbConn, err
+}
+
+func MigrateSessions(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating sessions table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS sessions (
+ id TEXT PRIMARY KEY,
+ user_id TEXT NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ expire_at TIMESTAMP NOT NULL,
+ FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+ );`)
+ return dbConn, err
+}
+
+func MigrateChat(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating chat table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS chat (
+ id TEXT PRIMARY KEY,
+ user_id TEXT NOT NULL,
+ body TEXT NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+ );`)
+ if err != nil {
+ return dbConn, err
+ }
+
+ _, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_chat_created_at ON chat (created_at);`)
+ return dbConn, err
+}
+
+func MigrateConnections(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating connections table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS connections (
+ ip TEXT PRIMARY KEY,
+ mac TEXT NOT NULL,
+ updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ );`)
+ return dbConn, err
+}
+
+func MigrateMinecraft(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating minecraft_status table")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS minecraft_status (
+ id INTEGER PRIMARY KEY CHECK (id = 1),
+ status TEXT NOT NULL,
+ updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ );`)
+ return dbConn, err
+}
+
+func MigrateCanvas(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating canvas tables")
+
+ _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_pixels (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user_id TEXT,
+ x INTEGER NOT NULL,
+ y INTEGER NOT NULL,
+ color INTEGER NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
+ );`)
+ if err != nil {
+ return dbConn, err
+ }
+
+ _, err = dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_snapshots (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ data BLOB NOT NULL,
+ through_id INTEGER NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ );`)
+ return dbConn, err
+}
+
+func Migrate(dbConn *sql.DB) (*sql.DB, error) {
+ log.Println("migrating database")
+
+ migrations := []Migrator{
+ MigrateUsers,
+ MigrateMacs,
+ MigrateSessions,
+ MigrateChat,
+ MigrateConnections,
+ MigrateMinecraft,
+ MigrateCanvas,
+ }
+
+ for _, migration := range migrations {
+ var err error
+ if dbConn, err = migration(dbConn); err != nil {
+ return dbConn, err
+ }
+ }
+
+ return dbConn, nil
+}
diff --git a/database/minecraft.go b/database/minecraft.go
new file mode 100644
index 0000000..d12b4b8
--- /dev/null
+++ b/database/minecraft.go
@@ -0,0 +1,28 @@
+package database
+
+import (
+ "database/sql"
+ "errors"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+func SaveMinecraftStatus(db *sql.DB, status string) error {
+ _, err := db.Exec(`INSERT INTO minecraft_status (id, status, updated_at) VALUES (1, ?, ?)
+ ON CONFLICT(id) DO UPDATE SET status = excluded.status, updated_at = excluded.updated_at`,
+ status, time.Now())
+ return err
+}
+
+// GetMinecraftStatus returns the cached status JSON and when it was written. An
+// empty string (with no error) means nothing has been cached yet.
+func GetMinecraftStatus(db *sql.DB) (string, time.Time, error) {
+ var status string
+ var updatedAt time.Time
+ err := db.QueryRow("SELECT status, updated_at FROM minecraft_status WHERE id = 1").Scan(&status, &updatedAt)
+ if errors.Is(err, sql.ErrNoRows) {
+ return "", time.Time{}, nil
+ }
+ return status, updatedAt, err
+}
diff --git a/database/sessions.go b/database/sessions.go
new file mode 100644
index 0000000..5644281
--- /dev/null
+++ b/database/sessions.go
@@ -0,0 +1,44 @@
+package database
+
+import (
+ "database/sql"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type Session struct {
+ ID string
+ UserID string
+ ExpireAt time.Time
+}
+
+func CreateSession(db *sql.DB, id, userID string, ttl time.Duration) (*Session, error) {
+ now := time.Now()
+ expire := now.Add(ttl)
+ _, err := db.Exec(
+ "INSERT INTO sessions (id, user_id, created_at, expire_at) VALUES (?, ?, ?, ?)",
+ id, userID, now, expire,
+ )
+ if err != nil {
+ return nil, err
+ }
+ return &Session{ID: id, UserID: userID, ExpireAt: expire}, nil
+}
+
+func GetSessionUser(db *sql.DB, id string) (*User, error) {
+ return scanUser(db.QueryRow(
+ "SELECT u.id, u.username, u.created_at, u.last_seen FROM users u JOIN sessions s ON s.user_id = u.id WHERE s.id = ? AND s.expire_at > ?",
+ id, time.Now(),
+ ))
+}
+
+func DeleteExpiredSessions(db *sql.DB) error {
+ _, err := db.Exec("DELETE FROM sessions WHERE expire_at < ?", time.Now())
+ return err
+}
+
+func DeleteSession(db *sql.DB, id string) error {
+ _, err := db.Exec("DELETE FROM sessions WHERE id = ?", id)
+ return err
+}
diff --git a/database/users.go b/database/users.go
new file mode 100644
index 0000000..169cce1
--- /dev/null
+++ b/database/users.go
@@ -0,0 +1,101 @@
+package database
+
+import (
+ "database/sql"
+ "errors"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+var ErrNotFound = errors.New("not found")
+
+type User struct {
+ ID string
+ Username string
+ CreatedAt time.Time
+ LastSeen time.Time
+}
+
+func scanUser(row *sql.Row) (*User, error) {
+ var u User
+ err := row.Scan(&u.ID, &u.Username, &u.CreatedAt, &u.LastSeen)
+ if errors.Is(err, sql.ErrNoRows) {
+ return nil, ErrNotFound
+ }
+ if err != nil {
+ return nil, err
+ }
+ return &u, nil
+}
+
+const userColumns = "id, username, created_at, last_seen"
+
+func CreateUser(db *sql.DB, id, username string) (*User, error) {
+ now := time.Now()
+ _, err := db.Exec(
+ "INSERT INTO users ("+userColumns+") VALUES (?, ?, ?, ?)",
+ id, username, now, now,
+ )
+ if err != nil {
+ return nil, err
+ }
+ return &User{ID: id, Username: username, CreatedAt: now, LastSeen: now}, nil
+}
+
+func GetUserByID(db *sql.DB, id string) (*User, error) {
+ return scanUser(db.QueryRow("SELECT "+userColumns+" FROM users WHERE id = ?", id))
+}
+
+func RenameUser(db *sql.DB, userID, username string) error {
+ _, err := db.Exec("UPDATE users SET username = ? WHERE id = ?", username, userID)
+ return err
+}
+
+func ListUsers(db *sql.DB) ([]User, error) {
+ rows, err := db.Query("SELECT " + userColumns + " FROM users ORDER BY last_seen DESC")
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ users := []User{}
+ for rows.Next() {
+ var u User
+ if err := rows.Scan(&u.ID, &u.Username, &u.CreatedAt, &u.LastSeen); err != nil {
+ return nil, err
+ }
+ users = append(users, u)
+ }
+ return users, rows.Err()
+}
+
+func GetUserByUsername(db *sql.DB, username string) (*User, error) {
+ return scanUser(db.QueryRow("SELECT "+userColumns+" FROM users WHERE username = ?", username))
+}
+
+// GetUserByMAC finds the account a hardware address currently belongs to.
+// Columns are alias-qualified because macs also has a last_seen column.
+func GetUserByMAC(db *sql.DB, mac string) (*User, error) {
+ return scanUser(db.QueryRow(
+ "SELECT u.id, u.username, u.created_at, u.last_seen FROM users u JOIN macs m ON m.user_id = u.id WHERE m.mac = ?",
+ mac,
+ ))
+}
+
+// TouchUser bumps a user's last_seen — this is the timestamp the portal shows
+// when someone tries to reclaim a taken handle.
+func TouchUser(db *sql.DB, id string) error {
+ _, err := db.Exec("UPDATE users SET last_seen = ? WHERE id = ?", time.Now(), id)
+ return err
+}
+
+// AssociateMAC binds a MAC to a user, moving it from any previous owner. This is
+// what "logging back in" from a rotated MAC does.
+func AssociateMAC(db *sql.DB, mac, userID string) error {
+ now := time.Now()
+ _, err := db.Exec(`INSERT INTO macs (mac, user_id, first_seen, last_seen) VALUES (?, ?, ?, ?)
+ ON CONFLICT(mac) DO UPDATE SET user_id = excluded.user_id, last_seen = excluded.last_seen`,
+ mac, userID, now, now)
+ return err
+}
generated by cgit v1.3 (git 2.53.0) at 2026-07-18 14:47:43 +0000