this repo has no description
tangled.org
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "maps"
7 "slices"
8 "sort"
9 "strings"
10 "time"
11
12 "github.com/bluesky-social/indigo/atproto/syntax"
13 "tangled.org/core/api/tangled"
14 "tangled.org/core/appview/models"
15 "tangled.org/core/appview/pagination"
16)
17
18type Issue struct {
19 Id int64
20 Did string
21 Rkey string
22 RepoAt syntax.ATURI
23 IssueId int
24 Created time.Time
25 Edited *time.Time
26 Deleted *time.Time
27 Title string
28 Body string
29 Open bool
30
31 // optionally, populate this when querying for reverse mappings
32 // like comment counts, parent repo etc.
33 Comments []IssueComment
34 Labels models.LabelState
35 Repo *Repo
36}
37
38func (i *Issue) AtUri() syntax.ATURI {
39 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", i.Did, tangled.RepoIssueNSID, i.Rkey))
40}
41
42func (i *Issue) AsRecord() tangled.RepoIssue {
43 return tangled.RepoIssue{
44 Repo: i.RepoAt.String(),
45 Title: i.Title,
46 Body: &i.Body,
47 CreatedAt: i.Created.Format(time.RFC3339),
48 }
49}
50
51func (i *Issue) State() string {
52 if i.Open {
53 return "open"
54 }
55 return "closed"
56}
57
58type CommentListItem struct {
59 Self *IssueComment
60 Replies []*IssueComment
61}
62
63func (i *Issue) CommentList() []CommentListItem {
64 // Create a map to quickly find comments by their aturi
65 toplevel := make(map[string]*CommentListItem)
66 var replies []*IssueComment
67
68 // collect top level comments into the map
69 for _, comment := range i.Comments {
70 if comment.IsTopLevel() {
71 toplevel[comment.AtUri().String()] = &CommentListItem{
72 Self: &comment,
73 }
74 } else {
75 replies = append(replies, &comment)
76 }
77 }
78
79 for _, r := range replies {
80 parentAt := *r.ReplyTo
81 if parent, exists := toplevel[parentAt]; exists {
82 parent.Replies = append(parent.Replies, r)
83 }
84 }
85
86 var listing []CommentListItem
87 for _, v := range toplevel {
88 listing = append(listing, *v)
89 }
90
91 // sort everything
92 sortFunc := func(a, b *IssueComment) bool {
93 return a.Created.Before(b.Created)
94 }
95 sort.Slice(listing, func(i, j int) bool {
96 return sortFunc(listing[i].Self, listing[j].Self)
97 })
98 for _, r := range listing {
99 sort.Slice(r.Replies, func(i, j int) bool {
100 return sortFunc(r.Replies[i], r.Replies[j])
101 })
102 }
103
104 return listing
105}
106
107func (i *Issue) Participants() []string {
108 participantSet := make(map[string]struct{})
109 participants := []string{}
110
111 addParticipant := func(did string) {
112 if _, exists := participantSet[did]; !exists {
113 participantSet[did] = struct{}{}
114 participants = append(participants, did)
115 }
116 }
117
118 addParticipant(i.Did)
119
120 for _, c := range i.Comments {
121 addParticipant(c.Did)
122 }
123
124 return participants
125}
126
127func IssueFromRecord(did, rkey string, record tangled.RepoIssue) Issue {
128 created, err := time.Parse(time.RFC3339, record.CreatedAt)
129 if err != nil {
130 created = time.Now()
131 }
132
133 body := ""
134 if record.Body != nil {
135 body = *record.Body
136 }
137
138 return Issue{
139 RepoAt: syntax.ATURI(record.Repo),
140 Did: did,
141 Rkey: rkey,
142 Created: created,
143 Title: record.Title,
144 Body: body,
145 Open: true, // new issues are open by default
146 }
147}
148
149type IssueComment struct {
150 Id int64
151 Did string
152 Rkey string
153 IssueAt string
154 ReplyTo *string
155 Body string
156 Created time.Time
157 Edited *time.Time
158 Deleted *time.Time
159}
160
161func (i *IssueComment) AtUri() syntax.ATURI {
162 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", i.Did, tangled.RepoIssueCommentNSID, i.Rkey))
163}
164
165func (i *IssueComment) AsRecord() tangled.RepoIssueComment {
166 return tangled.RepoIssueComment{
167 Body: i.Body,
168 Issue: i.IssueAt,
169 CreatedAt: i.Created.Format(time.RFC3339),
170 ReplyTo: i.ReplyTo,
171 }
172}
173
174func (i *IssueComment) IsTopLevel() bool {
175 return i.ReplyTo == nil
176}
177
178func IssueCommentFromRecord(did, rkey string, record tangled.RepoIssueComment) (*IssueComment, error) {
179 created, err := time.Parse(time.RFC3339, record.CreatedAt)
180 if err != nil {
181 created = time.Now()
182 }
183
184 ownerDid := did
185
186 if _, err = syntax.ParseATURI(record.Issue); err != nil {
187 return nil, err
188 }
189
190 comment := IssueComment{
191 Did: ownerDid,
192 Rkey: rkey,
193 Body: record.Body,
194 IssueAt: record.Issue,
195 ReplyTo: record.ReplyTo,
196 Created: created,
197 }
198
199 return &comment, nil
200}
201
202func PutIssue(tx *sql.Tx, issue *Issue) error {
203 // ensure sequence exists
204 _, err := tx.Exec(`
205 insert or ignore into repo_issue_seqs (repo_at, next_issue_id)
206 values (?, 1)
207 `, issue.RepoAt)
208 if err != nil {
209 return err
210 }
211
212 issues, err := GetIssues(
213 tx,
214 FilterEq("did", issue.Did),
215 FilterEq("rkey", issue.Rkey),
216 )
217 switch {
218 case err != nil:
219 return err
220 case len(issues) == 0:
221 return createNewIssue(tx, issue)
222 case len(issues) != 1: // should be unreachable
223 return fmt.Errorf("invalid number of issues returned: %d", len(issues))
224 default:
225 // if content is identical, do not edit
226 existingIssue := issues[0]
227 if existingIssue.Title == issue.Title && existingIssue.Body == issue.Body {
228 return nil
229 }
230
231 issue.Id = existingIssue.Id
232 issue.IssueId = existingIssue.IssueId
233 return updateIssue(tx, issue)
234 }
235}
236
237func createNewIssue(tx *sql.Tx, issue *Issue) error {
238 // get next issue_id
239 var newIssueId int
240 err := tx.QueryRow(`
241 update repo_issue_seqs
242 set next_issue_id = next_issue_id + 1
243 where repo_at = ?
244 returning next_issue_id - 1
245 `, issue.RepoAt).Scan(&newIssueId)
246 if err != nil {
247 return err
248 }
249
250 // insert new issue
251 row := tx.QueryRow(`
252 insert into issues (repo_at, did, rkey, issue_id, title, body)
253 values (?, ?, ?, ?, ?, ?)
254 returning rowid, issue_id
255 `, issue.RepoAt, issue.Did, issue.Rkey, newIssueId, issue.Title, issue.Body)
256
257 return row.Scan(&issue.Id, &issue.IssueId)
258}
259
260func updateIssue(tx *sql.Tx, issue *Issue) error {
261 // update existing issue
262 _, err := tx.Exec(`
263 update issues
264 set title = ?, body = ?, edited = ?
265 where did = ? and rkey = ?
266 `, issue.Title, issue.Body, time.Now().Format(time.RFC3339), issue.Did, issue.Rkey)
267 return err
268}
269
270func GetIssuesPaginated(e Execer, page pagination.Page, filters ...filter) ([]Issue, error) {
271 issueMap := make(map[string]*Issue) // at-uri -> issue
272
273 var conditions []string
274 var args []any
275
276 for _, filter := range filters {
277 conditions = append(conditions, filter.Condition())
278 args = append(args, filter.Arg()...)
279 }
280
281 whereClause := ""
282 if conditions != nil {
283 whereClause = " where " + strings.Join(conditions, " and ")
284 }
285
286 pLower := FilterGte("row_num", page.Offset+1)
287 pUpper := FilterLte("row_num", page.Offset+page.Limit)
288
289 args = append(args, pLower.Arg()...)
290 args = append(args, pUpper.Arg()...)
291 pagination := " where " + pLower.Condition() + " and " + pUpper.Condition()
292
293 query := fmt.Sprintf(
294 `
295 select * from (
296 select
297 id,
298 did,
299 rkey,
300 repo_at,
301 issue_id,
302 title,
303 body,
304 open,
305 created,
306 edited,
307 deleted,
308 row_number() over (order by created desc) as row_num
309 from
310 issues
311 %s
312 ) ranked_issues
313 %s
314 `,
315 whereClause,
316 pagination,
317 )
318
319 rows, err := e.Query(query, args...)
320 if err != nil {
321 return nil, fmt.Errorf("failed to query issues table: %w", err)
322 }
323 defer rows.Close()
324
325 for rows.Next() {
326 var issue Issue
327 var createdAt string
328 var editedAt, deletedAt sql.Null[string]
329 var rowNum int64
330 err := rows.Scan(
331 &issue.Id,
332 &issue.Did,
333 &issue.Rkey,
334 &issue.RepoAt,
335 &issue.IssueId,
336 &issue.Title,
337 &issue.Body,
338 &issue.Open,
339 &createdAt,
340 &editedAt,
341 &deletedAt,
342 &rowNum,
343 )
344 if err != nil {
345 return nil, fmt.Errorf("failed to scan issue: %w", err)
346 }
347
348 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
349 issue.Created = t
350 }
351
352 if editedAt.Valid {
353 if t, err := time.Parse(time.RFC3339, editedAt.V); err == nil {
354 issue.Edited = &t
355 }
356 }
357
358 if deletedAt.Valid {
359 if t, err := time.Parse(time.RFC3339, deletedAt.V); err == nil {
360 issue.Deleted = &t
361 }
362 }
363
364 atUri := issue.AtUri().String()
365 issueMap[atUri] = &issue
366 }
367
368 // collect reverse repos
369 repoAts := make([]string, 0, len(issueMap)) // or just []string{}
370 for _, issue := range issueMap {
371 repoAts = append(repoAts, string(issue.RepoAt))
372 }
373
374 repos, err := GetRepos(e, 0, FilterIn("at_uri", repoAts))
375 if err != nil {
376 return nil, fmt.Errorf("failed to build repo mappings: %w", err)
377 }
378
379 repoMap := make(map[string]*Repo)
380 for i := range repos {
381 repoMap[string(repos[i].RepoAt())] = &repos[i]
382 }
383
384 for issueAt, i := range issueMap {
385 if r, ok := repoMap[string(i.RepoAt)]; ok {
386 i.Repo = r
387 } else {
388 // do not show up the issue if the repo is deleted
389 // TODO: foreign key where?
390 delete(issueMap, issueAt)
391 }
392 }
393
394 // collect comments
395 issueAts := slices.Collect(maps.Keys(issueMap))
396
397 comments, err := GetIssueComments(e, FilterIn("issue_at", issueAts))
398 if err != nil {
399 return nil, fmt.Errorf("failed to query comments: %w", err)
400 }
401 for i := range comments {
402 issueAt := comments[i].IssueAt
403 if issue, ok := issueMap[issueAt]; ok {
404 issue.Comments = append(issue.Comments, comments[i])
405 }
406 }
407
408 // collect allLabels for each issue
409 allLabels, err := GetLabels(e, FilterIn("subject", issueAts))
410 if err != nil {
411 return nil, fmt.Errorf("failed to query labels: %w", err)
412 }
413 for issueAt, labels := range allLabels {
414 if issue, ok := issueMap[issueAt.String()]; ok {
415 issue.Labels = labels
416 }
417 }
418
419 var issues []Issue
420 for _, i := range issueMap {
421 issues = append(issues, *i)
422 }
423
424 sort.Slice(issues, func(i, j int) bool {
425 return issues[i].Created.After(issues[j].Created)
426 })
427
428 return issues, nil
429}
430
431func GetIssues(e Execer, filters ...filter) ([]Issue, error) {
432 return GetIssuesPaginated(e, pagination.FirstPage(), filters...)
433}
434
435func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
436 query := `select id, owner_did, rkey, created, title, body, open from issues where repo_at = ? and issue_id = ?`
437 row := e.QueryRow(query, repoAt, issueId)
438
439 var issue Issue
440 var createdAt string
441 err := row.Scan(&issue.Id, &issue.Did, &issue.Rkey, &createdAt, &issue.Title, &issue.Body, &issue.Open)
442 if err != nil {
443 return nil, err
444 }
445
446 createdTime, err := time.Parse(time.RFC3339, createdAt)
447 if err != nil {
448 return nil, err
449 }
450 issue.Created = createdTime
451
452 return &issue, nil
453}
454
455func AddIssueComment(e Execer, c IssueComment) (int64, error) {
456 result, err := e.Exec(
457 `insert into issue_comments (
458 did,
459 rkey,
460 issue_at,
461 body,
462 reply_to,
463 created,
464 edited
465 )
466 values (?, ?, ?, ?, ?, ?, null)
467 on conflict(did, rkey) do update set
468 issue_at = excluded.issue_at,
469 body = excluded.body,
470 edited = case
471 when
472 issue_comments.issue_at != excluded.issue_at
473 or issue_comments.body != excluded.body
474 or issue_comments.reply_to != excluded.reply_to
475 then ?
476 else issue_comments.edited
477 end`,
478 c.Did,
479 c.Rkey,
480 c.IssueAt,
481 c.Body,
482 c.ReplyTo,
483 c.Created.Format(time.RFC3339),
484 time.Now().Format(time.RFC3339),
485 )
486 if err != nil {
487 return 0, err
488 }
489
490 id, err := result.LastInsertId()
491 if err != nil {
492 return 0, err
493 }
494
495 return id, nil
496}
497
498func DeleteIssueComments(e Execer, filters ...filter) error {
499 var conditions []string
500 var args []any
501 for _, filter := range filters {
502 conditions = append(conditions, filter.Condition())
503 args = append(args, filter.Arg()...)
504 }
505
506 whereClause := ""
507 if conditions != nil {
508 whereClause = " where " + strings.Join(conditions, " and ")
509 }
510
511 query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
512
513 _, err := e.Exec(query, args...)
514 return err
515}
516
517func GetIssueComments(e Execer, filters ...filter) ([]IssueComment, error) {
518 var comments []IssueComment
519
520 var conditions []string
521 var args []any
522 for _, filter := range filters {
523 conditions = append(conditions, filter.Condition())
524 args = append(args, filter.Arg()...)
525 }
526
527 whereClause := ""
528 if conditions != nil {
529 whereClause = " where " + strings.Join(conditions, " and ")
530 }
531
532 query := fmt.Sprintf(`
533 select
534 id,
535 did,
536 rkey,
537 issue_at,
538 reply_to,
539 body,
540 created,
541 edited,
542 deleted
543 from
544 issue_comments
545 %s
546 `, whereClause)
547
548 rows, err := e.Query(query, args...)
549 if err != nil {
550 return nil, err
551 }
552
553 for rows.Next() {
554 var comment IssueComment
555 var created string
556 var rkey, edited, deleted, replyTo sql.Null[string]
557 err := rows.Scan(
558 &comment.Id,
559 &comment.Did,
560 &rkey,
561 &comment.IssueAt,
562 &replyTo,
563 &comment.Body,
564 &created,
565 &edited,
566 &deleted,
567 )
568 if err != nil {
569 return nil, err
570 }
571
572 // this is a remnant from old times, newer comments always have rkey
573 if rkey.Valid {
574 comment.Rkey = rkey.V
575 }
576
577 if t, err := time.Parse(time.RFC3339, created); err == nil {
578 comment.Created = t
579 }
580
581 if edited.Valid {
582 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
583 comment.Edited = &t
584 }
585 }
586
587 if deleted.Valid {
588 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
589 comment.Deleted = &t
590 }
591 }
592
593 if replyTo.Valid {
594 comment.ReplyTo = &replyTo.V
595 }
596
597 comments = append(comments, comment)
598 }
599
600 if err = rows.Err(); err != nil {
601 return nil, err
602 }
603
604 return comments, nil
605}
606
607func DeleteIssues(e Execer, filters ...filter) error {
608 var conditions []string
609 var args []any
610 for _, filter := range filters {
611 conditions = append(conditions, filter.Condition())
612 args = append(args, filter.Arg()...)
613 }
614
615 whereClause := ""
616 if conditions != nil {
617 whereClause = " where " + strings.Join(conditions, " and ")
618 }
619
620 query := fmt.Sprintf(`delete from issues %s`, whereClause)
621 _, err := e.Exec(query, args...)
622 return err
623}
624
625func CloseIssues(e Execer, filters ...filter) error {
626 var conditions []string
627 var args []any
628 for _, filter := range filters {
629 conditions = append(conditions, filter.Condition())
630 args = append(args, filter.Arg()...)
631 }
632
633 whereClause := ""
634 if conditions != nil {
635 whereClause = " where " + strings.Join(conditions, " and ")
636 }
637
638 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
639 _, err := e.Exec(query, args...)
640 return err
641}
642
643func ReopenIssues(e Execer, filters ...filter) error {
644 var conditions []string
645 var args []any
646 for _, filter := range filters {
647 conditions = append(conditions, filter.Condition())
648 args = append(args, filter.Arg()...)
649 }
650
651 whereClause := ""
652 if conditions != nil {
653 whereClause = " where " + strings.Join(conditions, " and ")
654 }
655
656 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
657 _, err := e.Exec(query, args...)
658 return err
659}
660
661type IssueCount struct {
662 Open int
663 Closed int
664}
665
666func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
667 row := e.QueryRow(`
668 select
669 count(case when open = 1 then 1 end) as open_count,
670 count(case when open = 0 then 1 end) as closed_count
671 from issues
672 where repo_at = ?`,
673 repoAt,
674 )
675
676 var count IssueCount
677 if err := row.Scan(&count.Open, &count.Closed); err != nil {
678 return IssueCount{0, 0}, err
679 }
680
681 return count, nil
682}