willdot.net
A feed generator that allows Bluesky bookmarks via DMs
1package store
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "log/slog"
8)
9
10var ErrBookmarkAlreadyExists = errors.New("bookmark already exists")
11
12func createBookmarksTable(db *sql.DB) error {
13 createBooksmarksTableSQL := `CREATE TABLE IF NOT EXISTS bookmarks (
14 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
15 "postRKey" TEXT,
16 "postURI" TEXT,
17 "postATURI" TEXT,
18 "authorDID" TEXT,
19 "authorHandle" TEXT,
20 "userDID" TEXT,
21 "content" TEXT,
22 "createdAt" integer NOT NULL,
23 UNIQUE(postRKey, userDID)
24 );`
25
26 slog.Info("Create bookmarks table...")
27 statement, err := db.Prepare(createBooksmarksTableSQL)
28 if err != nil {
29 return fmt.Errorf("prepare DB statement to create bookmarks table: %w", err)
30 }
31 _, err = statement.Exec()
32 if err != nil {
33 return fmt.Errorf("exec sql statement to create bookmarks table: %w", err)
34 }
35 slog.Info("bookmarks table created")
36
37 return nil
38}
39
40type Bookmark struct {
41 ID int
42 PostRKey string
43 PostURI string
44 PostATURI string
45 AuthorDID string
46 AuthorHandle string
47 UserDID string
48 Content string
49 CreatedAt int64
50}
51
52func (s *Store) CreateBookmark(postRKey, postURI, postATURI, authorDID, authorHandle, userDID, content string, createdAt int64) error {
53 sql := `INSERT INTO bookmarks (postRKey, postURI,postATURI, authorDID, authorHandle, userDID, content, createdAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(postRKey, userDID) DO NOTHING;`
54 res, err := s.db.Exec(sql, postRKey, postURI, postATURI, authorDID, authorHandle, userDID, content, createdAt)
55 if err != nil {
56 return fmt.Errorf("exec insert bookmark: %w", err)
57 }
58
59 if x, _ := res.RowsAffected(); x == 0 {
60 return ErrBookmarkAlreadyExists
61 }
62 return nil
63}
64
65func (s *Store) GetBookmarksForUser(userDID string) ([]Bookmark, error) {
66 sql := "SELECT id, postRKey, postURI, postATURI, authorDID, authorHandle, userDID, content, createdAt FROM bookmarks WHERE userDID = ?;"
67 rows, err := s.db.Query(sql, userDID)
68 if err != nil {
69 return nil, fmt.Errorf("run query to get bookmarked posts for user: %w", err)
70 }
71 defer rows.Close()
72
73 var results []Bookmark
74 for rows.Next() {
75 var bookmark Bookmark
76 if err := rows.Scan(&bookmark.ID, &bookmark.PostRKey, &bookmark.PostURI, &bookmark.PostATURI, &bookmark.AuthorDID, &bookmark.AuthorHandle, &bookmark.UserDID, &bookmark.Content, &bookmark.CreatedAt); err != nil {
77 return nil, fmt.Errorf("scan row: %w", err)
78 }
79
80 results = append(results, bookmark)
81 }
82 return results, nil
83}
84
85func (s *Store) GetBookmarksForUserWithPaging(userDID string, cursor int64, limit int) ([]Bookmark, error) {
86 sql := `SELECT id, postRKey, postURI, postATURI, authorDID, authorHandle, userDID, content, createdAt FROM bookmarks
87 WHERE userDID = ? AND createdAt < ?
88 ORDER BY createdAt DESC LIMIT ?;`
89 rows, err := s.db.Query(sql, userDID, cursor, limit)
90 if err != nil {
91 return nil, fmt.Errorf("run query to get bookmarked posts for user: %w", err)
92 }
93 defer rows.Close()
94
95 var results []Bookmark
96 for rows.Next() {
97 var bookmark Bookmark
98 if err := rows.Scan(&bookmark.ID, &bookmark.PostRKey, &bookmark.PostURI, &bookmark.PostATURI, &bookmark.AuthorDID, &bookmark.AuthorHandle, &bookmark.UserDID, &bookmark.Content, &bookmark.CreatedAt); err != nil {
99 return nil, fmt.Errorf("scan row: %w", err)
100 }
101
102 results = append(results, bookmark)
103 }
104 return results, nil
105}
106
107func (s *Store) DeleteBookmark(postRKey, userDID string) error {
108 sql := "DELETE FROM bookmarks WHERE postRKey = ? AND userDID = ?;"
109 _, err := s.db.Exec(sql, postRKey, userDID)
110 if err != nil {
111 return fmt.Errorf("exec delete bookmark by postRKey and userDID: %w", err)
112 }
113 return nil
114}
115
116func (s *Store) GetBookmarksForPost(postURI string) ([]string, error) {
117 sql := "SELECT userDID FROM bookmarks WHERE postATURI = ?"
118 rows, err := s.db.Query(sql, postURI)
119 if err != nil {
120 return nil, fmt.Errorf("run query to get bookmarks for post: %w", err)
121 }
122 defer rows.Close()
123
124 dids := make([]string, 0)
125 for rows.Next() {
126 var bookmark Bookmark
127 if err := rows.Scan(&bookmark.UserDID); err != nil {
128 return nil, fmt.Errorf("scan row: %w", err)
129 }
130 dids = append(dids, bookmark.UserDID)
131 }
132
133 return dids, nil
134}
135
136func (s *Store) GetBookmarkByRKeyForUser(rkey, userDID string) (*Bookmark, error) {
137 sql := "SELECT id, postRKey, postURI, postATURI, authorDID, authorHandle, userDID, content FROM bookmarks WHERE postRKey = ? AND userDID = ?;"
138 rows, err := s.db.Query(sql, rkey, userDID)
139 if err != nil {
140 return nil, fmt.Errorf("run query to get bookmark by rkey and user: %w", err)
141 }
142 defer rows.Close()
143
144 if rows.Next() {
145 var bookmark Bookmark
146 if err := rows.Scan(&bookmark.ID, &bookmark.PostRKey, &bookmark.PostURI, &bookmark.PostATURI, &bookmark.AuthorDID, &bookmark.AuthorHandle, &bookmark.UserDID, &bookmark.Content); err != nil {
147 return nil, fmt.Errorf("scan row: %w", err)
148 }
149
150 return &bookmark, nil
151 }
152
153 return nil, nil
154}