this repo has no description
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
253func AddIssueComment(e Execer, c models.IssueComment) (int64, error) {
254 result, err := e.Exec(
255 `insert into issue_comments (
256 did,
257 rkey,
258 issue_at,
259 body,
260 reply_to,
261 created,
262 edited
263 )
264 values (?, ?, ?, ?, ?, ?, null)
265 on conflict(did, rkey) do update set
266 issue_at = excluded.issue_at,
267 body = excluded.body,
268 edited = case
269 when
270 issue_comments.issue_at != excluded.issue_at
271 or issue_comments.body != excluded.body
272 or issue_comments.reply_to != excluded.reply_to
273 then ?
274 else issue_comments.edited
275 end`,
276 c.Did,
277 c.Rkey,
278 c.IssueAt,
279 c.Body,
280 c.ReplyTo,
281 c.Created.Format(time.RFC3339),
282 time.Now().Format(time.RFC3339),
283 )
284 if err != nil {
285 return 0, err
286 }
287
288 id, err := result.LastInsertId()
289 if err != nil {
290 return 0, err
291 }
292
293 return id, nil
294}
295
296func DeleteIssueComments(e Execer, filters ...filter) error {
297 var conditions []string
298 var args []any
299 for _, filter := range filters {
300 conditions = append(conditions, filter.Condition())
301 args = append(args, filter.Arg()...)
302 }
303
304 whereClause := ""
305 if conditions != nil {
306 whereClause = " where " + strings.Join(conditions, " and ")
307 }
308
309 query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
310
311 _, err := e.Exec(query, args...)
312 return err
313}
314
315func GetIssueComments(e Execer, filters ...filter) ([]models.IssueComment, error) {
316 var comments []models.IssueComment
317
318 var conditions []string
319 var args []any
320 for _, filter := range filters {
321 conditions = append(conditions, filter.Condition())
322 args = append(args, filter.Arg()...)
323 }
324
325 whereClause := ""
326 if conditions != nil {
327 whereClause = " where " + strings.Join(conditions, " and ")
328 }
329
330 query := fmt.Sprintf(`
331 select
332 id,
333 did,
334 rkey,
335 issue_at,
336 reply_to,
337 body,
338 created,
339 edited,
340 deleted
341 from
342 issue_comments
343 %s
344 `, whereClause)
345
346 rows, err := e.Query(query, args...)
347 if err != nil {
348 return nil, err
349 }
350
351 for rows.Next() {
352 var comment models.IssueComment
353 var created string
354 var rkey, edited, deleted, replyTo sql.Null[string]
355 err := rows.Scan(
356 &comment.Id,
357 &comment.Did,
358 &rkey,
359 &comment.IssueAt,
360 &replyTo,
361 &comment.Body,
362 &created,
363 &edited,
364 &deleted,
365 )
366 if err != nil {
367 return nil, err
368 }
369
370 // this is a remnant from old times, newer comments always have rkey
371 if rkey.Valid {
372 comment.Rkey = rkey.V
373 }
374
375 if t, err := time.Parse(time.RFC3339, created); err == nil {
376 comment.Created = t
377 }
378
379 if edited.Valid {
380 if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
381 comment.Edited = &t
382 }
383 }
384
385 if deleted.Valid {
386 if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
387 comment.Deleted = &t
388 }
389 }
390
391 if replyTo.Valid {
392 comment.ReplyTo = &replyTo.V
393 }
394
395 comments = append(comments, comment)
396 }
397
398 if err = rows.Err(); err != nil {
399 return nil, err
400 }
401
402 return comments, nil
403}
404
405func DeleteIssues(e Execer, filters ...filter) error {
406 var conditions []string
407 var args []any
408 for _, filter := range filters {
409 conditions = append(conditions, filter.Condition())
410 args = append(args, filter.Arg()...)
411 }
412
413 whereClause := ""
414 if conditions != nil {
415 whereClause = " where " + strings.Join(conditions, " and ")
416 }
417
418 query := fmt.Sprintf(`delete from issues %s`, whereClause)
419 _, err := e.Exec(query, args...)
420 return err
421}
422
423func CloseIssues(e Execer, filters ...filter) error {
424 var conditions []string
425 var args []any
426 for _, filter := range filters {
427 conditions = append(conditions, filter.Condition())
428 args = append(args, filter.Arg()...)
429 }
430
431 whereClause := ""
432 if conditions != nil {
433 whereClause = " where " + strings.Join(conditions, " and ")
434 }
435
436 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
437 _, err := e.Exec(query, args...)
438 return err
439}
440
441func ReopenIssues(e Execer, filters ...filter) error {
442 var conditions []string
443 var args []any
444 for _, filter := range filters {
445 conditions = append(conditions, filter.Condition())
446 args = append(args, filter.Arg()...)
447 }
448
449 whereClause := ""
450 if conditions != nil {
451 whereClause = " where " + strings.Join(conditions, " and ")
452 }
453
454 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
455 _, err := e.Exec(query, args...)
456 return err
457}
458
459func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) {
460 row := e.QueryRow(`
461 select
462 count(case when open = 1 then 1 end) as open_count,
463 count(case when open = 0 then 1 end) as closed_count
464 from issues
465 where repo_at = ?`,
466 repoAt,
467 )
468
469 var count models.IssueCount
470 if err := row.Scan(&count.Open, &count.Closed); err != nil {
471 return models.IssueCount{}, err
472 }
473
474 return count, nil
475}