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