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