Monorepo for Tangled
tangled.org
1package db
2
3import (
4 "database/sql"
5 "encoding/json"
6 "fmt"
7 "sort"
8 "strings"
9 "time"
10
11 "github.com/bluesky-social/indigo/api/atproto"
12 "github.com/bluesky-social/indigo/atproto/syntax"
13 "tangled.org/core/appview/models"
14 "tangled.org/core/orm"
15)
16
17func PutComment(tx *sql.Tx, c *models.Comment, references []syntax.ATURI) (bool, error) {
18 var bodyBlobs, replyToUri, replyToCid *string
19 if len(c.Body.Blobs) > 0 {
20 encoded, err := json.Marshal(c.Body.Blobs)
21 if err != nil {
22 return false, fmt.Errorf("encoding blobs to json: %w", err)
23 }
24 encodedStr := string(encoded)
25 bodyBlobs = &encodedStr
26 }
27 if c.ReplyTo != nil {
28 replyToUri = &c.ReplyTo.Uri
29 replyToCid = &c.ReplyTo.Cid
30 }
31 result, err := tx.Exec(
32 // users can change the 'created' date.
33 // skip update entirely if cid is unchanged.
34 `insert into comments (
35 did,
36 collection,
37 rkey,
38 cid,
39 subject_uri,
40 subject_cid,
41 body_text,
42 body_original,
43 body_blobs,
44 created,
45 reply_to_uri,
46 reply_to_cid,
47 pull_round_idx
48 )
49 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
50 on conflict(did, collection, rkey)
51 do update set
52 cid = excluded.cid,
53 subject_uri = excluded.subject_uri,
54 subject_cid = excluded.subject_cid,
55 body_text = excluded.body_text,
56 body_original = excluded.body_original,
57 body_blobs = excluded.body_blobs,
58 created = excluded.created,
59 reply_to_uri = excluded.reply_to_uri,
60 reply_to_cid = excluded.reply_to_cid,
61 pull_round_idx = excluded.pull_round_idx,
62 edited = ?
63 where comments.cid is not excluded.cid`,
64 c.Did,
65 c.Collection,
66 c.Rkey,
67 c.Cid,
68 c.Subject.Uri,
69 c.Subject.Cid,
70 c.Body.Text,
71 c.Body.Original,
72 bodyBlobs,
73 c.Created.Format(time.RFC3339),
74 replyToUri,
75 replyToCid,
76 c.PullRoundIdx,
77 time.Now().Format(time.RFC3339),
78 )
79 if err != nil {
80 return false, err
81 }
82
83 c.Id, err = result.LastInsertId()
84 if err != nil {
85 return false, err
86 }
87
88 affected, err := result.RowsAffected()
89 if err != nil {
90 return false, err
91 }
92
93 if affected < 1 {
94 return false, nil
95 }
96
97 // update references when comment is updated
98 if err := putReferences(tx, c.AtUri(), references); err != nil {
99 return false, fmt.Errorf("put reference_links: %w", err)
100 }
101
102 return true, nil
103}
104
105// PurgeComments actually purges a comment row from db instead of marking it as "deleted"
106func PurgeComments(e Execer, filters ...orm.Filter) error {
107 var conditions []string
108 var args []any
109 for _, filter := range filters {
110 conditions = append(conditions, filter.Condition())
111 args = append(args, filter.Arg()...)
112 }
113
114 whereClause := ""
115 if conditions != nil {
116 whereClause = " where " + strings.Join(conditions, " and ")
117 }
118
119 _, err := e.Exec(fmt.Sprintf(`delete from comments %s`, whereClause), args...)
120 return err
121}
122
123func DeleteComments(e Execer, filters ...orm.Filter) error {
124 var conditions []string
125 var args []any
126 for _, filter := range filters {
127 conditions = append(conditions, filter.Condition())
128 args = append(args, filter.Arg()...)
129 }
130
131 whereClause := ""
132 if conditions != nil {
133 whereClause = " where " + strings.Join(conditions, " and ")
134 }
135
136 query := fmt.Sprintf(
137 `update comments
138 set body_text = "",
139 body_original = null,
140 body_blobs = null,
141 deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now')
142 %s`,
143 whereClause,
144 )
145
146 _, err := e.Exec(query, args...)
147 return err
148}
149
150func GetComment(e Execer, filters ...orm.Filter) (models.Comment, error) {
151 comments, err := GetComments(e, filters...)
152 if err != nil {
153 return models.Comment{}, err
154 }
155 if len(comments) != 1 {
156 return models.Comment{}, fmt.Errorf("expected 1 comment, got %d", len(comments))
157 }
158 return comments[0], nil
159}
160
161func GetComments(e Execer, filters ...orm.Filter) ([]models.Comment, error) {
162 var comments []models.Comment
163
164 var conditions []string
165 var args []any
166 for _, filter := range filters {
167 conditions = append(conditions, filter.Condition())
168 args = append(args, filter.Arg()...)
169 }
170
171 whereClause := ""
172 if conditions != nil {
173 whereClause = " where " + strings.Join(conditions, " and ")
174 }
175
176 query := fmt.Sprintf(`
177 select
178 id,
179 did,
180 collection,
181 rkey,
182 cid,
183 subject_uri,
184 subject_cid,
185 body_text,
186 body_original,
187 body_blobs,
188 created,
189 reply_to_uri,
190 reply_to_cid,
191 pull_round_idx,
192 edited,
193 deleted
194 from
195 comments
196 %s
197 `, whereClause)
198
199 rows, err := e.Query(query, args...)
200 if err != nil {
201 return nil, err
202 }
203 defer rows.Close()
204
205 for rows.Next() {
206 var comment models.Comment
207 var created string
208 var cid, bodyBlobs, replyToUri, replyToCid, edited, deleted sql.Null[string]
209 err := rows.Scan(
210 &comment.Id,
211 &comment.Did,
212 &comment.Collection,
213 &comment.Rkey,
214 &cid,
215 &comment.Subject.Uri,
216 &comment.Subject.Cid,
217 &comment.Body.Text,
218 &comment.Body.Original,
219 &bodyBlobs,
220 &created,
221 &replyToUri,
222 &replyToCid,
223 &comment.PullRoundIdx,
224 &edited,
225 &deleted,
226 )
227 if err != nil {
228 return nil, err
229 }
230
231 if cid.Valid && cid.V != "" {
232 comment.Cid = syntax.CID(cid.V)
233 }
234
235 if bodyBlobs.Valid && bodyBlobs.V != "" {
236 if err := json.Unmarshal([]byte(bodyBlobs.V), &comment.Body.Blobs); err != nil {
237 return nil, fmt.Errorf("decoding blobs: %w", err)
238 }
239 }
240
241 if t, err := time.Parse(time.RFC3339, created); err == nil {
242 comment.Created = t
243 }
244
245 if replyToUri.Valid && replyToCid.Valid {
246 comment.ReplyTo = &atproto.RepoStrongRef{
247 Uri: replyToUri.V,
248 Cid: replyToCid.V,
249 }
250 }
251
252 if edited.Valid {
253 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
254 comment.Edited = &t
255 }
256 }
257
258 if deleted.Valid {
259 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
260 comment.Deleted = &t
261 }
262 }
263
264 comments = append(comments, comment)
265 }
266
267 if err := rows.Err(); err != nil {
268 return nil, err
269 }
270
271 sort.Slice(comments, func(i, j int) bool {
272 return comments[i].Created.Before(comments[j].Created)
273 })
274
275 return comments, nil
276}