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