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 := range issueMap {
363 i := issueMap[issueAt]
364 r := repoMap[string(i.RepoAt)]
365 i.Repo = r
366 }
367
368 // collect comments
369 issueAts := slices.Collect(maps.Keys(issueMap))
370 comments, err := GetIssueComments(e, FilterIn("issue_at", issueAts))
371 if err != nil {
372 return nil, fmt.Errorf("failed to query comments: %w", err)
373 }
374
375 for i := range comments {
376 issueAt := comments[i].IssueAt
377 if issue, ok := issueMap[issueAt]; ok {
378 issue.Comments = append(issue.Comments, comments[i])
379 }
380 }
381
382 var issues []Issue
383 for _, i := range issueMap {
384 issues = append(issues, *i)
385 }
386
387 sort.Slice(issues, func(i, j int) bool {
388 return issues[i].Created.After(issues[j].Created)
389 })
390
391 return issues, nil
392}
393
394func GetIssues(e Execer, filters ...filter) ([]Issue, error) {
395 return GetIssuesPaginated(e, pagination.FirstPage(), filters...)
396}
397
398func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
399 query := `select id, owner_did, rkey, created, title, body, open from issues where repo_at = ? and issue_id = ?`
400 row := e.QueryRow(query, repoAt, issueId)
401
402 var issue Issue
403 var createdAt string
404 err := row.Scan(&issue.Id, &issue.Did, &issue.Rkey, &createdAt, &issue.Title, &issue.Body, &issue.Open)
405 if err != nil {
406 return nil, err
407 }
408
409 createdTime, err := time.Parse(time.RFC3339, createdAt)
410 if err != nil {
411 return nil, err
412 }
413 issue.Created = createdTime
414
415 return &issue, nil
416}
417
418func AddIssueComment(e Execer, c IssueComment) (int64, error) {
419 result, err := e.Exec(
420 `insert into issue_comments (
421 did,
422 rkey,
423 issue_at,
424 body,
425 reply_to,
426 created,
427 edited
428 )
429 values (?, ?, ?, ?, ?, ?, null)
430 on conflict(did, rkey) do update set
431 issue_at = excluded.issue_at,
432 body = excluded.body,
433 edited = case
434 when
435 issue_comments.issue_at != excluded.issue_at
436 or issue_comments.body != excluded.body
437 or issue_comments.reply_to != excluded.reply_to
438 then ?
439 else issue_comments.edited
440 end`,
441 c.Did,
442 c.Rkey,
443 c.IssueAt,
444 c.Body,
445 c.ReplyTo,
446 c.Created.Format(time.RFC3339),
447 time.Now().Format(time.RFC3339),
448 )
449 if err != nil {
450 return 0, err
451 }
452
453 id, err := result.LastInsertId()
454 if err != nil {
455 return 0, err
456 }
457
458 return id, nil
459}
460
461func DeleteIssueComments(e Execer, filters ...filter) error {
462 var conditions []string
463 var args []any
464 for _, filter := range filters {
465 conditions = append(conditions, filter.Condition())
466 args = append(args, filter.Arg()...)
467 }
468
469 whereClause := ""
470 if conditions != nil {
471 whereClause = " where " + strings.Join(conditions, " and ")
472 }
473
474 query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
475
476 _, err := e.Exec(query, args...)
477 return err
478}
479
480func GetIssueComments(e Execer, filters ...filter) ([]IssueComment, error) {
481 var comments []IssueComment
482
483 var conditions []string
484 var args []any
485 for _, filter := range filters {
486 conditions = append(conditions, filter.Condition())
487 args = append(args, filter.Arg()...)
488 }
489
490 whereClause := ""
491 if conditions != nil {
492 whereClause = " where " + strings.Join(conditions, " and ")
493 }
494
495 query := fmt.Sprintf(`
496 select
497 id,
498 did,
499 rkey,
500 issue_at,
501 reply_to,
502 body,
503 created,
504 edited,
505 deleted
506 from
507 issue_comments
508 %s
509 `, whereClause)
510
511 rows, err := e.Query(query, args...)
512 if err != nil {
513 return nil, err
514 }
515
516 for rows.Next() {
517 var comment IssueComment
518 var created string
519 var rkey, edited, deleted, replyTo sql.Null[string]
520 err := rows.Scan(
521 &comment.Id,
522 &comment.Did,
523 &rkey,
524 &comment.IssueAt,
525 &replyTo,
526 &comment.Body,
527 &created,
528 &edited,
529 &deleted,
530 )
531 if err != nil {
532 return nil, err
533 }
534
535 // this is a remnant from old times, newer comments always have rkey
536 if rkey.Valid {
537 comment.Rkey = rkey.V
538 }
539
540 if t, err := time.Parse(time.RFC3339, created); err == nil {
541 comment.Created = t
542 }
543
544 if edited.Valid {
545 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
546 comment.Edited = &t
547 }
548 }
549
550 if deleted.Valid {
551 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
552 comment.Deleted = &t
553 }
554 }
555
556 if replyTo.Valid {
557 comment.ReplyTo = &replyTo.V
558 }
559
560 comments = append(comments, comment)
561 }
562
563 if err = rows.Err(); err != nil {
564 return nil, err
565 }
566
567 return comments, nil
568}
569
570func DeleteIssues(e Execer, filters ...filter) error {
571 var conditions []string
572 var args []any
573 for _, filter := range filters {
574 conditions = append(conditions, filter.Condition())
575 args = append(args, filter.Arg()...)
576 }
577
578 whereClause := ""
579 if conditions != nil {
580 whereClause = " where " + strings.Join(conditions, " and ")
581 }
582
583 query := fmt.Sprintf(`delete from issues %s`, whereClause)
584 _, err := e.Exec(query, args...)
585 return err
586}
587
588func CloseIssues(e Execer, filters ...filter) error {
589 var conditions []string
590 var args []any
591 for _, filter := range filters {
592 conditions = append(conditions, filter.Condition())
593 args = append(args, filter.Arg()...)
594 }
595
596 whereClause := ""
597 if conditions != nil {
598 whereClause = " where " + strings.Join(conditions, " and ")
599 }
600
601 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
602 _, err := e.Exec(query, args...)
603 return err
604}
605
606func ReopenIssues(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(`update issues set open = 1 %s`, whereClause)
620 _, err := e.Exec(query, args...)
621 return err
622}
623
624type IssueCount struct {
625 Open int
626 Closed int
627}
628
629func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
630 row := e.QueryRow(`
631 select
632 count(case when open = 1 then 1 end) as open_count,
633 count(case when open = 0 then 1 end) as closed_count
634 from issues
635 where repo_at = ?`,
636 repoAt,
637 )
638
639 var count IssueCount
640 if err := row.Scan(&count.Open, &count.Closed); err != nil {
641 return IssueCount{0, 0}, err
642 }
643
644 return count, nil
645}