| 1 | //! What other modules' queries read of work items, as SQL fragments: the |
| 2 | //! only place outside this module that names `work_items` or |
| 3 | //! `work_item_repos`. Each takes the SQL expression it reads from as a |
| 4 | //! string literal, and expands to one, for `concat!` in a `const`. |
| 5 | //! |
| 6 | //! Statements other modules run on an account's or a repository's rows, |
| 7 | //! for what is kept for work items, are here too ([`user_deleted`], |
| 8 | //! [`REPO_PURGED`]). |
| 9 | |
| 10 | /// The number of the work item whose id is `$id`. |
| 11 | macro_rules! item_number { |
| 12 | ($id:literal) => { |
| 13 | concat!("(SELECT number FROM work_items WHERE work_items.id = ", $id, ")") |
| 14 | }; |
| 15 | } |
| 16 | |
| 17 | /// The workspace of the work item whose id is `$id`. |
| 18 | macro_rules! item_workspace { |
| 19 | ($id:literal) => { |
| 20 | concat!("(SELECT workspace FROM work_items WHERE work_items.id = ", $id, ")") |
| 21 | }; |
| 22 | } |
| 23 | |
| 24 | /// The numbers, as a JSON array in the order given, of the work items |
| 25 | /// whose ids are the JSON array `$ids`. |
| 26 | macro_rules! item_numbers { |
| 27 | ($ids:literal) => { |
| 28 | concat!( |
| 29 | "(SELECT json_group_array(number) FROM |
| 30 | (SELECT w.number AS number FROM json_each(", |
| 31 | $ids, |
| 32 | ") j JOIN work_items w ON w.id = j.value |
| 33 | WHERE w.number IS NOT NULL ORDER BY j.key))" |
| 34 | ) |
| 35 | }; |
| 36 | } |
| 37 | |
| 38 | /// The ids of the work items linked to repository `$repo`, any role. |
| 39 | macro_rules! items_linked_to { |
| 40 | ($repo:literal) => { |
| 41 | concat!("(SELECT item_id FROM work_item_repos WHERE repo_id = ", $repo, ")") |
| 42 | }; |
| 43 | } |
| 44 | |
| 45 | /// How many numbered work items linked to repository `$repo` are open. |
| 46 | macro_rules! open_items_linked_to { |
| 47 | ($repo:literal) => { |
| 48 | concat!( |
| 49 | "(SELECT count(*) FROM work_item_repos r JOIN work_items i ON i.id = r.item_id |
| 50 | WHERE r.repo_id = ", |
| 51 | $repo, |
| 52 | " AND i.state = 'open' AND i.number IS NOT NULL)" |
| 53 | ) |
| 54 | }; |
| 55 | } |
| 56 | |
| 57 | /// Work items, each with its origin's id (`origin_id`, NULL for none): a |
| 58 | /// table to select from. |
| 59 | macro_rules! items_with_origin { |
| 60 | () => { |
| 61 | "(SELECT i.id, i.workspace, i.number, i.title, i.state, i.reason, i.author_id, i.requested_by_id, |
| 62 | i.created_at, i.updated_at, o.repo_id AS origin_id |
| 63 | FROM work_items i LEFT JOIN work_item_repos o ON o.item_id = i.id AND o.role = 'origin')" |
| 64 | }; |
| 65 | } |
| 66 | |
| 67 | pub(crate) use {item_number, item_numbers, item_workspace, items_linked_to, items_with_origin, open_items_linked_to}; |
| 68 | |
| 69 | /// An account purged (ghost.rs, `?1` its id, `?2` its username): its work |
| 70 | /// items show as ghost's, and it is assigned to none. |
| 71 | pub(crate) fn user_deleted() -> Vec<String> { |
| 72 | use g1t_contracts::account_deletion::{GHOST_ID, GHOST_USERNAME}; |
| 73 | vec![ |
| 74 | format!("UPDATE work_items SET author_id = '{GHOST_ID}', author_name = '{GHOST_USERNAME}' WHERE author_id = ?1"), |
| 75 | format!( |
| 76 | "UPDATE work_items SET requested_by_id = '{GHOST_ID}', requested_by_name = '{GHOST_USERNAME}' WHERE requested_by_id = ?1" |
| 77 | ), |
| 78 | "UPDATE work_items SET assignees = (SELECT json_group_array(value) FROM json_each(work_items.assignees) WHERE value <> ?2) |
| 79 | WHERE ?1 IS NOT NULL AND json_valid(assignees) AND EXISTS (SELECT 1 FROM json_each(work_items.assignees) WHERE value = ?2)" |
| 80 | .to_owned(), |
| 81 | ] |
| 82 | } |
| 83 | |
| 84 | /// `repo.purged` (retired.rs, `?1` its id): its work items stay, their |
| 85 | /// links keeping the path it had. |
| 86 | pub(crate) const REPO_PURGED: &str = |
| 87 | "UPDATE work_item_repos SET path = COALESCE((SELECT path FROM repo_workspaces WHERE repo_id = ?1), path) WHERE repo_id = ?1"; |