julien.rbrt.fr
forked from
tangled.org/core
Monorepo for Tangled — https://tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "time"
7
8 "github.com/bluesky-social/indigo/atproto/syntax"
9 "go.opentelemetry.io/otel"
10 "go.opentelemetry.io/otel/attribute"
11 "tangled.sh/tangled.sh/core/appview/pagination"
12)
13
14type Issue struct {
15 RepoAt syntax.ATURI
16 OwnerDid string
17 IssueId int
18 IssueAt string
19 Created time.Time
20 Title string
21 Body string
22 Open bool
23
24 // optionally, populate this when querying for reverse mappings
25 // like comment counts, parent repo etc.
26 Metadata *IssueMetadata
27}
28
29type IssueMetadata struct {
30 CommentCount int
31 Repo *Repo
32 // labels, assignee etc.
33}
34
35type Comment struct {
36 OwnerDid string
37 RepoAt syntax.ATURI
38 Rkey string
39 Issue int
40 CommentId int
41 Body string
42 Created *time.Time
43 Deleted *time.Time
44 Edited *time.Time
45}
46
47func NewIssue(tx *sql.Tx, issue *Issue) error {
48 defer tx.Rollback()
49
50 _, err := tx.Exec(`
51 insert or ignore into repo_issue_seqs (repo_at, next_issue_id)
52 values (?, 1)
53 `, issue.RepoAt)
54 if err != nil {
55 return err
56 }
57
58 var nextId int
59 err = tx.QueryRow(`
60 update repo_issue_seqs
61 set next_issue_id = next_issue_id + 1
62 where repo_at = ?
63 returning next_issue_id - 1
64 `, issue.RepoAt).Scan(&nextId)
65 if err != nil {
66 return err
67 }
68
69 issue.IssueId = nextId
70
71 _, err = tx.Exec(`
72 insert into issues (repo_at, owner_did, issue_id, title, body)
73 values (?, ?, ?, ?, ?)
74 `, issue.RepoAt, issue.OwnerDid, issue.IssueId, issue.Title, issue.Body)
75 if err != nil {
76 return err
77 }
78
79 if err := tx.Commit(); err != nil {
80 return err
81 }
82
83 return nil
84}
85
86func SetIssueAt(e Execer, repoAt syntax.ATURI, issueId int, issueAt string) error {
87 _, err := e.Exec(`update issues set issue_at = ? where repo_at = ? and issue_id = ?`, issueAt, repoAt, issueId)
88 return err
89}
90
91func GetIssueAt(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
92 var issueAt string
93 err := e.QueryRow(`select issue_at from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&issueAt)
94 return issueAt, err
95}
96
97func GetIssueId(e Execer, repoAt syntax.ATURI) (int, error) {
98 var issueId int
99 err := e.QueryRow(`select next_issue_id from repo_issue_seqs where repo_at = ?`, repoAt).Scan(&issueId)
100 return issueId - 1, err
101}
102
103func GetIssueOwnerDid(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
104 var ownerDid string
105 err := e.QueryRow(`select owner_did from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&ownerDid)
106 return ownerDid, err
107}
108
109func GetIssues(ctx context.Context, e Execer, repoAt syntax.ATURI, isOpen bool, page pagination.Page) ([]Issue, error) {
110 ctx, span := otel.Tracer("db").Start(ctx, "GetIssues")
111 defer span.End()
112
113 span.SetAttributes(
114 attribute.String("repo_at", repoAt.String()),
115 attribute.Bool("is_open", isOpen),
116 attribute.Int("page.offset", page.Offset),
117 attribute.Int("page.limit", page.Limit),
118 )
119
120 var issues []Issue
121 openValue := 0
122 if isOpen {
123 openValue = 1
124 }
125
126 rows, err := e.QueryContext(
127 ctx,
128 `
129 with numbered_issue as (
130 select
131 i.owner_did,
132 i.issue_id,
133 i.created,
134 i.title,
135 i.body,
136 i.open,
137 count(c.id) as comment_count,
138 row_number() over (order by i.created desc) as row_num
139 from
140 issues i
141 left join
142 comments c on i.repo_at = c.repo_at and i.issue_id = c.issue_id
143 where
144 i.repo_at = ? and i.open = ?
145 group by
146 i.id, i.owner_did, i.issue_id, i.created, i.title, i.body, i.open
147 )
148 select
149 owner_did,
150 issue_id,
151 created,
152 title,
153 body,
154 open,
155 comment_count
156 from
157 numbered_issue
158 where
159 row_num between ? and ?`,
160 repoAt, openValue, page.Offset+1, page.Offset+page.Limit)
161 if err != nil {
162 span.RecordError(err)
163 return nil, err
164 }
165 defer rows.Close()
166
167 for rows.Next() {
168 var issue Issue
169 var createdAt string
170 var metadata IssueMetadata
171 err := rows.Scan(&issue.OwnerDid, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open, &metadata.CommentCount)
172 if err != nil {
173 span.RecordError(err)
174 return nil, err
175 }
176
177 createdTime, err := time.Parse(time.RFC3339, createdAt)
178 if err != nil {
179 span.RecordError(err)
180 return nil, err
181 }
182 issue.Created = createdTime
183 issue.Metadata = &metadata
184
185 issues = append(issues, issue)
186 }
187
188 if err := rows.Err(); err != nil {
189 span.RecordError(err)
190 return nil, err
191 }
192
193 span.SetAttributes(attribute.Int("issues.count", len(issues)))
194 return issues, nil
195}
196
197// timeframe here is directly passed into the sql query filter, and any
198// timeframe in the past should be negative; e.g.: "-3 months"
199func GetIssuesByOwnerDid(e Execer, ownerDid string, timeframe string) ([]Issue, error) {
200 var issues []Issue
201
202 rows, err := e.Query(
203 `select
204 i.owner_did,
205 i.repo_at,
206 i.issue_id,
207 i.created,
208 i.title,
209 i.body,
210 i.open,
211 r.did,
212 r.name,
213 r.knot,
214 r.rkey,
215 r.created
216 from
217 issues i
218 join
219 repos r on i.repo_at = r.at_uri
220 where
221 i.owner_did = ? and i.created >= date ('now', ?)
222 order by
223 i.created desc`,
224 ownerDid, timeframe)
225 if err != nil {
226 return nil, err
227 }
228 defer rows.Close()
229
230 for rows.Next() {
231 var issue Issue
232 var issueCreatedAt, repoCreatedAt string
233 var repo Repo
234 err := rows.Scan(
235 &issue.OwnerDid,
236 &issue.RepoAt,
237 &issue.IssueId,
238 &issueCreatedAt,
239 &issue.Title,
240 &issue.Body,
241 &issue.Open,
242 &repo.Did,
243 &repo.Name,
244 &repo.Knot,
245 &repo.Rkey,
246 &repoCreatedAt,
247 )
248 if err != nil {
249 return nil, err
250 }
251
252 issueCreatedTime, err := time.Parse(time.RFC3339, issueCreatedAt)
253 if err != nil {
254 return nil, err
255 }
256 issue.Created = issueCreatedTime
257
258 repoCreatedTime, err := time.Parse(time.RFC3339, repoCreatedAt)
259 if err != nil {
260 return nil, err
261 }
262 repo.Created = repoCreatedTime
263
264 issue.Metadata = &IssueMetadata{
265 Repo: &repo,
266 }
267
268 issues = append(issues, issue)
269 }
270
271 if err := rows.Err(); err != nil {
272 return nil, err
273 }
274
275 return issues, nil
276}
277
278func GetIssue(ctx context.Context, e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
279 ctx, span := otel.Tracer("db").Start(ctx, "GetIssue")
280 defer span.End()
281
282 query := `select owner_did, created, title, body, open from issues where repo_at = ? and issue_id = ?`
283 row := e.QueryRow(query, repoAt, issueId)
284
285 var issue Issue
286 var createdAt string
287 err := row.Scan(&issue.OwnerDid, &createdAt, &issue.Title, &issue.Body, &issue.Open)
288 if err != nil {
289 return nil, err
290 }
291
292 createdTime, err := time.Parse(time.RFC3339, createdAt)
293 if err != nil {
294 return nil, err
295 }
296 issue.Created = createdTime
297
298 return &issue, nil
299}
300
301func GetIssueWithComments(ctx context.Context, e Execer, repoAt syntax.ATURI, issueId int) (*Issue, []Comment, error) {
302 ctx, span := otel.Tracer("db").Start(ctx, "GetIssueWithComments")
303 defer span.End()
304
305 query := `select owner_did, issue_id, created, title, body, open from issues where repo_at = ? and issue_id = ?`
306 row := e.QueryRow(query, repoAt, issueId)
307
308 var issue Issue
309 var createdAt string
310 err := row.Scan(&issue.OwnerDid, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open)
311 if err != nil {
312 return nil, nil, err
313 }
314
315 createdTime, err := time.Parse(time.RFC3339, createdAt)
316 if err != nil {
317 return nil, nil, err
318 }
319 issue.Created = createdTime
320
321 comments, err := GetComments(e, repoAt, issueId)
322 if err != nil {
323 return nil, nil, err
324 }
325
326 return &issue, comments, nil
327}
328
329func NewIssueComment(e Execer, comment *Comment) error {
330 query := `insert into comments (owner_did, repo_at, rkey, issue_id, comment_id, body) values (?, ?, ?, ?, ?, ?)`
331 _, err := e.Exec(
332 query,
333 comment.OwnerDid,
334 comment.RepoAt,
335 comment.Rkey,
336 comment.Issue,
337 comment.CommentId,
338 comment.Body,
339 )
340 return err
341}
342
343func GetComments(e Execer, repoAt syntax.ATURI, issueId int) ([]Comment, error) {
344 var comments []Comment
345
346 rows, err := e.Query(`
347 select
348 owner_did,
349 issue_id,
350 comment_id,
351 rkey,
352 body,
353 created,
354 edited,
355 deleted
356 from
357 comments
358 where
359 repo_at = ? and issue_id = ?
360 order by
361 created asc`,
362 repoAt,
363 issueId,
364 )
365 if err == sql.ErrNoRows {
366 return []Comment{}, nil
367 }
368 if err != nil {
369 return nil, err
370 }
371 defer rows.Close()
372
373 for rows.Next() {
374 var comment Comment
375 var createdAt string
376 var deletedAt, editedAt, rkey sql.NullString
377 err := rows.Scan(&comment.OwnerDid, &comment.Issue, &comment.CommentId, &rkey, &comment.Body, &createdAt, &editedAt, &deletedAt)
378 if err != nil {
379 return nil, err
380 }
381
382 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
383 if err != nil {
384 return nil, err
385 }
386 comment.Created = &createdAtTime
387
388 if deletedAt.Valid {
389 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
390 if err != nil {
391 return nil, err
392 }
393 comment.Deleted = &deletedTime
394 }
395
396 if editedAt.Valid {
397 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
398 if err != nil {
399 return nil, err
400 }
401 comment.Edited = &editedTime
402 }
403
404 if rkey.Valid {
405 comment.Rkey = rkey.String
406 }
407
408 comments = append(comments, comment)
409 }
410
411 if err := rows.Err(); err != nil {
412 return nil, err
413 }
414
415 return comments, nil
416}
417
418func GetComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) (*Comment, error) {
419 query := `
420 select
421 owner_did, body, rkey, created, deleted, edited
422 from
423 comments where repo_at = ? and issue_id = ? and comment_id = ?
424 `
425 row := e.QueryRow(query, repoAt, issueId, commentId)
426
427 var comment Comment
428 var createdAt string
429 var deletedAt, editedAt, rkey sql.NullString
430 err := row.Scan(&comment.OwnerDid, &comment.Body, &rkey, &createdAt, &deletedAt, &editedAt)
431 if err != nil {
432 return nil, err
433 }
434
435 createdTime, err := time.Parse(time.RFC3339, createdAt)
436 if err != nil {
437 return nil, err
438 }
439 comment.Created = &createdTime
440
441 if deletedAt.Valid {
442 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
443 if err != nil {
444 return nil, err
445 }
446 comment.Deleted = &deletedTime
447 }
448
449 if editedAt.Valid {
450 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
451 if err != nil {
452 return nil, err
453 }
454 comment.Edited = &editedTime
455 }
456
457 if rkey.Valid {
458 comment.Rkey = rkey.String
459 }
460
461 comment.RepoAt = repoAt
462 comment.Issue = issueId
463 comment.CommentId = commentId
464
465 return &comment, nil
466}
467
468func EditComment(e Execer, repoAt syntax.ATURI, issueId, commentId int, newBody string) error {
469 _, err := e.Exec(
470 `
471 update comments
472 set body = ?,
473 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
474 where repo_at = ? and issue_id = ? and comment_id = ?
475 `, newBody, repoAt, issueId, commentId)
476 return err
477}
478
479func DeleteComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) error {
480 _, err := e.Exec(
481 `
482 update comments
483 set body = "",
484 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
485 where repo_at = ? and issue_id = ? and comment_id = ?
486 `, repoAt, issueId, commentId)
487 return err
488}
489
490func CloseIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
491 _, err := e.Exec(`update issues set open = 0 where repo_at = ? and issue_id = ?`, repoAt, issueId)
492 return err
493}
494
495func ReopenIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
496 _, err := e.Exec(`update issues set open = 1 where repo_at = ? and issue_id = ?`, repoAt, issueId)
497 return err
498}
499
500type IssueCount struct {
501 Open int
502 Closed int
503}
504
505func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
506 row := e.QueryRow(`
507 select
508 count(case when open = 1 then 1 end) as open_count,
509 count(case when open = 0 then 1 end) as closed_count
510 from issues
511 where repo_at = ?`,
512 repoAt,
513 )
514
515 var count IssueCount
516 if err := row.Scan(&count.Open, &count.Closed); err != nil {
517 return IssueCount{0, 0}, err
518 }
519
520 return count, nil
521}