A demo of a Bluesky feed generator in Go
willdot.net
1package feed
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "log/slog"
8 "os"
9
10 _ "github.com/glebarez/go-sqlite"
11)
12
13// Database is a sqlite database
14type Database struct {
15 db *sql.DB
16}
17
18// NewDatabase will open a new database. It will ping the database to ensure it is available and error if not
19func NewDatabase(dbPath string) (*Database, error) {
20 if dbPath != ":memory:" {
21 err := createDbFile(dbPath)
22 if err != nil {
23 return nil, fmt.Errorf("create db file: %w", err)
24 }
25 }
26
27 db, err := sql.Open("sqlite", dbPath)
28 if err != nil {
29 return nil, fmt.Errorf("open database: %w", err)
30 }
31
32 err = db.Ping()
33 if err != nil {
34 return nil, fmt.Errorf("ping db: %w", err)
35 }
36
37 err = createPostsTable(db)
38 if err != nil {
39 return nil, fmt.Errorf("creating posts table: %w", err)
40 }
41
42 return &Database{db: db}, nil
43}
44
45// Close will cleanly stop the database connection
46func (d *Database) Close() {
47 err := d.db.Close()
48 if err != nil {
49 slog.Error("failed to close db", "error", err)
50 }
51}
52
53func createDbFile(dbFilename string) error {
54 if _, err := os.Stat(dbFilename); !errors.Is(err, os.ErrNotExist) {
55 return nil
56 }
57
58 f, err := os.Create(dbFilename)
59 if err != nil {
60 return fmt.Errorf("create db file : %w", err)
61 }
62 err = f.Close()
63 if err != nil {
64 return fmt.Errorf("failed to close DB file: %w", err)
65 }
66 return nil
67}
68
69func createPostsTable(db *sql.DB) error {
70 createTableSQL := `CREATE TABLE IF NOT EXISTS posts (
71 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
72 "postRKey" TEXT,
73 "postURI" TEXT,
74 "createdAt" integer NOT NULL,
75 UNIQUE(postRKey)
76 );`
77
78 slog.Info("Create posts table...")
79 statement, err := db.Prepare(createTableSQL)
80 if err != nil {
81 return fmt.Errorf("prepare DB statement to create posts table: %w", err)
82 }
83 _, err = statement.Exec()
84 if err != nil {
85 return fmt.Errorf("exec sql statement to create posts table: %w", err)
86 }
87 slog.Info("posts table created")
88
89 return nil
90}
91
92// CreatePost will insert a post into a database
93func (d *Database) CreatePost(post Post) error {
94 sql := `INSERT INTO posts (postRKey, postURI, createdAt) VALUES (?, ?, ?) ON CONFLICT(postRKey) DO NOTHING;`
95 _, err := d.db.Exec(sql, post.RKey, post.PostURI, post.CreatedAt)
96 if err != nil {
97 return fmt.Errorf("exec insert post: %w", err)
98 }
99 return nil
100}
101
102// GetFeedPosts return a slice of posts
103func (d *Database) GetFeedPosts(cursor, limit int) ([]Post, error) {
104 sql := `SELECT id, postRKey, postURI, createdAt FROM posts
105 WHERE createdAt < ?
106 ORDER BY createdAt DESC LIMIT ?;`
107 rows, err := d.db.Query(sql, cursor, limit)
108 if err != nil {
109 return nil, fmt.Errorf("run query to get feed posts: %w", err)
110 }
111 defer func() {
112 _ = rows.Close()
113 }()
114
115 posts := make([]Post, 0)
116 for rows.Next() {
117 var post Post
118 if err := rows.Scan(&post.ID, &post.RKey, &post.PostURI, &post.CreatedAt); err != nil {
119 return nil, fmt.Errorf("scan row: %w", err)
120 }
121 posts = append(posts, post)
122 }
123
124 return posts, nil
125}