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