1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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
}
|
