Monorepo for Tangled
tangled.org
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "strings"
8 "time"
9
10 "github.com/bluesky-social/indigo/atproto/syntax"
11 "github.com/ipfs/go-cid"
12 "tangled.org/core/appview/models"
13 "tangled.org/core/appview/pagination"
14 "tangled.org/core/orm"
15)
16
17func AddVouch(e Execer, vouch *models.Vouch) error {
18 // insert if not exists
19 _, err := e.Exec(
20 `insert or ignore into vouches (did, subject_did, cid, kind, reason) values (?, ?, ?, ?, ?)`,
21 vouch.Did, vouch.SubjectDid, vouch.Cid.String(), vouch.Kind, vouch.Reason,
22 )
23 if err != nil {
24 return err
25 }
26
27 // then update
28 _, err = e.Exec(
29 `update vouches set cid = ?, kind = ?, reason = ? where did = ? and subject_did = ?`,
30 vouch.Cid.String(), vouch.Kind, vouch.Reason, vouch.Did, vouch.SubjectDid,
31 )
32 if err != nil {
33 return err
34 }
35
36 // replace evidences: delete all existing, then insert new ones.
37 _, err = e.Exec(
38 `delete from vouch_evidences where vouch_id = (select id from vouches where did = ? and subject_did = ?)`,
39 vouch.Did, vouch.SubjectDid,
40 )
41 if err != nil {
42 return err
43 }
44 for _, uri := range vouch.Evidences {
45 _, err = e.Exec(
46 `insert into vouch_evidences (vouch_id, at_uri)
47 values ((select id from vouches where did = ? and subject_did = ?), ?)`,
48 vouch.Did, vouch.SubjectDid, uri.String(),
49 )
50 if err != nil {
51 return err
52 }
53 }
54 return nil
55}
56
57func GetVouch(e Execer, did, subjectDid string) (*models.Vouch, error) {
58 vouches, err := GetVouches(e, pagination.Page{Limit: 1},
59 orm.FilterEq("did", did),
60 orm.FilterEq("subject_did", subjectDid),
61 )
62 if err != nil {
63 return nil, err
64 }
65 if len(vouches) == 0 {
66 return nil, sql.ErrNoRows
67 }
68 return &vouches[0], nil
69}
70
71func GetVouches(e Execer, page pagination.Page, filters ...orm.Filter) ([]models.Vouch, error) {
72 var conditions []string
73 var args []any
74 for _, filter := range filters {
75 conditions = append(conditions, filter.Condition())
76 args = append(args, filter.Arg()...)
77 }
78
79 whereClause := ""
80 if len(conditions) > 0 {
81 whereClause = "where " + strings.Join(conditions, " and ")
82 }
83
84 pageClause := ""
85 if page.Limit > 0 {
86 pageClause = fmt.Sprintf("limit %d offset %d", page.Limit, page.Offset)
87 }
88
89 query := fmt.Sprintf(
90 `select did, subject_did, cid, kind, reason, created_at
91 from vouches
92 %s
93 order by created_at desc
94 %s`,
95 whereClause, pageClause)
96
97 rows, err := e.Query(query, args...)
98 if err != nil {
99 return nil, err
100 }
101 defer rows.Close()
102
103 var vouches []models.Vouch
104 for rows.Next() {
105 var v models.Vouch
106 var cidStr string
107 var createdAt string
108 var reason sql.NullString
109
110 if err := rows.Scan(&v.Did, &v.SubjectDid, &cidStr, &v.Kind, &reason, &createdAt); err != nil {
111 log.Println("error scanning vouch:", err)
112 continue
113 }
114
115 v.Cid, err = cid.Parse(cidStr)
116 if err != nil {
117 log.Println("unable to parse CID:", err)
118 continue
119 }
120
121 t, err := time.Parse(time.RFC3339, createdAt)
122 if err != nil {
123 log.Println("unable to determine created at time")
124 v.CreatedAt = time.Now()
125 } else {
126 v.CreatedAt = t
127 }
128
129 if reason.Valid {
130 v.Reason = &reason.String
131 }
132
133 vouches = append(vouches, v)
134 }
135 return vouches, nil
136}
137
138func GetVouchEvidences(e Execer, did, subjectDid string) ([]syntax.ATURI, error) {
139 rows, err := e.Query(
140 `select at_uri from vouch_evidences
141 where vouch_id = (select id from vouches where did = ? and subject_did = ?)
142 order by id asc`,
143 did, subjectDid,
144 )
145 if err != nil {
146 return nil, err
147 }
148 defer rows.Close()
149
150 var evidences []syntax.ATURI
151 for rows.Next() {
152 var uri string
153 if err := rows.Scan(&uri); err != nil {
154 log.Println("error scanning vouch evidence:", err)
155 continue
156 }
157 evidences = append(evidences, syntax.ATURI(uri))
158 }
159 return evidences, nil
160}
161
162func DeleteVouch(e Execer, did, subjectDid string) error {
163 _, err := e.Exec(`delete from vouches where did = ? and subject_did = ?`, did, subjectDid)
164 return err
165}
166
167func DeleteVouchByRkey(e Execer, did, rkey string) error {
168 _, err := e.Exec(`delete from vouches where did = ? and subject_did = ?`, did, rkey)
169 return err
170}
171
172func GetNetworkVouchTimeline(e Execer, viewerDid, profileDid string, page pagination.Page) ([]models.Vouch, error) {
173 pageClause := ""
174 if page.Limit > 0 {
175 pageClause = fmt.Sprintf("limit %d offset %d", page.Limit, page.Offset)
176 }
177
178 query := fmt.Sprintf(
179 `select v.did, v.subject_did, v.cid, v.kind, v.reason, v.created_at,
180 group_concat(ve.at_uri, '|') as evidences
181 from vouches v
182 left join vouch_evidences ve on ve.vouch_id = v.id
183 where (
184 v.subject_did = ? and v.did in (select subject_did from vouches where did = ? and kind = 'vouch')
185 ) or (
186 v.did = ? and v.subject_did in (select subject_did from vouches where did = ? and kind = 'vouch')
187 )
188 group by v.did, v.subject_did
189 order by v.created_at desc
190 %s`,
191 pageClause)
192
193 rows, err := e.Query(query, profileDid, viewerDid, profileDid, viewerDid)
194 if err != nil {
195 return nil, err
196 }
197 defer rows.Close()
198
199 var vouches []models.Vouch
200 for rows.Next() {
201 var v models.Vouch
202 var cidStr string
203 var createdAt string
204 var reason sql.NullString
205 var evidences sql.NullString
206
207 if err := rows.Scan(&v.Did, &v.SubjectDid, &cidStr, &v.Kind, &reason, &createdAt, &evidences); err != nil {
208 log.Println("error scanning vouch:", err)
209 continue
210 }
211
212 v.Cid, err = cid.Parse(cidStr)
213 if err != nil {
214 log.Println("unable to parse CID:", err)
215 continue
216 }
217
218 t, err := time.Parse(time.RFC3339, createdAt)
219 if err != nil {
220 log.Println("unable to determine created at time")
221 v.CreatedAt = time.Now()
222 } else {
223 v.CreatedAt = t
224 }
225
226 if reason.Valid {
227 v.Reason = &reason.String
228 }
229
230 if evidences.Valid && evidences.String != "" {
231 for _, s := range strings.Split(evidences.String, "|") {
232 v.Evidences = append(v.Evidences, syntax.ATURI(s))
233 }
234 }
235
236 vouches = append(vouches, v)
237 }
238 return vouches, nil
239}
240
241func GetVouchRelationshipsBatch(e Execer, viewerDid syntax.DID, subjectDids []syntax.DID) (map[syntax.DID]*models.VouchRelationship, error) {
242 if viewerDid == "" {
243 return nil, fmt.Errorf("viewerDid cannot be empty")
244 }
245
246 result := make(map[syntax.DID]*models.VouchRelationship)
247 for _, subjectDid := range subjectDids {
248 result[subjectDid] = &models.VouchRelationship{
249 ViewerDid: viewerDid,
250 SubjectDid: subjectDid,
251 NetworkVouches: []models.Vouch{},
252 }
253 }
254
255 if len(subjectDids) == 0 {
256 return result, nil
257 }
258
259 directVouches, err := GetVouches(e, pagination.Page{},
260 orm.FilterEq("did", viewerDid),
261 orm.FilterIn("subject_did", subjectDids),
262 )
263 if err != nil {
264 return nil, err
265 }
266 for _, v := range directVouches {
267 if rel, ok := result[v.SubjectDid]; ok {
268 rel.NetworkVouches = append(rel.NetworkVouches, v)
269 }
270 }
271
272 networkVouches, err := GetVouches(e, pagination.Page{},
273 orm.FilterEq("did", viewerDid),
274 orm.FilterEq("kind", string(models.VouchKindVouch)),
275 )
276 if err != nil {
277 return nil, err
278 }
279
280 network := make([]syntax.DID, 0, len(networkVouches))
281 for _, v := range networkVouches {
282 network = append(network, v.SubjectDid)
283 }
284
285 if len(network) > 0 {
286 networkToSubject, err := GetVouches(e, pagination.Page{},
287 orm.FilterIn("subject_did", subjectDids),
288 orm.FilterIn("did", network),
289 )
290 if err != nil {
291 return nil, err
292 }
293 for _, v := range networkToSubject {
294 if rel, ok := result[v.SubjectDid]; ok {
295 rel.NetworkVouches = append(rel.NetworkVouches, v)
296 }
297 }
298 }
299
300 return result, nil
301}
302
303func GetVouchRelationship(e Execer, viewerDid, subjectDid syntax.DID) (*models.VouchRelationship, error) {
304 batch, err := GetVouchRelationshipsBatch(e, viewerDid, []syntax.DID{subjectDid})
305 if err != nil {
306 return nil, err
307 }
308 return batch[subjectDid], nil
309}
310
311// priority:
312// 1. collaborator invites sent
313// 2. knot member invites sent
314// 3. PR authors on FOO's repositories
315// 4. issue authors on FOO's repositories
316// 5. PR comment authors on FOO's repositories
317// 6. issue comment authors on FOO's repositories
318// 7. users FOO recently followed
319// 8. owners of repositories FOO recently starred
320func GetVouchSuggestions(e Execer, did string, limit int) ([]models.VouchSuggestion, error) {
321 query := `
322 select did, reason from (
323 select subject_did as did, 1 as priority, created,
324 'You invited this user to collaborate on a repository' as reason
325 from collaborators
326 where collaborators.did = ?
327 and subject_did != ?
328
329 union all
330
331 select subject as did, 2 as priority, created,
332 'You invited this user to your knot' as reason
333 from spindle_members
334 where spindle_members.did = ?
335 and subject != ?
336
337 union all
338
339 select p.owner_did as did, 3 as priority, p.created,
340 'This user opened a pull request on your repository' as reason
341 from pulls p
342 join repos r on r.at_uri = p.repo_at
343 where r.did = ?
344 and p.owner_did != ?
345
346 union all
347
348 select i.did as did, 4 as priority, i.created,
349 'This user opened an issue on your repository' as reason
350 from issues i
351 join repos r on r.at_uri = i.repo_at
352 where r.did = ?
353 and i.did != ?
354
355 union all
356
357 select pc.owner_did as did, 5 as priority, pc.created,
358 'This user commented on a pull request on your repository' as reason
359 from pull_comments pc
360 join repos r on r.at_uri = pc.repo_at
361 where r.did = ?
362 and pc.owner_did != ?
363
364 union all
365
366 select ic.did as did, 6 as priority, ic.created,
367 'This user commented on an issue on your repository' as reason
368 from issue_comments ic
369 join issues i on i.at_uri = ic.issue_at
370 join repos r on r.at_uri = i.repo_at
371 where r.did = ?
372 and ic.did != ?
373
374 union all
375
376 select f.subject_did as did, 7 as priority, f.followed_at as created,
377 'You recently followed this user' as reason
378 from follows f
379 where f.user_did = ?
380 and f.subject_did != ?
381
382 union all
383
384 select r.did as did, 8 as priority, s.created,
385 'You recently starred a repository by this user' as reason
386 from stars s
387 join repos r on r.at_uri = s.subject_at
388 where s.did = ?
389 and r.did != ?
390 )
391 where did not in (
392 select subject_did from vouches where vouches.did = ?
393 )
394 group by did
395 order by min(priority) asc, max(created) desc
396 limit ?
397 `
398
399 args := []any{
400 did, did, // collaborators
401 did, did, // spindle_members
402 did, did, // pulls
403 did, did, // issues
404 did, did, // pull_comments
405 did, did, // issue_comments
406 did, did, // follows
407 did, did, // stars
408 did, // vouches exclusion
409 limit,
410 }
411
412 rows, err := e.Query(query, args...)
413 if err != nil {
414 return nil, fmt.Errorf("GetVouchSuggestions: %w", err)
415 }
416 defer rows.Close()
417
418 var suggestions []models.VouchSuggestion
419 for rows.Next() {
420 var s models.VouchSuggestion
421 if err := rows.Scan(&s.Did, &s.Reason); err != nil {
422 log.Println("error scanning vouch suggestion:", err)
423 continue
424 }
425 suggestions = append(suggestions, s)
426 }
427 return suggestions, nil
428}