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[syntax.ATURI]*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 issueMap[issue.AtUri()] = &issue
200 }
201
202 // collect reverse repos
203 repoAts := make([]string, 0, len(issueMap)) // or just []string{}
204 for _, issue := range issueMap {
205 repoAts = append(repoAts, string(issue.RepoAt))
206 }
207
208 repos, err := GetRepos(e, 0, orm.FilterIn("at_uri", repoAts))
209 if err != nil {
210 return nil, fmt.Errorf("failed to build repo mappings: %w", err)
211 }
212
213 repoMap := make(map[string]*models.Repo)
214 for i := range repos {
215 repoMap[string(repos[i].RepoAt())] = &repos[i]
216 }
217
218 for issueAt, i := range issueMap {
219 if r, ok := repoMap[string(i.RepoAt)]; ok {
220 i.Repo = r
221 } else {
222 // do not show up the issue if the repo is deleted
223 // TODO: foreign key where?
224 delete(issueMap, issueAt)
225 }
226 }
227
228 // collect comments
229 issueAts := slices.Collect(maps.Keys(issueMap))
230
231 comments, err := GetComments(e, orm.FilterIn("subject_at", issueAts))
232 if err != nil {
233 return nil, fmt.Errorf("failed to query comments: %w", err)
234 }
235 for i := range comments {
236 issueAt := comments[i].Subject
237 if issue, ok := issueMap[issueAt]; ok {
238 issue.Comments = append(issue.Comments, comments[i])
239 }
240 }
241
242 // collect allLabels for each issue
243 allLabels, err := GetLabels(e, orm.FilterIn("subject", issueAts))
244 if err != nil {
245 return nil, fmt.Errorf("failed to query labels: %w", err)
246 }
247 for issueAt, labels := range allLabels {
248 if issue, ok := issueMap[issueAt]; ok {
249 issue.Labels = labels
250 }
251 }
252
253 // collect references for each issue
254 allReferencs, err := GetReferencesAll(e, orm.FilterIn("from_at", issueAts))
255 if err != nil {
256 return nil, fmt.Errorf("failed to query reference_links: %w", err)
257 }
258 for issueAt, references := range allReferencs {
259 if issue, ok := issueMap[issueAt]; ok {
260 issue.References = references
261 }
262 }
263
264 var issues []models.Issue
265 for _, i := range issueMap {
266 issues = append(issues, *i)
267 }
268
269 sort.Slice(issues, func(i, j int) bool {
270 return issues[i].Created.After(issues[j].Created)
271 })
272
273 return issues, nil
274}
275
276func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*models.Issue, error) {
277 issues, err := GetIssuesPaginated(
278 e,
279 pagination.Page{},
280 orm.FilterEq("repo_at", repoAt),
281 orm.FilterEq("issue_id", issueId),
282 )
283 if err != nil {
284 return nil, err
285 }
286 if len(issues) != 1 {
287 return nil, sql.ErrNoRows
288 }
289
290 return &issues[0], nil
291}
292
293func GetIssues(e Execer, filters ...orm.Filter) ([]models.Issue, error) {
294 return GetIssuesPaginated(e, pagination.Page{}, filters...)
295}
296
297func DeleteIssues(tx *sql.Tx, did, rkey string) error {
298 _, err := tx.Exec(
299 `delete from issues
300 where did = ? and rkey = ?`,
301 did,
302 rkey,
303 )
304 if err != nil {
305 return fmt.Errorf("delete issue: %w", err)
306 }
307
308 uri := syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", did, tangled.RepoIssueNSID, rkey))
309 err = deleteReferences(tx, uri)
310 if err != nil {
311 return fmt.Errorf("delete reference_links: %w", err)
312 }
313
314 return nil
315}
316
317func CloseIssues(e Execer, filters ...orm.Filter) error {
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(`update issues set open = 0 %s`, whereClause)
331 _, err := e.Exec(query, args...)
332 return err
333}
334
335func ReopenIssues(e Execer, filters ...orm.Filter) error {
336 var conditions []string
337 var args []any
338 for _, filter := range filters {
339 conditions = append(conditions, filter.Condition())
340 args = append(args, filter.Arg()...)
341 }
342
343 whereClause := ""
344 if conditions != nil {
345 whereClause = " where " + strings.Join(conditions, " and ")
346 }
347
348 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
349 _, err := e.Exec(query, args...)
350 return err
351}
352
353func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) {
354 row := e.QueryRow(`
355 select
356 count(case when open = 1 then 1 end) as open_count,
357 count(case when open = 0 then 1 end) as closed_count
358 from issues
359 where repo_at = ?`,
360 repoAt,
361 )
362
363 var count models.IssueCount
364 if err := row.Scan(&count.Open, &count.Closed); err != nil {
365 return models.IssueCount{}, err
366 }
367
368 return count, nil
369}