kot.pink
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 GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
160 query := `select owner_did, created, title, body, open from issues where repo_at = ? and issue_id = ?`
161 row := e.QueryRow(query, repoAt, issueId)
162
163 var issue Issue
164 var createdAt string
165 err := row.Scan(&issue.OwnerDid, &createdAt, &issue.Title, &issue.Body, &issue.Open)
166 if err != nil {
167 return nil, err
168 }
169
170 createdTime, err := time.Parse(time.RFC3339, createdAt)
171 if err != nil {
172 return nil, err
173 }
174 issue.Created = &createdTime
175
176 return &issue, nil
177}
178
179func GetIssueWithComments(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, []Comment, error) {
180 query := `select owner_did, issue_id, created, title, body, open from issues where repo_at = ? and issue_id = ?`
181 row := e.QueryRow(query, repoAt, issueId)
182
183 var issue Issue
184 var createdAt string
185 err := row.Scan(&issue.OwnerDid, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open)
186 if err != nil {
187 return nil, nil, err
188 }
189
190 createdTime, err := time.Parse(time.RFC3339, createdAt)
191 if err != nil {
192 return nil, nil, err
193 }
194 issue.Created = &createdTime
195
196 comments, err := GetComments(e, repoAt, issueId)
197 if err != nil {
198 return nil, nil, err
199 }
200
201 return &issue, comments, nil
202}
203
204func NewIssueComment(e Execer, comment *Comment) error {
205 query := `insert into comments (owner_did, repo_at, rkey, issue_id, comment_id, body) values (?, ?, ?, ?, ?, ?)`
206 _, err := e.Exec(
207 query,
208 comment.OwnerDid,
209 comment.RepoAt,
210 comment.Rkey,
211 comment.Issue,
212 comment.CommentId,
213 comment.Body,
214 )
215 return err
216}
217
218func GetComments(e Execer, repoAt syntax.ATURI, issueId int) ([]Comment, error) {
219 var comments []Comment
220
221 rows, err := e.Query(`
222 select
223 owner_did,
224 issue_id,
225 comment_id,
226 rkey,
227 body,
228 created,
229 edited,
230 deleted
231 from
232 comments
233 where
234 repo_at = ? and issue_id = ?
235 order by
236 created asc`,
237 repoAt,
238 issueId,
239 )
240 if err == sql.ErrNoRows {
241 return []Comment{}, nil
242 }
243 if err != nil {
244 return nil, err
245 }
246 defer rows.Close()
247
248 for rows.Next() {
249 var comment Comment
250 var createdAt string
251 var deletedAt, editedAt, rkey sql.NullString
252 err := rows.Scan(&comment.OwnerDid, &comment.Issue, &comment.CommentId, &rkey, &comment.Body, &createdAt, &editedAt, &deletedAt)
253 if err != nil {
254 return nil, err
255 }
256
257 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
258 if err != nil {
259 return nil, err
260 }
261 comment.Created = &createdAtTime
262
263 if deletedAt.Valid {
264 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
265 if err != nil {
266 return nil, err
267 }
268 comment.Deleted = &deletedTime
269 }
270
271 if editedAt.Valid {
272 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
273 if err != nil {
274 return nil, err
275 }
276 comment.Edited = &editedTime
277 }
278
279 if rkey.Valid {
280 comment.Rkey = rkey.String
281 }
282
283 comments = append(comments, comment)
284 }
285
286 if err := rows.Err(); err != nil {
287 return nil, err
288 }
289
290 return comments, nil
291}
292
293func GetComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) (*Comment, error) {
294 query := `
295 select
296 owner_did, body, rkey, created, deleted, edited
297 from
298 comments where repo_at = ? and issue_id = ? and comment_id = ?
299 `
300 row := e.QueryRow(query, repoAt, issueId, commentId)
301
302 var comment Comment
303 var createdAt string
304 var deletedAt, editedAt, rkey sql.NullString
305 err := row.Scan(&comment.OwnerDid, &comment.Body, &rkey, &createdAt, &deletedAt, &editedAt)
306 if err != nil {
307 return nil, err
308 }
309
310 createdTime, err := time.Parse(time.RFC3339, createdAt)
311 if err != nil {
312 return nil, err
313 }
314 comment.Created = &createdTime
315
316 if deletedAt.Valid {
317 deletedTime, err := time.Parse(time.RFC3339, deletedAt.String)
318 if err != nil {
319 return nil, err
320 }
321 comment.Deleted = &deletedTime
322 }
323
324 if editedAt.Valid {
325 editedTime, err := time.Parse(time.RFC3339, editedAt.String)
326 if err != nil {
327 return nil, err
328 }
329 comment.Edited = &editedTime
330 }
331
332 if rkey.Valid {
333 comment.Rkey = rkey.String
334 }
335
336 comment.RepoAt = repoAt
337 comment.Issue = issueId
338 comment.CommentId = commentId
339
340 return &comment, nil
341}
342
343func EditComment(e Execer, repoAt syntax.ATURI, issueId, commentId int, newBody string) error {
344 _, err := e.Exec(
345 `
346 update comments
347 set body = ?,
348 edited = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
349 where repo_at = ? and issue_id = ? and comment_id = ?
350 `, newBody, repoAt, issueId, commentId)
351 return err
352}
353
354func DeleteComment(e Execer, repoAt syntax.ATURI, issueId, commentId int) error {
355 _, err := e.Exec(
356 `
357 update comments
358 set body = "",
359 deleted = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
360 where repo_at = ? and issue_id = ? and comment_id = ?
361 `, repoAt, issueId, commentId)
362 return err
363}
364
365func CloseIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
366 _, err := e.Exec(`update issues set open = 0 where repo_at = ? and issue_id = ?`, repoAt, issueId)
367 return err
368}
369
370func ReopenIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
371 _, err := e.Exec(`update issues set open = 1 where repo_at = ? and issue_id = ?`, repoAt, issueId)
372 return err
373}
374
375type IssueCount struct {
376 Open int
377 Closed int
378}
379
380func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
381 row := e.QueryRow(`
382 select
383 count(case when open = 1 then 1 end) as open_count,
384 count(case when open = 0 then 1 end) as closed_count
385 from issues
386 where repo_at = ?`,
387 repoAt,
388 )
389
390 var count IssueCount
391 if err := row.Scan(&count.Open, &count.Closed); err != nil {
392 return IssueCount{0, 0}, err
393 }
394
395 return count, nil
396}