Initial commit

1 files changed, 80 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
+}