this repo has no description
1package store
2
3import (
4 "database/sql"
5 "fmt"
6 "log/slog"
7)
8
9func createRepliesTable(db *sql.DB) error {
10 createRepliesTableSQL := `CREATE TABLE IF NOT EXISTS replies (
11 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
12 "replyURI" TEXT,
13 "userDID" TEXT,
14 "subscribedPostURI" TEXT,
15 "createdAt" integer NOT NULL,
16 UNIQUE(replyURI, userDID)
17 );`
18
19 slog.Info("Create replies table...")
20 statement, err := db.Prepare(createRepliesTableSQL)
21 if err != nil {
22 return fmt.Errorf("prepare DB statement to create replies table: %w", err)
23 }
24 _, err = statement.Exec()
25 if err != nil {
26 return fmt.Errorf("exec sql statement to create replies table: %w", err)
27 }
28 slog.Info("replies table created")
29
30 return nil
31}
32
33type ReplyPost struct {
34 ID int
35 ReplyURI string
36 UserDID string
37 SubscribedPostURI string
38 CreatedAt int64
39}
40
41func (s *Store) AddRepliedPost(replyPost ReplyPost) error {
42 sql := `INSERT INTO replies (replyURI, userDID, subscribedPostURI, createdAt) VALUES (?, ?, ?, ?) ON CONFLICT(replyURI, userDID) DO NOTHING;`
43 _, err := s.db.Exec(sql, replyPost.ReplyURI, replyPost.UserDID, replyPost.SubscribedPostURI, replyPost.CreatedAt)
44 if err != nil {
45 return fmt.Errorf("exec insert replies post: %w", err)
46 }
47 return nil
48}
49
50func (s *Store) GetUsersReplies(usersDID string, cursor int64, limit int) ([]ReplyPost, error) {
51 sql := `SELECT id, replyURI, userDID, subscribedPostURI, createdAt FROM replies
52 WHERE userDID = ? AND createdAt < ?
53 ORDER BY createdAt DESC LIMIT ?;`
54 rows, err := s.db.Query(sql, usersDID, cursor, limit)
55 if err != nil {
56 return nil, fmt.Errorf("run query to get users replied posts: %w", err)
57 }
58 defer rows.Close()
59
60 repliedPosts := make([]ReplyPost, 0)
61 for rows.Next() {
62 var replyPost ReplyPost
63 if err := rows.Scan(&replyPost.ID, &replyPost.ReplyURI, &replyPost.UserDID, &replyPost.SubscribedPostURI, &replyPost.CreatedAt); err != nil {
64 return nil, fmt.Errorf("scan row: %w", err)
65 }
66 repliedPosts = append(repliedPosts, replyPost)
67 }
68
69 return repliedPosts, nil
70}
71
72func (s *Store) DeleteRepliedPostsForBookmarkedPostURIandUserDID(subscribedPostURI, userDID string) error {
73 sql := "DELETE FROM replies WHERE subscribedPostURI = ? AND userDID = ?;"
74 statement, err := s.db.Prepare(sql)
75 if err != nil {
76 return fmt.Errorf("prepare delete replies: %w", err)
77 }
78 res, err := statement.Exec(subscribedPostURI, userDID)
79 if err != nil {
80 return fmt.Errorf("exec delete replies: %w", err)
81 }
82
83 n, _ := res.RowsAffected()
84
85 slog.Info("delete replies result", "affected rows", n)
86 return nil
87}