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 CountNetworkVouchTimeline(e Execer, viewerDid, profileDid string) (int, error) {
173 var count int
174 err := e.QueryRow(
175 `select count(*) from (
176 select v.id from vouches v
177 where (
178 v.subject_did = ? and v.did in (select subject_did from vouches where did = ? and kind = 'vouch')
179 ) or (
180 v.did = ? and v.subject_did in (select subject_did from vouches where did = ? and kind = 'vouch')
181 )
182 group by v.did, v.subject_did
183 )`,
184 profileDid, viewerDid, profileDid, viewerDid,
185 ).Scan(&count)
186 return count, err
187}
188
189func GetNetworkVouchTimeline(e Execer, viewerDid, profileDid string, page pagination.Page) ([]models.Vouch, error) {
190 pageClause := ""
191 if page.Limit > 0 {
192 pageClause = fmt.Sprintf("limit %d offset %d", page.Limit, page.Offset)
193 }
194
195 query := fmt.Sprintf(
196 `select v.did, v.subject_did, v.cid, v.kind, v.reason, v.created_at,
197 group_concat(ve.at_uri, '|') as evidences
198 from vouches v
199 left join vouch_evidences ve on ve.vouch_id = v.id
200 where (
201 v.subject_did = ? and v.did in (select subject_did from vouches where did = ? and kind = 'vouch')
202 ) or (
203 v.did = ? and v.subject_did in (select subject_did from vouches where did = ? and kind = 'vouch')
204 )
205 group by v.did, v.subject_did
206 order by v.created_at desc
207 %s`,
208 pageClause)
209
210 rows, err := e.Query(query, profileDid, viewerDid, profileDid, viewerDid)
211 if err != nil {
212 return nil, err
213 }
214 defer rows.Close()
215
216 var vouches []models.Vouch
217 for rows.Next() {
218 var v models.Vouch
219 var cidStr string
220 var createdAt string
221 var reason sql.NullString
222 var evidences sql.NullString
223
224 if err := rows.Scan(&v.Did, &v.SubjectDid, &cidStr, &v.Kind, &reason, &createdAt, &evidences); err != nil {
225 log.Println("error scanning vouch:", err)
226 continue
227 }
228
229 v.Cid, err = cid.Parse(cidStr)
230 if err != nil {
231 log.Println("unable to parse CID:", err)
232 continue
233 }
234
235 t, err := time.Parse(time.RFC3339, createdAt)
236 if err != nil {
237 log.Println("unable to determine created at time")
238 v.CreatedAt = time.Now()
239 } else {
240 v.CreatedAt = t
241 }
242
243 if reason.Valid {
244 v.Reason = &reason.String
245 }
246
247 if evidences.Valid && evidences.String != "" {
248 for _, s := range strings.Split(evidences.String, "|") {
249 v.Evidences = append(v.Evidences, syntax.ATURI(s))
250 }
251 }
252
253 vouches = append(vouches, v)
254 }
255 return vouches, nil
256}
257
258func GetVouchRelationshipsBatch(e Execer, viewerDid syntax.DID, subjectDids []syntax.DID) (map[syntax.DID]*models.VouchRelationship, error) {
259 if viewerDid == "" {
260 return nil, fmt.Errorf("viewerDid cannot be empty")
261 }
262
263 result := make(map[syntax.DID]*models.VouchRelationship)
264 for _, subjectDid := range subjectDids {
265 result[subjectDid] = &models.VouchRelationship{
266 ViewerDid: viewerDid,
267 SubjectDid: subjectDid,
268 NetworkVouches: []models.Vouch{},
269 }
270 }
271
272 if len(subjectDids) == 0 {
273 return result, nil
274 }
275
276 directVouches, err := GetVouches(e, pagination.Page{},
277 orm.FilterEq("did", viewerDid),
278 orm.FilterIn("subject_did", subjectDids),
279 )
280 if err != nil {
281 return nil, err
282 }
283 for _, v := range directVouches {
284 if rel, ok := result[v.SubjectDid]; ok {
285 rel.NetworkVouches = append(rel.NetworkVouches, v)
286 }
287 }
288
289 networkVouches, err := GetVouches(e, pagination.Page{},
290 orm.FilterEq("did", viewerDid),
291 orm.FilterEq("kind", string(models.VouchKindVouch)),
292 )
293 if err != nil {
294 return nil, err
295 }
296
297 network := make([]syntax.DID, 0, len(networkVouches))
298 for _, v := range networkVouches {
299 network = append(network, v.SubjectDid)
300 }
301
302 if len(network) > 0 {
303 networkToSubject, err := GetVouches(e, pagination.Page{},
304 orm.FilterIn("subject_did", subjectDids),
305 orm.FilterIn("did", network),
306 )
307 if err != nil {
308 return nil, err
309 }
310 for _, v := range networkToSubject {
311 if rel, ok := result[v.SubjectDid]; ok {
312 rel.NetworkVouches = append(rel.NetworkVouches, v)
313 }
314 }
315 }
316
317 return result, nil
318}
319
320func GetVouchRelationship(e Execer, viewerDid, subjectDid syntax.DID) (*models.VouchRelationship, error) {
321 batch, err := GetVouchRelationshipsBatch(e, viewerDid, []syntax.DID{subjectDid})
322 if err != nil {
323 return nil, err
324 }
325 return batch[subjectDid], nil
326}
327
328func IsVouchSkipped(e Execer, did, subjectDid string) (bool, error) {
329 var exists bool
330 err := e.QueryRow(
331 `select exists(select 1 from vouch_skips where did = ? and subject_did = ?)`,
332 did, subjectDid,
333 ).Scan(&exists)
334 return exists, err
335}
336
337func SkipVouchSuggestion(e Execer, did, subjectDid string) error {
338 _, err := e.Exec(
339 `insert or ignore into vouch_skips (did, subject_did) values (?, ?)`,
340 did, subjectDid,
341 )
342 return err
343}
344
345// priority:
346// 1. collaborator invites sent
347// 2. knot member invites sent
348// 3. PR authors on FOO's repositories
349// 4. issue authors on FOO's repositories
350// 5. PR comment authors on FOO's repositories
351// 6. issue comment authors on FOO's repositories
352// 7. users FOO recently followed
353// 8. owners of repositories FOO recently starred
354func GetVouchSuggestions(e Execer, did string, limit int) ([]models.VouchSuggestion, error) {
355 query := `
356 select did, reason from (
357 select subject_did as did, 1 as priority, created,
358 'You invited this user to collaborate on a repository' as reason
359 from collaborators
360 where collaborators.did = ?
361 and subject_did != ?
362
363 union all
364
365 select subject as did, 2 as priority, created,
366 'You invited this user to your knot' as reason
367 from spindle_members
368 where spindle_members.did = ?
369 and subject != ?
370
371 union all
372
373 select p.owner_did as did, 3 as priority, p.created,
374 'This user opened a pull request on your repository' as reason
375 from pulls p
376 join repos r on r.repo_did = p.repo_did
377 where r.did = ?
378 and p.owner_did != ?
379
380 union all
381
382 select i.did as did, 4 as priority, i.created,
383 'This user opened an issue on your repository' as reason
384 from issues i
385 join repos r on r.repo_did = i.repo_did
386 where r.did = ?
387 and i.did != ?
388
389 union all
390
391 select c.did as did, 5 as priority, c.created,
392 'This user commented on a pull request on your repository' as reason
393 from comments c
394 join pulls p on p.at_uri = c.subject_uri
395 join repos r on r.repo_did = p.repo_did
396 where r.did = ?
397 and c.did != ?
398
399 union all
400
401 select c.did as did, 6 as priority, c.created,
402 'This user commented on an issue on your repository' as reason
403 from comments c
404 join issues i on i.at_uri = c.subject_uri
405 join repos r on r.repo_did = i.repo_did
406 where r.did = ?
407 and c.did != ?
408
409 union all
410
411 select f.subject_did as did, 7 as priority, f.followed_at as created,
412 'You recently followed this user' as reason
413 from follows f
414 where f.user_did = ?
415 and f.subject_did != ?
416
417 union all
418
419 select r.did as did, 8 as priority, s.created,
420 'You recently starred a repository by this user' as reason
421 from stars s
422 join repos r on r.at_uri = s.subject
423 where s.did = ?
424 and r.did != ?
425 )
426 where did not in (
427 select subject_did from vouches where vouches.did = ?
428 union
429 select subject_did from vouch_skips where vouch_skips.did = ?
430 )
431 group by did
432 order by min(priority) asc, max(created) desc
433 limit ?
434 `
435
436 args := []any{
437 did, did, // collaborators
438 did, did, // spindle_members
439 did, did, // pulls
440 did, did, // issues
441 did, did, // pull_comments
442 did, did, // issue_comments
443 did, did, // follows
444 did, did, // stars
445 did, did, // existing vouches + skips exclusion
446 limit,
447 }
448
449 rows, err := e.Query(query, args...)
450 if err != nil {
451 return nil, fmt.Errorf("GetVouchSuggestions: %w", err)
452 }
453 defer rows.Close()
454
455 var suggestions []models.VouchSuggestion
456 for rows.Next() {
457 var s models.VouchSuggestion
458 if err := rows.Scan(&s.Did, &s.Reason); err != nil {
459 log.Println("error scanning vouch suggestion:", err)
460 continue
461 }
462 suggestions = append(suggestions, s)
463 }
464 return suggestions, nil
465}