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