OXYGENHUB
# 3b3a51afde59 oxygenhub / crates / hub / src / db / issues.rs
14.3 KiB · Rust · last changed Postgres, step 1: every query numbers its placeholders $1, $2, ... 20 days ago source blame history raw
1 
//! Issues, labels, milestones and their events (hub spec §8).
2
3 
use super::{now_ms, Pool};
4 
use sqlx::FromRow;
5
6 
#[derive(Clone, Debug, FromRow)]
7 
pub struct IssueRow {
8 
    pub id: i64,
9 
    pub repo_id: i64,
10 
    pub number: i64,
11 
    pub title: String,
12 
    pub body: String,
13 
    pub author_id: Option<i64>,
14 
    pub author: String,
15 
    pub state: String,
16 
    pub reason: String,
17 
    pub locked: bool,
18 
    pub pinned: bool,
19 
    pub milestone_id: Option<i64>,
20 
    pub created_ms: i64,
21 
    pub updated_ms: i64,
22 
    pub closed_ms: Option<i64>,
23 
}
24
25 
#[derive(Clone, Debug, FromRow)]
26 
pub struct IssueComment {
27 
    pub id: i64,
28 
    pub issue_id: i64,
29 
    pub author_id: Option<i64>,
30 
    pub author: String,
31 
    pub body: String,
32 
    pub created_ms: i64,
33 
    pub edited_ms: Option<i64>,
34 
}
35
36 
#[derive(Clone, Debug, FromRow)]
37 
pub struct IssueEvent {
38 
    pub id: i64,
39 
    pub at_ms: i64,
40 
    pub actor: String,
41 
    pub event: String,
42 
    pub detail: String,
43 
}
44
45 
#[derive(Clone, Debug, FromRow, PartialEq, Eq)]
46 
pub struct Label {
47 
    pub id: i64,
48 
    pub repo_id: i64,
49 
    pub name: String,
50 
    pub colour: String,
51 
    pub description: String,
52 
}
53
54 
#[derive(Clone, Debug, FromRow)]
55 
pub struct Milestone {
56 
    pub id: i64,
57 
    pub repo_id: i64,
58 
    pub title: String,
59 
    pub description: String,
60 
    pub due_ms: Option<i64>,
61 
    pub state: String,
62 
}
63
64 
pub struct NewIssue<'a> {
65 
    pub repo_id: i64,
66 
    pub title: &'a str,
67 
    pub body: &'a str,
68 
    pub author_id: Option<i64>,
69 
    pub author: &'a str,
