crates/hub/src/db/issues.rs at v0.1.0 · andymccall/oxygenhub

◆ v0.1.0 oxygenhub / crates / hub / src / db / issues.rs

13.9 KiB · Rust · last changed Lists: filters, sort orders, pages, and counts on the tabs 1 month ago source blame history raw

//! Issues, labels, milestones and their events (hub spec §8).

use super::{now_ms, Pool};
use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]
pub struct IssueRow {
    pub id: i64,
    pub repo_id: i64,
    pub number: i64,
    pub title: String,
    pub body: String,
    pub author_id: Option<i64>,
    pub author: String,
    pub state: String,
    pub reason: String,
    pub locked: bool,
    pub pinned: bool,
    pub milestone_id: Option<i64>,
    pub created_ms: i64,
    pub updated_ms: i64,
    pub closed_ms: Option<i64>,
}

#[derive(Clone, Debug, FromRow)]
pub struct IssueComment {
    pub id: i64,
    pub issue_id: i64,
    pub author_id: Option<i64>,
    pub author: String,
    pub body: String,
    pub created_ms: i64,
    pub edited_ms: Option<i64>,
}

#[derive(Clone, Debug, FromRow)]
pub struct IssueEvent {
    pub id: i64,
    pub at_ms: i64,
    pub actor: String,
    pub event: String,
    pub detail: String,
}

#[derive(Clone, Debug, FromRow, PartialEq, Eq)]
pub struct Label {
    pub id: i64,
    pub repo_id: i64,
    pub name: String,
    pub colour: String,
    pub description: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct Milestone {
    pub id: i64,
    pub repo_id: i64,
    pub title: String,
    pub description: String,
    pub due_ms: Option<i64>,
    pub state: String,
}

pub struct NewIssue<'a> {
    pub repo_id: i64,
    pub title: &'a str,
    pub body: &'a str,
    pub author_id: Option<i64>,
    pub author: &'a str,
}

pub async fn create(pool: &Pool, i: NewIssue<'_>) -> sqlx::Result<IssueRow> {
    let number = super::proposals::next_number(pool, i.repo_id).await?;
    let now = now_ms();
    sqlx::query_as("INSERT INTO issues (repo_id, number, title, body, author_id, author, created_ms, updated_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *")
        .bind(i.repo_id)
        .bind(number)
        .bind(i.title)
        .bind(i.body)
        .bind(i.author_id)
        .bind(i.author)
        .bind(now)
        .bind(now)
        .fetch_one(pool)
        .await
}

pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<IssueRow>> {
    sqlx::query_as("SELECT * FROM issues WHERE repo_id = ? AND number = ?").bind(repo_id).bind(number).fetch_optional(pool).await
}

pub async fn get_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<IssueRow>> {
    sqlx::query_as("SELECT * FROM issues WHERE id = ?").bind(id).fetch_optional(pool).await
}

