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