70 
}
71
72 
pub async fn create(pool: &Pool, i: NewIssue<'_>) -> sqlx::Result<IssueRow> {
73 
    let number = super::proposals::next_number(pool, i.repo_id).await?;
74 
    let now = now_ms();
75 
    sqlx::query_as("INSERT INTO issues (repo_id, number, title, body, author_id, author, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *")
76 
        .bind(i.repo_id)
77 
        .bind(number)
78 
        .bind(i.title)
79 
        .bind(i.body)
80 
        .bind(i.author_id)
81 
        .bind(i.author)
82 
        .bind(now)
83 
        .bind(now)
84 
        .fetch_one(pool)
85 
        .await
86 
}
87
88 
pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<IssueRow>> {
89 
    sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await
90 
}
91
92 
pub async fn get_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<IssueRow>> {
93 
    sqlx::query_as("SELECT * FROM issues WHERE id = $1").bind(id).fetch_optional(pool).await
94 
}
95
96 
pub async fn list(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<IssueRow>> {
97 
    sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 ORDER BY pinned DESC, number DESC").bind(repo_id).fetch_all(pool).await
98 
}
99
100 
/// Numbers in use by issues and proposals, for cross references.
101 
pub async fn numbers(pool: &Pool, repo_id: i64) -> sqlx::Result<(Vec<i64>, Vec<i64>)> {
102 
    let issues: Vec<i64> = sqlx::query_scalar("SELECT number FROM issues WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
103 
    let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
104 
    Ok((issues, proposals))
105 
}
106
107 
pub async fn assigned_to(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<IssueRow>> {
108 
    sqlx::query_as("SELECT i.* FROM issues i JOIN issue_assignees a ON a.issue_id = i.id WHERE a.account_id = $1 AND i.state = 'open' ORDER BY i.updated_ms DESC")
109 
        .bind(account_id)
110 
        .fetch_all(pool)
111 
        .await
112 
}
113
114 
pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str) -> sqlx::Result<()> {
115 
    sqlx::query("UPDATE issues SET title = $1, body = $2, updated_ms = $3 WHERE id = $4")
116 
        .bind(title)
117 
        .bind(body)
118 
        .bind(now_ms())
119 
        .bind(id)
120 
        .execute(pool)
121 
        .await?;
122 
    Ok(())
123 
}
124
125 
pub async fn set_state(pool: &Pool, id: i64, state: &str, reason: &str) -> sqlx::Result<()> {
126 
    let now = now_ms();
127 
    let closed: Option<i64> = if state == "closed" { Some(now) } else { None };
128 
    sqlx::query("UPDATE issues SET state = $1, reason = $2, closed_ms = $3, updated_ms = $4 WHERE id = $5")
129 
        .bind(state)
130 
        .bind(reason)
131 
        .bind(closed)
132 
        .bind(now)
133 
        .bind(id)
134 
        .execute(pool)
135 
        .await?;
136 
    Ok(())
137 
}
138
139 
pub async fn set_flags(pool: &Pool, id: i64, locked: Option<bool>, pinned: Option<bool>) -> sqlx::Result<()> {
140 
    if let Some(l) = locked {
141 
        sqlx::query("UPDATE issues SET locked = $1 WHERE id = $2").bind(l).bind(id).execute(pool).await?;
142 
    }
143 
    if let Some(p) = pinned {
144 
        sqlx::query("UPDATE issues SET pinned = $1 WHERE id = $2").bind(p).bind(id).execute(pool).await?;
145 
    }
146 
    Ok(())
147 
}
148
149 
pub async fn set_milestone(pool: &Pool, id: i64, milestone_id: Option<i64>) -> sqlx::Result<()> {
150 
    sqlx::query("UPDATE issues SET milestone_id = $1, updated_ms = $2 WHERE id = $3")
151 
        .bind(milestone_id)
152 
        .bind(now_ms())
153 
        .bind(id)
154 
        .execute(pool)
155 
        .await?;
156 
    Ok(())
157 
}
158
159 
pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
160 
    sqlx::query("UPDATE issues SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
161 
    Ok(())
162 
}
163
164 
pub async fn comments(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueComment>> {
165 
    sqlx::query_as("SELECT * FROM issue_comments WHERE issue_id = $1 ORDER BY created_ms, id").bind(issue_id).fetch_all(pool).await
166 
}
167
168 
pub async fn add_comment(pool: &Pool, issue_id: i64, author_id: Option<i64>, author: &str, body: &str) -> sqlx::Result<IssueComment> {
169 
    let row = sqlx::query_as(
170 
        "INSERT INTO issue_comments (issue_id, author_id, author, body, created_ms) VALUES ($1, $2, $3, $4, $5) RETURNING *",
171 
    )
172 
    .bind(issue_id)
173 
    .bind(author_id)
174 
    .bind(author)
175 
    .bind(body)
176 
    .bind(now_ms())
177 
    .fetch_one(pool)
178 
    .await?;
179 
    touch(pool, issue_id).await?;
180 
    Ok(row)
181 
}
182
183 
pub async fn edit_comment(pool: &Pool, id: i64, author_id: i64, body: &str) -> sqlx::Result<bool> {
184 
    let n = sqlx::query("UPDATE issue_comments SET body = $1, edited_ms = $2 WHERE id = $3 AND author_id = $4")
185 
        .bind(body)
186 
        .bind(now_ms())
187 
        .bind(id)
188 
        .bind(author_id)
189 
        .execute(pool)
190 
        .await?;
191 
    Ok(n.rows_affected() > 0)
192 
}
193
194 
pub async fn delete_comment(pool: &Pool, id: i64) -> sqlx::Result<()> {
195 
    sqlx::query("DELETE FROM issue_comments WHERE id = $1").bind(id).execute(pool).await?;
196 
    Ok(())
197 
}
198
199 
pub async fn events(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueEvent>> {
200 
    sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM issue_events WHERE issue_id = $1 ORDER BY at_ms, id")
201 
        .bind(issue_id)
202 
        .fetch_all(pool)
203 
        .await
204 
}
205
206 
pub async fn add_event(pool: &Pool, issue_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
207 
    sqlx::query("INSERT INTO issue_events (issue_id, at_ms, actor, event, detail) VALUES ($1, $2, $3, $4, $5)")
208 
        .bind(issue_id)
209 
        .bind(now_ms())
210 
        .bind(actor)
211 
        .bind(event)
212 
        .bind(detail)
213 
        .execute(pool)
214 
        .await?;
215 
    Ok(())
216 
}
217
218 
// ------------------------------------------------------------------ labels
219
220 
pub async fn labels(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Label>> {
221 
    sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 ORDER BY name").bind(repo_id).fetch_all(pool).await
222 
}
223
224 
pub async fn label_by_name(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<Label>> {
225 
    sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 AND name = $2").bind(repo_id).bind(name).fetch_optional(pool).await
226 
}
227
228 
pub async fn upsert_label(pool: &Pool, repo_id: i64, name: &str, colour: &str, description: &str) -> sqlx::Result<Label> {
229 
    sqlx::query_as("INSERT INTO labels (repo_id, name, colour, description) VALUES ($1, $2, $3, $4) ON CONFLICT (repo_id, name) DO UPDATE SET colour = excluded.colour, description = excluded.description RETURNING *")
230 
        .bind(repo_id)
231 
        .bind(name)
232 
        .bind(colour)
233 
        .bind(description)
234 
        .fetch_one(pool)
235 
        .await
236 
}
237
238 
pub async fn delete_label(pool: &Pool, id: i64) -> sqlx::Result<()> {
239 
    sqlx::query("DELETE FROM labels WHERE id = $1").bind(id).execute(pool).await?;
240 
    Ok(())
241 
}
242
243 
pub async fn issue_labels(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<Label>> {
244 
    sqlx::query_as("SELECT l.* FROM labels l JOIN issue_labels il ON il.label_id = l.id WHERE il.issue_id = $1 ORDER BY l.name")
245 
        .bind(issue_id)
246 
        .fetch_all(pool)
247 
        .await
248 
}
249
250 
pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
251 
    if on {
252 
        sqlx::query("INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES ($1, $2)")
253 
            .bind(issue_id)
254 
            .bind(label_id)
255 
            .execute(pool)
256 
            .await?;
257 
    } else {
258 
        sqlx::query("DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2").bind(issue_id).bind(label_id).execute(pool).await?;
259 
    }
260 
    Ok(())
261 
}
262
263 
/// Labels of every issue of a repository, for the list page.
264 
/// Every proposal's labels in a repository, for filtering the list.
265 
pub async fn labels_by_proposal(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
266 
    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
267 
        "SELECT pl.proposal_id, l.id, l.repo_id, l.name, l.colour, l.description FROM proposal_labels pl JOIN labels l ON l.id = pl.label_id JOIN proposals p ON p.id = pl.proposal_id WHERE p.repo_id = $1 ORDER BY l.name",
268 
    )
269 
    .bind(repo_id)
270 
    .fetch_all(pool)
271 
    .await?;
272 
    Ok(rows
273 
        .into_iter()
274 
        .map(|(proposal_id, id, repo_id, name, colour, description)| (proposal_id, Label { id, repo_id, name, colour, description }))
275 
        .collect())
276 
}
277
278 
pub async fn labels_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
279 
    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
280 
        "SELECT il.issue_id, l.id, l.repo_id, l.name, l.colour, l.description FROM issue_labels il JOIN labels l ON l.id = il.label_id JOIN issues i ON i.id = il.issue_id WHERE i.repo_id = $1 ORDER BY l.name",
281 
    )
282 
    .bind(repo_id)
283 
    .fetch_all(pool)
284 
    .await?;
285 
    Ok(rows
286 
        .into_iter()
287 
        .map(|(issue_id, id, repo_id, name, colour, description)| (issue_id, Label { id, repo_id, name, colour, description }))
288 
        .collect())
289 
}
290
291 
pub async fn proposal_labels(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<Label>> {
292 
    sqlx::query_as("SELECT l.* FROM labels l JOIN proposal_labels pl ON pl.label_id = l.id WHERE pl.proposal_id = $1 ORDER BY l.name")
293 
        .bind(proposal_id)
294 
        .fetch_all(pool)
295 
        .await
296 
}
297
298 
pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
299 
    if on {
300 
        sqlx::query("INSERT OR IGNORE INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2)")
301 
            .bind(proposal_id)
302 
            .bind(label_id)
303 
            .execute(pool)
304 
            .await?;
305 
    } else {
306 
        sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = $1 AND label_id = $2")
307 
            .bind(proposal_id)
308 
            .bind(label_id)
309 
            .execute(pool)
310 
            .await?;
311 
    }
312 
    Ok(())
313 
}
314
315 
// --------------------------------------------------------------- assignees
316
317 
pub async fn assignees(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<String>> {
318 
    sqlx::query_scalar(
319 
        "SELECT a.username FROM accounts a JOIN issue_assignees ia ON ia.account_id = a.id WHERE ia.issue_id = $1 ORDER BY a.username",
320 
    )
321 
    .bind(issue_id)
322 
    .fetch_all(pool)
323 
    .await
324 
}
325
326 
pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> {
327 
    if on {
328 
        sqlx::query("INSERT OR IGNORE INTO issue_assignees (issue_id, account_id) VALUES ($1, $2)")
329 
            .bind(issue_id)
330 
            .bind(account_id)
331 
            .execute(pool)
332 
            .await?;
333 
    } else {
334 
        sqlx::query("DELETE FROM issue_assignees WHERE issue_id = $1 AND account_id = $2")
335 
            .bind(issue_id)
336 
            .bind(account_id)
337 
            .execute(pool)
338 
            .await?;
339 
    }
340 
    Ok(())
341 
}
342
343 
/// Assignees of every issue of a repository, for the list page.
344 
pub async fn assignees_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, String)>> {
345 
    sqlx::query_as("SELECT ia.issue_id, a.username FROM issue_assignees ia JOIN accounts a ON a.id = ia.account_id JOIN issues i ON i.id = ia.issue_id WHERE i.repo_id = $1")
346 
        .bind(repo_id)
347 
        .fetch_all(pool)
348 
        .await
349 
}
350
351 
// -------------------------------------------------------------- milestones
352
353 
pub async fn milestones(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Milestone>> {
354 
    sqlx::query_as("SELECT * FROM milestones WHERE repo_id = $1 ORDER BY state, due_ms, title").bind(repo_id).fetch_all(pool).await
355 
}
356
357 
pub async fn milestone(pool: &Pool, id: i64) -> sqlx::Result<Option<Milestone>> {
358 
    sqlx::query_as("SELECT * FROM milestones WHERE id = $1").bind(id).fetch_optional(pool).await
359 
}
360
361 
pub async fn upsert_milestone(pool: &Pool, repo_id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<Milestone> {
362 
    sqlx::query_as("INSERT INTO milestones (repo_id, title, description, due_ms) VALUES ($1, $2, $3, $4) ON CONFLICT (repo_id, title) DO UPDATE SET description = excluded.description, due_ms = excluded.due_ms RETURNING *")
363 
        .bind(repo_id)
364 
        .bind(title)
365 
        .bind(description)
366 
        .bind(due_ms)
367 
        .fetch_one(pool)
368 
        .await
369 
}
370
371 
/// Change a milestone's title, description and due date.
372 
pub async fn update_milestone(pool: &Pool, id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<()> {
373 
    sqlx::query("UPDATE milestones SET title = $1, description = $2, due_ms = $3 WHERE id = $4")
374 
        .bind(title)
375 
        .bind(description)
376 
        .bind(due_ms)
377 
        .bind(id)
378 
        .execute(pool)
379 
        .await?;
380 
    Ok(())
381 
}
382
383 
pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {
384 
    sqlx::query("UPDATE milestones SET state = $1 WHERE id = $2").bind(state).bind(id).execute(pool).await?;
385 
    Ok(())
386 
}
387
388 
/// `(open, closed)` issue counts per milestone.
389 
pub async fn milestone_counts(pool: &Pool, milestone_id: i64) -> sqlx::Result<(i64, i64)> {
390 
    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'open'")
391 
        .bind(milestone_id)
392 
        .fetch_one(pool)
393 
        .await?;
394 
    let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'closed'")
395 
        .bind(milestone_id)
396 
        .fetch_one(pool)
397 
        .await?;
398 
    Ok((open, closed))
399 
}