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