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 args = append(args, pLower.Arg()...)
105 args = append(args, pUpper.Arg()...)
106 pagination := " where " + pLower.Condition() + " and " + pUpper.Condition()
107
108 query := fmt.Sprintf(
109 `
110 select * from (
111 select
112 id,
113 did,
114 rkey,
115 repo_at,
116 issue_id,
117 title,
118 body,
119 open,
120 created,
121 edited,
122 deleted,
123 row_number() over (order by created desc) as row_num
124 from
125 issues
126 %s
127 ) ranked_issues
128 %s
129 `,
130 whereClause,
131 pagination,
132 )
133
134 rows, err := e.Query(query, args...)
135 if err != nil {
136 return nil, fmt.Errorf("failed to query issues table: %w", err)
137 }
138 defer rows.Close()
139
140 for rows.Next() {
141 var issue models.Issue
142 var createdAt string
143 var editedAt, deletedAt sql.Null[string]
144 var rowNum int64
145 err := rows.Scan(
146 &issue.Id,
147 &issue.Did,
148 &issue.Rkey,
149 &issue.RepoAt,
150 &issue.IssueId,
151 &issue.Title,
152 &issue.Body,
153 &issue.Open,
154 &createdAt,
155 &editedAt,
156 &deletedAt,
157 &rowNum,
158 )
159 if err != nil {
160 return nil, fmt.Errorf("failed to scan issue: %w", err)
161 }
162
163 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
164 issue.Created = t
165 }
166
167 if editedAt.Valid {
168 if t, err := time.Parse(time.RFC3339, editedAt.V); err == nil {
169 issue.Edited = &t
170 }
171 }
172
173 if deletedAt.Valid {
174 if t, err := time.Parse(time.RFC3339, deletedAt.V); err == nil {
175 issue.Deleted = &t
176 }
177 }
178
179 atUri := issue.AtUri().String()
180 issueMap[atUri] = &issue
181 }
182
183 // collect reverse repos
184 repoAts := make([]string, 0, len(issueMap)) // or just []string{}
185 for _, issue := range issueMap {
186 repoAts = append(repoAts, string(issue.RepoAt))
187 }
188
189 repos, err := GetRepos(e, 0, FilterIn("at_uri", repoAts))
190 if err != nil {
191 return nil, fmt.Errorf("failed to build repo mappings: %w", err)
192 }
193
194 repoMap := make(map[string]*models.Repo)
195 for i := range repos {
196 repoMap[string(repos[i].RepoAt())] = &repos[i]
197 }
198
199 for issueAt, i := range issueMap {
200 if r, ok := repoMap[string(i.RepoAt)]; ok {
201 i.Repo = r
202 } else {
203 // do not show up the issue if the repo is deleted
204 // TODO: foreign key where?
205 delete(issueMap, issueAt)
206 }
207 }
208
209 // collect comments
210 issueAts := slices.Collect(maps.Keys(issueMap))
211
212 comments, err := GetIssueComments(e, FilterIn("issue_at", issueAts))
213 if err != nil {
214 return nil, fmt.Errorf("failed to query comments: %w", err)
215 }
216 for i := range comments {
217 issueAt := comments[i].IssueAt
218 if issue, ok := issueMap[issueAt]; ok {
219 issue.Comments = append(issue.Comments, comments[i])
220 }
221 }
222
223 // collect allLabels for each issue
224 allLabels, err := GetLabels(e, FilterIn("subject", issueAts))
225 if err != nil {
226 return nil, fmt.Errorf("failed to query labels: %w", err)
227 }
228 for issueAt, labels := range allLabels {
229 if issue, ok := issueMap[issueAt.String()]; ok {
230 issue.Labels = labels
231 }
232 }
233
234 var issues []models.Issue
235 for _, i := range issueMap {
236 issues = append(issues, *i)
237 }
238
239 sort.Slice(issues, func(i, j int) bool {
240 return issues[i].Created.After(issues[j].Created)
241 })
242
243 return issues, nil
244}
245
246func GetIssues(e Execer, filters ...filter) ([]models.Issue, error) {
247 return GetIssuesPaginated(e, pagination.FirstPage(), filters...)
248}
249
250func AddIssueComment(e Execer, c models.IssueComment) (int64, error) {
251 result, err := e.Exec(
252 `insert into issue_comments (
253 did,
254 rkey,
255 issue_at,
256 body,
257 reply_to,
258 created,
259 edited
260 )
261 values (?, ?, ?, ?, ?, ?, null)
262 on conflict(did, rkey) do update set
263 issue_at = excluded.issue_at,
264 body = excluded.body,
265 edited = case
266 when
267 issue_comments.issue_at != excluded.issue_at
268 or issue_comments.body != excluded.body
269 or issue_comments.reply_to != excluded.reply_to
270 then ?
271 else issue_comments.edited
272 end`,
273 c.Did,
274 c.Rkey,
275 c.IssueAt,
276 c.Body,
277 c.ReplyTo,
278 c.Created.Format(time.RFC3339),
279 time.Now().Format(time.RFC3339),
280 )
281 if err != nil {
282 return 0, err
283 }
284
285 id, err := result.LastInsertId()
286 if err != nil {
287 return 0, err
288 }
289
290 return id, nil
291}
292
293func DeleteIssueComments(e Execer, filters ...filter) error {
294 var conditions []string
295 var args []any
296 for _, filter := range filters {
297 conditions = append(conditions, filter.Condition())
298 args = append(args, filter.Arg()...)
299 }
300
301 whereClause := ""
302 if conditions != nil {
303 whereClause = " where " + strings.Join(conditions, " and ")
304 }
305
306 query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
307
308 _, err := e.Exec(query, args...)
309 return err
310}
311
312func GetIssueComments(e Execer, filters ...filter) ([]models.IssueComment, error) {
313 var comments []models.IssueComment
314
315 var conditions []string
316 var args []any
317 for _, filter := range filters {
318 conditions = append(conditions, filter.Condition())
319 args = append(args, filter.Arg()...)
320 }
321
322 whereClause := ""
323 if conditions != nil {
324 whereClause = " where " + strings.Join(conditions, " and ")
325 }
326
327 query := fmt.Sprintf(`
328 select
329 id,
330 did,
331 rkey,
332 issue_at,
333 reply_to,
334 body,
335 created,
336 edited,
337 deleted
338 from
339 issue_comments
340 %s
341 `, whereClause)
342
343 rows, err := e.Query(query, args...)
344 if err != nil {
345 return nil, err
346 }
347
348 for rows.Next() {
349 var comment models.IssueComment
350 var created string
351 var rkey, edited, deleted, replyTo sql.Null[string]
352 err := rows.Scan(
353 &comment.Id,
354 &comment.Did,
355 &rkey,
356 &comment.IssueAt,
357 &replyTo,
358 &comment.Body,
359 &created,
360 &edited,
361 &deleted,
362 )
363 if err != nil {
364 return nil, err
365 }
366
367 // this is a remnant from old times, newer comments always have rkey
368 if rkey.Valid {
369 comment.Rkey = rkey.V
370 }
371
372 if t, err := time.Parse(time.RFC3339, created); err == nil {
373 comment.Created = t
374 }
375
376 if edited.Valid {
377 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
378 comment.Edited = &t
379 }
380 }
381
382 if deleted.Valid {
383 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
384 comment.Deleted = &t
385 }
386 }
387
388 if replyTo.Valid {
389 comment.ReplyTo = &replyTo.V
390 }
391
392 comments = append(comments, comment)
393 }
394
395 if err = rows.Err(); err != nil {
396 return nil, err
397 }
398
399 return comments, nil
400}
401
402func DeleteIssues(e Execer, filters ...filter) error {
403 var conditions []string
404 var args []any
405 for _, filter := range filters {
406 conditions = append(conditions, filter.Condition())
407 args = append(args, filter.Arg()...)
408 }
409
410 whereClause := ""
411 if conditions != nil {
412 whereClause = " where " + strings.Join(conditions, " and ")
413 }
414
415 query := fmt.Sprintf(`delete from issues %s`, whereClause)
416 _, err := e.Exec(query, args...)
417 return err
418}
419
420func CloseIssues(e Execer, filters ...filter) error {
421 var conditions []string
422 var args []any
423 for _, filter := range filters {
424 conditions = append(conditions, filter.Condition())
425 args = append(args, filter.Arg()...)
426 }
427
428 whereClause := ""
429 if conditions != nil {
430 whereClause = " where " + strings.Join(conditions, " and ")
431 }
432
433 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
434 _, err := e.Exec(query, args...)
435 return err
436}
437
438func ReopenIssues(e Execer, filters ...filter) error {
439 var conditions []string
440 var args []any
441 for _, filter := range filters {
442 conditions = append(conditions, filter.Condition())
443 args = append(args, filter.Arg()...)
444 }
445
446 whereClause := ""
447 if conditions != nil {
448 whereClause = " where " + strings.Join(conditions, " and ")
449 }
450
451 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
452 _, err := e.Exec(query, args...)
453 return err
454}
455
456func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) {
457 row := e.QueryRow(`
458 select
459 count(case when open = 1 then 1 end) as open_count,
460 count(case when open = 0 then 1 end) as closed_count
461 from issues
462 where repo_at = ?`,
463 repoAt,
464 )
465
466 var count models.IssueCount
467 if err := row.Scan(&count.Open, &count.Closed); err != nil {
468 return models.IssueCount{}, err
469 }
470
471 return count, nil
472}