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