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