crates/hub/src/db/notify.rs at ea14c4ec5c3f · andymccall/oxygenhub

# ea14c4ec5c3f oxygenhub / crates / hub / src / db / notify.rs

12.3 KiB · Rust · last changed Postgres, step 2b: flags compared as TRUE and FALSE, counted with CASE 21 days ago source blame history raw

//! Notifications, watching, subscriptions and the search index (hub spec
//! §10, §11).

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

#[derive(Clone, Debug, FromRow)]
pub struct Notification {
    pub id: i64,
    pub account_id: i64,
    pub at_ms: i64,
    pub kind: String,
    pub title: String,
    pub url: String,
    pub actor: String,
    pub read: bool,
    pub repo_id: Option<i64>,
    pub thread: String,
    pub reason: String,
    pub done: bool,
}

/// Why someone is told: they take part in the thread, or only watch the repository.
#[derive(Clone, Copy, Debug, PartialEq, Eq)]
pub enum Reason {
    Participating,
    Watching,
}

impl Reason {
    pub fn as_str(self) -> &'static str {
        match self {
            Reason::Participating => "participating",
            Reason::Watching => "watching",
        }
    }
}

/// The thread a notification belongs to: its page, without a fragment.
pub fn thread_of(url: &str) -> &str {
    url.split('#').next().unwrap_or(url)
}

/// A notification to record.
pub struct New<'a> {
    pub account_id: i64,
    pub repo_id: Option<i64>,
    pub reason: Reason,
    pub kind: &'a str,
    pub title: &'a str,
    pub url: &'a str,
    pub actor: &'a str,
}

pub async fn add(pool: &Pool, n: New<'_>) -> sqlx::Result<()> {
    let New { account_id, repo_id, reason, kind, title, url, actor } = n;
    // A new event brings a thread that was set aside back to the inbox.
    sqlx::query("UPDATE notifications SET done = FALSE WHERE account_id = $1 AND thread = $2 AND done = TRUE")
        .bind(account_id)
        .bind(thread_of(url))
        .execute(pool)
        .await?;
    sqlx::query("INSERT INTO notifications (account_id, at_ms, kind, title, url, actor, repo_id, thread, reason) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)")
        .bind(account_id)
        .bind(now_ms())
        .bind(kind)
        .bind(title)
        .bind(url)
        .bind(actor)
        .bind(repo_id)
        .bind(thread_of(url))
        .bind(reason.as_str())
        .execute(pool)
        .await?;
    Ok(())
}

/// One inbox row: a thread, shown by its newest notification.
#[derive(Clone, Debug, FromRow)]
pub struct Thread {
    /// The newest notification's id.
    pub id: i64,
    pub at_ms: i64,
    pub kind: String,
    pub title: String,
    pub url: String,
    pub actor: String,
    pub thread: String,
    pub repo_id: Option<i64>,
    pub repo: Option<String>,
    pub count: i64,
    pub unread: i64,
    pub participating: bool,
    pub done: bool,
}

/// What the inbox shows.
#[derive(Clone, Debug, Default)]
pub struct Filter {
    pub done: bool,
    pub unread_only: bool,
    /// `participating` or `watching`.
    pub reason: Option<String>,
    /// A kind or its family (`issue` matches `issue.closed`).
    pub kind: Option<String>,
    pub repo_id: Option<i64>,
}

