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