projects.rs 81.6%

289 of 354 lines run, 43 of 106 functions · run #470 on main · run missed

//! Projects, their columns and cards (hub spec §8.2).

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

#[derive(Clone, Debug, FromRow)]
pub struct Project {
    pub id: i64,
    pub owner: String,
    pub number: i64,
    pub title: String,
    pub description: String,
    pub public: bool,
    pub closed: bool,
    pub created_ms: i64,
    pub updated_ms: i64,
}

#[derive(Clone, Debug, FromRow)]
pub struct Column {
    pub id: i64,
    pub project_id: i64,
    pub name: String,
    pub position: i64,
    /// Its cards are archived this many days after they joined it (#293).
    pub archive_after_days: Option<i64>,
}

#[derive(Clone, Debug, FromRow)]
pub struct Card {
    pub id: i64,
    pub project_id: i64,
    pub column_id: i64,
    pub position: i64,
    pub kind: String,
    pub repo_id: Option<i64>,
    pub number: Option<i64>,
    pub note: String,
    pub added_by: String,
    pub created_ms: i64,
    /// Off the board since then (#293): kept, and restorable.
    pub archived_ms: Option<i64>,
    /// When it joined its column.
    pub moved_ms: i64,
}

/// A day, for the archive's ages.
const DAY_MS: i64 = 86_400_000;

pub const DEFAULT_COLUMNS: [&str; 3] = ["Backlog", "Doing", "Done"];

