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