A URL shortener service that uses ATProto to allow self hosting and ensuring the user owns their data
1package database
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log/slog"
8)
9
10func createJetstreamTable(db *sql.DB) error {
11 createJetstreamTableSQL := `CREATE TABLE IF NOT EXISTS jetstream (
12 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
13 "did" TEXT,
14 "cursor" INTEGER,
15 UNIQUE(did)
16 );`
17
18 slog.Info("Create jetstream table...")
19 statement, err := db.Prepare(createJetstreamTableSQL)
20 if err != nil {
21 return fmt.Errorf("prepare DB statement to create jetstream table: %w", err)
22 }
23 _, err = statement.Exec()
24 if err != nil {
25 return fmt.Errorf("exec sql statement to create jetstream table: %w", err)
26 }
27 slog.Info("jetstream table created")
28
29 return nil
30}
31
32func (d *DB) SaveCursor(ctx context.Context, did string, cursor int64) error {
33 sql := `INSERT INTO jetstream (did, cursor) VALUES (?, ?) ON CONFLICT(did) DO UPDATE SET cursor = ?;`
34 _, err := d.db.Exec(sql, did, cursor, cursor)
35 if err != nil {
36 return fmt.Errorf("exec insert or update cursor: %w", err)
37 }
38
39 return nil
40}
41
42func (d *DB) GetCursor(ctx context.Context, did string) (int64, error) {
43 sql := "SELECT cursor FROM jetstream where did = ?;"
44 rows, err := d.db.Query(sql, did)
45 if err != nil {
46 return 0, fmt.Errorf("run query to get cursor: %w", err)
47 }
48 defer rows.Close()
49
50 cursor := 0
51 for rows.Next() {
52 if err := rows.Scan(&cursor); err != nil {
53 return 0, fmt.Errorf("scan row: %w", err)
54 }
55
56 return int64(cursor), nil
57 }
58 return 0, fmt.Errorf("not found")
59}