pub async fn create(pool: &Pool, owner: &str, title: &str, description: &str, public: bool) -> sqlx::Result<Project> {
    let mut tx = super::begin_write(pool).await?;
    let number: i64 =
        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(owner).fetch_one(&mut *tx).await?;
    let now = now_ms();
    let p: Project = sqlx::query_as(
        "INSERT INTO projects (owner, number, title, description, public, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
    )
    .bind(owner)
    .bind(number)
    .bind(title)
    .bind(description)
    .bind(public)
    .bind(now)
    .bind(now)
    .fetch_one(&mut *tx)
    .await?;
    // A board here now: an old address of a transferred one gives way (#125).
    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(owner).bind(number).execute(&mut *tx).await?;
    for (i, name) in DEFAULT_COLUMNS.iter().enumerate() {
        sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")
            .bind(p.id)
            .bind(name)
            .bind(i as i64)
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;
    Ok(p)
}

pub async fn of_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<Project>> {
    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 ORDER BY closed, updated_ms DESC").bind(owner).fetch_all(pool).await
}

/// Move project `id` to owner `to` (#125): it takes the next number there,
/// with its columns, cards and privacy, and its old address redirects to it.
pub async fn transfer(pool: &Pool, id: i64, to: &str) -> sqlx::Result<Project> {
    let mut tx = super::begin_write(pool).await?;
    let old: Project = sqlx::query_as("SELECT * FROM projects WHERE id = $1").bind(id).fetch_one(&mut *tx).await?;
    let number: i64 =
        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(to).fetch_one(&mut *tx).await?;
    sqlx::query("INSERT INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3) ON CONFLICT (owner, number) DO UPDATE SET project_id = excluded.project_id")
        .bind(&old.owner)
        .bind(old.number)
        .bind(id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(to).bind(number).execute(&mut *tx).await?;
    let moved: Project = sqlx::query_as("UPDATE projects SET owner = $1, number = $2, updated_ms = $3 WHERE id = $4 RETURNING *")
        .bind(to)
        .bind(number)
        .bind(now_ms())
        .bind(id)
        .fetch_one(&mut *tx)
        .await?;
    tx.commit().await?;
    Ok(moved)
}

/// The project an old address (`owner`, `number`) now redirects to, after
/// a transfer (#125).
pub async fn redirected(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
    sqlx::query_as("SELECT p.* FROM project_redirects r JOIN projects p ON p.id = r.project_id WHERE r.owner = $1 AND r.number = $2")
        .bind(owner)
        .bind(number)
        .fetch_optional(pool)
        .await
}

pub async fn get(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 AND number = $2").bind(owner).bind(number).fetch_optional(pool).await
}

pub async fn set(pool: &Pool, id: i64, title: &str, description: &str, public: bool, closed: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE projects SET title = $1, description = $2, public = $3, closed = $4, updated_ms = $5 WHERE id = $6")
        .bind(title)
        .bind(description)
        .bind(public)
        .bind(closed)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

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

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

pub async fn columns(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Column>> {
    sqlx::query_as("SELECT * FROM project_columns WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
}

pub async fn cards(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Card>> {
    sqlx::query_as("SELECT * FROM project_cards WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
}

pub async fn add_column(pool: &Pool, project_id: i64, name: &str) -> sqlx::Result<()> {
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_columns WHERE project_id = $1")
        .bind(project_id)
        .fetch_one(pool)
        .await?;
    sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")
        .bind(project_id)
        .bind(name)
        .bind(pos)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

pub async fn rename_column(pool: &Pool, project_id: i64, column_id: i64, name: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE project_columns SET name = $1 WHERE id = $2 AND project_id = $3")
        .bind(name)
        .bind(column_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Swap a column with its neighbour (`-1` left, `1` right).
pub async fn shift_column(pool: &Pool, project_id: i64, column_id: i64, by: i64) -> sqlx::Result<()> {
    let cols = columns(pool, project_id).await?;
    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };
    let j = i as i64 + by;
    if j < 0 || j as usize >= cols.len() {
        return Ok(());
    }
    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();
    ids.swap(i, j as usize);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

/// Remove a column; its cards go to the first remaining column. The last column stays.
pub async fn remove_column(pool: &Pool, project_id: i64, column_id: i64) -> sqlx::Result<bool> {
    let cols = columns(pool, project_id).await?;
    let Some(to) = cols.iter().find(|c| c.id != column_id) else { return Ok(false) };
    if !cols.iter().any(|c| c.id == column_id) {
        return Ok(false);
    }
    let mut tx = super::begin_write(pool).await?;
    let base: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(to.id)
        .fetch_one(&mut *tx)
        .await?;
    sqlx::query("UPDATE project_cards SET column_id = $1, position = position + $2 WHERE column_id = $3")
        .bind(to.id)
        .bind(base)
        .bind(column_id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_columns WHERE id = $1").bind(column_id).execute(&mut *tx).await?;
    tx.commit().await?;
    touch(pool, project_id).await?;
    Ok(true)
}

pub struct NewCard<'a> {
    pub column_id: i64,
    pub kind: &'a str,
    pub repo_id: Option<i64>,
    pub number: Option<i64>,
    pub note: &'a str,
    pub added_by: &'a str,
}

/// Add a card at the bottom of a column; `Ok(false)` when that issue or
/// proposal is already on the project.
pub async fn add_card(pool: &Pool, project_id: i64, c: NewCard<'_>) -> sqlx::Result<bool> {
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(c.column_id)
        .fetch_one(pool)
        .await?;
    let r = sqlx::query(
        "INSERT INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms, moved_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9) ON CONFLICT DO NOTHING",
    )
    .bind(project_id)
    .bind(c.column_id)
    .bind(pos)
    .bind(c.kind)
    .bind(c.repo_id)
    .bind(c.number)
    .bind(c.note)
    .bind(c.added_by)
    .bind(now_ms())
    .execute(pool)
    .await?;
    touch(pool, project_id).await?;
    Ok(r.rows_affected() == 1)
}

/// Move a card to the bottom of another column of the same project.
pub async fn move_card(pool: &Pool, project_id: i64, card_id: i64, column_id: i64) -> sqlx::Result<()> {
    let ok: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_columns WHERE id = $1 AND project_id = $2")
        .bind(column_id)
        .bind(project_id)
        .fetch_one(pool)
        .await?;
    if ok == 0 {
        return Ok(());
    }
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(column_id)
        .fetch_one(pool)
        .await?;
    sqlx::query("UPDATE project_cards SET moved_ms = CASE WHEN column_id = $1 THEN moved_ms ELSE $3 END, column_id = $1, position = $2 WHERE id = $4 AND project_id = $5")
        .bind(column_id)
        .bind(pos)
        .bind(now_ms())
        .bind(card_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Move `card_ids`, in that order, to the end of `column_id`, all or none
/// (#289).
pub async fn move_cards(pool: &Pool, project_id: i64, card_ids: &[i64], column_id: i64) -> sqlx::Result<()> {
    let mut tx = pool.begin().await?;
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(column_id)
        .fetch_one(&mut *tx)
        .await?;
    let now = now_ms();
    for (i, id) in card_ids.iter().enumerate() {
        sqlx::query("UPDATE project_cards SET column_id = $1, position = $2, moved_ms = $3 WHERE id = $4 AND project_id = $5")
            .bind(column_id)
            .bind(pos + i as i64)
            .bind(now)
            .bind(id)
            .bind(project_id)
            .execute(&mut *tx)
            .await?;
    }
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await
}

/// Take `card_ids` off the board, kept (#293); how many were on it.
pub async fn archive_cards(pool: &Pool, project_id: i64, card_ids: &[i64]) -> sqlx::Result<u64> {
    let mut tx = pool.begin().await?;
    let now = now_ms();
    let mut n = 0;
    for id in card_ids {
        n += sqlx::query("UPDATE project_cards SET archived_ms = $1 WHERE id = $2 AND project_id = $3 AND archived_ms IS NULL")
            .bind(now)
            .bind(id)
            .bind(project_id)
            .execute(&mut *tx)
            .await?
            .rows_affected();
    }
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(n)
}

/// Put an archived card back on the board, at the end of its column, as
/// if it had just joined it (so its column does not archive it again at
/// once); `false` when it was not archived.
pub async fn restore_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<bool> {
    let mut tx = pool.begin().await?;
    let pos: i64 = sqlx::query_scalar(
        "SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = (SELECT column_id FROM project_cards WHERE id = $1)",
    )
    .bind(card_id)
    .fetch_one(&mut *tx)
    .await?;
    let now = now_ms();
    let r = sqlx::query(
        "UPDATE project_cards SET archived_ms = NULL, moved_ms = $1, position = $2 WHERE id = $3 AND project_id = $4 AND archived_ms IS NOT NULL",
    )
    .bind(now)
    .bind(pos)
    .bind(card_id)
    .bind(project_id)
    .execute(&mut *tx)
    .await?;
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(r.rows_affected() == 1)
}

/// Whether `card` joined its column more than `days` ago, at `now`.
pub fn older_than(card: &Card, days: i64, now: i64) -> bool {
    card.moved_ms < now - days * DAY_MS
}

/// Archive a column's cards this many days after they join it; `None`
/// finished cards after [`FINISHED_KEPT_DAYS`] (the default); [`NEVER`]
/// none.
pub async fn set_archive_after(pool: &Pool, project_id: i64, column_id: i64, days: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("UPDATE project_columns SET archive_after_days = $1 WHERE id = $2 AND project_id = $3")
        .bind(days)
        .bind(column_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Days after which a column with no setting of its own archives its
/// finished cards (#293): a board shows the last fortnight's done work.
pub const FINISHED_KEPT_DAYS: i64 = 14;

/// A column's `archive_after_days` that means never.
pub const NEVER: i64 = -1;

/// Archive, on every board, the cards their columns keep no longer (#293);
/// how many. A column that archives after N days: every card that joined it
/// longer ago. A column with no setting: finished cards (a closed issue, a
/// closed or landed proposal) after [`FINISHED_KEPT_DAYS`], so a Done column
/// stops growing while open work and notes stay. [`NEVER`]: none.
pub async fn archive_due(pool: &Pool, now: i64) -> sqlx::Result<u64> {
    let set = sqlx::query(
        "UPDATE project_cards SET archived_ms = $1 WHERE archived_ms IS NULL AND EXISTS \
         (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days >= 0 \
          AND project_cards.moved_ms < $1 - c.archive_after_days * $2)",
    )
    .bind(now)
    .bind(DAY_MS)
    .execute(pool)
    .await?;
    let finished = sqlx::query(
        "UPDATE project_cards SET archived_ms = $1 WHERE archived_ms IS NULL AND moved_ms < $2 \
         AND EXISTS (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days IS NULL) \
         AND ((kind = 'issue' AND EXISTS (SELECT 1 FROM issues i WHERE i.repo_id = project_cards.repo_id \
               AND i.number = project_cards.number AND i.state = 'closed')) \
           OR (kind = 'proposal' AND EXISTS (SELECT 1 FROM proposals p WHERE p.repo_id = project_cards.repo_id \
               AND p.number = project_cards.number AND p.state IN ('closed', 'landed'))))",
    )
    .bind(now)
    .bind(now - FINISHED_KEPT_DAYS * DAY_MS)
    .execute(pool)
    .await?;
    Ok(set.rows_affected() + finished.rows_affected())
}

/// Swap a card with its neighbour in its column (`-1` up, `1` down).
pub async fn shift_card(pool: &Pool, project_id: i64, card_id: i64, by: i64) -> sqlx::Result<()> {
    let Some(card): Option<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE id = $1 AND project_id = $2")
        .bind(card_id)
        .bind(project_id)
        .fetch_optional(pool)
        .await?
    else {
        return Ok(());
    };
    let col: Vec<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE column_id = $1 ORDER BY position, id")
        .bind(card.column_id)
        .fetch_all(pool)
        .await?;
    let Some(i) = col.iter().position(|c| c.id == card_id) else { return Ok(()) };
    let j = i as i64 + by;
    if j < 0 || j as usize >= col.len() {
        return Ok(());
    }
    let mut ids: Vec<i64> = col.iter().map(|c| c.id).collect();
    ids.swap(i, j as usize);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_cards SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

pub async fn remove_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM project_cards WHERE id = $1 AND project_id = $2").bind(card_id).bind(project_id).execute(pool).await?;
    touch(pool, project_id).await
}

/// The projects (and column names) an issue or proposal is on.
pub async fn placements(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Vec<(Project, String)>> {
    let rows: Vec<(i64, String, Option<i64>)> = sqlx::query_as(
        "SELECT c.project_id, col.name, c.archived_ms FROM project_cards c JOIN project_columns col ON col.id = c.column_id WHERE c.repo_id = $1 AND c.number = $2 AND c.kind <> 'note'",
    )
    .bind(repo_id)
    .bind(number)
    .fetch_all(pool)
    .await?;
    let mut out = Vec::new();
    for (pid, col, archived) in rows {
        let col = if archived.is_some() { format!("{col}, archived") } else { col };
        if let Some(p) = sqlx::query_as::<_, Project>("SELECT * FROM projects WHERE id = $1").bind(pid).fetch_optional(pool).await? {
            out.push((p, col));
        }
    }
    Ok(out)
}

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

1		//! Projects, their columns and cards (hub spec §8.2).
2
3		use super::{now_ms, Pool};
4		use sqlx::FromRow;
5
6		#[derive(Clone, Debug, FromRow)]
7		pub struct Project {
8		pub id: i64,
9		pub owner: String,
10		pub number: i64,
11		pub title: String,
12		pub description: String,
13		pub public: bool,
14		pub closed: bool,
15		pub created_ms: i64,
16		pub updated_ms: i64,
17		}
18
19		#[derive(Clone, Debug, FromRow)]
20		pub struct Column {
21		pub id: i64,
22		pub project_id: i64,
23		pub name: String,
24		pub position: i64,
25		/// Its cards are archived this many days after they joined it (#293).
26		pub archive_after_days: Option<i64>,
27		}
28
29		#[derive(Clone, Debug, FromRow)]
30		pub struct Card {
31		pub id: i64,
32		pub project_id: i64,
33		pub column_id: i64,
34		pub position: i64,
35		pub kind: String,
36		pub repo_id: Option<i64>,
37		pub number: Option<i64>,
38		pub note: String,
39		pub added_by: String,
40		pub created_ms: i64,
41		/// Off the board since then (#293): kept, and restorable.
42		pub archived_ms: Option<i64>,
43		/// When it joined its column.
44		pub moved_ms: i64,
45		}
46
47		/// A day, for the archive's ages.
48		const DAY_MS: i64 = 86_400_000;
49
50		pub const DEFAULT_COLUMNS: [&str; 3] = ["Backlog", "Doing", "Done"];
51
52	9×	pub async fn create(pool: &Pool, owner: &str, title: &str, description: &str, public: bool) -> sqlx::Result<Project> {
53	9×	let mut tx = super::begin_write(pool).await?;
54	9×	let number: i64 =
55	9×	sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(owner).fetch_one(&mut *tx).await?;
56	9×	let now = now_ms();
57	9×	let p: Project = sqlx::query_as(
58	9×	"INSERT INTO projects (owner, number, title, description, public, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
59	9×	)
60	9×	.bind(owner)
61	9×	.bind(number)
62	9×	.bind(title)
63	9×	.bind(description)
64	9×	.bind(public)
65	9×	.bind(now)
66	9×	.bind(now)
67	9×	.fetch_one(&mut *tx)
68	9×	.await?;
69		// A board here now: an old address of a transferred one gives way (#125).
70	9×	sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(owner).bind(number).execute(&mut *tx).await?;
71	27×	for (i, name) in DEFAULT_COLUMNS.iter().enumerate() {
72	27×	sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")
73	27×	.bind(p.id)
74	27×	.bind(name)
75	27×	.bind(i as i64)
76	27×	.execute(&mut *tx)
77	27×	.await?;
78		}
79	9×	tx.commit().await?;
80	9×	Ok(p)
81	9×	}
82
83	67×	pub async fn of_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<Project>> {
84	67×	sqlx::query_as("SELECT * FROM projects WHERE owner = $1 ORDER BY closed, updated_ms DESC").bind(owner).fetch_all(pool).await
85	67×	}
86
87		/// Move project `id` to owner `to` (#125): it takes the next number there,
88		/// with its columns, cards and privacy, and its old address redirects to it.
89	1×	pub async fn transfer(pool: &Pool, id: i64, to: &str) -> sqlx::Result<Project> {
90	1×	let mut tx = super::begin_write(pool).await?;
91	1×	let old: Project = sqlx::query_as("SELECT * FROM projects WHERE id = $1").bind(id).fetch_one(&mut *tx).await?;
92	1×	let number: i64 =
93	1×	sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(to).fetch_one(&mut *tx).await?;
94	1×	sqlx::query("INSERT INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3) ON CONFLICT (owner, number) DO UPDATE SET project_id = excluded.project_id")
95	1×	.bind(&old.owner)
96	1×	.bind(old.number)
97	1×	.bind(id)
98	1×	.execute(&mut *tx)
99	1×	.await?;
100	1×	sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(to).bind(number).execute(&mut *tx).await?;
101	1×	let moved: Project = sqlx::query_as("UPDATE projects SET owner = $1, number = $2, updated_ms = $3 WHERE id = $4 RETURNING *")
102	1×	.bind(to)
103	1×	.bind(number)
104	1×	.bind(now_ms())
105	1×	.bind(id)
106	1×	.fetch_one(&mut *tx)
107	1×	.await?;
108	1×	tx.commit().await?;
109	1×	Ok(moved)
110	1×	}
111
112		/// The project an old address (`owner`, `number`) now redirects to, after
113		/// a transfer (#125).
114	2×	pub async fn redirected(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
115	2×	sqlx::query_as("SELECT p.* FROM project_redirects r JOIN projects p ON p.id = r.project_id WHERE r.owner = $1 AND r.number = $2")
116	2×	.bind(owner)
117	2×	.bind(number)
118	2×	.fetch_optional(pool)
119	2×	.await
120	2×	}
121
122	145×	pub async fn get(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
123	145×	sqlx::query_as("SELECT * FROM projects WHERE owner = $1 AND number = $2").bind(owner).bind(number).fetch_optional(pool).await
124	145×	}
125
126	6×	pub async fn set(pool: &Pool, id: i64, title: &str, description: &str, public: bool, closed: bool) -> sqlx::Result<()> {
127	6×	sqlx::query("UPDATE projects SET title = $1, description = $2, public = $3, closed = $4, updated_ms = $5 WHERE id = $6")
128	6×	.bind(title)
129	6×	.bind(description)
130	6×	.bind(public)
131	6×	.bind(closed)
132	6×	.bind(now_ms())
133	6×	.bind(id)
134	6×	.execute(pool)
135	6×	.await?;
136	6×	Ok(())
137	6×	}
138
139	1×	pub async fn delete(pool: &Pool, id: i64) -> sqlx::Result<()> {
140	1×	sqlx::query("DELETE FROM projects WHERE id = $1").bind(id).execute(pool).await?;
141	1×	Ok(())
142	1×	}
143
144	26×	async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
145	26×	sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
146	26×	Ok(())
147	26×	}
148
149	109×	pub async fn columns(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Column>> {
150	109×	sqlx::query_as("SELECT * FROM project_columns WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
151	109×	}
152
153	67×	pub async fn cards(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Card>> {
154	67×	sqlx::query_as("SELECT * FROM project_cards WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
155	67×	}
156
157	0×	pub async fn add_column(pool: &Pool, project_id: i64, name: &str) -> sqlx::Result<()> {
158	0×	let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_columns WHERE project_id = $1")
159	0×	.bind(project_id)
160	0×	.fetch_one(pool)
161	0×	.await?;
162	0×	sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")
163	0×	.bind(project_id)
164	0×	.bind(name)
165	0×	.bind(pos)
166	0×	.execute(pool)
167	0×	.await?;
168	0×	touch(pool, project_id).await
169	0×	}
170
171	0×	pub async fn rename_column(pool: &Pool, project_id: i64, column_id: i64, name: &str) -> sqlx::Result<()> {
172	0×	sqlx::query("UPDATE project_columns SET name = $1 WHERE id = $2 AND project_id = $3")
173	0×	.bind(name)
174	0×	.bind(column_id)
175	0×	.bind(project_id)
176	0×	.execute(pool)
177	0×	.await?;
178	0×	touch(pool, project_id).await
179	0×	}
180
181		/// Swap a column with its neighbour (`-1` left, `1` right).
182	0×	pub async fn shift_column(pool: &Pool, project_id: i64, column_id: i64, by: i64) -> sqlx::Result<()> {
183	0×	let cols = columns(pool, project_id).await?;
184	0×	let Some(i) = cols.iter().position(\|c\| c.id == column_id) else { return Ok(()) };
185	0×	let j = i as i64 + by;
186	0×	if j < 0 \|\| j as usize >= cols.len() {
187	0×	return Ok(());
188	0×	}
189	0×	let mut ids: Vec<i64> = cols.iter().map(\|c\| c.id).collect();
190	0×	ids.swap(i, j as usize);
191	0×	for (pos, id) in ids.iter().enumerate() {
192	0×	sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
193		}
194	0×	touch(pool, project_id).await
195	0×	}
196
197		/// Remove a column; its cards go to the first remaining column. The last column stays.
198	3×	pub async fn remove_column(pool: &Pool, project_id: i64, column_id: i64) -> sqlx::Result<bool> {
199	3×	let cols = columns(pool, project_id).await?;
200	3×	let Some(to) = cols.iter().find(\|c\| c.id != column_id) else { return Ok(false) };
201	4×	if !cols.iter().any(\|c\| c.id == column_id) {
202	0×	return Ok(false);
203	2×	}
204	2×	let mut tx = super::begin_write(pool).await?;
205	2×	let base: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
206	2×	.bind(to.id)
207	2×	.fetch_one(&mut *tx)
208	2×	.await?;
209	2×	sqlx::query("UPDATE project_cards SET column_id = $1, position = position + $2 WHERE column_id = $3")
210	2×	.bind(to.id)
211	2×	.bind(base)
212	2×	.bind(column_id)
213	2×	.execute(&mut *tx)
214	2×	.await?;
215	2×	sqlx::query("DELETE FROM project_columns WHERE id = $1").bind(column_id).execute(&mut *tx).await?;
216	2×	tx.commit().await?;
217	2×	touch(pool, project_id).await?;
218	2×	Ok(true)
219	3×	}
220
221		pub struct NewCard<'a> {
222		pub column_id: i64,
223		pub kind: &'a str,
224		pub repo_id: Option<i64>,
225		pub number: Option<i64>,
226		pub note: &'a str,
227		pub added_by: &'a str,
228		}
229
230		/// Add a card at the bottom of a column; `Ok(false)` when that issue or
231		/// proposal is already on the project.
232	15×	pub async fn add_card(pool: &Pool, project_id: i64, c: NewCard<'_>) -> sqlx::Result<bool> {
233	15×	let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
234	15×	.bind(c.column_id)
235	15×	.fetch_one(pool)
236	15×	.await?;
237	15×	let r = sqlx::query(
238	15×	"INSERT INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms, moved_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9) ON CONFLICT DO NOTHING",
239	15×	)
240	15×	.bind(project_id)
241	15×	.bind(c.column_id)
242	15×	.bind(pos)
243	15×	.bind(c.kind)
244	15×	.bind(c.repo_id)
245	15×	.bind(c.number)
246	15×	.bind(c.note)
247	15×	.bind(c.added_by)
248	15×	.bind(now_ms())
249	15×	.execute(pool)
250	15×	.await?;
251	15×	touch(pool, project_id).await?;
252	15×	Ok(r.rows_affected() == 1)
253	15×	}
254
255		/// Move a card to the bottom of another column of the same project.
256	3×	pub async fn move_card(pool: &Pool, project_id: i64, card_id: i64, column_id: i64) -> sqlx::Result<()> {
257	3×	let ok: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_columns WHERE id = $1 AND project_id = $2")
258	3×	.bind(column_id)
259	3×	.bind(project_id)
260	3×	.fetch_one(pool)
261	3×	.await?;
262	3×	if ok == 0 {
263	0×	return Ok(());
264	3×	}
265	3×	let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
266	3×	.bind(column_id)
267	3×	.fetch_one(pool)
268	3×	.await?;
269	3×	sqlx::query("UPDATE project_cards SET moved_ms = CASE WHEN column_id = $1 THEN moved_ms ELSE $3 END, column_id = $1, position = $2 WHERE id = $4 AND project_id = $5")
270	3×	.bind(column_id)
271	3×	.bind(pos)
272	3×	.bind(now_ms())
273	3×	.bind(card_id)
274	3×	.bind(project_id)
275	3×	.execute(pool)
276	3×	.await?;
277	3×	touch(pool, project_id).await
278	3×	}
279
280		/// Move `card_ids`, in that order, to the end of `column_id`, all or none
281		/// (#289).
282	1×	pub async fn move_cards(pool: &Pool, project_id: i64, card_ids: &[i64], column_id: i64) -> sqlx::Result<()> {
283	1×	let mut tx = pool.begin().await?;
284	1×	let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
285	1×	.bind(column_id)
286	1×	.fetch_one(&mut *tx)
287	1×	.await?;
288	1×	let now = now_ms();
289	1×	for (i, id) in card_ids.iter().enumerate() {
290	1×	sqlx::query("UPDATE project_cards SET column_id = $1, position = $2, moved_ms = $3 WHERE id = $4 AND project_id = $5")
291	1×	.bind(column_id)
292	1×	.bind(pos + i as i64)
293	1×	.bind(now)
294	1×	.bind(id)
295	1×	.bind(project_id)
296	1×	.execute(&mut *tx)
297	1×	.await?;
298		}
299	1×	sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
300	1×	tx.commit().await
301	1×	}
302
303		/// Take `card_ids` off the board, kept (#293); how many were on it.
304	3×	pub async fn archive_cards(pool: &Pool, project_id: i64, card_ids: &[i64]) -> sqlx::Result<u64> {
305	3×	let mut tx = pool.begin().await?;
306	3×	let now = now_ms();
307	3×	let mut n = 0;
308	3×	for id in card_ids {
309	3×	n += sqlx::query("UPDATE project_cards SET archived_ms = $1 WHERE id = $2 AND project_id = $3 AND archived_ms IS NULL")
310	3×	.bind(now)
311	3×	.bind(id)
312	3×	.bind(project_id)
313	3×	.execute(&mut *tx)
314	3×	.await?
315	3×	.rows_affected();
316		}
317	3×	sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
318	3×	tx.commit().await?;
319	3×	Ok(n)
320	3×	}
321
322		/// Put an archived card back on the board, at the end of its column, as
323		/// if it had just joined it (so its column does not archive it again at
324		/// once); `false` when it was not archived.
325	2×	pub async fn restore_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<bool> {
326	2×	let mut tx = pool.begin().await?;
327	2×	let pos: i64 = sqlx::query_scalar(
328	2×	"SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = (SELECT column_id FROM project_cards WHERE id = $1)",
329	2×	)
330	2×	.bind(card_id)
331	2×	.fetch_one(&mut *tx)
332	2×	.await?;
333	2×	let now = now_ms();
334	2×	let r = sqlx::query(
335	2×	"UPDATE project_cards SET archived_ms = NULL, moved_ms = $1, position = $2 WHERE id = $3 AND project_id = $4 AND archived_ms IS NOT NULL",
336	2×	)
337	2×	.bind(now)
338	2×	.bind(pos)
339	2×	.bind(card_id)
340	2×	.bind(project_id)
341	2×	.execute(&mut *tx)
342	2×	.await?;
343	2×	sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
344	2×	tx.commit().await?;
345	2×	Ok(r.rows_affected() == 1)
346	2×	}
347
348		/// Whether `card` joined its column more than `days` ago, at `now`.
349	8×	pub fn older_than(card: &Card, days: i64, now: i64) -> bool {
350	8×	card.moved_ms < now - days * DAY_MS
351	8×	}
352
353		/// Archive a column's cards this many days after they join it; `None`
354		/// finished cards after [`FINISHED_KEPT_DAYS`] (the default); [`NEVER`]
355		/// none.
356	3×	pub async fn set_archive_after(pool: &Pool, project_id: i64, column_id: i64, days: Option<i64>) -> sqlx::Result<()> {
357	3×	sqlx::query("UPDATE project_columns SET archive_after_days = $1 WHERE id = $2 AND project_id = $3")
358	3×	.bind(days)
359	3×	.bind(column_id)
360	3×	.bind(project_id)
361	3×	.execute(pool)
362	3×	.await?;
363	3×	touch(pool, project_id).await
364	3×	}
365
366		/// Days after which a column with no setting of its own archives its
367		/// finished cards (#293): a board shows the last fortnight's done work.
368		pub const FINISHED_KEPT_DAYS: i64 = 14;
369
370		/// A column's `archive_after_days` that means never.
371		pub const NEVER: i64 = -1;
372
373		/// Archive, on every board, the cards their columns keep no longer (#293);
374		/// how many. A column that archives after N days: every card that joined it
375		/// longer ago. A column with no setting: finished cards (a closed issue, a
376		/// closed or landed proposal) after [`FINISHED_KEPT_DAYS`], so a Done column
377		/// stops growing while open work and notes stay. [`NEVER`]: none.
378	192×	pub async fn archive_due(pool: &Pool, now: i64) -> sqlx::Result<u64> {
379	192×	let set = sqlx::query(
380	192×	"UPDATE project_cards SET archived_ms = $1 WHERE archived_ms IS NULL AND EXISTS \
381	192×	(SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days >= 0 \
382	192×	AND project_cards.moved_ms < $1 - c.archive_after_days * $2)",
383	192×	)
384	192×	.bind(now)
385	192×	.bind(DAY_MS)
386	192×	.execute(pool)
387	192×	.await?;
388	192×	let finished = sqlx::query(
389	192×	"UPDATE project_cards SET archived_ms = $1 WHERE archived_ms IS NULL AND moved_ms < $2 \
390	192×	AND EXISTS (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days IS NULL) \
391	192×	AND ((kind = 'issue' AND EXISTS (SELECT 1 FROM issues i WHERE i.repo_id = project_cards.repo_id \
392	192×	AND i.number = project_cards.number AND i.state = 'closed')) \
393	192×	OR (kind = 'proposal' AND EXISTS (SELECT 1 FROM proposals p WHERE p.repo_id = project_cards.repo_id \
394	192×	AND p.number = project_cards.number AND p.state IN ('closed', 'landed'))))",
395	192×	)
396	192×	.bind(now)
397	192×	.bind(now - FINISHED_KEPT_DAYS * DAY_MS)
398	192×	.execute(pool)
399	192×	.await?;
400	191×	Ok(set.rows_affected() + finished.rows_affected())
401	191×	}
402
403		/// Swap a card with its neighbour in its column (`-1` up, `1` down).
404	0×	pub async fn shift_card(pool: &Pool, project_id: i64, card_id: i64, by: i64) -> sqlx::Result<()> {
405	0×	let Some(card): Option<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE id = $1 AND project_id = $2")
406	0×	.bind(card_id)
407	0×	.bind(project_id)
408	0×	.fetch_optional(pool)
409	0×	.await?
410		else {
411	0×	return Ok(());
412		};
413	0×	let col: Vec<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE column_id = $1 ORDER BY position, id")
414	0×	.bind(card.column_id)
415	0×	.fetch_all(pool)
416	0×	.await?;
417	0×	let Some(i) = col.iter().position(\|c\| c.id == card_id) else { return Ok(()) };
418	0×	let j = i as i64 + by;
419	0×	if j < 0 \|\| j as usize >= col.len() {
420	0×	return Ok(());
421	0×	}
422	0×	let mut ids: Vec<i64> = col.iter().map(\|c\| c.id).collect();
423	0×	ids.swap(i, j as usize);
424	0×	for (pos, id) in ids.iter().enumerate() {
425	0×	sqlx::query("UPDATE project_cards SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
426		}
427	0×	touch(pool, project_id).await
428	0×	}
429
430	3×	pub async fn remove_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<()> {
431	3×	sqlx::query("DELETE FROM project_cards WHERE id = $1 AND project_id = $2").bind(card_id).bind(project_id).execute(pool).await?;
432	3×	touch(pool, project_id).await
433	3×	}
434
435		/// The projects (and column names) an issue or proposal is on.
436	99×	pub async fn placements(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Vec<(Project, String)>> {
437	99×	let rows: Vec<(i64, String, Option<i64>)> = sqlx::query_as(
438	99×	"SELECT c.project_id, col.name, c.archived_ms FROM project_cards c JOIN project_columns col ON col.id = c.column_id WHERE c.repo_id = $1 AND c.number = $2 AND c.kind <> 'note'",
439	99×	)
440	99×	.bind(repo_id)
441	99×	.bind(number)
442	99×	.fetch_all(pool)
443	99×	.await?;
444	99×	let mut out = Vec::new();
445	99×	for (pid, col, archived) in rows {
446	3×	let col = if archived.is_some() { format!("{col}, archived") } else { col };
447	3×	if let Some(p) = sqlx::query_as::<_, Project>("SELECT * FROM projects WHERE id = $1").bind(pid).fetch_optional(pool).await? {
448	3×	out.push((p, col));
449	3×	}
450		}
451	99×	Ok(out)
452	99×	}

Coverage / crates/hub/src/db/projects.rs 81.6%

Keyboard