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