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/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
297// GetIssueIDs gets list of all existing issue's IDs
298func GetIssueIDs(e Execer, opts models.IssueSearchOptions) ([]int64, error) {
299 var ids []int64
300
301 var filters []orm.Filter
302 openValue := 0
303 if opts.IsOpen {
304 openValue = 1
305 }
306 filters = append(filters, orm.FilterEq("open", openValue))
307 if opts.RepoAt != "" {
308 filters = append(filters, orm.FilterEq("repo_at", opts.RepoAt))
309 }
310
311 var conditions []string
312 var args []any
313
314 for _, filter := range filters {
315 conditions = append(conditions, filter.Condition())
316 args = append(args, filter.Arg()...)
317 }
318
319 whereClause := ""
320 if conditions != nil {
321 whereClause = " where " + strings.Join(conditions, " and ")
322 }
323 query := fmt.Sprintf(
324 `
325 select
326 id
327 from
328 issues
329 %s
330 limit ? offset ?`,
331 whereClause,
332 )
333 args = append(args, opts.Page.Limit, opts.Page.Offset)
334 rows, err := e.Query(query, args...)
335 if err != nil {
336 return nil, err
337 }
338 defer rows.Close()
339
340 for rows.Next() {
341 var id int64
342 err := rows.Scan(&id)
343 if err != nil {
344 return nil, err
345 }
346
347 ids = append(ids, id)
348 }
349
350 return ids, nil
351}
352
353func DeleteIssues(tx *sql.Tx, did, rkey string) error {
354 _, err := tx.Exec(
355 `delete from issues
356 where did = ? and rkey = ?`,
357 did,
358 rkey,
359 )
360 if err != nil {
361 return fmt.Errorf("delete issue: %w", err)
362 }
363
364 uri := syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", did, tangled.RepoIssueNSID, rkey))
365 err = deleteReferences(tx, uri)
366 if err != nil {
367 return fmt.Errorf("delete reference_links: %w", err)
368 }
369
370 return nil
371}
372
373func CloseIssues(e Execer, filters ...orm.Filter) error {
374 var conditions []string
375 var args []any
376 for _, filter := range filters {
377 conditions = append(conditions, filter.Condition())
378 args = append(args, filter.Arg()...)
379 }
380
381 whereClause := ""
382 if conditions != nil {
383 whereClause = " where " + strings.Join(conditions, " and ")
384 }
385
386 query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause)
387 _, err := e.Exec(query, args...)
388 return err
389}
390
391func ReopenIssues(e Execer, filters ...orm.Filter) error {
392 var conditions []string
393 var args []any
394 for _, filter := range filters {
395 conditions = append(conditions, filter.Condition())
396 args = append(args, filter.Arg()...)
397 }
398
399 whereClause := ""
400 if conditions != nil {
401 whereClause = " where " + strings.Join(conditions, " and ")
402 }
403
404 query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause)
405 _, err := e.Exec(query, args...)
406 return err
407}
408
409func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) {
410 row := e.QueryRow(`
411 select
412 count(case when open = 1 then 1 end) as open_count,
413 count(case when open = 0 then 1 end) as closed_count
414 from issues
415 where repo_at = ?`,
416 repoAt,
417 )
418
419 var count models.IssueCount
420 if err := row.Scan(&count.Open, &count.Closed); err != nil {
421 return models.IssueCount{}, err
422 }
423
424 return count, nil
425}