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

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?;

    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, takes_finished) VALUES ($1, $2, $3, $4)")

            .bind(p.id)

            .bind(name)

            .bind(i as i64)

            .bind(*name == "Done")

            .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

}

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)

}

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 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) {

    }

    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 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)

}

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 {

    }

    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

}

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

}

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)

}

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)

}

pub fn older_than(card: &Card, days: i64, now: i64) -> bool {

    card.moved_ms < now - days * DAY_MS

}

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

}

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())

}

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

}

pub async fn set_takes_finished(pool: &Pool, project_id: i64, column_id: Option<i64>) -> sqlx::Result<()> {

    let mut tx = super::begin_write(pool).await?;

    sqlx::query("UPDATE project_columns SET takes_finished = FALSE WHERE project_id = $1").bind(project_id).execute(&mut *tx).await?;

    if let Some(c) = column_id {

        sqlx::query("UPDATE project_columns SET takes_finished = TRUE WHERE id = $1 AND project_id = $2")

            .bind(c)

            .bind(project_id)

            .execute(&mut *tx)

            .await?;

    }

    tx.commit().await?;

    touch(pool, project_id).await

}

pub async fn finished(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<()> {

    let cards: Vec<(i64, i64, i64)> = sqlx::query_as(

        "SELECT c.id, c.project_id, c.column_id FROM project_cards c

         WHERE c.repo_id = $1 AND c.number = $2 AND c.kind <> 'note' AND c.archived_ms IS NULL",

    )

    .bind(repo_id)

    .bind(number)

    .fetch_all(pool)

    .await?;

    for (card, project, column) in cards {

        let to: Option<i64> =

            sqlx::query_scalar("SELECT id FROM project_columns WHERE project_id = $1 AND takes_finished ORDER BY position LIMIT 1")

                .bind(project)

                .fetch_optional(pool)

                .await?;

        if let Some(to) = to.filter(|to| *to != column) {

            move_card(pool, project, card, to).await?;

        }

    Ok(())

}

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)

}