crates/hub/src/db/proposals.rs at 62cdf34daec1 · andymccall/oxygenhub

# 62cdf34daec1 oxygenhub / crates / hub / src / db / proposals.rs

12.8 KiB · Rust · last changed UI polish: keys, backups, wiki, editor, bookmarks, summaries, backdrops 21 days ago source blame history raw

//! The `proposals`, `proposal_comments`, `proposal_reviews` and
//! `proposal_events` tables, and the per-repository number space.

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

#[derive(Clone, Debug, FromRow)]
pub struct ProposalRow {
    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 target: String,
    pub source_kind: String,
    pub source_repo_id: Option<i64>,
    pub source_bookmark: String,
    pub source_changes: String,
    pub projected_state: String,
    pub projected_heads: String,
    pub projected_target: String,
    pub projected_changes: String,
    pub conflicted: bool,
    pub landed_state: String,
    pub landed_by: String,
    pub landed_via: String,
    pub landed_ms: i64,
    pub created_ms: i64,
    pub updated_ms: i64,
}

impl ProposalRow {
    pub fn projected_heads(&self) -> Vec<String> {
        self.projected_heads.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn projected_changes(&self) -> Vec<String> {
        self.projected_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn source_changes(&self) -> Vec<String> {
        self.source_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn is_open(&self) -> bool {
        self.state == "open" || self.state == "draft"
    }
}

#[derive(Clone, Debug, FromRow)]
pub struct CommentRow {
    pub id: i64,
    pub proposal_id: i64,
    pub author_id: Option<i64>,
    pub author: String,
    pub body: String,
    pub path: String,
    pub anchor_change: String,
    pub anchor_index: i64,
    pub resolved: bool,
    pub created_ms: i64,
    /// The AI assistant that wrote it for the author (migration 0066),
    /// empty for the author's own comment.
    #[sqlx(default)]
    pub via: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct ReviewRow {
    pub id: i64,
    pub proposal_id: i64,
    /// `None` once the author's account was deleted (its name stays).
    pub author_id: Option<i64>,
    pub author: String,
    pub verdict: String,
    pub body: String,
    pub created_ms: i64,
    /// The AI assistant that answered for the author (migration 0036),
    /// empty for the author's own review.
    #[sqlx(default)]
    pub via: String,
}

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

/// Take the next number of a repository's shared number space.
pub async fn next_number(pool: &Pool, repo_id: i64) -> sqlx::Result<i64> {
    let mut tx = super::begin_write(pool).await?;
    sqlx::query("INSERT OR IGNORE INTO numbers (repo_id, next) VALUES (?, 1)").bind(repo_id).execute(&mut *tx).await?;
    let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = ?").bind(repo_id).fetch_one(&mut *tx).await?;
    sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = ?").bind(repo_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(n)
}

pub struct NewProposal<'a> {
    pub repo_id: i64,
    pub title: &'a str,
    pub body: &'a str,
    pub author_id: Option<i64>,
    pub author: &'a str,
    pub draft: bool,
    pub target: &'a str,
    pub source_kind: &'a str,
    pub source_repo_id: Option<i64>,
    pub source_bookmark: &'a str,
    pub source_changes: &'a str,
}

pub async fn create(pool: &Pool, p: NewProposal<'_>) -> sqlx::Result<ProposalRow> {
    let number = next_number(pool, p.repo_id).await?;
    let now = now_ms();
    sqlx::query_as(
        "INSERT INTO proposals (repo_id, number, title, body, author_id, author, state, target, source_kind, source_repo_id, source_bookmark, source_changes, created_ms, updated_ms) \
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(p.repo_id)
    .bind(number)
    .bind(p.title)
    .bind(p.body)
    .bind(p.author_id)
    .bind(p.author)
    .bind(if p.draft { "draft" } else { "open" })
    .bind(p.target)
    .bind(p.source_kind)
    .bind(p.source_repo_id)
    .bind(p.source_bookmark)
    .bind(p.source_changes)
    .bind(now)
    .bind(now)
    .fetch_one(pool)
    .await
}

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

pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {
    let all: Vec<ProposalRow> =
        sqlx::query_as("SELECT * FROM proposals WHERE repo_id = ? ORDER BY number DESC").bind(repo_id).fetch_all(pool).await?;
    Ok(all.into_iter().filter(|p| states.is_empty() || states.contains(&p.state.as_str())).collect())
}

/// Open proposals of every repository, for the front page.
pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {
    sqlx::query_as("SELECT * FROM proposals WHERE author_id = ? AND state IN ('open', 'draft') ORDER BY updated_ms DESC")
        .bind(author_id)
        .fetch_all(pool)
        .await
}

pub async fn set_projection(
    pool: &Pool,
    id: i64,
    state: &str,
    heads: &str,
    target_state: &str,
    changes: &str,
    conflicted: bool,
) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET projected_state = ?, projected_heads = ?, projected_target = ?, projected_changes = ?, conflicted = ? WHERE id = ?")
        .bind(state)
        .bind(heads)
        .bind(target_state)
        .bind(changes)
        .bind(conflicted)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

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

/// Mark a proposal landed, with who landed it and how.
pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {
    let now = now_ms();
    sqlx::query(
        "UPDATE proposals SET state = 'landed', landed_state = ?, landed_by = ?, landed_via = ?, landed_ms = ?, updated_ms = ? WHERE id = ?",
    )
    .bind(landed_state)
    .bind(by)
    .bind(via)
    .bind(now)
    .bind(now)
    .bind(id)
    .execute(pool)
    .await?;
    Ok(())
}

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

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

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

pub async fn add_comment(
    pool: &Pool,
    proposal_id: i64,
    author_id: Option<i64>,
    author: &str,
    body: &str,
    path: &str,
    anchor: Option<(&str, i64)>,
) -> sqlx::Result<CommentRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(body)
    .bind(path)
    .bind(anchor.map(|a| a.0).unwrap_or(""))
    .bind(anchor.map(|a| a.1).unwrap_or(0))
    .bind(now_ms())
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

/// A conversation comment an AI assistant (`via`) wrote for `author`,
/// who asked it.
pub async fn add_comment_via(
    pool: &Pool,
    proposal_id: i64,
    author_id: i64,
    author: &str,
    body: &str,
    via: &str,
) -> sqlx::Result<CommentRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms, via) VALUES (?, ?, ?, ?, '', '', 0, ?, ?) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(body)
    .bind(now_ms())
    .bind(via)
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

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

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

pub async fn add_review(pool: &Pool, proposal_id: i64, author_id: i64, author: &str, verdict: &str, body: &str) -> sqlx::Result<ReviewRow> {
    add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await
}

/// A review answered by an AI assistant (`via`) for `author`, who asked.
pub async fn add_review_via(
    pool: &Pool,
    proposal_id: i64,
    author_id: i64,
    author: &str,
    verdict: &str,
    body: &str,
    via: &str,
) -> sqlx::Result<ReviewRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_reviews (proposal_id, author_id, author, verdict, body, created_ms, via) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(verdict)
    .bind(body)
    .bind(now_ms())
    .bind(via)
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

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

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

/// Landing requirements of a repository.
#[derive(Clone, Copy, Debug, Default, FromRow)]
pub struct Requirements {
    pub required_approvals: i64,
    pub require_owners: bool,
    pub allow_conflicts: bool,
}

pub async fn requirements(pool: &Pool, repo_id: i64) -> sqlx::Result<Requirements> {
    sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = ?")
        .bind(repo_id)
        .fetch_one(pool)
        .await
}

pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {
    sqlx::query("UPDATE repositories SET required_approvals = ?, require_owners = ?, allow_conflicts = ? WHERE id = ?")
        .bind(r.required_approvals)
        .bind(r.require_owners)
        .bind(r.allow_conflicts)
        .bind(repo_id)
        .execute(pool)
        .await?;
    Ok(())
}

// ------------------------------------------------------- review requests

/// Ask an account to review a proposal; false when it was already asked.
pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {
    let r = sqlx::query(
        "INSERT OR IGNORE INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES (?, ?, ?, ?)",
    )
    .bind(proposal_id)
    .bind(account_id)
    .bind(by)
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(r.rows_affected() == 1)
}

/// Withdraw a request, or mark it answered; false when there was none.
pub async fn unrequest_review(pool: &Pool, proposal_id: i64, account_id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = ? AND account_id = ?")
        .bind(proposal_id)
        .bind(account_id)
        .execute(pool)
        .await?;
    Ok(r.rows_affected() == 1)
}

/// The usernames asked to review a proposal and not yet reviewing, in the
/// order they were asked.
pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar(
        "SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \
         WHERE r.proposal_id = ? ORDER BY r.requested_ms, a.username",
    )
    .bind(proposal_id)
    .fetch_all(pool)
    .await
}

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

1	//! The `proposals`, `proposal_comments`, `proposal_reviews` and
2	//! `proposal_events` tables, and the per-repository number space.
3
4	use super::{now_ms, Pool};
5	use sqlx::FromRow;
6
7	#[derive(Clone, Debug, FromRow)]
8	pub struct ProposalRow {
9	pub id: i64,
10	pub repo_id: i64,
11	pub number: i64,
12	pub title: String,
13	pub body: String,
14	pub author_id: Option<i64>,
15	pub author: String,
16	pub state: String,
17	pub target: String,
18	pub source_kind: String,
19	pub source_repo_id: Option<i64>,
20	pub source_bookmark: String,
21	pub source_changes: String,
22	pub projected_state: String,
23	pub projected_heads: String,
24	pub projected_target: String,
25	pub projected_changes: String,
26	pub conflicted: bool,
27	pub landed_state: String,
28	pub landed_by: String,
29	pub landed_via: String,
30	pub landed_ms: i64,
31	pub created_ms: i64,
32	pub updated_ms: i64,
33	}
34
35	impl ProposalRow {
36	pub fn projected_heads(&self) -> Vec<String> {
37	self.projected_heads.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
38	}
39
40	pub fn projected_changes(&self) -> Vec<String> {
41	self.projected_changes.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
42	}
43
44	pub fn source_changes(&self) -> Vec<String> {
45	self.source_changes.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
46	}
47
48	pub fn is_open(&self) -> bool {
49	self.state == "open" \|\| self.state == "draft"
50	}
51	}
52
53	#[derive(Clone, Debug, FromRow)]
54	pub struct CommentRow {
55	pub id: i64,
56	pub proposal_id: i64,
57	pub author_id: Option<i64>,
58	pub author: String,
59	pub body: String,
60	pub path: String,
61	pub anchor_change: String,
62	pub anchor_index: i64,
63	pub resolved: bool,
64	pub created_ms: i64,
65	/// The AI assistant that wrote it for the author (migration 0066),
66	/// empty for the author's own comment.
67	#[sqlx(default)]
68	pub via: String,
69	}
70
71	#[derive(Clone, Debug, FromRow)]
72	pub struct ReviewRow {
73	pub id: i64,
74	pub proposal_id: i64,
75	/// `None` once the author's account was deleted (its name stays).
76	pub author_id: Option<i64>,
77	pub author: String,
78	pub verdict: String,
79	pub body: String,
80	pub created_ms: i64,
81	/// The AI assistant that answered for the author (migration 0036),
82	/// empty for the author's own review.
83	#[sqlx(default)]
84	pub via: String,
85	}
86
87	#[derive(Clone, Debug, FromRow)]
88	pub struct EventRow {
89	pub id: i64,
90	pub at_ms: i64,
91	pub actor: String,
92	pub event: String,
93	pub detail: String,
94	}
95
96	/// Take the next number of a repository's shared number space.
97	pub async fn next_number(pool: &Pool, repo_id: i64) -> sqlx::Result<i64> {
98	let mut tx = super::begin_write(pool).await?;
99	sqlx::query("INSERT OR IGNORE INTO numbers (repo_id, next) VALUES (?, 1)").bind(repo_id).execute(&mut *tx).await?;
100	let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = ?").bind(repo_id).fetch_one(&mut *tx).await?;
101	sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = ?").bind(repo_id).execute(&mut *tx).await?;
102	tx.commit().await?;
103	Ok(n)
104	}
105
106	pub struct NewProposal<'a> {
107	pub repo_id: i64,
108	pub title: &'a str,
109	pub body: &'a str,
110	pub author_id: Option<i64>,
111	pub author: &'a str,
112	pub draft: bool,
113	pub target: &'a str,
114	pub source_kind: &'a str,
115	pub source_repo_id: Option<i64>,
116	pub source_bookmark: &'a str,
117	pub source_changes: &'a str,
118	}
119
120	pub async fn create(pool: &Pool, p: NewProposal<'_>) -> sqlx::Result<ProposalRow> {
121	let number = next_number(pool, p.repo_id).await?;
122	let now = now_ms();
123	sqlx::query_as(
124	"INSERT INTO proposals (repo_id, number, title, body, author_id, author, state, target, source_kind, source_repo_id, source_bookmark, source_changes, created_ms, updated_ms) \
125	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",
126	)
127	.bind(p.repo_id)
128	.bind(number)
129	.bind(p.title)
130	.bind(p.body)
131	.bind(p.author_id)
132	.bind(p.author)
133	.bind(if p.draft { "draft" } else { "open" })
134	.bind(p.target)
135	.bind(p.source_kind)
136	.bind(p.source_repo_id)
137	.bind(p.source_bookmark)
138	.bind(p.source_changes)
139	.bind(now)
140	.bind(now)
141	.fetch_one(pool)
142	.await
143	}
144
145	pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<ProposalRow>> {
146	sqlx::query_as("SELECT * FROM proposals WHERE repo_id = ? AND number = ?").bind(repo_id).bind(number).fetch_optional(pool).await
147	}
148
149	pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {
150	let all: Vec<ProposalRow> =
151	sqlx::query_as("SELECT * FROM proposals WHERE repo_id = ? ORDER BY number DESC").bind(repo_id).fetch_all(pool).await?;
152	Ok(all.into_iter().filter(\|p\| states.is_empty() \|\| states.contains(&p.state.as_str())).collect())
153	}
154
155	/// Open proposals of every repository, for the front page.
156	pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {
157	sqlx::query_as("SELECT * FROM proposals WHERE author_id = ? AND state IN ('open', 'draft') ORDER BY updated_ms DESC")
158	.bind(author_id)
159	.fetch_all(pool)
160	.await
161	}
162
163	pub async fn set_projection(
164	pool: &Pool,
165	id: i64,
166	state: &str,
167	heads: &str,
168	target_state: &str,
169	changes: &str,
170	conflicted: bool,
171	) -> sqlx::Result<()> {
172	sqlx::query("UPDATE proposals SET projected_state = ?, projected_heads = ?, projected_target = ?, projected_changes = ?, conflicted = ? WHERE id = ?")
173	.bind(state)
174	.bind(heads)
175	.bind(target_state)
176	.bind(changes)
177	.bind(conflicted)
178	.bind(id)
179	.execute(pool)
180	.await?;
181	Ok(())
182	}
183
184	pub async fn set_state(pool: &Pool, id: i64, state: &str, landed_state: &str) -> sqlx::Result<()> {
185	sqlx::query("UPDATE proposals SET state = ?, landed_state = ?, updated_ms = ? WHERE id = ?")
186	.bind(state)
187	.bind(landed_state)
188	.bind(now_ms())
189	.bind(id)
190	.execute(pool)
191	.await?;
192	Ok(())
193	}
194
195	/// Mark a proposal landed, with who landed it and how.
196	pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {
197	let now = now_ms();
198	sqlx::query(
199	"UPDATE proposals SET state = 'landed', landed_state = ?, landed_by = ?, landed_via = ?, landed_ms = ?, updated_ms = ? WHERE id = ?",
200	)
201	.bind(landed_state)
202	.bind(by)
203	.bind(via)
204	.bind(now)
205	.bind(now)
206	.bind(id)
207	.execute(pool)
208	.await?;
209	Ok(())
210	}
211
212	pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str, target: &str) -> sqlx::Result<()> {
213	sqlx::query("UPDATE proposals SET title = ?, body = ?, target = ?, updated_ms = ? WHERE id = ?")
214	.bind(title)
215	.bind(body)
216	.bind(target)
217	.bind(now_ms())
218	.bind(id)
219	.execute(pool)
220	.await?;
221	Ok(())
222	}
223
224	pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
225	sqlx::query("UPDATE proposals SET updated_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
226	Ok(())
227	}
228
229	pub async fn comments(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<CommentRow>> {
230	sqlx::query_as("SELECT * FROM proposal_comments WHERE proposal_id = ? ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
231	}
232
233	pub async fn add_comment(
234	pool: &Pool,
235	proposal_id: i64,
236	author_id: Option<i64>,
237	author: &str,
238	body: &str,
239	path: &str,
240	anchor: Option<(&str, i64)>,
241	) -> sqlx::Result<CommentRow> {
242	let row = sqlx::query_as(
243	"INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",
244	)
245	.bind(proposal_id)
246	.bind(author_id)
247	.bind(author)
248	.bind(body)
249	.bind(path)
250	.bind(anchor.map(\|a\| a.0).unwrap_or(""))
251	.bind(anchor.map(\|a\| a.1).unwrap_or(0))
252	.bind(now_ms())
253	.fetch_one(pool)
254	.await?;
255	touch(pool, proposal_id).await?;
256	Ok(row)
257	}
258
259	/// A conversation comment an AI assistant (`via`) wrote for `author`,
260	/// who asked it.
261	pub async fn add_comment_via(
262	pool: &Pool,
263	proposal_id: i64,
264	author_id: i64,
265	author: &str,
266	body: &str,
267	via: &str,
268	) -> sqlx::Result<CommentRow> {
269	let row = sqlx::query_as(
270	"INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms, via) VALUES (?, ?, ?, ?, '', '', 0, ?, ?) RETURNING *",
271	)
272	.bind(proposal_id)
273	.bind(author_id)
274	.bind(author)
275	.bind(body)
276	.bind(now_ms())
277	.bind(via)
278	.fetch_one(pool)
279	.await?;
280	touch(pool, proposal_id).await?;
281	Ok(row)
282	}
283
284	pub async fn set_comment_resolved(pool: &Pool, id: i64, resolved: bool) -> sqlx::Result<()> {
285	sqlx::query("UPDATE proposal_comments SET resolved = ? WHERE id = ?").bind(resolved).bind(id).execute(pool).await?;
286	Ok(())
287	}
288
289	pub async fn reviews(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<ReviewRow>> {
290	sqlx::query_as("SELECT * FROM proposal_reviews WHERE proposal_id = ? ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
291	}
292
293	pub async fn add_review(pool: &Pool, proposal_id: i64, author_id: i64, author: &str, verdict: &str, body: &str) -> sqlx::Result<ReviewRow> {
294	add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await
295	}
296
297	/// A review answered by an AI assistant (`via`) for `author`, who asked.
298	pub async fn add_review_via(
299	pool: &Pool,
300	proposal_id: i64,
301	author_id: i64,
302	author: &str,
303	verdict: &str,
304	body: &str,
305	via: &str,
306	) -> sqlx::Result<ReviewRow> {
307	let row = sqlx::query_as(
308	"INSERT INTO proposal_reviews (proposal_id, author_id, author, verdict, body, created_ms, via) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
309	)
310	.bind(proposal_id)
311	.bind(author_id)
312	.bind(author)
313	.bind(verdict)
314	.bind(body)
315	.bind(now_ms())
316	.bind(via)
317	.fetch_one(pool)
318	.await?;
319	touch(pool, proposal_id).await?;
320	Ok(row)
321	}
322
323	pub async fn events(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<EventRow>> {
324	sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM proposal_events WHERE proposal_id = ? ORDER BY at_ms, id")
325	.bind(proposal_id)
326	.fetch_all(pool)
327	.await
328	}
329
330	pub async fn add_event(pool: &Pool, proposal_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
331	sqlx::query("INSERT INTO proposal_events (proposal_id, at_ms, actor, event, detail) VALUES (?, ?, ?, ?, ?)")
332	.bind(proposal_id)
333	.bind(now_ms())
334	.bind(actor)
335	.bind(event)
336	.bind(detail)
337	.execute(pool)
338	.await?;
339	Ok(())
340	}
341
342	/// Landing requirements of a repository.
343	#[derive(Clone, Copy, Debug, Default, FromRow)]
344	pub struct Requirements {
345	pub required_approvals: i64,
346	pub require_owners: bool,
347	pub allow_conflicts: bool,
348	}
349
350	pub async fn requirements(pool: &Pool, repo_id: i64) -> sqlx::Result<Requirements> {
351	sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = ?")
352	.bind(repo_id)
353	.fetch_one(pool)
354	.await
355	}
356
357	pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {
358	sqlx::query("UPDATE repositories SET required_approvals = ?, require_owners = ?, allow_conflicts = ? WHERE id = ?")
359	.bind(r.required_approvals)
360	.bind(r.require_owners)
361	.bind(r.allow_conflicts)
362	.bind(repo_id)
363	.execute(pool)
364	.await?;
365	Ok(())
366	}
367
368	// ------------------------------------------------------- review requests
369
370	/// Ask an account to review a proposal; false when it was already asked.
371	pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {
372	let r = sqlx::query(
373	"INSERT OR IGNORE INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES (?, ?, ?, ?)",
374	)
375	.bind(proposal_id)
376	.bind(account_id)
377	.bind(by)
378	.bind(now_ms())
379	.execute(pool)
380	.await?;
381	Ok(r.rows_affected() == 1)
382	}
383
384	/// Withdraw a request, or mark it answered; false when there was none.
385	pub async fn unrequest_review(pool: &Pool, proposal_id: i64, account_id: i64) -> sqlx::Result<bool> {
386	let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = ? AND account_id = ?")
387	.bind(proposal_id)
388	.bind(account_id)
389	.execute(pool)
390	.await?;
391	Ok(r.rows_affected() == 1)
392	}
393
394	/// The usernames asked to review a proposal and not yet reviewing, in the
395	/// order they were asked.
396	pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {
397	sqlx::query_scalar(
398	"SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \
399	WHERE r.proposal_id = ? ORDER BY r.requested_ms, a.username",
400	)
401	.bind(proposal_id)
402	.fetch_all(pool)
403	.await
404	}

Keyboard