teamcraft/services/requests/
queries.rs

1//! Reading change requests: lists, one request, its items, comments, attachments and change orders.
2
3use super::*;
4
5/// Filters of the request list.
6#[derive(Clone, Debug, Default)]
7pub struct RequestFilter {
8    pub status: Option<RequestStatus>,
9    /// Only requests that are not finished (new, in triage, accepted, deferred).
10    pub open_only: bool,
11    pub requested_by: Option<Uuid>,
12    pub assignee: Option<Uuid>,
13    pub item_id: Option<Uuid>,
14}
15
16const LIST_WHERE: &str = "
17    WHERE ($1::text IS NULL OR cr.status = $1)
18      AND (NOT $2 OR cr.status IN ('new', 'in_triage', 'accepted', 'deferred'))
19      AND ($3::uuid IS NULL OR cr.requested_by = $3)
20      AND ($4::uuid IS NULL OR cr.assignee = $4)
21      AND ($5::uuid IS NULL OR EXISTS (SELECT 1 FROM change_request_items f
22                                       WHERE f.request_id = cr.id AND f.item_id = $5))";
23
24/// Change requests the actor may see (see `access::request_visible`); the
25/// total counts the same rows.
26pub async fn list(
27    pool: &PgPool,
28    actor: &Actor,
29    filter: &RequestFilter,
30    page: Page,
31) -> AppResult<(Vec<ChangeRequest>, i64)> {
32    let status = filter.status.map(RequestStatus::as_str);
33    let rows = sqlx::query_as::<_, ChangeRequest>(&format!(
34        "{REQUEST_SELECT} {LIST_WHERE} AND {}
35         ORDER BY cr.created_at DESC LIMIT $6 OFFSET $7",
36        access::request_visible("cr", 8, 9)
37    ))
38    .bind(status)
39    .bind(filter.open_only)
40    .bind(filter.requested_by)
41    .bind(filter.assignee)
42    .bind(filter.item_id)
43    .bind(page.limit)
44    .bind(page.offset)
45    .bind(actor.id)
46    .bind(&actor.hidden_projects)
47    .fetch_all(pool)
48    .await?;
49    let total = sqlx::query_scalar::<_, i64>(&format!(
50        "SELECT COUNT(*) FROM change_requests cr {LIST_WHERE} AND {}",
51        access::request_visible("cr", 6, 7)
52    ))
53    .bind(status)
54    .bind(filter.open_only)
55    .bind(filter.requested_by)
56    .bind(filter.assignee)
57    .bind(filter.item_id)
58    .bind(actor.id)
59    .bind(&actor.hidden_projects)
60    .fetch_one(pool)
61    .await?;
62    Ok((rows, total))
63}
64
65/// Whether the actor may see the request (`access::request_visible`).
66pub(crate) async fn is_visible(
67    conn: &mut PgConnection,
68    actor: &Actor,
69    request_id: Uuid,
70) -> AppResult<bool> {
71    Ok(sqlx::query_scalar::<_, bool>(&format!(
72        "SELECT EXISTS(SELECT 1 FROM change_requests cr WHERE cr.id = $1 AND {})",
73        access::request_visible("cr", 2, 3)
74    ))
75    .bind(request_id)
76    .bind(actor.id)
77    .bind(&actor.hidden_projects)
78    .fetch_one(&mut *conn)
79    .await?)
80}
81
82/// Of the given requests, those the actor may see.
83async fn visible_ids(pool: &PgPool, actor: &Actor, ids: &[Uuid]) -> AppResult<Vec<Uuid>> {
84    if ids.is_empty() {
85        return Ok(Vec::new());
86    }
87    Ok(sqlx::query_scalar::<_, Uuid>(&format!(
88        "SELECT cr.id FROM change_requests cr WHERE cr.id = ANY($1) AND {}",
89        access::request_visible("cr", 2, 3)
90    ))
91    .bind(ids)
92    .bind(actor.id)
93    .bind(&actor.hidden_projects)
94    .fetch_all(pool)
95    .await?)
96}
97
98async fn only_visible(
99    pool: &PgPool,
100    actor: &Actor,
101    rows: Vec<ChangeRequest>,
102) -> AppResult<Vec<ChangeRequest>> {
103    let ids: Vec<Uuid> = rows.iter().map(|r| r.id).collect();
104    let visible = visible_ids(pool, actor, &ids).await?;
105    Ok(rows
106        .into_iter()
107        .filter(|r| visible.contains(&r.id))
108        .collect())
109}
110
111pub(crate) async fn get_unchecked(pool: &PgPool, id: Uuid) -> AppResult<ChangeRequest> {
112    sqlx::query_as::<_, ChangeRequest>(&format!("{REQUEST_SELECT} WHERE cr.id = $1"))
113        .bind(id)
114        .fetch_optional(pool)
115        .await?
116        .ok_or_else(|| AppError::not_found("Change request not found."))
117}
118
119/// A request the actor may see; hidden requests answer like missing ones.
120pub async fn get(pool: &PgPool, actor: &Actor, id: Uuid) -> AppResult<ChangeRequest> {
121    let request = get_unchecked(pool, id).await?;
122    let mut conn = pool.acquire().await?;
123    if !is_visible(&mut conn, actor, id).await? {
124        return Err(AppError::not_found("Change request not found."));
125    }
126    Ok(request)
127}
128
129/// A request by number, if the actor may see it.
130pub async fn get_by_number(pool: &PgPool, actor: &Actor, number: &str) -> AppResult<ChangeRequest> {
131    let number = number.trim().to_ascii_uppercase();
132    let missing = || AppError::not_found(format!("Change request '{number}' not found."));
133    let request =
134        sqlx::query_as::<_, ChangeRequest>(&format!("{REQUEST_SELECT} WHERE cr.number = $1"))
135            .bind(&number)
136            .fetch_optional(pool)
137            .await?
138            .ok_or_else(missing)?;
139    let mut conn = pool.acquire().await?;
140    if !is_visible(&mut conn, actor, request.id).await? {
141        return Err(missing());
142    }
143    Ok(request)
144}
145
146/// Locks a request the actor may see (hidden ones answer "not found").
147pub(super) async fn lock(
148    conn: &mut PgConnection,
149    actor: &Actor,
150    id: Uuid,
151) -> AppResult<ChangeRequest> {
152    let request = lock_unchecked(conn, id).await?;
153    if !is_visible(conn, actor, id).await? {
154        return Err(AppError::not_found("Change request not found."));
155    }
156    Ok(request)
157}
158
159/// Locks the request row until the transaction ends and loads it.
160pub(super) async fn lock_unchecked(conn: &mut PgConnection, id: Uuid) -> AppResult<ChangeRequest> {
161    // the row lock first: the full select has outer joins and a count
162    sqlx::query_scalar::<_, Uuid>("SELECT id FROM change_requests WHERE id = $1 FOR UPDATE")
163        .bind(id)
164        .fetch_optional(&mut *conn)
165        .await?
166        .ok_or_else(|| AppError::not_found("Change request not found."))?;
167    Ok(
168        sqlx::query_as::<_, ChangeRequest>(&format!("{REQUEST_SELECT} WHERE cr.id = $1"))
169            .bind(id)
170            .fetch_one(&mut *conn)
171            .await?,
172    )
173}
174
175const ITEM_SELECT: &str = "
176    SELECT i.id AS item_id, i.number AS item_number, i.item_type, i.project_id, p.code AS project_code,
177           cri.revision_id, r.code AS revision_code, r.state AS revision_state,
178           lr.name, lr.id AS latest_revision_id, lr.code AS latest_code, lr.state AS latest_state,
179           cri.note
180    FROM change_request_items cri
181    JOIN items i ON i.id = cri.item_id
182    LEFT JOIN projects p ON p.id = i.project_id
183    LEFT JOIN revisions r ON r.id = cri.revision_id
184    CROSS JOIN LATERAL (SELECT r2.id, r2.code, r2.name, r2.state FROM revisions r2
185                        WHERE r2.item_id = i.id ORDER BY r2.seq DESC LIMIT 1) lr";
186
187/// The affected items; items the actor may not see are placeholder rows
188/// (without identity and note), listed last.
189pub async fn items(pool: &PgPool, actor: &Actor, request_id: Uuid) -> AppResult<Vec<RequestItem>> {
190    let mut rows = items_unchecked(pool, request_id).await?;
191    for row in &mut rows {
192        if !actor.can_see_project(row.project_id) {
193            row.mask();
194        }
195    }
196    // placeholders last: their position would bound the hidden number
197    rows.sort_by_key(|r| r.restricted);
198    Ok(rows)
199}
200
201pub(crate) async fn items_unchecked(
202    pool: &PgPool,
203    request_id: Uuid,
204) -> AppResult<Vec<RequestItem>> {
205    Ok(sqlx::query_as::<_, RequestItem>(&format!(
206        "{ITEM_SELECT} WHERE cri.request_id = $1 ORDER BY i.number"
207    ))
208    .bind(request_id)
209    .fetch_all(pool)
210    .await?)
211}
212
213/// The discussion, oldest first.
214pub async fn comments(pool: &PgPool, request_id: Uuid) -> AppResult<Vec<RequestComment>> {
215    Ok(sqlx::query_as::<_, RequestComment>(
216        "SELECT c.id, c.author_id, u.display_name AS author_name, c.body, c.created_at
217         FROM change_request_comments c JOIN users u ON u.id = c.author_id
218         WHERE c.request_id = $1 ORDER BY c.created_at, c.id",
219    )
220    .bind(request_id)
221    .fetch_all(pool)
222    .await?)
223}
224
225const ATTACHMENT_SELECT: &str = "
226    SELECT a.id, a.request_id, a.filename, a.content_type, a.size_bytes, a.sha256,
227           a.uploaded_by, u.display_name AS uploaded_by_name, a.uploaded_at
228    FROM change_request_attachments a JOIN users u ON u.id = a.uploaded_by";
229
230pub async fn attachments(pool: &PgPool, request_id: Uuid) -> AppResult<Vec<RequestAttachment>> {
231    Ok(sqlx::query_as::<_, RequestAttachment>(&format!(
232        "{ATTACHMENT_SELECT} WHERE a.request_id = $1 ORDER BY a.uploaded_at, a.id"
233    ))
234    .bind(request_id)
235    .fetch_all(pool)
236    .await?)
237}
238
239pub(crate) async fn attachment_unchecked(pool: &PgPool, id: Uuid) -> AppResult<RequestAttachment> {
240    sqlx::query_as::<_, RequestAttachment>(&format!("{ATTACHMENT_SELECT} WHERE a.id = $1"))
241        .bind(id)
242        .fetch_optional(pool)
243        .await?
244        .ok_or_else(|| AppError::not_found("Attachment not found."))
245}
246
247/// An attachment of a request the actor may see.
248pub async fn attachment(pool: &PgPool, actor: &Actor, id: Uuid) -> AppResult<RequestAttachment> {
249    let att = attachment_unchecked(pool, id).await?;
250    let mut conn = pool.acquire().await?;
251    if !is_visible(&mut conn, actor, att.request_id).await? {
252        return Err(AppError::not_found("Attachment not found."));
253    }
254    Ok(att)
255}
256
257/// The change orders that implement a request and that the actor may see,
258/// plus the number of those it may not see.
259pub async fn linked_changes(
260    pool: &PgPool,
261    actor: &Actor,
262    request_id: Uuid,
263) -> AppResult<(Vec<Change>, i64)> {
264    let all = sqlx::query_as::<_, Change>(&format!(
265        "{} JOIN change_request_changes x ON x.change_id = c.id
266         WHERE x.request_id = $1 ORDER BY c.created_at",
267        changes::CHANGE_SELECT
268    ))
269    .bind(request_id)
270    .fetch_all(pool)
271    .await?;
272    let ids: Vec<Uuid> = all.iter().map(|c| c.id).collect();
273    let visible = changes::visible_ids(pool, actor, &ids).await?;
274    let hidden = (all.len() - visible.len()) as i64;
275    Ok((
276        all.into_iter()
277            .filter(|c| visible.contains(&c.id))
278            .collect(),
279        hidden,
280    ))
281}
282
283/// The requests a change order implements (those the actor may see).
284pub async fn for_change(
285    pool: &PgPool,
286    actor: &Actor,
287    change_id: Uuid,
288) -> AppResult<Vec<ChangeRequest>> {
289    let rows = sqlx::query_as::<_, ChangeRequest>(&format!(
290        "{REQUEST_SELECT} JOIN change_request_changes x ON x.request_id = cr.id
291         WHERE x.change_id = $1 ORDER BY cr.number"
292    ))
293    .bind(change_id)
294    .fetch_all(pool)
295    .await?;
296    only_visible(pool, actor, rows).await
297}
298
299/// Open requests on an item the actor may see (revision page, desktop app).
300pub async fn open_for_item(
301    pool: &PgPool,
302    actor: &Actor,
303    item_id: Uuid,
304) -> AppResult<Vec<ChangeRequest>> {
305    let rows = sqlx::query_as::<_, ChangeRequest>(&format!(
306        "{REQUEST_SELECT} JOIN change_request_items f ON f.request_id = cr.id
307         WHERE f.item_id = $1 AND cr.status IN {OPEN_STATES}
308         ORDER BY cr.created_at DESC"
309    ))
310    .bind(item_id)
311    .fetch_all(pool)
312    .await?;
313    only_visible(pool, actor, rows).await
314}
315
316/// Open requests assigned to the actor (worklist).
317pub async fn assigned_to(pool: &PgPool, actor: &Actor) -> AppResult<Vec<ChangeRequest>> {
318    Ok(sqlx::query_as::<_, ChangeRequest>(&format!(
319        "{REQUEST_SELECT} WHERE cr.assignee = $1 AND cr.status IN {OPEN_STATES}
320         ORDER BY cr.due_date NULLS LAST, cr.created_at"
321    ))
322    .bind(actor.id)
323    .fetch_all(pool)
324    .await?)
325}
326
327/// Open requests raised by the actor (worklist).
328pub async fn open_by(pool: &PgPool, actor: &Actor) -> AppResult<Vec<ChangeRequest>> {
329    Ok(sqlx::query_as::<_, ChangeRequest>(&format!(
330        "{REQUEST_SELECT} WHERE cr.requested_by = $1 AND cr.status IN {OPEN_STATES}
331         ORDER BY cr.updated_at DESC"
332    ))
333    .bind(actor.id)
334    .fetch_all(pool)
335    .await?)
336}
teamcraft/services/requests/queries.rs

teamcraft/services/requests/
queries.rs
