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.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 GetIssueAt(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
244 var issueAt string
245 err := e.QueryRow(`select issue_at from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&issueAt)
246 return issueAt, err
247}
248
249func GetIssueOwnerDid(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
250 var ownerDid string
251 err := e.QueryRow(`select owner_did from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&ownerDid)
252 return ownerDid, err
253}
254
255func GetIssuesPaginated(e Execer, repoAt syntax.ATURI, isOpen bool, page pagination.Page) ([]Issue, error) {
256 var issues []Issue
257 openValue := 0
258 if isOpen {
259 openValue = 1
260 }
261
262 rows, err := e.Query(
263 `
264 with numbered_issue as (
265 select
266 i.id,
267 i.owner_did,
268 i.rkey,
269 i.issue_id,
270 i.created,
271 i.title,
272 i.body,
273 i.open,
274 count(c.id) as comment_count,
275 row_number() over (order by i.created desc) as row_num
276 from
277 issues i
278 left join
279 comments c on i.repo_at = c.repo_at and i.issue_id = c.issue_id
280 where
281 i.repo_at = ? and i.open = ?
282 group by
283 i.id, i.owner_did, i.issue_id, i.created, i.title, i.body, i.open
284 )
285 select
286 id,
287 owner_did,
288 rkey,
289 issue_id,
290 created,
291 title,
292 body,
293 open,
294 comment_count
295 from
296 numbered_issue
297 where
298 row_num between ? and ?`,
299 repoAt, openValue, page.Offset+1, page.Offset+page.Limit)
300 if err != nil {
301 return nil, err
302 }
303 defer rows.Close()
304
305 for rows.Next() {
306 var issue Issue
307 var createdAt string
308 var metadata IssueMetadata
309 err := rows.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open, &metadata.CommentCount)
310 if err != nil {
311 return nil, err
312 }
313
314 createdTime, err := time.Parse(time.RFC3339, createdAt)
315 if err != nil {
316 return nil, err
317 }
318 issue.Created = createdTime
319 issue.Metadata = &metadata
320
321 issues = append(issues, issue)
322 }
323
324 if err := rows.Err(); err != nil {
325 return nil, err
326 }
327
328 return issues, nil
329}
330
331func GetIssuesWithLimit(e Execer, limit int, filters ...filter) ([]Issue, error) {
332 issues := make([]Issue, 0, limit)
333
334 var conditions []string
335 var args []any
336 for _, filter := range filters {
337 conditions = append(conditions, filter.Condition())
338 args = append(args, filter.Arg()...)
339 }
340
341 whereClause := ""
342 if conditions != nil {
343 whereClause = " where " + strings.Join(conditions, " and ")
344 }
345 limitClause := ""
346 if limit != 0 {
347 limitClause = fmt.Sprintf(" limit %d ", limit)
348 }
349
350 query := fmt.Sprintf(
351 `select
352 i.id,
353 i.owner_did,
354 i.repo_at,
355 i.issue_id,
356 i.created,
357 i.title,
358 i.body,
359 i.open
360 from
361 issues i
362 %s
363 order by
364 i.created desc
365 %s`,
366 whereClause, limitClause)
367
368 rows, err := e.Query(query, args...)
369 if err != nil {
370 return nil, err
371 }
372 defer rows.Close()
373
374 for rows.Next() {
375 var issue Issue
376 var issueCreatedAt string
377 err := rows.Scan(
378 &issue.ID,
379 &issue.OwnerDid,
380 &issue.RepoAt,
381 &issue.IssueId,
382 &issueCreatedAt,
383 &issue.Title,
384 &issue.Body,
385 &issue.Open,
386 )
387 if err != nil {
388 return nil, err
389 }
390
391 issueCreatedTime, err := time.Parse(time.RFC3339, issueCreatedAt)
392 if err != nil {
393 return nil, err
394 }
395 issue.Created = issueCreatedTime
396
397 issues = append(issues, issue)
398 }
399
400 if err := rows.Err(); err != nil {
401 return nil, err
402 }
403
404 return issues, nil
405}
406
407func GetIssues(e Execer, filters ...filter) ([]Issue, error) {
408 return GetIssuesWithLimit(e, 0, filters...)
409}
410
411// timeframe here is directly passed into the sql query filter, and any
412// timeframe in the past should be negative; e.g.: "-3 months"
413func GetIssuesByOwnerDid(e Execer, ownerDid string, timeframe string) ([]Issue, error) {
414 var issues []Issue
415
416 rows, err := e.Query(
417 `select
418 i.id,
419 i.owner_did,
420 i.rkey,
421 i.repo_at,
422 i.issue_id,
423 i.created,
424 i.title,
425 i.body,
426 i.open,
427 r.did,
428 r.name,
429 r.knot,
430 r.rkey,
431 r.created
432 from
433 issues i
434 join
435 repos r on i.repo_at = r.at_uri
436 where
437 i.owner_did = ? and i.created >= date ('now', ?)
438 order by
439 i.created desc`,
440 ownerDid, timeframe)
441 if err != nil {
442 return nil, err
443 }
444 defer rows.Close()
445
446 for rows.Next() {
447 var issue Issue
448 var issueCreatedAt, repoCreatedAt string
449 var repo Repo
450 err := rows.Scan(
451 &issue.ID,
452 &issue.OwnerDid,
453 &issue.Rkey,
454 &issue.RepoAt,
455 &issue.IssueId,
456 &issueCreatedAt,
457 &issue.Title,
458 &issue.Body,
459 &issue.Open,
460 &repo.Did,
461 &repo.Name,
462 &repo.Knot,
463 &repo.Rkey,
464 &repoCreatedAt,
465 )
466 if err != nil {
467 return nil, err
468 }
469
470 issueCreatedTime, err := time.Parse(time.RFC3339, issueCreatedAt)
471 if err != nil {
472 return nil, err
473 }
474 issue.Created = issueCreatedTime
475
476 repoCreatedTime, err := time.Parse(time.RFC3339, repoCreatedAt)
477 if err != nil {
478 return nil, err
479 }
480 repo.Created = repoCreatedTime
481
482 issue.Metadata = &IssueMetadata{
483 Repo: &repo,
484 }
485
486 issues = append(issues, issue)
487 }
488
489 if err := rows.Err(); err != nil {
490 return nil, err
491 }
492
493 return issues, nil
494}
495
496func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
497 query := `select id, owner_did, rkey, created, title, body, open from issues where repo_at = ? and issue_id = ?`
498 row := e.QueryRow(query, repoAt, issueId)
499
500 var issue Issue
501 var createdAt string
502 err := row.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &createdAt, &issue.Title, &issue.Body, &issue.Open)
503 if err != nil {
504 return nil, err
505 }
506
507 createdTime, err := time.Parse(time.RFC3339, createdAt)
508 if err != nil {
509 return nil, err
510 }
511 issue.Created = createdTime
512
513 return &issue, nil
514}
515
516func GetIssueWithComments(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, []Comment, error) {
517 query := `select id, owner_did, rkey, issue_id, created, title, body, open from issues where repo_at = ? and issue_id = ?`
518 row := e.QueryRow(query, repoAt, issueId)
519
520 var issue Issue
521 var createdAt string
522 err := row.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open)
523 if err != nil {
524 return nil, nil, err
525 }
526
527 createdTime, err := time.Parse(time.RFC3339, createdAt)
528 if err != nil {
529 return nil, nil, err
530 }
531 issue.Created = createdTime
532
533 comments, err := GetComments(e, repoAt, issueId)
534 if err != nil {
535 return nil, nil, err
536 }
537
538 return &issue, comments, nil
539}
540
541func NewIssueComment(e Execer, comment *Comment) error {
542 query := `insert into comments (owner_did, repo_at, rkey, issue_id, comment_id, body) values (?, ?, ?, ?, ?, ?)`
543 _, err := e.Exec(
544 query,
545 comment.OwnerDid,
546 comment.RepoAt,
547 comment.Rkey,
548 comment.Issue,
549 comment.CommentId,
550 comment.Body,
551 )
552 return err
553}
554
555func GetComments(e Execer, repoAt syntax.ATURI, issueId int) ([]Comment, error) {
556 var comments []Comment
557
558 rows, err := e.Query(`
559 select
560 owner_did,
561 issue_id,
562 comment_id,
563 rkey,
564 body,
565 created,
566 edited,
567 deleted
568 from
569 comments
570 where
571 repo_at = ? and issue_id = ?
572 order by
573 created asc`,
574 repoAt,
575 issueId,
576 )
577 if err == sql.ErrNoRows {
578 return []Comment{}, nil
579 }
580 if err != nil {
581 return nil, err
582 }
583 defer rows.Close()
584
585 for rows.Next() {
586 var comment Comment
587 var createdAt string
588 var deletedAt, editedAt, rkey sql.NullString
589 err := rows.Scan(&comment.OwnerDid, &comment.Issue, &comment.CommentId, &rkey, &comment.Body, &createdAt, &editedAt, &deletedAt)
590 if err != nil {
591 return nil, err
592 }
593
594 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
595 if err != nil {
596 return nil, err
597 }
598 comment.Created = &createdAtTime
599
600 if deletedAt.Valid {
601 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
602 if err != nil {
603 return nil, err
604 }
605 comment.Deleted = &deletedTime
606 }
607
608 if editedAt.Valid {
609 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
610 if err != nil {
611 return nil, err
612 }
613 comment.Edited = &editedTime
614 }
615
616 if rkey.Valid {
617 comment.Rkey = rkey.String
618 }
619
620 comments = append(comments, comment)
621 }
622
623 if err := rows.Err(); err != nil {
624 return nil, err
625 }
626
627 return comments, nil
628}
629
630func GetComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) (*Comment, error) {
631 query := `
632 select
633 owner_did, body, rkey, created, deleted, edited
634 from
635 comments where repo_at = ? and issue_id = ? and comment_id = ?
636 `
637 row := e.QueryRow(query, repoAt, issueId, commentId)
638
639 var comment Comment
640 var createdAt string
641 var deletedAt, editedAt, rkey sql.NullString
642 err := row.Scan(&comment.OwnerDid, &comment.Body, &rkey, &createdAt, &deletedAt, &editedAt)
643 if err != nil {
644 return nil, err
645 }
646
647 createdTime, err := time.Parse(time.RFC3339, createdAt)
648 if err != nil {
649 return nil, err
650 }
651 comment.Created = &createdTime
652
653 if deletedAt.Valid {
654 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
655 if err != nil {
656 return nil, err
657 }
658 comment.Deleted = &deletedTime
659 }
660
661 if editedAt.Valid {
662 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
663 if err != nil {
664 return nil, err
665 }
666 comment.Edited = &editedTime
667 }
668
669 if rkey.Valid {
670 comment.Rkey = rkey.String
671 }
672
673 comment.RepoAt = repoAt
674 comment.Issue = issueId
675 comment.CommentId = commentId
676
677 return &comment, nil
678}
679
680func EditComment(e Execer, repoAt syntax.ATURI, issueId, commentId int, newBody string) error {
681 _, err := e.Exec(
682 `
683 update comments
684 set body = ?,
685 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
686 where repo_at = ? and issue_id = ? and comment_id = ?
687 `, newBody, repoAt, issueId, commentId)
688 return err
689}
690
691func DeleteComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) error {
692 _, err := e.Exec(
693 `
694 update comments
695 set body = "",
696 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
697 where repo_at = ? and issue_id = ? and comment_id = ?
698 `, repoAt, issueId, commentId)
699 return err
700}
701
702func UpdateCommentByRkey(e Execer, ownerDid, rkey, newBody string) error {
703 _, err := e.Exec(
704 `
705 update comments
706 set body = ?,
707 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
708 where owner_did = ? and rkey = ?
709 `, newBody, ownerDid, rkey)
710 return err
711}
712
713func DeleteCommentByRkey(e Execer, ownerDid, rkey string) error {
714 _, err := e.Exec(
715 `
716 update comments
717 set body = "",
718 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
719 where owner_did = ? and rkey = ?
720 `, ownerDid, rkey)
721 return err
722}
723
724func UpdateIssueByRkey(e Execer, ownerDid, rkey, title, body string) error {
725 _, err := e.Exec(`update issues set title = ?, body = ? where owner_did = ? and rkey = ?`, title, body, ownerDid, rkey)
726 return err
727}
728
729func DeleteIssueByRkey(e Execer, ownerDid, rkey string) error {
730 _, err := e.Exec(`delete from issues where owner_did = ? and rkey = ?`, ownerDid, rkey)
731 return err
732}
733
734func CloseIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
735 _, err := e.Exec(`update issues set open = 0 where repo_at = ? and issue_id = ?`, repoAt, issueId)
736 return err
737}
738
739func ReopenIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
740 _, err := e.Exec(`update issues set open = 1 where repo_at = ? and issue_id = ?`, repoAt, issueId)
741 return err
742}
743
744type IssueCount struct {
745 Open int
746 Closed int
747}
748
749func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
750 row := e.QueryRow(`
751 select
752 count(case when open = 1 then 1 end) as open_count,
753 count(case when open = 0 then 1 end) as closed_count
754 from issues
755 where repo_at = ?`,
756 repoAt,
757 )
758
759 var count IssueCount
760 if err := row.Scan(&count.Open, &count.Closed); err != nil {
761 return IssueCount{0, 0}, err
762 }
763
764 return count, nil
765}