pub async fn threads(pool: &Pool, account_id: i64, f: &Filter, limit: i64) -> sqlx::Result<Vec<Thread>> {
    // SQLite takes bare columns from the row holding MAX(at_ms): the newest.
    let mut sql = String::from(
        "SELECT n.id, MAX(n.at_ms) AS at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, \
         (SELECT r.owner || '/' || r.name FROM repositories r WHERE r.id = n.repo_id) AS repo, \
         COUNT(*) AS count, SUM(CASE WHEN n.read THEN 0 ELSE 1 END) AS unread, SUM(CASE WHEN n.reason = 'participating' THEN 1 ELSE 0 END) > 0 AS participating, n.done \
         FROM notifications n WHERE n.account_id = $1 AND n.done = $2",
    );
    // Placeholders numbered as they are added: $1 and $2 above.
    let mut next = 3;
    if f.kind.is_some() {
        sql.push_str(&format!(" AND (n.kind = ${} OR n.kind LIKE ${} || '.%')", next, next + 1));
        next += 2;
    }
    if f.repo_id.is_some() {
        sql.push_str(&format!(" AND n.repo_id = ${next}"));
        next += 1;
    }
    sql.push_str(" GROUP BY n.thread HAVING 1");
    if f.unread_only {
        sql.push_str(" AND SUM(CASE WHEN n.read THEN 0 ELSE 1 END) > 0");
    }
    match f.reason.as_deref() {
        Some("participating") => sql.push_str(" AND SUM(n.reason = 'participating') > 0"),
        Some("watching") => sql.push_str(" AND SUM(n.reason = 'participating') = 0"),
        _ => {}
    }
    sql.push_str(&format!(" ORDER BY at_ms DESC LIMIT ${next}"));
    let mut q = sqlx::query_as::<_, Thread>(&sql).bind(account_id).bind(f.done);
    if let Some(k) = &f.kind {
        q = q.bind(k).bind(k);
    }
    if let Some(r) = f.repo_id {
        q = q.bind(r);
    }
    q.bind(limit).fetch_all(pool).await
}

/// Mark a thread read or unread, done or back in the inbox.
pub async fn set_thread(pool: &Pool, account_id: i64, thread: &str, read: Option<bool>, done: Option<bool>) -> sqlx::Result<()> {
    if let Some(r) = read {
        if r {
            sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND thread = $2")
                .bind(account_id)
                .bind(thread)
                .execute(pool)
                .await?;
        } else {
            // Unread: the newest notification of the thread only.
            sqlx::query("UPDATE notifications SET read = FALSE WHERE id = (SELECT id FROM notifications WHERE account_id = $1 AND thread = $2 ORDER BY at_ms DESC, id DESC LIMIT 1)")
                .bind(account_id)
                .bind(thread)
                .execute(pool)
                .await?;
        }
    }
    if let Some(d) = done {
        let read_too = if d { ", read = TRUE" } else { "" };
        sqlx::query(&format!("UPDATE notifications SET done = $1{read_too} WHERE account_id = $2 AND thread = $3"))
            .bind(d)
            .bind(account_id)
            .bind(thread)
            .execute(pool)
            .await?;
    }
    Ok(())
}