pub async fn list(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<IssueRow>> {
    sqlx::query_as("SELECT * FROM issues WHERE repo_id = ? ORDER BY pinned DESC, number DESC").bind(repo_id).fetch_all(pool).await
}

/// Numbers in use by issues and proposals, for cross references.
pub async fn numbers(pool: &Pool, repo_id: i64) -> sqlx::Result<(Vec<i64>, Vec<i64>)> {
    let issues: Vec<i64> = sqlx::query_scalar("SELECT number FROM issues WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
    let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
    Ok((issues, proposals))
}

pub async fn assigned_to(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<IssueRow>> {
    sqlx::query_as("SELECT i.* FROM issues i JOIN issue_assignees a ON a.issue_id = i.id WHERE a.account_id = ? AND i.state = 'open' ORDER BY i.updated_ms DESC")
        .bind(account_id)
        .fetch_all(pool)
        .await
}

pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE issues SET title = ?, body = ?, updated_ms = ? WHERE id = ?")
        .bind(title)
        .bind(body)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_state(pool: &Pool, id: i64, state: &str, reason: &str) -> sqlx::Result<()> {
    let now = now_ms();
    let closed: Option<i64> = if state == "closed" { Some(now) } else { None };
    sqlx::query("UPDATE issues SET state = ?, reason = ?, closed_ms = ?, updated_ms = ? WHERE id = ?")
        .bind(state)
        .bind(reason)
        .bind(closed)
        .bind(now)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_flags(pool: &Pool, id: i64, locked: Option<bool>, pinned: Option<bool>) -> sqlx::Result<()> {
    if let Some(l) = locked {
        sqlx::query("UPDATE issues SET locked = ? WHERE id = ?").bind(l).bind(id).execute(pool).await?;
    }
    if let Some(p) = pinned {
        sqlx::query("UPDATE issues SET pinned = ? WHERE id = ?").bind(p).bind(id).execute(pool).await?;
    }
    Ok(())
}

pub async fn set_milestone(pool: &Pool, id: i64, milestone_id: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("UPDATE issues SET milestone_id = ?, updated_ms = ? WHERE id = ?")
        .bind(milestone_id)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE issues SET updated_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn comments(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueComment>> {
    sqlx::query_as("SELECT * FROM issue_comments WHERE issue_id = ? ORDER BY created_ms, id").bind(issue_id).fetch_all(pool).await
}

pub async fn add_comment(pool: &Pool, issue_id: i64, author_id: Option<i64>, author: &str, body: &str) -> sqlx::Result<IssueComment> {
    let row =
        sqlx::query_as("INSERT INTO issue_comments (issue_id, author_id, author, body, created_ms) VALUES (?, ?, ?, ?, ?) RETURNING *")
            .bind(issue_id)
            .bind(author_id)
            .bind(author)
            .bind(body)
            .bind(now_ms())
            .fetch_one(pool)
            .await?;
    touch(pool, issue_id).await?;
    Ok(row)
}

pub async fn edit_comment(pool: &Pool, id: i64, author_id: i64, body: &str) -> sqlx::Result<bool> {
    let n = sqlx::query("UPDATE issue_comments SET body = ?, edited_ms = ? WHERE id = ? AND author_id = ?")
        .bind(body)
        .bind(now_ms())
        .bind(id)
        .bind(author_id)
        .execute(pool)
        .await?;
    Ok(n.rows_affected() > 0)
}

pub async fn delete_comment(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM issue_comments WHERE id = ?").bind(id).execute(pool).await?;
    Ok(())
}

pub async fn events(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueEvent>> {
    sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM issue_events WHERE issue_id = ? ORDER BY at_ms, id")
        .bind(issue_id)
        .fetch_all(pool)
        .await
}

pub async fn add_event(pool: &Pool, issue_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO issue_events (issue_id, at_ms, actor, event, detail) VALUES (?, ?, ?, ?, ?)")
        .bind(issue_id)
        .bind(now_ms())
        .bind(actor)
        .bind(event)
        .bind(detail)
        .execute(pool)
        .await?;
    Ok(())
}

// ------------------------------------------------------------------ labels

pub async fn labels(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Label>> {
    sqlx::query_as("SELECT * FROM labels WHERE repo_id = ? ORDER BY name").bind(repo_id).fetch_all(pool).await
}

pub async fn label_by_name(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<Label>> {
    sqlx::query_as("SELECT * FROM labels WHERE repo_id = ? AND name = ?").bind(repo_id).bind(name).fetch_optional(pool).await
}

pub async fn upsert_label(pool: &Pool, repo_id: i64, name: &str, colour: &str, description: &str) -> sqlx::Result<Label> {
    sqlx::query_as("INSERT INTO labels (repo_id, name, colour, description) VALUES (?, ?, ?, ?) ON CONFLICT (repo_id, name) DO UPDATE SET colour = excluded.colour, description = excluded.description RETURNING *")
        .bind(repo_id)
        .bind(name)
        .bind(colour)
        .bind(description)
        .fetch_one(pool)
        .await
}

pub async fn delete_label(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM labels WHERE id = ?").bind(id).execute(pool).await?;
    Ok(())
}

pub async fn issue_labels(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<Label>> {
    sqlx::query_as("SELECT l.* FROM labels l JOIN issue_labels il ON il.label_id = l.id WHERE il.issue_id = ? ORDER BY l.name")
        .bind(issue_id)
        .fetch_all(pool)
        .await
}

pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
    if on {
        sqlx::query("INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)")
            .bind(issue_id)
            .bind(label_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM issue_labels WHERE issue_id = ? AND label_id = ?").bind(issue_id).bind(label_id).execute(pool).await?;
    }
    Ok(())
}

/// Labels of every issue of a repository, for the list page.
/// Every proposal's labels in a repository, for filtering the list.
pub async fn labels_by_proposal(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
        "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 = ? ORDER BY l.name",
    )
    .bind(repo_id)
    .fetch_all(pool)
    .await?;
    Ok(rows
        .into_iter()
        .map(|(proposal_id, id, repo_id, name, colour, description)| (proposal_id, Label { id, repo_id, name, colour, description }))
        .collect())
}

pub async fn labels_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
        "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 = ? ORDER BY l.name",
    )
    .bind(repo_id)
    .fetch_all(pool)
    .await?;
    Ok(rows
        .into_iter()
        .map(|(issue_id, id, repo_id, name, colour, description)| (issue_id, Label { id, repo_id, name, colour, description }))
        .collect())
}

pub async fn proposal_labels(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<Label>> {
    sqlx::query_as("SELECT l.* FROM labels l JOIN proposal_labels pl ON pl.label_id = l.id WHERE pl.proposal_id = ? ORDER BY l.name")
        .bind(proposal_id)
        .fetch_all(pool)
        .await
}

pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
    if on {
        sqlx::query("INSERT OR IGNORE INTO proposal_labels (proposal_id, label_id) VALUES (?, ?)")
            .bind(proposal_id)
            .bind(label_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = ? AND label_id = ?")
            .bind(proposal_id)
            .bind(label_id)
            .execute(pool)
            .await?;
    }
    Ok(())
}

// --------------------------------------------------------------- assignees

pub async fn assignees(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar(
        "SELECT a.username FROM accounts a JOIN issue_assignees ia ON ia.account_id = a.id WHERE ia.issue_id = ? ORDER BY a.username",
    )
    .bind(issue_id)
    .fetch_all(pool)
    .await
}

pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> {
    if on {
        sqlx::query("INSERT OR IGNORE INTO issue_assignees (issue_id, account_id) VALUES (?, ?)")
            .bind(issue_id)
            .bind(account_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM issue_assignees WHERE issue_id = ? AND account_id = ?")
            .bind(issue_id)
            .bind(account_id)
            .execute(pool)
            .await?;
    }
    Ok(())
}

/// Assignees of every issue of a repository, for the list page.
pub async fn assignees_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, String)>> {
    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 = ?")
        .bind(repo_id)
        .fetch_all(pool)
        .await
}

// -------------------------------------------------------------- milestones

pub async fn milestones(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Milestone>> {
    sqlx::query_as("SELECT * FROM milestones WHERE repo_id = ? ORDER BY state, due_ms, title").bind(repo_id).fetch_all(pool).await
}

pub async fn milestone(pool: &Pool, id: i64) -> sqlx::Result<Option<Milestone>> {
    sqlx::query_as("SELECT * FROM milestones WHERE id = ?").bind(id).fetch_optional(pool).await
}

pub async fn upsert_milestone(pool: &Pool, repo_id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<Milestone> {
    sqlx::query_as("INSERT INTO milestones (repo_id, title, description, due_ms) VALUES (?, ?, ?, ?) ON CONFLICT (repo_id, title) DO UPDATE SET description = excluded.description, due_ms = excluded.due_ms RETURNING *")
        .bind(repo_id)
        .bind(title)
        .bind(description)
        .bind(due_ms)
        .fetch_one(pool)
        .await
}

pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE milestones SET state = ? WHERE id = ?").bind(state).bind(id).execute(pool).await?;
    Ok(())
}

/// `(open, closed)` issue counts per milestone.
pub async fn milestone_counts(pool: &Pool, milestone_id: i64) -> sqlx::Result<(i64, i64)> {
    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = ? AND state = 'open'")
        .bind(milestone_id)
        .fetch_one(pool)
        .await?;
    let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = ? AND state = 'closed'")
        .bind(milestone_id)
        .fetch_one(pool)
        .await?;
    Ok((open, closed))
}

`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet

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 (?, ?, ?, ?, ?, ?, ?, ?) 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 = ? AND number = ?").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 = ?").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 = ? 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 = ?").bind(repo_id).fetch_all(pool).await?;
103	let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = ?").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 = ? 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 = ?, body = ?, updated_ms = ? WHERE id = ?")
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 = ?, reason = ?, closed_ms = ?, updated_ms = ? WHERE id = ?")
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 = ? WHERE id = ?").bind(l).bind(id).execute(pool).await?;
142	}
143	if let Some(p) = pinned {
144	sqlx::query("UPDATE issues SET pinned = ? WHERE id = ?").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 = ?, updated_ms = ? WHERE id = ?")
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 = ? WHERE id = ?").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 = ? 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 =
170	sqlx::query_as("INSERT INTO issue_comments (issue_id, author_id, author, body, created_ms) VALUES (?, ?, ?, ?, ?) RETURNING *")
171	.bind(issue_id)
172	.bind(author_id)
173	.bind(author)
174	.bind(body)
175	.bind(now_ms())
176	.fetch_one(pool)
177	.await?;
178	touch(pool, issue_id).await?;
179	Ok(row)
180	}
181
182	pub async fn edit_comment(pool: &Pool, id: i64, author_id: i64, body: &str) -> sqlx::Result<bool> {
183	let n = sqlx::query("UPDATE issue_comments SET body = ?, edited_ms = ? WHERE id = ? AND author_id = ?")
184	.bind(body)
185	.bind(now_ms())
186	.bind(id)
187	.bind(author_id)
188	.execute(pool)
189	.await?;
190	Ok(n.rows_affected() > 0)
191	}
192
193	pub async fn delete_comment(pool: &Pool, id: i64) -> sqlx::Result<()> {
194	sqlx::query("DELETE FROM issue_comments WHERE id = ?").bind(id).execute(pool).await?;
195	Ok(())
196	}
197
198	pub async fn events(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueEvent>> {
199	sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM issue_events WHERE issue_id = ? ORDER BY at_ms, id")
200	.bind(issue_id)
201	.fetch_all(pool)
202	.await
203	}
204
205	pub async fn add_event(pool: &Pool, issue_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
206	sqlx::query("INSERT INTO issue_events (issue_id, at_ms, actor, event, detail) VALUES (?, ?, ?, ?, ?)")
207	.bind(issue_id)
208	.bind(now_ms())
209	.bind(actor)
210	.bind(event)
211	.bind(detail)
212	.execute(pool)
213	.await?;
214	Ok(())
215	}
216
217	// ------------------------------------------------------------------ labels
218
219	pub async fn labels(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Label>> {
220	sqlx::query_as("SELECT * FROM labels WHERE repo_id = ? ORDER BY name").bind(repo_id).fetch_all(pool).await
221	}
222
223	pub async fn label_by_name(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<Label>> {
224	sqlx::query_as("SELECT * FROM labels WHERE repo_id = ? AND name = ?").bind(repo_id).bind(name).fetch_optional(pool).await
225	}
226
227	pub async fn upsert_label(pool: &Pool, repo_id: i64, name: &str, colour: &str, description: &str) -> sqlx::Result<Label> {
228	sqlx::query_as("INSERT INTO labels (repo_id, name, colour, description) VALUES (?, ?, ?, ?) ON CONFLICT (repo_id, name) DO UPDATE SET colour = excluded.colour, description = excluded.description RETURNING *")
229	.bind(repo_id)
230	.bind(name)
231	.bind(colour)
232	.bind(description)
233	.fetch_one(pool)
234	.await
235	}
236
237	pub async fn delete_label(pool: &Pool, id: i64) -> sqlx::Result<()> {
238	sqlx::query("DELETE FROM labels WHERE id = ?").bind(id).execute(pool).await?;
239	Ok(())
240	}
241
242	pub async fn issue_labels(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<Label>> {
243	sqlx::query_as("SELECT l.* FROM labels l JOIN issue_labels il ON il.label_id = l.id WHERE il.issue_id = ? ORDER BY l.name")
244	.bind(issue_id)
245	.fetch_all(pool)
246	.await
247	}
248
249	pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
250	if on {
251	sqlx::query("INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)")
252	.bind(issue_id)
253	.bind(label_id)
254	.execute(pool)
255	.await?;
256	} else {
257	sqlx::query("DELETE FROM issue_labels WHERE issue_id = ? AND label_id = ?").bind(issue_id).bind(label_id).execute(pool).await?;
258	}
259	Ok(())
260	}
261
262	/// Labels of every issue of a repository, for the list page.
263	/// Every proposal's labels in a repository, for filtering the list.
264	pub async fn labels_by_proposal(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
265	let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
266	"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 = ? ORDER BY l.name",
267	)
268	.bind(repo_id)
269	.fetch_all(pool)
270	.await?;
271	Ok(rows
272	.into_iter()
273	.map(\|(proposal_id, id, repo_id, name, colour, description)\| (proposal_id, Label { id, repo_id, name, colour, description }))
274	.collect())
275	}
276
277	pub async fn labels_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
278	let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
279	"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 = ? ORDER BY l.name",
280	)
281	.bind(repo_id)
282	.fetch_all(pool)
283	.await?;
284	Ok(rows
285	.into_iter()
286	.map(\|(issue_id, id, repo_id, name, colour, description)\| (issue_id, Label { id, repo_id, name, colour, description }))
287	.collect())
288	}
289
290	pub async fn proposal_labels(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<Label>> {
291	sqlx::query_as("SELECT l.* FROM labels l JOIN proposal_labels pl ON pl.label_id = l.id WHERE pl.proposal_id = ? ORDER BY l.name")
292	.bind(proposal_id)
293	.fetch_all(pool)
294	.await
295	}
296
297	pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
298	if on {
299	sqlx::query("INSERT OR IGNORE INTO proposal_labels (proposal_id, label_id) VALUES (?, ?)")
300	.bind(proposal_id)
301	.bind(label_id)
302	.execute(pool)
303	.await?;
304	} else {
305	sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = ? AND label_id = ?")
306	.bind(proposal_id)
307	.bind(label_id)
308	.execute(pool)
309	.await?;
310	}
311	Ok(())
312	}
313
314	// --------------------------------------------------------------- assignees
315
316	pub async fn assignees(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<String>> {
317	sqlx::query_scalar(
318	"SELECT a.username FROM accounts a JOIN issue_assignees ia ON ia.account_id = a.id WHERE ia.issue_id = ? ORDER BY a.username",
319	)
320	.bind(issue_id)
321	.fetch_all(pool)
322	.await
323	}
324
325	pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> {
326	if on {
327	sqlx::query("INSERT OR IGNORE INTO issue_assignees (issue_id, account_id) VALUES (?, ?)")
328	.bind(issue_id)
329	.bind(account_id)
330	.execute(pool)
331	.await?;
332	} else {
333	sqlx::query("DELETE FROM issue_assignees WHERE issue_id = ? AND account_id = ?")
334	.bind(issue_id)
335	.bind(account_id)
336	.execute(pool)
337	.await?;
338	}
339	Ok(())
340	}
341
342	/// Assignees of every issue of a repository, for the list page.
343	pub async fn assignees_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, String)>> {
344	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 = ?")
345	.bind(repo_id)
346	.fetch_all(pool)
347	.await
348	}
349
350	// -------------------------------------------------------------- milestones
351
352	pub async fn milestones(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Milestone>> {
353	sqlx::query_as("SELECT * FROM milestones WHERE repo_id = ? ORDER BY state, due_ms, title").bind(repo_id).fetch_all(pool).await
354	}
355
356	pub async fn milestone(pool: &Pool, id: i64) -> sqlx::Result<Option<Milestone>> {
357	sqlx::query_as("SELECT * FROM milestones WHERE id = ?").bind(id).fetch_optional(pool).await
358	}
359
360	pub async fn upsert_milestone(pool: &Pool, repo_id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<Milestone> {
361	sqlx::query_as("INSERT INTO milestones (repo_id, title, description, due_ms) VALUES (?, ?, ?, ?) ON CONFLICT (repo_id, title) DO UPDATE SET description = excluded.description, due_ms = excluded.due_ms RETURNING *")
362	.bind(repo_id)
363	.bind(title)
364	.bind(description)
365	.bind(due_ms)
366	.fetch_one(pool)
367	.await
368	}
369
370	pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {
371	sqlx::query("UPDATE milestones SET state = ? WHERE id = ?").bind(state).bind(id).execute(pool).await?;
372	Ok(())
373	}
374
375	/// `(open, closed)` issue counts per milestone.
376	pub async fn milestone_counts(pool: &Pool, milestone_id: i64) -> sqlx::Result<(i64, i64)> {
377	let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = ? AND state = 'open'")
378	.bind(milestone_id)
379	.fetch_one(pool)
380	.await?;
381	let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = ? AND state = 'closed'")
382	.bind(milestone_id)
383	.fetch_one(pool)
384	.await?;
385	Ok((open, closed))
386	}

Keyboard