alice.pds.demo.boltless.dev
This repository has no description
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
311func IsVouchSkipped(e Execer, did, subjectDid string) (bool, error) {
312 var exists bool
313 err := e.QueryRow(
314 `select exists(select 1 from vouch_skips where did = ? and subject_did = ?)`,
315 did, subjectDid,
316 ).Scan(&exists)
317 return exists, err
318}
319
320func SkipVouchSuggestion(e Execer, did, subjectDid string) error {
321 _, err := e.Exec(
322 `insert or ignore into vouch_skips (did, subject_did) values (?, ?)`,
323 did, subjectDid,
324 )
325 return err
326}
327
328// priority:
329// 1. collaborator invites sent
330// 2. knot member invites sent
331// 3. PR authors on FOO's repositories
332// 4. issue authors on FOO's repositories
333// 5. PR comment authors on FOO's repositories
334// 6. issue comment authors on FOO's repositories
335// 7. users FOO recently followed
336// 8. owners of repositories FOO recently starred
337func GetVouchSuggestions(e Execer, did string, limit int) ([]models.VouchSuggestion, error) {
338 query := `
339 select did, reason from (
340 select subject_did as did, 1 as priority, created,
341 'You invited this user to collaborate on a repository' as reason
342 from collaborators
343 where collaborators.did = ?
344 and subject_did != ?
345
346 union all
347
348 select subject as did, 2 as priority, created,
349 'You invited this user to your knot' as reason
350 from spindle_members
351 where spindle_members.did = ?
352 and subject != ?
353
354 union all
355
356 select p.owner_did as did, 3 as priority, p.created,
357 'This user opened a pull request on your repository' as reason
358 from pulls p
359 join repos r on r.at_uri = p.repo_at
360 where r.did = ?
361 and p.owner_did != ?
362
363 union all
364
365 select i.did as did, 4 as priority, i.created,
366 'This user opened an issue on your repository' as reason
367 from issues i
368 join repos r on r.at_uri = i.repo_at
369 where r.did = ?
370 and i.did != ?
371
372 union all
373
374 select pc.owner_did as did, 5 as priority, pc.created,
375 'This user commented on a pull request on your repository' as reason
376 from pull_comments pc
377 join repos r on r.at_uri = pc.repo_at
378 where r.did = ?
379 and pc.owner_did != ?
380
381 union all
382
383 select ic.did as did, 6 as priority, ic.created,
384 'This user commented on an issue on your repository' as reason
385 from issue_comments ic
386 join issues i on i.at_uri = ic.issue_at
387 join repos r on r.at_uri = i.repo_at
388 where r.did = ?
389 and ic.did != ?
390
391 union all
392
393 select f.subject_did as did, 7 as priority, f.followed_at as created,
394 'You recently followed this user' as reason
395 from follows f
396 where f.user_did = ?
397 and f.subject_did != ?
398
399 union all
400
401 select r.did as did, 8 as priority, s.created,
402 'You recently starred a repository by this user' as reason
403 from stars s
404 join repos r on r.at_uri = s.subject_at
405 where s.did = ?
406 and r.did != ?
407 )
408 where did not in (
409 select subject_did from vouches where vouches.did = ?
410 union
411 select subject_did from vouch_skips where vouch_skips.did = ?
412 )
413 group by did
414 order by min(priority) asc, max(created) desc
415 limit ?
416 `
417
418 args := []any{
419 did, did, // collaborators
420 did, did, // spindle_members
421 did, did, // pulls
422 did, did, // issues
423 did, did, // pull_comments
424 did, did, // issue_comments
425 did, did, // follows
426 did, did, // stars
427 did, did, // existing vouches + skips exclusion
428 limit,
429 }
430
431 rows, err := e.Query(query, args...)
432 if err != nil {
433 return nil, fmt.Errorf("GetVouchSuggestions: %w", err)
434 }
435 defer rows.Close()
436
437 var suggestions []models.VouchSuggestion
438 for rows.Next() {
439 var s models.VouchSuggestion
440 if err := rows.Scan(&s.Did, &s.Reason); err != nil {
441 log.Println("error scanning vouch suggestion:", err)
442 continue
443 }
444 suggestions = append(suggestions, s)
445 }
446 return suggestions, nil
447}