Initial commit

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
+}