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