An implementation of the ATProto statusphere example app but in Go
willdot.net
1package database
2
3import (
4 "database/sql"
5 "fmt"
6 "log/slog"
7
8 statusphere "github.com/willdot/statusphere-go"
9)
10
11func createStatusTable(db *sql.DB) error {
12 createStatusTableSQL := `CREATE TABLE IF NOT EXISTS status (
13 "uri" TEXT NOT NULL PRIMARY KEY,
14 "did" TEXT,
15 "status" TEXT,
16 "createdAt" integer,
17 "indexedAt" integer
18 );`
19
20 slog.Info("Create status table...")
21 statement, err := db.Prepare(createStatusTableSQL)
22 if err != nil {
23 return fmt.Errorf("prepare DB statement to create status table: %w", err)
24 }
25 _, err = statement.Exec()
26 if err != nil {
27 return fmt.Errorf("exec sql statement to create status table: %w", err)
28 }
29 slog.Info("status table created")
30
31 return nil
32}
33
34func (d *DB) CreateStatus(status statusphere.Status) error {
35 sql := `INSERT INTO status (uri, did, status, createdAt, indexedAt) VALUES (?, ?, ?, ?, ?) ON CONFLICT(uri) DO NOTHING;`
36 _, err := d.db.Exec(sql, status.URI, status.Did, status.Status, status.CreatedAt, status.IndexedAt)
37 if err != nil {
38 return fmt.Errorf("exec insert status: %w", err)
39 }
40
41 return nil
42}
43
44func (d *DB) GetStatuses(limit int) ([]statusphere.Status, error) {
45 sql := "SELECT uri, did, status, createdAt FROM status ORDER BY createdAt desc LIMIT ?;"
46 rows, err := d.db.Query(sql, limit)
47 if err != nil {
48 return nil, fmt.Errorf("run query to get status': %w", err)
49 }
50 defer rows.Close()
51
52 var results []statusphere.Status
53 for rows.Next() {
54 var status statusphere.Status
55 if err := rows.Scan(&status.URI, &status.Did, &status.Status, &status.CreatedAt); err != nil {
56 return nil, fmt.Errorf("scan row: %w", err)
57 }
58
59 results = append(results, status)
60 }
61 return results, nil
62}