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