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