julien.rbrt.fr
forked from
tangled.org/core
Monorepo for Tangled — https://tangled.org
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 GetIssues(e Execer, filters ...filter) ([]models.Issue, error) {
250 return GetIssuesPaginated(e, pagination.Page{}, filters...)
251}
252
253// GetIssueIDs gets list of all existing issue's IDs
254func GetIssueIDs(e Execer, opts models.IssueSearchOptions) ([]int64, error) {
255 var ids []int64
256
257 var filters []filter
258 openValue := 0
259 if opts.IsOpen {
260 openValue = 1
261 }
262 filters = append(filters, FilterEq("open", openValue))
263 if opts.RepoAt != "" {
264 filters = append(filters, FilterEq("repo_at", opts.RepoAt))
265 }
266
267 var conditions []string
268 var args []any
269
270 for _, filter := range filters {
271 conditions = append(conditions, filter.Condition())
272 args = append(args, filter.Arg()...)
273 }
274
275 whereClause := ""
276 if conditions != nil {
277 whereClause = " where " + strings.Join(conditions, " and ")
278 }
279 query := fmt.Sprintf(
280 `
281 select
282 id
283 from
284 issues
285 %s
286 limit ? offset ?`,
287 whereClause,
288 )
289 args = append(args, opts.Page.Limit, opts.Page.Offset)
290 rows, err := e.Query(query, args...)
291 if err != nil {
292 return nil, err
293 }
294 defer rows.Close()
295
296 for rows.Next() {
297 var id int64
298 err := rows.Scan(&id)
299 if err != nil {
300 return nil, err
301 }
302
303 ids = append(ids, id)
304 }
305
306 return ids, nil
307}
308
309func AddIssueComment(e Execer, c models.IssueComment) (int64, error) {
310 result, err := e.Exec(
311 `insert into issue_comments (
312 did,
313 rkey,
314 issue_at,
315 body,
316 reply_to,
317 created,
318 edited
319 )
320 values (?, ?, ?, ?, ?, ?, null)
321 on conflict(did, rkey) do update set
322 issue_at = excluded.issue_at,
323 body = excluded.body,
324 edited = case
325 when
326 issue_comments.issue_at != excluded.issue_at
327 or issue_comments.body != excluded.body
328 or issue_comments.reply_to != excluded.reply_to
329 then ?
330 else issue_comments.edited
331 end`,
332 c.Did,
333 c.Rkey,
334 c.IssueAt,
335 c.Body,
336 c.ReplyTo,
337 c.Created.Format(time.RFC3339),
338 time.Now().Format(time.RFC3339),
339 )
340 if err != nil {
341 return 0, err
342 }
343
344 id, err := result.LastInsertId()
345 if err != nil {
346 return 0, err
347 }
348
349 return id, nil
350}
351
352func DeleteIssueComments(e Execer, filters ...filter) error {
353 var conditions []string
354 var args []any
355 for _, filter := range filters {
356 conditions = append(conditions, filter.Condition())
357 args = append(args, filter.Arg()...)
358 }
359
360 whereClause := ""
361 if conditions != nil {
362 whereClause = " where " + strings.Join(conditions, " and ")
363 }
364
365 query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
366
367 _, err := e.Exec(query, args...)
368 return err
369}
370
371func GetIssueComments(e Execer, filters ...filter) ([]models.IssueComment, error) {
372 var comments []models.IssueComment
373
374 var conditions []string
375 var args []any
376 for _, filter := range filters {
377 conditions = append(conditions, filter.Condition())
378 args = append(args, filter.Arg()...)
379 }
380
381 whereClause := ""
382 if conditions != nil {
383 whereClause = " where " + strings.Join(conditions, " and ")
384 }
385
386 query := fmt.Sprintf(`
387 select
388 id,
389 did,
390 rkey,
391 issue_at,
392 reply_to,
393 body,
394 created,
395 edited,
396 deleted
397 from
398 issue_comments
399 %s
400 `, whereClause)
401
402 rows, err := e.Query(query, args...)
403 if err != nil {
404 return nil, err
405 }
406
407 for rows.Next() {
408 var comment models.IssueComment
409 var created string
410 var rkey, edited, deleted, replyTo sql.Null[string]
411 err := rows.Scan(
412 &comment.Id,
413 &comment.Did,
414 &rkey,
415 &comment.IssueAt,
416 &replyTo,
417 &comment.Body,
418 &created,
419 &edited,
420 &deleted,
421 )
422 if err != nil {
423 return nil, err
424 }
425
426 // this is a remnant from old times, newer comments always have rkey
427 if rkey.Valid {
428 comment.Rkey = rkey.V
429 }
430
431 if t, err := time.Parse(time.RFC3339, created); err == nil {
432 comment.Created = t
433 }
434
435 if edited.Valid {
436 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
437 comment.Edited = &t
438 }
439 }
440
441 if deleted.Valid {
442 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
443 comment.Deleted = &t
444 }
445 }
446
447 if replyTo.Valid {
448 comment.ReplyTo = &replyTo.V
449 }
450
451 comments = append(comments, comment)
452 }
453
454 if err = rows.Err(); err != nil {
455 return nil, err
456 }
457
458 return comments, nil
459}
460
461func DeleteIssues(e Execer, filters ...filter) error {
462 var conditions []string
463 var args []any
464 for _, filter := range filters {
465 conditions = append(conditions, filter.Condition())
466 args = append(args, filter.Arg()...)
467 }
468
469 whereClause := ""
470 if conditions != nil {
471 whereClause = " where " + strings.Join(conditions, " and ")
472 }
473
474 query := fmt.Sprintf(`delete from issues %s`, whereClause)
475 _, err := e.Exec(query, args...)
476 return err
477}
478
479func CloseIssues(e Execer, filters ...filter) error {
480 var conditions []string
481 var args []any
482 for _, filter := range filters {
483 conditions = append(conditions, filter.Condition())
484 args = append(args, filter.Arg()...)
485 }
486
487 whereClause := ""
488 if conditions != nil {
489 whereClause = " where " + strings.Join(conditions, " and ")
490 }
491
492 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
493 _, err := e.Exec(query, args...)
494 return err
495}
496
497func ReopenIssues(e Execer, filters ...filter) error {
498 var conditions []string
499 var args []any
500 for _, filter := range filters {
501 conditions = append(conditions, filter.Condition())
502 args = append(args, filter.Arg()...)
503 }
504
505 whereClause := ""
506 if conditions != nil {
507 whereClause = " where " + strings.Join(conditions, " and ")
508 }
509
510 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
511 _, err := e.Exec(query, args...)
512 return err
513}
514
515func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) {
516 row := e.QueryRow(`
517 select
518 count(case when open = 1 then 1 end) as open_count,
519 count(case when open = 0 then 1 end) as closed_count
520 from issues
521 where repo_at = ?`,
522 repoAt,
523 )
524
525 var count models.IssueCount
526 if err := row.Scan(&count.Open, &count.Closed); err != nil {
527 return models.IssueCount{}, err
528 }
529
530 return count, nil
531}