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

◆ main oxygenhub / crates / hub / src / db / issues.rs

15.6 KiB · Rust · last changed New repository page with board and starting files; assetlinks.json; hung tests stopped; changelog on the WASM tier (closes #360, closes #365, closes #370) 1 day 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();
    let row: IssueRow = 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 *")
        .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?;
    // Onto the boards the repository is linked to (#360).
    super::projects::opened(pool, row.repo_id, "issue", row.number, &row.author).await?;
    Ok(row)
}

pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<IssueRow>> {
    sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 AND number = $2").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 = $1").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 = $1 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 = $1").bind(repo_id).fetch_all(pool).await?;
    let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = $1").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 = $1 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 = $1, body = $2, updated_ms = $3 WHERE id = $4")
        .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 = $1, reason = $2, closed_ms = $3, updated_ms = $4 WHERE id = $5")
        .bind(state)
        .bind(reason)
        .bind(closed)
        .bind(now)
        .bind(id)
        .execute(pool)
        .await?;
    if state == "closed" {
        finished(pool, "issues", id).await?;
    }
    Ok(())
}

/// Move the cards of finished issue or proposal `id` of `table` (#325).
pub(super) async fn finished(pool: &Pool, table: &str, id: i64) -> sqlx::Result<()> {
    let at: Option<(i64, i64)> =
        sqlx::query_as(&format!("SELECT repo_id, number FROM {table} WHERE id = $1")).bind(id).fetch_optional(pool).await?;
    match at {
        Some((repo, number)) => super::projects::finished(pool, repo, number).await,
        None => 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 = $1 WHERE id = $2").bind(l).bind(id).execute(pool).await?;
    }
    if let Some(p) = pinned {
        sqlx::query("UPDATE issues SET pinned = $1 WHERE id = $2").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 = $1, updated_ms = $2 WHERE id = $3")
        .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 = $1 WHERE id = $2").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 = $1 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 ($1, $2, $3, $4, $5) 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 = $1, edited_ms = $2 WHERE id = $3 AND author_id = $4")
        .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 = $1").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 = $1 ORDER BY at_ms, id")
        .bind(issue_id)
        .fetch_all(pool)
        .await
}

