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