/// The notification with this id, if it is the account's.
pub async fn get(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<Notification>> {
    sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 AND id = $2").bind(account_id).bind(id).fetch_optional(pool).await
}

pub async fn inbox(pool: &Pool, account_id: i64, limit: i64) -> sqlx::Result<Vec<Notification>> {
    sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 ORDER BY read, at_ms DESC, id DESC LIMIT $2")
        .bind(account_id)
        .bind(limit)
        .fetch_all(pool)
        .await
}

pub async fn unread(pool: &Pool, account_id: i64) -> sqlx::Result<i64> {
    sqlx::query_scalar("SELECT COUNT(*) FROM notifications WHERE account_id = $1 AND read = FALSE AND done = FALSE")
        .bind(account_id)
        .fetch_one(pool)
        .await
}

pub async fn mark_read(pool: &Pool, account_id: i64, id: Option<i64>) -> sqlx::Result<()> {
    match id {
        Some(id) => {
            sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND id = $2")
                .bind(account_id)
                .bind(id)
                .execute(pool)
                .await?
        }
        None => sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1").bind(account_id).execute(pool).await?,
    };
    Ok(())
}

// ---------------------------------------------------------------- watching

/// The accounts that own repository `repo_id`: its owner, or the owners of
/// the organisation that owns it. They watch it unless they mute it
/// (oxygenhub#183).
pub async fn owners_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {
    let owner: Option<String> =
        sqlx::query_scalar("SELECT owner FROM repositories WHERE id = $1").bind(repo_id).fetch_optional(pool).await?;
    let Some(owner) = owner else { return Ok(Vec::new()) };
    if let Some(a) = super::accounts::account_by_name(pool, &owner).await? {
        return Ok(vec![a.id]);
    }
    match super::accounts::org_by_name(pool, &owner).await? {
        Some(o) => {
            Ok(super::accounts::org_members(pool, o.id).await?.into_iter().filter(|(_, r)| r == "owner").map(|(a, _)| a.id).collect())
        }
        None => Ok(Vec::new()),
    }
}

/// Whether `account_id` watches `repo_id`: a watch it chose, or, for an
/// owner, unless it muted it.
pub async fn watching(pool: &Pool, account_id: i64, repo_id: i64) -> sqlx::Result<bool> {
    let muted: Option<bool> = sqlx::query_scalar("SELECT muted FROM watches WHERE account_id = $1 AND repo_id = $2")
        .bind(account_id)
        .bind(repo_id)
        .fetch_optional(pool)
        .await?;
    match muted {
        Some(m) => Ok(!m),
        None => Ok(owners_of(pool, repo_id).await?.contains(&account_id)),
    }
}

/// Watch `repo_id`, or stop: for an owner, stopping mutes it.
pub async fn set_watch(pool: &Pool, account_id: i64, repo_id: i64, on: bool) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO watches (account_id, repo_id, muted) VALUES ($1, $2, $3) ON CONFLICT (account_id, repo_id) DO UPDATE SET muted = excluded.muted")
        .bind(account_id)
        .bind(repo_id)
        .bind(!on)
        .execute(pool)
        .await?;
    Ok(())
}

