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