kot.pink
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "strings"
7
8 "github.com/bluesky-social/indigo/atproto/syntax"
9 "tangled.org/core/api/tangled"
10 "tangled.org/core/appview/models"
11)
12
13// ValidateReferenceLinks resolves refLinks to Issue/PR/IssueComment/PullComment ATURIs.
14// It will ignore missing refLinks.
15func ValidateReferenceLinks(e Execer, refLinks []models.ReferenceLink) ([]syntax.ATURI, error) {
16 var (
17 issueRefs []models.ReferenceLink
18 pullRefs []models.ReferenceLink
19 )
20 for _, ref := range refLinks {
21 switch ref.Kind {
22 case models.RefKindIssue:
23 issueRefs = append(issueRefs, ref)
24 case models.RefKindPull:
25 pullRefs = append(pullRefs, ref)
26 }
27 }
28 issueUris, err := findIssueReferences(e, issueRefs)
29 if err != nil {
30 return nil, fmt.Errorf("find issue references: %w", err)
31 }
32 pullUris, err := findPullReferences(e, pullRefs)
33 if err != nil {
34 return nil, fmt.Errorf("find pull references: %w", err)
35 }
36
37 return append(issueUris, pullUris...), nil
38}
39
40func findIssueReferences(e Execer, refLinks []models.ReferenceLink) ([]syntax.ATURI, error) {
41 if len(refLinks) == 0 {
42 return nil, nil
43 }
44 vals := make([]string, len(refLinks))
45 args := make([]any, 0, len(refLinks)*4)
46 for i, ref := range refLinks {
47 vals[i] = "(?, ?, ?, ?)"
48 args = append(args, ref.Handle, ref.Repo, ref.SubjectId, ref.CommentId)
49 }
50 query := fmt.Sprintf(
51 `with input(owner_did, name, issue_id, comment_id) as (
52 values %s
53 )
54 select
55 i.did, i.rkey,
56 c.did, c.rkey
57 from input inp
58 join repos r
59 on r.did = inp.owner_did
60 and r.name = inp.name
61 join issues i
62 on i.repo_at = r.at_uri
63 and i.issue_id = inp.issue_id
64 left join issue_comments c
65 on inp.comment_id is not null
66 and c.issue_at = i.at_uri
67 and c.id = inp.comment_id
68 `,
69 strings.Join(vals, ","),
70 )
71 rows, err := e.Query(query, args...)
72 if err != nil {
73 return nil, err
74 }
75 defer rows.Close()
76
77 var uris []syntax.ATURI
78
79 for rows.Next() {
80 // Scan rows
81 var issueOwner, issueRkey string
82 var commentOwner, commentRkey sql.NullString
83 var uri syntax.ATURI
84 if err := rows.Scan(&issueOwner, &issueRkey, &commentOwner, &commentRkey); err != nil {
85 return nil, err
86 }
87 if commentOwner.Valid && commentRkey.Valid {
88 uri = syntax.ATURI(fmt.Sprintf(
89 "at://%s/%s/%s",
90 commentOwner.String,
91 tangled.RepoIssueCommentNSID,
92 commentRkey.String,
93 ))
94 } else {
95 uri = syntax.ATURI(fmt.Sprintf(
96 "at://%s/%s/%s",
97 issueOwner,
98 tangled.RepoIssueNSID,
99 issueRkey,
100 ))
101 }
102 uris = append(uris, uri)
103 }
104 if err := rows.Err(); err != nil {
105 return nil, fmt.Errorf("iterate rows: %w", err)
106 }
107
108 return uris, nil
109}
110
111func findPullReferences(e Execer, refLinks []models.ReferenceLink) ([]syntax.ATURI, error) {
112 if len(refLinks) == 0 {
113 return nil, nil
114 }
115 vals := make([]string, len(refLinks))
116 args := make([]any, 0, len(refLinks)*4)
117 for i, ref := range refLinks {
118 vals[i] = "(?, ?, ?, ?)"
119 args = append(args, ref.Handle, ref.Repo, ref.SubjectId, ref.CommentId)
120 }
121 query := fmt.Sprintf(
122 `with input(owner_did, name, pull_id, comment_id) as (
123 values %s
124 )
125 select
126 p.owner_did, p.rkey,
127 c.comment_at
128 from input inp
129 join repos r
130 on r.did = inp.owner_did
131 and r.name = inp.name
132 join pulls p
133 on p.repo_at = r.at_uri
134 and p.pull_id = inp.pull_id
135 left join pull_comments c
136 on inp.comment_id is not null
137 and c.repo_at = r.at_uri and c.pull_id = p.pull_id
138 and c.id = inp.comment_id
139 `,
140 strings.Join(vals, ","),
141 )
142 rows, err := e.Query(query, args...)
143 if err != nil {
144 return nil, err
145 }
146 defer rows.Close()
147
148 var uris []syntax.ATURI
149
150 for rows.Next() {
151 // Scan rows
152 var pullOwner, pullRkey string
153 var commentUri sql.NullString
154 var uri syntax.ATURI
155 if err := rows.Scan(&pullOwner, &pullRkey, &commentUri); err != nil {
156 return nil, err
157 }
158 if commentUri.Valid {
159 // no-op
160 uri = syntax.ATURI(commentUri.String)
161 } else {
162 uri = syntax.ATURI(fmt.Sprintf(
163 "at://%s/%s/%s",
164 pullOwner,
165 tangled.RepoPullNSID,
166 pullRkey,
167 ))
168 }
169 uris = append(uris, uri)
170 }
171 return uris, nil
172}
173
174func putReferences(tx *sql.Tx, fromAt syntax.ATURI, references []syntax.ATURI) error {
175 err := deleteReferences(tx, fromAt)
176 if err != nil {
177 return fmt.Errorf("delete old reference_links: %w", err)
178 }
179 if len(references) == 0 {
180 return nil
181 }
182
183 values := make([]string, 0, len(references))
184 args := make([]any, 0, len(references)*2)
185 for _, ref := range references {
186 values = append(values, "(?, ?)")
187 args = append(args, fromAt, ref)
188 }
189 _, err = tx.Exec(
190 fmt.Sprintf(
191 `insert into reference_links (from_at, to_at)
192 values %s`,
193 strings.Join(values, ","),
194 ),
195 args...,
196 )
197 if err != nil {
198 return fmt.Errorf("insert new reference_links: %w", err)
199 }
200 return nil
201}
202
203func deleteReferences(tx *sql.Tx, fromAt syntax.ATURI) error {
204 _, err := tx.Exec(`delete from reference_links where from_at = ?`, fromAt)
205 return err
206}
207
208func GetReferencesAll(e Execer, filters ...filter) (map[syntax.ATURI][]syntax.ATURI, error) {
209 var (
210 conditions []string
211 args []any
212 )
213 for _, filter := range filters {
214 conditions = append(conditions, filter.Condition())
215 args = append(args, filter.Arg()...)
216 }
217
218 whereClause := ""
219 if conditions != nil {
220 whereClause = " where " + strings.Join(conditions, " and ")
221 }
222
223 rows, err := e.Query(
224 fmt.Sprintf(
225 `select from_at, to_at from reference_links %s`,
226 whereClause,
227 ),
228 args...,
229 )
230 if err != nil {
231 return nil, fmt.Errorf("query reference_links: %w", err)
232 }
233 defer rows.Close()
234
235 result := make(map[syntax.ATURI][]syntax.ATURI)
236
237 for rows.Next() {
238 var from, to syntax.ATURI
239 if err := rows.Scan(&from, &to); err != nil {
240 return nil, fmt.Errorf("scan row: %w", err)
241 }
242
243 result[from] = append(result[from], to)
244 }
245 if err := rows.Err(); err != nil {
246 return nil, fmt.Errorf("iterate rows: %w", err)
247 }
248
249 return result, nil
250}
251
252func GetBacklinks(e Execer, target syntax.ATURI) ([]models.RichReferenceLink, error) {
253 rows, err := e.Query(
254 `select from_at from reference_links
255 where to_at = ?`,
256 target,
257 )
258 if err != nil {
259 return nil, fmt.Errorf("query backlinks: %w", err)
260 }
261 defer rows.Close()
262
263 var (
264 backlinks []models.RichReferenceLink
265 backlinksMap = make(map[string][]syntax.ATURI)
266 )
267 for rows.Next() {
268 var from syntax.ATURI
269 if err := rows.Scan(&from); err != nil {
270 return nil, fmt.Errorf("scan row: %w", err)
271 }
272 nsid := from.Collection().String()
273 backlinksMap[nsid] = append(backlinksMap[nsid], from)
274 }
275 if err := rows.Err(); err != nil {
276 return nil, fmt.Errorf("iterate rows: %w", err)
277 }
278
279 var ls []models.RichReferenceLink
280 ls, err = getIssueBacklinks(e, backlinksMap[tangled.RepoIssueNSID])
281 if err != nil {
282 return nil, fmt.Errorf("get issue backlinks: %w", err)
283 }
284 backlinks = append(backlinks, ls...)
285 ls, err = getIssueCommentBacklinks(e, backlinksMap[tangled.RepoIssueCommentNSID])
286 if err != nil {
287 return nil, fmt.Errorf("get issue_comment backlinks: %w", err)
288 }
289 backlinks = append(backlinks, ls...)
290 ls, err = getPullBacklinks(e, backlinksMap[tangled.RepoPullNSID])
291 if err != nil {
292 return nil, fmt.Errorf("get pull backlinks: %w", err)
293 }
294 backlinks = append(backlinks, ls...)
295 ls, err = getPullCommentBacklinks(e, backlinksMap[tangled.RepoPullCommentNSID])
296 if err != nil {
297 return nil, fmt.Errorf("get pull_comment backlinks: %w", err)
298 }
299 backlinks = append(backlinks, ls...)
300
301 return backlinks, nil
302}
303
304func getIssueBacklinks(e Execer, aturis []syntax.ATURI) ([]models.RichReferenceLink, error) {
305 if len(aturis) == 0 {
306 return nil, nil
307 }
308 vals := make([]string, len(aturis))
309 args := make([]any, 0, len(aturis)*2)
310 for i, aturi := range aturis {
311 vals[i] = "(?, ?)"
312 did := aturi.Authority().String()
313 rkey := aturi.RecordKey().String()
314 args = append(args, did, rkey)
315 }
316 rows, err := e.Query(
317 fmt.Sprintf(
318 `select r.did, r.name, i.issue_id, i.title, i.open
319 from issues i
320 join repos r
321 on r.at_uri = i.repo_at
322 where (i.did, i.rkey) in (%s)`,
323 strings.Join(vals, ","),
324 ),
325 args...,
326 )
327 if err != nil {
328 return nil, err
329 }
330 defer rows.Close()
331 var refLinks []models.RichReferenceLink
332 for rows.Next() {
333 var l models.RichReferenceLink
334 l.Kind = models.RefKindIssue
335 if err := rows.Scan(&l.Handle, &l.Repo, &l.SubjectId, &l.Title, &l.State); err != nil {
336 return nil, err
337 }
338 refLinks = append(refLinks, l)
339 }
340 if err := rows.Err(); err != nil {
341 return nil, fmt.Errorf("iterate rows: %w", err)
342 }
343 return refLinks, nil
344}
345
346func getIssueCommentBacklinks(e Execer, aturis []syntax.ATURI) ([]models.RichReferenceLink, error) {
347 if len(aturis) == 0 {
348 return nil, nil
349 }
350 filter := FilterIn("c.at_uri", aturis)
351 rows, err := e.Query(
352 fmt.Sprintf(
353 `select r.did, r.name, i.issue_id, c.id, i.title, i.open
354 from issue_comments c
355 join issues i
356 on i.at_uri = c.issue_at
357 join repos r
358 on r.at_uri = i.repo_at
359 where %s`,
360 filter.Condition(),
361 ),
362 filter.Arg()...,
363 )
364 if err != nil {
365 return nil, err
366 }
367 defer rows.Close()
368 var refLinks []models.RichReferenceLink
369 for rows.Next() {
370 var l models.RichReferenceLink
371 l.Kind = models.RefKindIssue
372 l.CommentId = new(int)
373 if err := rows.Scan(&l.Handle, &l.Repo, &l.SubjectId, l.CommentId, &l.Title, &l.State); err != nil {
374 return nil, err
375 }
376 refLinks = append(refLinks, l)
377 }
378 if err := rows.Err(); err != nil {
379 return nil, fmt.Errorf("iterate rows: %w", err)
380 }
381 return refLinks, nil
382}
383
384func getPullBacklinks(e Execer, aturis []syntax.ATURI) ([]models.RichReferenceLink, error) {
385 if len(aturis) == 0 {
386 return nil, nil
387 }
388 vals := make([]string, len(aturis))
389 args := make([]any, 0, len(aturis)*2)
390 for i, aturi := range aturis {
391 vals[i] = "(?, ?)"
392 did := aturi.Authority().String()
393 rkey := aturi.RecordKey().String()
394 args = append(args, did, rkey)
395 }
396 rows, err := e.Query(
397 fmt.Sprintf(
398 `select r.did, r.name, p.pull_id, p.title, p.state
399 from pulls p
400 join repos r
401 on r.at_uri = p.repo_at
402 where (p.owner_did, p.rkey) in (%s)`,
403 strings.Join(vals, ","),
404 ),
405 args...,
406 )
407 if err != nil {
408 return nil, err
409 }
410 defer rows.Close()
411 var refLinks []models.RichReferenceLink
412 for rows.Next() {
413 var l models.RichReferenceLink
414 l.Kind = models.RefKindPull
415 if err := rows.Scan(&l.Handle, &l.Repo, &l.SubjectId, &l.Title, &l.State); err != nil {
416 return nil, err
417 }
418 refLinks = append(refLinks, l)
419 }
420 if err := rows.Err(); err != nil {
421 return nil, fmt.Errorf("iterate rows: %w", err)
422 }
423 return refLinks, nil
424}
425
426func getPullCommentBacklinks(e Execer, aturis []syntax.ATURI) ([]models.RichReferenceLink, error) {
427 if len(aturis) == 0 {
428 return nil, nil
429 }
430 filter := FilterIn("c.comment_at", aturis)
431 rows, err := e.Query(
432 fmt.Sprintf(
433 `select r.did, r.name, p.pull_id, c.id, p.title, p.state
434 from repos r
435 join pulls p
436 on r.at_uri = p.repo_at
437 join pull_comments c
438 on r.at_uri = c.repo_at and p.pull_id = c.pull_id
439 where %s`,
440 filter.Condition(),
441 ),
442 filter.Arg()...,
443 )
444 if err != nil {
445 return nil, err
446 }
447 defer rows.Close()
448 var refLinks []models.RichReferenceLink
449 for rows.Next() {
450 var l models.RichReferenceLink
451 l.Kind = models.RefKindPull
452 l.CommentId = new(int)
453 if err := rows.Scan(&l.Handle, &l.Repo, &l.SubjectId, l.CommentId, &l.Title, &l.State); err != nil {
454 return nil, err
455 }
456 refLinks = append(refLinks, l)
457 }
458 if err := rows.Err(); err != nil {
459 return nil, fmt.Errorf("iterate rows: %w", err)
460 }
461 return refLinks, nil
462}