/// Everyone watching `repo_id`: those who chose to, and its owners who
/// have not muted it.
pub async fn watchers(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {
    let rows: Vec<(i64, bool)> =
        sqlx::query_as("SELECT account_id, muted FROM watches WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
    let mut out: Vec<i64> = rows.iter().filter(|(_, m)| !m).map(|(a, _)| *a).collect();
    for o in owners_of(pool, repo_id).await? {
        if !rows.iter().any(|(a, _)| *a == o) && !out.contains(&o) {
            out.push(o);
        }
    }
    Ok(out)
}

pub async fn subscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {
    sqlx::query("INSERT OR IGNORE INTO subscriptions (account_id, kind, target_id) VALUES ($1, $2, $3)")
        .bind(account_id)
        .bind(kind)
        .bind(target_id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn unsubscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")
        .bind(account_id)
        .bind(kind)
        .bind(target_id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn subscribed(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<bool> {
    let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")
        .bind(account_id)
        .bind(kind)
        .bind(target_id)
        .fetch_one(pool)
        .await?;
    Ok(n > 0)
}

pub async fn subscribers(pool: &Pool, kind: &str, target_id: i64) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar("SELECT account_id FROM subscriptions WHERE kind = $1 AND target_id = $2")
        .bind(kind)
        .bind(target_id)
        .fetch_all(pool)
        .await
}

// ------------------------------------------------------------------ search

#[derive(Clone, Debug, FromRow)]
pub struct Hit {
    pub kind: String,
    pub repo_id: i64,
    pub target_id: i64,
    pub number: i64,
    pub title: String,
    pub snippet: String,
}

/// Replace the indexed text of one thread (title plus body plus comments).
pub async fn index(pool: &Pool, kind: &str, repo_id: i64, target_id: i64, number: i64, title: &str, body: &str) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM search WHERE kind = $1 AND target_id = $2").bind(kind).bind(target_id).execute(pool).await?;
    sqlx::query("INSERT INTO search (kind, repo_id, target_id, number, title, body) VALUES ($1, $2, $3, $4, $5, $6)")
        .bind(kind)
        .bind(repo_id)
        .bind(target_id)
        .bind(number)
        .bind(title)
        .bind(body)
        .execute(pool)
        .await?;
    Ok(())
}

/// Full-text hits, best first. The query is quoted so punctuation cannot
/// reach the FTS5 parser.
pub async fn search(pool: &Pool, query: &str, repo_id: Option<i64>, limit: i64) -> sqlx::Result<Vec<Hit>> {
    let terms: Vec<String> = query.split_whitespace().map(|t| format!("\"{}\"", t.replace('"', ""))).collect();
    if terms.is_empty() {
        return Ok(vec![]);
    }
    let q = terms.join(" ");
    let sql = "SELECT kind, repo_id, target_id, number, title, snippet(search, 5, '<mark>', '</mark>', '…', 12) AS snippet FROM search WHERE search MATCH $1 AND ($2 IS NULL OR repo_id = $3) ORDER BY rank LIMIT $4";
    sqlx::query_as(sql).bind(q).bind(repo_id).bind(repo_id).bind(limit).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

18e381ea Andy McCall 1 month ago ↶	1	//! Notifications, watching, subscriptions and the search index (hub spec
	2	//! §10, §11).
	3
	4	use super::{now_ms, Pool};
	5	use sqlx::FromRow;
	6
	7	#[derive(Clone, Debug, FromRow)]
	8	pub struct Notification {
	9	pub id: i64,
	10	pub account_id: i64,
	11	pub at_ms: i64,
	12	pub kind: String,
	13	pub title: String,
	14	pub url: String,
	15	pub actor: String,
	16	pub read: bool,
eb0c44a9 Andy McCall 1 month ago ↶	17	pub repo_id: Option<i64>,
	18	pub thread: String,
	19	pub reason: String,
	20	pub done: bool,
	21	}
	22
	23	/// Why someone is told: they take part in the thread, or only watch the repository.
	24	#[derive(Clone, Copy, Debug, PartialEq, Eq)]
	25	pub enum Reason {
	26	Participating,
	27	Watching,
	28	}
	29
	30	impl Reason {
	31	pub fn as_str(self) -> &'static str {
	32	match self {
	33	Reason::Participating => "participating",
	34	Reason::Watching => "watching",
	35	}
	36	}
18e381ea Andy McCall 1 month ago ↶	37	}
18e381ea Andy McCall 1 month ago ↶	38
eb0c44a9 Andy McCall 1 month ago ↶	39	/// The thread a notification belongs to: its page, without a fragment.
	40	pub fn thread_of(url: &str) -> &str {
	41	url.split('#').next().unwrap_or(url)
	42	}
	43
	44	/// A notification to record.
	45	pub struct New<'a> {
	46	pub account_id: i64,
	47	pub repo_id: Option<i64>,
	48	pub reason: Reason,
	49	pub kind: &'a str,
	50	pub title: &'a str,
	51	pub url: &'a str,
	52	pub actor: &'a str,
	53	}
	54
	55	pub async fn add(pool: &Pool, n: New<'_>) -> sqlx::Result<()> {
	56	let New { account_id, repo_id, reason, kind, title, url, actor } = n;
	57	// A new event brings a thread that was set aside back to the inbox.
ea14c4ec Andy McCall 21 days ago ↶	58	sqlx::query("UPDATE notifications SET done = FALSE WHERE account_id = $1 AND thread = $2 AND done = TRUE")
eb0c44a9 Andy McCall 1 month ago ↶	59	.bind(account_id)
	60	.bind(thread_of(url))
	61	.execute(pool)
	62	.await?;
5d90e0e0 Andy McCall 21 days ago	63	sqlx::query("INSERT INTO notifications (account_id, at_ms, kind, title, url, actor, repo_id, thread, reason) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)")
18e381ea Andy McCall 1 month ago ↶	64	.bind(account_id)
	65	.bind(now_ms())
	66	.bind(kind)
	67	.bind(title)
	68	.bind(url)
	69	.bind(actor)
eb0c44a9 Andy McCall 1 month ago ↶	70	.bind(repo_id)
	71	.bind(thread_of(url))
	72	.bind(reason.as_str())
18e381ea Andy McCall 1 month ago ↶	73	.execute(pool)
	74	.await?;
	75	Ok(())
	76	}
	77
eb0c44a9 Andy McCall 1 month ago ↶	78	/// One inbox row: a thread, shown by its newest notification.
	79	#[derive(Clone, Debug, FromRow)]
	80	pub struct Thread {
	81	/// The newest notification's id.
	82	pub id: i64,
	83	pub at_ms: i64,
	84	pub kind: String,
	85	pub title: String,
	86	pub url: String,
	87	pub actor: String,
	88	pub thread: String,
	89	pub repo_id: Option<i64>,
	90	pub repo: Option<String>,
	91	pub count: i64,
	92	pub unread: i64,
	93	pub participating: bool,
	94	pub done: bool,
	95	}
	96
	97	/// What the inbox shows.
	98	#[derive(Clone, Debug, Default)]
	99	pub struct Filter {
	100	pub done: bool,
	101	pub unread_only: bool,
	102	/// `participating` or `watching`.
	103	pub reason: Option<String>,
	104	/// A kind or its family (`issue` matches `issue.closed`).
	105	pub kind: Option<String>,
	106	pub repo_id: Option<i64>,
	107	}
	108
	109	pub async fn threads(pool: &Pool, account_id: i64, f: &Filter, limit: i64) -> sqlx::Result<Vec<Thread>> {
	110	// SQLite takes bare columns from the row holding MAX(at_ms): the newest.
	111	let mut sql = String::from(
	112	"SELECT n.id, MAX(n.at_ms) AS at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, \
	113	(SELECT r.owner \|\| '/' \|\| r.name FROM repositories r WHERE r.id = n.repo_id) AS repo, \
ea14c4ec Andy McCall 21 days ago ↶	114	COUNT(*) AS count, SUM(CASE WHEN n.read THEN 0 ELSE 1 END) AS unread, SUM(CASE WHEN n.reason = 'participating' THEN 1 ELSE 0 END) > 0 AS participating, n.done \
5d90e0e0 Andy McCall 21 days ago	115	FROM notifications n WHERE n.account_id = $1 AND n.done = $2",
eb0c44a9 Andy McCall 1 month ago ↶	116	);
5d90e0e0 Andy McCall 21 days ago	117	// Placeholders numbered as they are added: $1 and $2 above.
5d90e0e0 Andy McCall 21 days ago	118	let mut next = 3;
eb0c44a9 Andy McCall 1 month ago ↶	119	if f.kind.is_some() {
5d90e0e0 Andy McCall 21 days ago	120	sql.push_str(&format!(" AND (n.kind = ${} OR n.kind LIKE ${} \|\| '.%')", next, next + 1));
5d90e0e0 Andy McCall 21 days ago	121	next += 2;
eb0c44a9 Andy McCall 1 month ago ↶	122	}
eb0c44a9 Andy McCall 1 month ago ↶	123	if f.repo_id.is_some() {
5d90e0e0 Andy McCall 21 days ago	124	sql.push_str(&format!(" AND n.repo_id = ${next}"));
5d90e0e0 Andy McCall 21 days ago	125	next += 1;
eb0c44a9 Andy McCall 1 month ago ↶	126	}
	127	sql.push_str(" GROUP BY n.thread HAVING 1");
	128	if f.unread_only {
ea14c4ec Andy McCall 21 days ago ↶	129	sql.push_str(" AND SUM(CASE WHEN n.read THEN 0 ELSE 1 END) > 0");
eb0c44a9 Andy McCall 1 month ago ↶	130	}
	131	match f.reason.as_deref() {
	132	Some("participating") => sql.push_str(" AND SUM(n.reason = 'participating') > 0"),
	133	Some("watching") => sql.push_str(" AND SUM(n.reason = 'participating') = 0"),
	134	_ => {}
	135	}
5d90e0e0 Andy McCall 21 days ago	136	sql.push_str(&format!(" ORDER BY at_ms DESC LIMIT ${next}"));
eb0c44a9 Andy McCall 1 month ago ↶	137	let mut q = sqlx::query_as::<_, Thread>(&sql).bind(account_id).bind(f.done);
	138	if let Some(k) = &f.kind {
	139	q = q.bind(k).bind(k);
	140	}
	141	if let Some(r) = f.repo_id {
	142	q = q.bind(r);
	143	}
	144	q.bind(limit).fetch_all(pool).await
	145	}
	146
	147	/// Mark a thread read or unread, done or back in the inbox.
	148	pub async fn set_thread(pool: &Pool, account_id: i64, thread: &str, read: Option<bool>, done: Option<bool>) -> sqlx::Result<()> {
	149	if let Some(r) = read {
	150	if r {
ea14c4ec Andy McCall 21 days ago ↶	151	sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND thread = $2")
eb0c44a9 Andy McCall 1 month ago ↶	152	.bind(account_id)
	153	.bind(thread)
	154	.execute(pool)
	155	.await?;
	156	} else {
	157	// Unread: the newest notification of the thread only.
ea14c4ec Andy McCall 21 days ago ↶	158	sqlx::query("UPDATE notifications SET read = FALSE WHERE id = (SELECT id FROM notifications WHERE account_id = $1 AND thread = $2 ORDER BY at_ms DESC, id DESC LIMIT 1)")
eb0c44a9 Andy McCall 1 month ago ↶	159	.bind(account_id)
	160	.bind(thread)
	161	.execute(pool)
	162	.await?;
	163	}
	164	}
	165	if let Some(d) = done {
ea14c4ec Andy McCall 21 days ago ↶	166	let read_too = if d { ", read = TRUE" } else { "" };
5d90e0e0 Andy McCall 21 days ago	167	sqlx::query(&format!("UPDATE notifications SET done = $1{read_too} WHERE account_id = $2 AND thread = $3"))
eb0c44a9 Andy McCall 1 month ago ↶	168	.bind(d)
	169	.bind(account_id)
	170	.bind(thread)
	171	.execute(pool)
	172	.await?;
	173	}
	174	Ok(())
	175	}
	176
	177	/// The notification with this id, if it is the account's.
	178	pub async fn get(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<Notification>> {
5d90e0e0 Andy McCall 21 days ago	179	sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 AND id = $2").bind(account_id).bind(id).fetch_optional(pool).await
eb0c44a9 Andy McCall 1 month ago ↶	180	}
eb0c44a9 Andy McCall 1 month ago ↶	181
18e381ea Andy McCall 1 month ago ↶	182	pub async fn inbox(pool: &Pool, account_id: i64, limit: i64) -> sqlx::Result<Vec<Notification>> {
5d90e0e0 Andy McCall 21 days ago	183	sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 ORDER BY read, at_ms DESC, id DESC LIMIT $2")
18e381ea Andy McCall 1 month ago ↶	184	.bind(account_id)
	185	.bind(limit)
	186	.fetch_all(pool)
	187	.await
	188	}
	189
	190	pub async fn unread(pool: &Pool, account_id: i64) -> sqlx::Result<i64> {
ea14c4ec Andy McCall 21 days ago ↶	191	sqlx::query_scalar("SELECT COUNT(*) FROM notifications WHERE account_id = $1 AND read = FALSE AND done = FALSE")
eb0c44a9 Andy McCall 1 month ago ↶	192	.bind(account_id)
	193	.fetch_one(pool)
	194	.await
18e381ea Andy McCall 1 month ago ↶	195	}
	196
	197	pub async fn mark_read(pool: &Pool, account_id: i64, id: Option<i64>) -> sqlx::Result<()> {
	198	match id {
	199	Some(id) => {
ea14c4ec Andy McCall 21 days ago ↶	200	sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND id = $2")
5d90e0e0 Andy McCall 21 days ago	201	.bind(account_id)
	202	.bind(id)
	203	.execute(pool)
	204	.await?
18e381ea Andy McCall 1 month ago ↶	205	}
ea14c4ec Andy McCall 21 days ago ↶	206	None => sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1").bind(account_id).execute(pool).await?,
18e381ea Andy McCall 1 month ago ↶	207	};
	208	Ok(())
	209	}
	210
	211	// ---------------------------------------------------------------- watching
	212
bff9106e Andy McCall 23 days ago ↶	213	/// The accounts that own repository `repo_id`: its owner, or the owners of
	214	/// the organisation that owns it. They watch it unless they mute it
	215	/// (oxygenhub#183).
	216	pub async fn owners_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {
	217	let owner: Option<String> =
5d90e0e0 Andy McCall 21 days ago	218	sqlx::query_scalar("SELECT owner FROM repositories WHERE id = $1").bind(repo_id).fetch_optional(pool).await?;
bff9106e Andy McCall 23 days ago ↶	219	let Some(owner) = owner else { return Ok(Vec::new()) };
	220	if let Some(a) = super::accounts::account_by_name(pool, &owner).await? {
	221	return Ok(vec![a.id]);
	222	}
	223	match super::accounts::org_by_name(pool, &owner).await? {
	224	Some(o) => {
	225	Ok(super::accounts::org_members(pool, o.id).await?.into_iter().filter(\|(_, r)\| r == "owner").map(\|(a, _)\| a.id).collect())
	226	}
	227	None => Ok(Vec::new()),
	228	}
	229	}
	230
	231	/// Whether `account_id` watches `repo_id`: a watch it chose, or, for an
	232	/// owner, unless it muted it.
18e381ea Andy McCall 1 month ago ↶	233	pub async fn watching(pool: &Pool, account_id: i64, repo_id: i64) -> sqlx::Result<bool> {
5d90e0e0 Andy McCall 21 days ago	234	let muted: Option<bool> = sqlx::query_scalar("SELECT muted FROM watches WHERE account_id = $1 AND repo_id = $2")
18e381ea Andy McCall 1 month ago ↶	235	.bind(account_id)
18e381ea Andy McCall 1 month ago ↶	236	.bind(repo_id)
bff9106e Andy McCall 23 days ago ↶	237	.fetch_optional(pool)
18e381ea Andy McCall 1 month ago ↶	238	.await?;
bff9106e Andy McCall 23 days ago ↶	239	match muted {
	240	Some(m) => Ok(!m),
	241	None => Ok(owners_of(pool, repo_id).await?.contains(&account_id)),
	242	}
18e381ea Andy McCall 1 month ago ↶	243	}
18e381ea Andy McCall 1 month ago ↶	244
bff9106e Andy McCall 23 days ago ↶	245	/// Watch `repo_id`, or stop: for an owner, stopping mutes it.
18e381ea Andy McCall 1 month ago ↶	246	pub async fn set_watch(pool: &Pool, account_id: i64, repo_id: i64, on: bool) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 21 days ago	247	sqlx::query("INSERT INTO watches (account_id, repo_id, muted) VALUES ($1, $2, $3) ON CONFLICT (account_id, repo_id) DO UPDATE SET muted = excluded.muted")
bff9106e Andy McCall 23 days ago ↶	248	.bind(account_id)
	249	.bind(repo_id)
	250	.bind(!on)
	251	.execute(pool)
	252	.await?;
18e381ea Andy McCall 1 month ago ↶	253	Ok(())
	254	}
	255
bff9106e Andy McCall 23 days ago ↶	256	/// Everyone watching `repo_id`: those who chose to, and its owners who
bff9106e Andy McCall 23 days ago ↶	257	/// have not muted it.
18e381ea Andy McCall 1 month ago ↶	258	pub async fn watchers(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {
bff9106e Andy McCall 23 days ago ↶	259	let rows: Vec<(i64, bool)> =
5d90e0e0 Andy McCall 21 days ago	260	sqlx::query_as("SELECT account_id, muted FROM watches WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
bff9106e Andy McCall 23 days ago ↶	261	let mut out: Vec<i64> = rows.iter().filter(\|(_, m)\| !m).map(\|(a, _)\| *a).collect();
	262	for o in owners_of(pool, repo_id).await? {
	263	if !rows.iter().any(\|(a, _)\| *a == o) && !out.contains(&o) {
	264	out.push(o);
	265	}
	266	}
	267	Ok(out)
18e381ea Andy McCall 1 month ago ↶	268	}
	269
	270	pub async fn subscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 21 days ago	271	sqlx::query("INSERT OR IGNORE INTO subscriptions (account_id, kind, target_id) VALUES ($1, $2, $3)")
18e381ea Andy McCall 1 month ago ↶	272	.bind(account_id)
	273	.bind(kind)
	274	.bind(target_id)
	275	.execute(pool)
	276	.await?;
	277	Ok(())
	278	}
	279
	280	pub async fn unsubscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 21 days ago	281	sqlx::query("DELETE FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")
18e381ea Andy McCall 1 month ago ↶	282	.bind(account_id)
	283	.bind(kind)
	284	.bind(target_id)
	285	.execute(pool)
	286	.await?;
	287	Ok(())
	288	}
	289
	290	pub async fn subscribed(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<bool> {
5d90e0e0 Andy McCall 21 days ago	291	let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")
18e381ea Andy McCall 1 month ago ↶	292	.bind(account_id)
	293	.bind(kind)
	294	.bind(target_id)
	295	.fetch_one(pool)
	296	.await?;
	297	Ok(n > 0)
	298	}
	299
	300	pub async fn subscribers(pool: &Pool, kind: &str, target_id: i64) -> sqlx::Result<Vec<i64>> {
5d90e0e0 Andy McCall 21 days ago	301	sqlx::query_scalar("SELECT account_id FROM subscriptions WHERE kind = $1 AND target_id = $2")
18e381ea Andy McCall 1 month ago ↶	302	.bind(kind)
	303	.bind(target_id)
	304	.fetch_all(pool)
	305	.await
	306	}
	307
	308	// ------------------------------------------------------------------ search
	309
	310	#[derive(Clone, Debug, FromRow)]
	311	pub struct Hit {
	312	pub kind: String,
	313	pub repo_id: i64,
	314	pub target_id: i64,
	315	pub number: i64,
	316	pub title: String,
	317	pub snippet: String,
	318	}
	319
	320	/// Replace the indexed text of one thread (title plus body plus comments).
	321	pub async fn index(pool: &Pool, kind: &str, repo_id: i64, target_id: i64, number: i64, title: &str, body: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 21 days ago	322	sqlx::query("DELETE FROM search WHERE kind = $1 AND target_id = $2").bind(kind).bind(target_id).execute(pool).await?;
5d90e0e0 Andy McCall 21 days ago	323	sqlx::query("INSERT INTO search (kind, repo_id, target_id, number, title, body) VALUES ($1, $2, $3, $4, $5, $6)")
18e381ea Andy McCall 1 month ago ↶	324	.bind(kind)
	325	.bind(repo_id)
	326	.bind(target_id)
	327	.bind(number)
	328	.bind(title)
	329	.bind(body)
	330	.execute(pool)
	331	.await?;
	332	Ok(())
	333	}
	334
	335	/// Full-text hits, best first. The query is quoted so punctuation cannot
	336	/// reach the FTS5 parser.
	337	pub async fn search(pool: &Pool, query: &str, repo_id: Option<i64>, limit: i64) -> sqlx::Result<Vec<Hit>> {
	338	let terms: Vec<String> = query.split_whitespace().map(\|t\| format!("\"{}\"", t.replace('"', ""))).collect();
	339	if terms.is_empty() {
	340	return Ok(vec![]);
	341	}
	342	let q = terms.join(" ");
5d90e0e0 Andy McCall 21 days ago	343	let sql = "SELECT kind, repo_id, target_id, number, title, snippet(search, 5, '<mark>', '</mark>', '…', 12) AS snippet FROM search WHERE search MATCH $1 AND ($2 IS NULL OR repo_id = $3) ORDER BY rank LIMIT $4";
18e381ea Andy McCall 1 month ago ↶	344	sqlx::query_as(sql).bind(q).bind(repo_id).bind(repo_id).bind(limit).fetch_all(pool).await
18e381ea Andy McCall 1 month ago ↶	345	}

Keyboard