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