/// Whether change `hash` (its short form) already references it (#318):
/// a change is recorded once, whichever bookmarks it is pushed on.
pub async fn referenced_by(pool: &Pool, issue_id: i64, hash: &str) -> sqlx::Result<bool> {
    let n: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM issue_events WHERE issue_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",
    )
    .bind(issue_id)
    .bind(hash)
    .bind(format!("{hash} %"))
    .fetch_one(pool)
    .await?;
    Ok(n > 0)
}

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 ($1, $2, $3, $4, $5)")
        .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 = $1 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 = $1 AND name = $2").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 ($1, $2, $3, $4) 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 = $1").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 = $1 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 INTO issue_labels (issue_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
            .bind(issue_id)
            .bind(label_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2").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 = $1 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 = $1 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 = $1 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 INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
            .bind(proposal_id)
            .bind(label_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = $1 AND label_id = $2")
            .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 = $1 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 INTO issue_assignees (issue_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
            .bind(issue_id)
            .bind(account_id)
            .execute(pool)
            .await?;
    } else {
        sqlx::query("DELETE FROM issue_assignees WHERE issue_id = $1 AND account_id = $2")
            .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 = $1")
        .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 = $1 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 = $1").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 ($1, $2, $3, $4) 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
}

/// Change a milestone's title, description and due date.
pub async fn update_milestone(pool: &Pool, id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("UPDATE milestones SET title = $1, description = $2, due_ms = $3 WHERE id = $4")
        .bind(title)
        .bind(description)
        .bind(due_ms)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE milestones SET state = $1 WHERE id = $2").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 = $1 AND state = 'open'")
        .bind(milestone_id)
        .fetch_one(pool)
        .await?;
    let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 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

18e381ea Andy McCall 1 month ago ↶	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();
40a43921 Andy McCall 1 day ago ↶	75	let row: IssueRow = 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 *")
18e381ea Andy McCall 1 month ago ↶	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)
40a43921 Andy McCall 1 day ago ↶	85	.await?;
	86	// Onto the boards the repository is linked to (#360).
	87	super::projects::opened(pool, row.repo_id, "issue", row.number, &row.author).await?;
	88	Ok(row)
18e381ea Andy McCall 1 month ago ↶	89	}
	90
	91	pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<IssueRow>> {
5d90e0e0 Andy McCall 19 days ago	92	sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await
18e381ea Andy McCall 1 month ago ↶	93	}
	94
	95	pub async fn get_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<IssueRow>> {
5d90e0e0 Andy McCall 19 days ago	96	sqlx::query_as("SELECT * FROM issues WHERE id = $1").bind(id).fetch_optional(pool).await
18e381ea Andy McCall 1 month ago ↶	97	}
	98
	99	pub async fn list(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<IssueRow>> {
5d90e0e0 Andy McCall 19 days ago	100	sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 ORDER BY pinned DESC, number DESC").bind(repo_id).fetch_all(pool).await
18e381ea Andy McCall 1 month ago ↶	101	}
	102
	103	/// Numbers in use by issues and proposals, for cross references.
	104	pub async fn numbers(pool: &Pool, repo_id: i64) -> sqlx::Result<(Vec<i64>, Vec<i64>)> {
5d90e0e0 Andy McCall 19 days ago	105	let issues: Vec<i64> = sqlx::query_scalar("SELECT number FROM issues WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
5d90e0e0 Andy McCall 19 days ago	106	let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
18e381ea Andy McCall 1 month ago ↶	107	Ok((issues, proposals))
	108	}
	109
	110	pub async fn assigned_to(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<IssueRow>> {
5d90e0e0 Andy McCall 19 days ago	111	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")
18e381ea Andy McCall 1 month ago ↶	112	.bind(account_id)
	113	.fetch_all(pool)
	114	.await
	115	}
	116
	117	pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	118	sqlx::query("UPDATE issues SET title = $1, body = $2, updated_ms = $3 WHERE id = $4")
18e381ea Andy McCall 1 month ago ↶	119	.bind(title)
	120	.bind(body)
	121	.bind(now_ms())
	122	.bind(id)
	123	.execute(pool)
	124	.await?;
	125	Ok(())
	126	}
	127
	128	pub async fn set_state(pool: &Pool, id: i64, state: &str, reason: &str) -> sqlx::Result<()> {
	129	let now = now_ms();
	130	let closed: Option<i64> = if state == "closed" { Some(now) } else { None };
5d90e0e0 Andy McCall 19 days ago	131	sqlx::query("UPDATE issues SET state = $1, reason = $2, closed_ms = $3, updated_ms = $4 WHERE id = $5")
18e381ea Andy McCall 1 month ago ↶	132	.bind(state)
	133	.bind(reason)
	134	.bind(closed)
	135	.bind(now)
	136	.bind(id)
	137	.execute(pool)
	138	.await?;
1000b40a Andy McCall 10 days ago ↶	139	if state == "closed" {
	140	finished(pool, "issues", id).await?;
	141	}
18e381ea Andy McCall 1 month ago ↶	142	Ok(())
1000b40a Andy McCall 10 days ago ↶	143	}
	144
	145	/// Move the cards of finished issue or proposal `id` of `table` (#325).
	146	pub(super) async fn finished(pool: &Pool, table: &str, id: i64) -> sqlx::Result<()> {
	147	let at: Option<(i64, i64)> =
	148	sqlx::query_as(&format!("SELECT repo_id, number FROM {table} WHERE id = $1")).bind(id).fetch_optional(pool).await?;
	149	match at {
	150	Some((repo, number)) => super::projects::finished(pool, repo, number).await,
	151	None => Ok(()),
	152	}
18e381ea Andy McCall 1 month ago ↶	153	}
	154
	155	pub async fn set_flags(pool: &Pool, id: i64, locked: Option<bool>, pinned: Option<bool>) -> sqlx::Result<()> {
	156	if let Some(l) = locked {
5d90e0e0 Andy McCall 19 days ago	157	sqlx::query("UPDATE issues SET locked = $1 WHERE id = $2").bind(l).bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	158	}
18e381ea Andy McCall 1 month ago ↶	159	if let Some(p) = pinned {
5d90e0e0 Andy McCall 19 days ago	160	sqlx::query("UPDATE issues SET pinned = $1 WHERE id = $2").bind(p).bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	161	}
	162	Ok(())
	163	}
	164
	165	pub async fn set_milestone(pool: &Pool, id: i64, milestone_id: Option<i64>) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	166	sqlx::query("UPDATE issues SET milestone_id = $1, updated_ms = $2 WHERE id = $3")
18e381ea Andy McCall 1 month ago ↶	167	.bind(milestone_id)
	168	.bind(now_ms())
	169	.bind(id)
	170	.execute(pool)
	171	.await?;
	172	Ok(())
	173	}
	174
	175	pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	176	sqlx::query("UPDATE issues SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	177	Ok(())
	178	}
	179
	180	pub async fn comments(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueComment>> {
5d90e0e0 Andy McCall 19 days ago	181	sqlx::query_as("SELECT * FROM issue_comments WHERE issue_id = $1 ORDER BY created_ms, id").bind(issue_id).fetch_all(pool).await
18e381ea Andy McCall 1 month ago ↶	182	}
	183
	184	pub async fn add_comment(pool: &Pool, issue_id: i64, author_id: Option<i64>, author: &str, body: &str) -> sqlx::Result<IssueComment> {
5d90e0e0 Andy McCall 19 days ago	185	let row = sqlx::query_as(
	186	"INSERT INTO issue_comments (issue_id, author_id, author, body, created_ms) VALUES ($1, $2, $3, $4, $5) RETURNING *",
	187	)
	188	.bind(issue_id)
	189	.bind(author_id)
	190	.bind(author)
	191	.bind(body)
	192	.bind(now_ms())
	193	.fetch_one(pool)
	194	.await?;
18e381ea Andy McCall 1 month ago ↶	195	touch(pool, issue_id).await?;
	196	Ok(row)
	197	}
	198
	199	pub async fn edit_comment(pool: &Pool, id: i64, author_id: i64, body: &str) -> sqlx::Result<bool> {
5d90e0e0 Andy McCall 19 days ago	200	let n = sqlx::query("UPDATE issue_comments SET body = $1, edited_ms = $2 WHERE id = $3 AND author_id = $4")
18e381ea Andy McCall 1 month ago ↶	201	.bind(body)
	202	.bind(now_ms())
	203	.bind(id)
	204	.bind(author_id)
	205	.execute(pool)
	206	.await?;
	207	Ok(n.rows_affected() > 0)
	208	}
	209
	210	pub async fn delete_comment(pool: &Pool, id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	211	sqlx::query("DELETE FROM issue_comments WHERE id = $1").bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	212	Ok(())
	213	}
	214
	215	pub async fn events(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueEvent>> {
5d90e0e0 Andy McCall 19 days ago	216	sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM issue_events WHERE issue_id = $1 ORDER BY at_ms, id")
18e381ea Andy McCall 1 month ago ↶	217	.bind(issue_id)
	218	.fetch_all(pool)
	219	.await
	220	}
	221
8bb7baeb Andy McCall 10 days ago ↶	222	/// Whether change `hash` (its short form) already references it (#318):
	223	/// a change is recorded once, whichever bookmarks it is pushed on.
	224	pub async fn referenced_by(pool: &Pool, issue_id: i64, hash: &str) -> sqlx::Result<bool> {
	225	let n: i64 = sqlx::query_scalar(
	226	"SELECT COUNT(*) FROM issue_events WHERE issue_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",
	227	)
	228	.bind(issue_id)
	229	.bind(hash)
	230	.bind(format!("{hash} %"))
	231	.fetch_one(pool)
	232	.await?;
	233	Ok(n > 0)
	234	}
	235
18e381ea Andy McCall 1 month ago ↶	236	pub async fn add_event(pool: &Pool, issue_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	237	sqlx::query("INSERT INTO issue_events (issue_id, at_ms, actor, event, detail) VALUES ($1, $2, $3, $4, $5)")
18e381ea Andy McCall 1 month ago ↶	238	.bind(issue_id)
	239	.bind(now_ms())
	240	.bind(actor)
	241	.bind(event)
	242	.bind(detail)
	243	.execute(pool)
	244	.await?;
	245	Ok(())
	246	}
	247
	248	// ------------------------------------------------------------------ labels
	249
	250	pub async fn labels(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Label>> {
5d90e0e0 Andy McCall 19 days ago	251	sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 ORDER BY name").bind(repo_id).fetch_all(pool).await
18e381ea Andy McCall 1 month ago ↶	252	}
	253
	254	pub async fn label_by_name(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<Label>> {
5d90e0e0 Andy McCall 19 days ago	255	sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 AND name = $2").bind(repo_id).bind(name).fetch_optional(pool).await
18e381ea Andy McCall 1 month ago ↶	256	}
	257
	258	pub async fn upsert_label(pool: &Pool, repo_id: i64, name: &str, colour: &str, description: &str) -> sqlx::Result<Label> {
5d90e0e0 Andy McCall 19 days ago	259	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 *")
18e381ea Andy McCall 1 month ago ↶	260	.bind(repo_id)
	261	.bind(name)
	262	.bind(colour)
	263	.bind(description)
	264	.fetch_one(pool)
	265	.await
	266	}
	267
	268	pub async fn delete_label(pool: &Pool, id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	269	sqlx::query("DELETE FROM labels WHERE id = $1").bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	270	Ok(())
	271	}
	272
	273	pub async fn issue_labels(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<Label>> {
5d90e0e0 Andy McCall 19 days ago	274	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")
18e381ea Andy McCall 1 month ago ↶	275	.bind(issue_id)
	276	.fetch_all(pool)
	277	.await
	278	}
	279
	280	pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
	281	if on {
d1bf2d69 Andy McCall 19 days ago ↶	282	sqlx::query("INSERT INTO issue_labels (issue_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
18e381ea Andy McCall 1 month ago ↶	283	.bind(issue_id)
	284	.bind(label_id)
	285	.execute(pool)
	286	.await?;
	287	} else {
5d90e0e0 Andy McCall 19 days ago	288	sqlx::query("DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2").bind(issue_id).bind(label_id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	289	}
	290	Ok(())
	291	}
	292
	293	/// Labels of every issue of a repository, for the list page.
1ac84c62 Andy McCall 1 month ago ↶	294	/// Every proposal's labels in a repository, for filtering the list.
	295	pub async fn labels_by_proposal(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
	296	let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
5d90e0e0 Andy McCall 19 days ago	297	"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",
1ac84c62 Andy McCall 1 month ago ↶	298	)
	299	.bind(repo_id)
	300	.fetch_all(pool)
	301	.await?;
	302	Ok(rows
	303	.into_iter()
	304	.map(\|(proposal_id, id, repo_id, name, colour, description)\| (proposal_id, Label { id, repo_id, name, colour, description }))
	305	.collect())
	306	}
	307
18e381ea Andy McCall 1 month ago ↶	308	pub async fn labels_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {
18e381ea Andy McCall 1 month ago ↶	309	let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(
5d90e0e0 Andy McCall 19 days ago	310	"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",
18e381ea Andy McCall 1 month ago ↶	311	)
	312	.bind(repo_id)
	313	.fetch_all(pool)
	314	.await?;
	315	Ok(rows
	316	.into_iter()
	317	.map(\|(issue_id, id, repo_id, name, colour, description)\| (issue_id, Label { id, repo_id, name, colour, description }))
	318	.collect())
	319	}
	320
	321	pub async fn proposal_labels(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<Label>> {
5d90e0e0 Andy McCall 19 days ago	322	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")
18e381ea Andy McCall 1 month ago ↶	323	.bind(proposal_id)
	324	.fetch_all(pool)
	325	.await
	326	}
	327
	328	pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {
	329	if on {
d1bf2d69 Andy McCall 19 days ago ↶	330	sqlx::query("INSERT INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
18e381ea Andy McCall 1 month ago ↶	331	.bind(proposal_id)
	332	.bind(label_id)
	333	.execute(pool)
	334	.await?;
	335	} else {
5d90e0e0 Andy McCall 19 days ago	336	sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = $1 AND label_id = $2")
18e381ea Andy McCall 1 month ago ↶	337	.bind(proposal_id)
	338	.bind(label_id)
	339	.execute(pool)
	340	.await?;
	341	}
	342	Ok(())
	343	}
	344
	345	// --------------------------------------------------------------- assignees
	346
	347	pub async fn assignees(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<String>> {
	348	sqlx::query_scalar(
5d90e0e0 Andy McCall 19 days ago	349	"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",
18e381ea Andy McCall 1 month ago ↶	350	)
	351	.bind(issue_id)
	352	.fetch_all(pool)
	353	.await
	354	}
	355
	356	pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> {
	357	if on {
d1bf2d69 Andy McCall 19 days ago ↶	358	sqlx::query("INSERT INTO issue_assignees (issue_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
18e381ea Andy McCall 1 month ago ↶	359	.bind(issue_id)
	360	.bind(account_id)
	361	.execute(pool)
	362	.await?;
	363	} else {
5d90e0e0 Andy McCall 19 days ago	364	sqlx::query("DELETE FROM issue_assignees WHERE issue_id = $1 AND account_id = $2")
18e381ea Andy McCall 1 month ago ↶	365	.bind(issue_id)
	366	.bind(account_id)
	367	.execute(pool)
	368	.await?;
	369	}
	370	Ok(())
	371	}
	372
	373	/// Assignees of every issue of a repository, for the list page.
	374	pub async fn assignees_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, String)>> {
5d90e0e0 Andy McCall 19 days ago	375	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")
18e381ea Andy McCall 1 month ago ↶	376	.bind(repo_id)
	377	.fetch_all(pool)
	378	.await
	379	}
	380
	381	// -------------------------------------------------------------- milestones
	382
	383	pub async fn milestones(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Milestone>> {
5d90e0e0 Andy McCall 19 days ago	384	sqlx::query_as("SELECT * FROM milestones WHERE repo_id = $1 ORDER BY state, due_ms, title").bind(repo_id).fetch_all(pool).await
18e381ea Andy McCall 1 month ago ↶	385	}
	386
	387	pub async fn milestone(pool: &Pool, id: i64) -> sqlx::Result<Option<Milestone>> {
5d90e0e0 Andy McCall 19 days ago	388	sqlx::query_as("SELECT * FROM milestones WHERE id = $1").bind(id).fetch_optional(pool).await
18e381ea Andy McCall 1 month ago ↶	389	}
	390
	391	pub async fn upsert_milestone(pool: &Pool, repo_id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<Milestone> {
5d90e0e0 Andy McCall 19 days ago	392	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 *")
18e381ea Andy McCall 1 month ago ↶	393	.bind(repo_id)
	394	.bind(title)
	395	.bind(description)
	396	.bind(due_ms)
	397	.fetch_one(pool)
	398	.await
	399	}
	400
5ea4872f Andy McCall 1 month ago ↶	401	/// Change a milestone's title, description and due date.
5ea4872f Andy McCall 1 month ago ↶	402	pub async fn update_milestone(pool: &Pool, id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	403	sqlx::query("UPDATE milestones SET title = $1, description = $2, due_ms = $3 WHERE id = $4")
5ea4872f Andy McCall 1 month ago ↶	404	.bind(title)
	405	.bind(description)
	406	.bind(due_ms)
	407	.bind(id)
	408	.execute(pool)
	409	.await?;
	410	Ok(())
	411	}
	412
18e381ea Andy McCall 1 month ago ↶	413	pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 19 days ago	414	sqlx::query("UPDATE milestones SET state = $1 WHERE id = $2").bind(state).bind(id).execute(pool).await?;
18e381ea Andy McCall 1 month ago ↶	415	Ok(())
	416	}
	417
	418	/// `(open, closed)` issue counts per milestone.
	419	pub async fn milestone_counts(pool: &Pool, milestone_id: i64) -> sqlx::Result<(i64, i64)> {
5d90e0e0 Andy McCall 19 days ago	420	let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'open'")
18e381ea Andy McCall 1 month ago ↶	421	.bind(milestone_id)
	422	.fetch_one(pool)
	423	.await?;
5d90e0e0 Andy McCall 19 days ago	424	let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'closed'")
18e381ea Andy McCall 1 month ago ↶	425	.bind(milestone_id)
	426	.fetch_one(pool)
	427	.await?;
	428	Ok((open, closed))
	429	}

Keyboard