veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 mathrand "math/rand/v2"
7 "strings"
8 "time"
9
10 "github.com/bluesky-social/indigo/atproto/syntax"
11 "tangled.sh/tangled.sh/core/api/tangled"
12 "tangled.sh/tangled.sh/core/appview/pagination"
13)
14
15type Issue struct {
16 ID int64
17 RepoAt syntax.ATURI
18 OwnerDid string
19 IssueId int
20 Rkey string
21 Created time.Time
22 Title string
23 Body string
24 Open bool
25
26 // optionally, populate this when querying for reverse mappings
27 // like comment counts, parent repo etc.
28 Metadata *IssueMetadata
29}
30
31type IssueMetadata struct {
32 CommentCount int
33 Repo *Repo
34 // labels, assignee etc.
35}
36
37type Comment struct {
38 OwnerDid string
39 RepoAt syntax.ATURI
40 Rkey string
41 Issue int
42 CommentId int
43 Body string
44 Created *time.Time
45 Deleted *time.Time
46 Edited *time.Time
47}
48
49func (i *Issue) AtUri() syntax.ATURI {
50 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", i.OwnerDid, tangled.RepoIssueNSID, i.Rkey))
51}
52
53func IssueFromRecord(did, rkey string, record tangled.RepoIssue) Issue {
54 created, err := time.Parse(time.RFC3339, record.CreatedAt)
55 if err != nil {
56 created = time.Now()
57 }
58
59 body := ""
60 if record.Body != nil {
61 body = *record.Body
62 }
63
64 return Issue{
65 RepoAt: syntax.ATURI(record.Repo),
66 OwnerDid: did,
67 Rkey: rkey,
68 Created: created,
69 Title: record.Title,
70 Body: body,
71 Open: true, // new issues are open by default
72 }
73}
74
75func ResolveIssueFromAtUri(e Execer, issueUri syntax.ATURI) (syntax.ATURI, int, error) {
76 ownerDid := issueUri.Authority().String()
77 issueRkey := issueUri.RecordKey().String()
78
79 var repoAt string
80 var issueId int
81
82 query := `select repo_at, issue_id from issues where owner_did = ? and rkey = ?`
83 err := e.QueryRow(query, ownerDid, issueRkey).Scan(&repoAt, &issueId)
84 if err != nil {
85 return "", 0, err
86 }
87
88 return syntax.ATURI(repoAt), issueId, nil
89}
90
91func IssueCommentFromRecord(e Execer, did, rkey string, record tangled.RepoIssueComment) (Comment, error) {
92 created, err := time.Parse(time.RFC3339, record.CreatedAt)
93 if err != nil {
94 created = time.Now()
95 }
96
97 ownerDid := did
98 if record.Owner != nil {
99 ownerDid = *record.Owner
100 }
101
102 issueUri, err := syntax.ParseATURI(record.Issue)
103 if err != nil {
104 return Comment{}, err
105 }
106
107 repoAt, issueId, err := ResolveIssueFromAtUri(e, issueUri)
108 if err != nil {
109 return Comment{}, err
110 }
111
112 comment := Comment{
113 OwnerDid: ownerDid,
114 RepoAt: repoAt,
115 Rkey: rkey,
116 Body: record.Body,
117 Issue: issueId,
118 CommentId: mathrand.IntN(1000000),
119 Created: &created,
120 }
121
122 return comment, nil
123}
124
125func NewIssue(tx *sql.Tx, issue *Issue) error {
126 defer tx.Rollback()
127
128 _, err := tx.Exec(`
129 insert or ignore into repo_issue_seqs (repo_at, next_issue_id)
130 values (?, 1)
131 `, issue.RepoAt)
132 if err != nil {
133 return err
134 }
135
136 var nextId int
137 err = tx.QueryRow(`
138 update repo_issue_seqs
139 set next_issue_id = next_issue_id + 1
140 where repo_at = ?
141 returning next_issue_id - 1
142 `, issue.RepoAt).Scan(&nextId)
143 if err != nil {
144 return err
145 }
146
147 issue.IssueId = nextId
148
149 res, err := tx.Exec(`
150 insert into issues (repo_at, owner_did, rkey, issue_at, issue_id, title, body)
151 values (?, ?, ?, ?, ?, ?, ?)
152 `, issue.RepoAt, issue.OwnerDid, issue.Rkey, issue.AtUri(), issue.IssueId, issue.Title, issue.Body)
153 if err != nil {
154 return err
155 }
156
157 lastID, err := res.LastInsertId()
158 if err != nil {
159 return err
160 }
161 issue.ID = lastID
162
163 if err := tx.Commit(); err != nil {
164 return err
165 }
166
167 return nil
168}
169
170func GetIssueAt(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
171 var issueAt string
172 err := e.QueryRow(`select issue_at from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&issueAt)
173 return issueAt, err
174}
175
176func GetIssueOwnerDid(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
177 var ownerDid string
178 err := e.QueryRow(`select owner_did from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&ownerDid)
179 return ownerDid, err
180}
181
182func GetIssuesPaginated(e Execer, repoAt syntax.ATURI, isOpen bool, page pagination.Page) ([]Issue, error) {
183 var issues []Issue
184 openValue := 0
185 if isOpen {
186 openValue = 1
187 }
188
189 rows, err := e.Query(
190 `
191 with numbered_issue as (
192 select
193 i.id,
194 i.owner_did,
195 i.rkey,
196 i.issue_id,
197 i.created,
198 i.title,
199 i.body,
200 i.open,
201 count(c.id) as comment_count,
202 row_number() over (order by i.created desc) as row_num
203 from
204 issues i
205 left join
206 comments c on i.repo_at = c.repo_at and i.issue_id = c.issue_id
207 where
208 i.repo_at = ? and i.open = ?
209 group by
210 i.id, i.owner_did, i.issue_id, i.created, i.title, i.body, i.open
211 )
212 select
213 id,
214 owner_did,
215 rkey,
216 issue_id,
217 created,
218 title,
219 body,
220 open,
221 comment_count
222 from
223 numbered_issue
224 where
225 row_num between ? and ?`,
226 repoAt, openValue, page.Offset+1, page.Offset+page.Limit)
227 if err != nil {
228 return nil, err
229 }
230 defer rows.Close()
231
232 for rows.Next() {
233 var issue Issue
234 var createdAt string
235 var metadata IssueMetadata
236 err := rows.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open, &metadata.CommentCount)
237 if err != nil {
238 return nil, err
239 }
240
241 createdTime, err := time.Parse(time.RFC3339, createdAt)
242 if err != nil {
243 return nil, err
244 }
245 issue.Created = createdTime
246 issue.Metadata = &metadata
247
248 issues = append(issues, issue)
249 }
250
251 if err := rows.Err(); err != nil {
252 return nil, err
253 }
254
255 return issues, nil
256}
257
258func GetIssuesWithLimit(e Execer, limit int, filters ...filter) ([]Issue, error) {
259 issues := make([]Issue, 0, limit)
260
261 var conditions []string
262 var args []any
263 for _, filter := range filters {
264 conditions = append(conditions, filter.Condition())
265 args = append(args, filter.Arg()...)
266 }
267
268 whereClause := ""
269 if conditions != nil {
270 whereClause = " where " + strings.Join(conditions, " and ")
271 }
272 limitClause := ""
273 if limit != 0 {
274 limitClause = fmt.Sprintf(" limit %d ", limit)
275 }
276
277 query := fmt.Sprintf(
278 `select
279 i.id,
280 i.owner_did,
281 i.repo_at,
282 i.issue_id,
283 i.created,
284 i.title,
285 i.body,
286 i.open
287 from
288 issues i
289 %s
290 order by
291 i.created desc
292 %s`,
293 whereClause, limitClause)
294
295 rows, err := e.Query(query, args...)
296 if err != nil {
297 return nil, err
298 }
299 defer rows.Close()
300
301 for rows.Next() {
302 var issue Issue
303 var issueCreatedAt string
304 err := rows.Scan(
305 &issue.ID,
306 &issue.OwnerDid,
307 &issue.RepoAt,
308 &issue.IssueId,
309 &issueCreatedAt,
310 &issue.Title,
311 &issue.Body,
312 &issue.Open,
313 )
314 if err != nil {
315 return nil, err
316 }
317
318 issueCreatedTime, err := time.Parse(time.RFC3339, issueCreatedAt)
319 if err != nil {
320 return nil, err
321 }
322 issue.Created = issueCreatedTime
323
324 issues = append(issues, issue)
325 }
326
327 if err := rows.Err(); err != nil {
328 return nil, err
329 }
330
331 return issues, nil
332}
333
334func GetIssues(e Execer, filters ...filter) ([]Issue, error) {
335 return GetIssuesWithLimit(e, 0, filters...)
336}
337
338// timeframe here is directly passed into the sql query filter, and any
339// timeframe in the past should be negative; e.g.: "-3 months"
340func GetIssuesByOwnerDid(e Execer, ownerDid string, timeframe string) ([]Issue, error) {
341 var issues []Issue
342
343 rows, err := e.Query(
344 `select
345 i.id,
346 i.owner_did,
347 i.rkey,
348 i.repo_at,
349 i.issue_id,
350 i.created,
351 i.title,
352 i.body,
353 i.open,
354 r.did,
355 r.name,
356 r.knot,
357 r.rkey,
358 r.created
359 from
360 issues i
361 join
362 repos r on i.repo_at = r.at_uri
363 where
364 i.owner_did = ? and i.created >= date ('now', ?)
365 order by
366 i.created desc`,
367 ownerDid, timeframe)
368 if err != nil {
369 return nil, err
370 }
371 defer rows.Close()
372
373 for rows.Next() {
374 var issue Issue
375 var issueCreatedAt, repoCreatedAt string
376 var repo Repo
377 err := rows.Scan(
378 &issue.ID,
379 &issue.OwnerDid,
380 &issue.Rkey,
381 &issue.RepoAt,
382 &issue.IssueId,
383 &issueCreatedAt,
384 &issue.Title,
385 &issue.Body,
386 &issue.Open,
387 &repo.Did,
388 &repo.Name,
389 &repo.Knot,
390 &repo.Rkey,
391 &repoCreatedAt,
392 )
393 if err != nil {
394 return nil, err
395 }
396
397 issueCreatedTime, err := time.Parse(time.RFC3339, issueCreatedAt)
398 if err != nil {
399 return nil, err
400 }
401 issue.Created = issueCreatedTime
402
403 repoCreatedTime, err := time.Parse(time.RFC3339, repoCreatedAt)
404 if err != nil {
405 return nil, err
406 }
407 repo.Created = repoCreatedTime
408
409 issue.Metadata = &IssueMetadata{
410 Repo: &repo,
411 }
412
413 issues = append(issues, issue)
414 }
415
416 if err := rows.Err(); err != nil {
417 return nil, err
418 }
419
420 return issues, nil
421}
422
423func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
424 query := `select id, owner_did, rkey, created, title, body, open from issues where repo_at = ? and issue_id = ?`
425 row := e.QueryRow(query, repoAt, issueId)
426
427 var issue Issue
428 var createdAt string
429 err := row.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &createdAt, &issue.Title, &issue.Body, &issue.Open)
430 if err != nil {
431 return nil, err
432 }
433
434 createdTime, err := time.Parse(time.RFC3339, createdAt)
435 if err != nil {
436 return nil, err
437 }
438 issue.Created = createdTime
439
440 return &issue, nil
441}
442
443func GetIssueWithComments(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, []Comment, error) {
444 query := `select id, owner_did, rkey, issue_id, created, title, body, open from issues where repo_at = ? and issue_id = ?`
445 row := e.QueryRow(query, repoAt, issueId)
446
447 var issue Issue
448 var createdAt string
449 err := row.Scan(&issue.ID, &issue.OwnerDid, &issue.Rkey, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open)
450 if err != nil {
451 return nil, nil, err
452 }
453
454 createdTime, err := time.Parse(time.RFC3339, createdAt)
455 if err != nil {
456 return nil, nil, err
457 }
458 issue.Created = createdTime
459
460 comments, err := GetComments(e, repoAt, issueId)
461 if err != nil {
462 return nil, nil, err
463 }
464
465 return &issue, comments, nil
466}
467
468func NewIssueComment(e Execer, comment *Comment) error {
469 query := `insert into comments (owner_did, repo_at, rkey, issue_id, comment_id, body) values (?, ?, ?, ?, ?, ?)`
470 _, err := e.Exec(
471 query,
472 comment.OwnerDid,
473 comment.RepoAt,
474 comment.Rkey,
475 comment.Issue,
476 comment.CommentId,
477 comment.Body,
478 )
479 return err
480}
481
482func GetComments(e Execer, repoAt syntax.ATURI, issueId int) ([]Comment, error) {
483 var comments []Comment
484
485 rows, err := e.Query(`
486 select
487 owner_did,
488 issue_id,
489 comment_id,
490 rkey,
491 body,
492 created,
493 edited,
494 deleted
495 from
496 comments
497 where
498 repo_at = ? and issue_id = ?
499 order by
500 created asc`,
501 repoAt,
502 issueId,
503 )
504 if err == sql.ErrNoRows {
505 return []Comment{}, nil
506 }
507 if err != nil {
508 return nil, err
509 }
510 defer rows.Close()
511
512 for rows.Next() {
513 var comment Comment
514 var createdAt string
515 var deletedAt, editedAt, rkey sql.NullString
516 err := rows.Scan(&comment.OwnerDid, &comment.Issue, &comment.CommentId, &rkey, &comment.Body, &createdAt, &editedAt, &deletedAt)
517 if err != nil {
518 return nil, err
519 }
520
521 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
522 if err != nil {
523 return nil, err
524 }
525 comment.Created = &createdAtTime
526
527 if deletedAt.Valid {
528 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
529 if err != nil {
530 return nil, err
531 }
532 comment.Deleted = &deletedTime
533 }
534
535 if editedAt.Valid {
536 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
537 if err != nil {
538 return nil, err
539 }
540 comment.Edited = &editedTime
541 }
542
543 if rkey.Valid {
544 comment.Rkey = rkey.String
545 }
546
547 comments = append(comments, comment)
548 }
549
550 if err := rows.Err(); err != nil {
551 return nil, err
552 }
553
554 return comments, nil
555}
556
557func GetComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) (*Comment, error) {
558 query := `
559 select
560 owner_did, body, rkey, created, deleted, edited
561 from
562 comments where repo_at = ? and issue_id = ? and comment_id = ?
563 `
564 row := e.QueryRow(query, repoAt, issueId, commentId)
565
566 var comment Comment
567 var createdAt string
568 var deletedAt, editedAt, rkey sql.NullString
569 err := row.Scan(&comment.OwnerDid, &comment.Body, &rkey, &createdAt, &deletedAt, &editedAt)
570 if err != nil {
571 return nil, err
572 }
573
574 createdTime, err := time.Parse(time.RFC3339, createdAt)
575 if err != nil {
576 return nil, err
577 }
578 comment.Created = &createdTime
579
580 if deletedAt.Valid {
581 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
582 if err != nil {
583 return nil, err
584 }
585 comment.Deleted = &deletedTime
586 }
587
588 if editedAt.Valid {
589 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
590 if err != nil {
591 return nil, err
592 }
593 comment.Edited = &editedTime
594 }
595
596 if rkey.Valid {
597 comment.Rkey = rkey.String
598 }
599
600 comment.RepoAt = repoAt
601 comment.Issue = issueId
602 comment.CommentId = commentId
603
604 return &comment, nil
605}
606
607func EditComment(e Execer, repoAt syntax.ATURI, issueId, commentId int, newBody string) error {
608 _, err := e.Exec(
609 `
610 update comments
611 set body = ?,
612 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
613 where repo_at = ? and issue_id = ? and comment_id = ?
614 `, newBody, repoAt, issueId, commentId)
615 return err
616}
617
618func DeleteComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) error {
619 _, err := e.Exec(
620 `
621 update comments
622 set body = "",
623 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
624 where repo_at = ? and issue_id = ? and comment_id = ?
625 `, repoAt, issueId, commentId)
626 return err
627}
628
629func UpdateCommentByRkey(e Execer, ownerDid, rkey, newBody string) error {
630 _, err := e.Exec(
631 `
632 update comments
633 set body = ?,
634 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
635 where owner_did = ? and rkey = ?
636 `, newBody, ownerDid, rkey)
637 return err
638}
639
640func DeleteCommentByRkey(e Execer, ownerDid, rkey string) error {
641 _, err := e.Exec(
642 `
643 update comments
644 set body = "",
645 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
646 where owner_did = ? and rkey = ?
647 `, ownerDid, rkey)
648 return err
649}
650
651func UpdateIssueByRkey(e Execer, ownerDid, rkey, title, body string) error {
652 _, err := e.Exec(`update issues set title = ?, body = ? where owner_did = ? and rkey = ?`, title, body, ownerDid, rkey)
653 return err
654}
655
656func DeleteIssueByRkey(e Execer, ownerDid, rkey string) error {
657 _, err := e.Exec(`delete from issues where owner_did = ? and rkey = ?`, ownerDid, rkey)
658 return err
659}
660
661func CloseIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
662 _, err := e.Exec(`update issues set open = 0 where repo_at = ? and issue_id = ?`, repoAt, issueId)
663 return err
664}
665
666func ReopenIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
667 _, err := e.Exec(`update issues set open = 1 where repo_at = ? and issue_id = ?`, repoAt, issueId)
668 return err
669}
670
671type IssueCount struct {
672 Open int
673 Closed int
674}
675
676func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
677 row := e.QueryRow(`
678 select
679 count(case when open = 1 then 1 end) as open_count,
680 count(case when open = 0 then 1 end) as closed_count
681 from issues
682 where repo_at = ?`,
683 repoAt,
684 )
685
686 var count IssueCount
687 if err := row.Scan(&count.Open, &count.Closed); err != nil {
688 return IssueCount{0, 0}, err
689 }
690
691 return count, nil
692}