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