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 CommentAt string
31 Issue int
32 CommentId int
33 Body string
34 Created *time.Time
35}
36
37func NewIssue(tx *sql.Tx, issue *Issue) error {
38 defer tx.Rollback()
39
40 _, err := tx.Exec(`
41 insert or ignore into repo_issue_seqs (repo_at, next_issue_id)
42 values (?, 1)
43 `, issue.RepoAt)
44 if err != nil {
45 return err
46 }
47
48 var nextId int
49 err = tx.QueryRow(`
50 update repo_issue_seqs
51 set next_issue_id = next_issue_id + 1
52 where repo_at = ?
53 returning next_issue_id - 1
54 `, issue.RepoAt).Scan(&nextId)
55 if err != nil {
56 return err
57 }
58
59 issue.IssueId = nextId
60
61 _, err = tx.Exec(`
62 insert into issues (repo_at, owner_did, issue_id, title, body)
63 values (?, ?, ?, ?, ?)
64 `, issue.RepoAt, issue.OwnerDid, issue.IssueId, issue.Title, issue.Body)
65 if err != nil {
66 return err
67 }
68
69 if err := tx.Commit(); err != nil {
70 return err
71 }
72
73 return nil
74}
75
76func SetIssueAt(e Execer, repoAt syntax.ATURI, issueId int, issueAt string) error {
77 _, err := e.Exec(`update issues set issue_at = ? where repo_at = ? and issue_id = ?`, issueAt, repoAt, issueId)
78 return err
79}
80
81func GetIssueAt(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
82 var issueAt string
83 err := e.QueryRow(`select issue_at from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&issueAt)
84 return issueAt, err
85}
86
87func GetIssueId(e Execer, repoAt syntax.ATURI) (int, error) {
88 var issueId int
89 err := e.QueryRow(`select next_issue_id from repo_issue_seqs where repo_at = ?`, repoAt).Scan(&issueId)
90 return issueId - 1, err
91}
92
93func GetIssueOwnerDid(e Execer, repoAt syntax.ATURI, issueId int) (string, error) {
94 var ownerDid string
95 err := e.QueryRow(`select owner_did from issues where repo_at = ? and issue_id = ?`, repoAt, issueId).Scan(&ownerDid)
96 return ownerDid, err
97}
98
99func GetIssues(e Execer, repoAt syntax.ATURI, isOpen bool) ([]Issue, error) {
100 var issues []Issue
101 openValue := 0
102 if isOpen {
103 openValue = 1
104 }
105
106 rows, err := e.Query(
107 `select
108 i.owner_did,
109 i.issue_id,
110 i.created,
111 i.title,
112 i.body,
113 i.open,
114 count(c.id)
115 from
116 issues i
117 left join
118 comments c on i.repo_at = c.repo_at and i.issue_id = c.issue_id
119 where
120 i.repo_at = ? and i.open = ?
121 group by
122 i.id, i.owner_did, i.issue_id, i.created, i.title, i.body, i.open
123 order by
124 i.created desc`,
125 repoAt, openValue)
126 if err != nil {
127 return nil, err
128 }
129 defer rows.Close()
130
131 for rows.Next() {
132 var issue Issue
133 var createdAt string
134 var metadata IssueMetadata
135 err := rows.Scan(&issue.OwnerDid, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open, &metadata.CommentCount)
136 if err != nil {
137 return nil, err
138 }
139
140 createdTime, err := time.Parse(time.RFC3339, createdAt)
141 if err != nil {
142 return nil, err
143 }
144 issue.Created = &createdTime
145 issue.Metadata = &metadata
146
147 issues = append(issues, issue)
148 }
149
150 if err := rows.Err(); err != nil {
151 return nil, err
152 }
153
154 return issues, nil
155}
156
157func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, error) {
158 query := `select owner_did, created, title, body, open from issues where repo_at = ? and issue_id = ?`
159 row := e.QueryRow(query, repoAt, issueId)
160
161 var issue Issue
162 var createdAt string
163 err := row.Scan(&issue.OwnerDid, &createdAt, &issue.Title, &issue.Body, &issue.Open)
164 if err != nil {
165 return nil, err
166 }
167
168 createdTime, err := time.Parse(time.RFC3339, createdAt)
169 if err != nil {
170 return nil, err
171 }
172 issue.Created = &createdTime
173
174 return &issue, nil
175}
176
177func GetIssueWithComments(e Execer, repoAt syntax.ATURI, issueId int) (*Issue, []Comment, error) {
178 query := `select owner_did, issue_id, created, title, body, open from issues where repo_at = ? and issue_id = ?`
179 row := e.QueryRow(query, repoAt, issueId)
180
181 var issue Issue
182 var createdAt string
183 err := row.Scan(&issue.OwnerDid, &issue.IssueId, &createdAt, &issue.Title, &issue.Body, &issue.Open)
184 if err != nil {
185 return nil, nil, err
186 }
187
188 createdTime, err := time.Parse(time.RFC3339, createdAt)
189 if err != nil {
190 return nil, nil, err
191 }
192 issue.Created = &createdTime
193
194 comments, err := GetComments(e, repoAt, issueId)
195 if err != nil {
196 return nil, nil, err
197 }
198
199 return &issue, comments, nil
200}
201
202func NewComment(e Execer, comment *Comment) error {
203 query := `insert into comments (owner_did, repo_at, comment_at, issue_id, comment_id, body) values (?, ?, ?, ?, ?, ?)`
204 _, err := e.Exec(
205 query,
206 comment.OwnerDid,
207 comment.RepoAt,
208 comment.CommentAt,
209 comment.Issue,
210 comment.CommentId,
211 comment.Body,
212 )
213 return err
214}
215
216func GetComments(e Execer, repoAt syntax.ATURI, issueId int) ([]Comment, error) {
217 var comments []Comment
218
219 rows, err := e.Query(`select owner_did, issue_id, comment_id, comment_at, body, created from comments where repo_at = ? and issue_id = ? order by created asc`, repoAt, issueId)
220 if err == sql.ErrNoRows {
221 return []Comment{}, nil
222 }
223 if err != nil {
224 return nil, err
225 }
226 defer rows.Close()
227
228 for rows.Next() {
229 var comment Comment
230 var createdAt string
231 err := rows.Scan(&comment.OwnerDid, &comment.Issue, &comment.CommentId, &comment.CommentAt, &comment.Body, &createdAt)
232 if err != nil {
233 return nil, err
234 }
235
236 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
237 if err != nil {
238 return nil, err
239 }
240 comment.Created = &createdAtTime
241
242 comments = append(comments, comment)
243 }
244
245 if err := rows.Err(); err != nil {
246 return nil, err
247 }
248
249 return comments, nil
250}
251
252func CloseIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
253 _, err := e.Exec(`update issues set open = 0 where repo_at = ? and issue_id = ?`, repoAt, issueId)
254 return err
255}
256
257func ReopenIssue(e Execer, repoAt syntax.ATURI, issueId int) error {
258 _, err := e.Exec(`update issues set open = 1 where repo_at = ? and issue_id = ?`, repoAt, issueId)
259 return err
260}
261
262type IssueCount struct {
263 Open int
264 Closed int
265}
266
267func GetIssueCount(e Execer, repoAt syntax.ATURI) (IssueCount, error) {
268 row := e.QueryRow(`
269 select
270 count(case when open = 1 then 1 end) as open_count,
271 count(case when open = 0 then 1 end) as closed_count
272 from issues
273 where repo_at = ?`,
274 repoAt,
275 )
276
277 var count IssueCount
278 if err := row.Scan(&count.Open, &count.Closed); err != nil {
279 return IssueCount{0, 0}, err
280 }
281
282 return count, nil
283}