Skip to main content

max / goingson

5.9 KB · 154 lines History Blame Raw
1 //! Read-only email queries: the flat list views and the single-row fetch by id.
2 //!
3 //! `get_by_id` is the shared read the write paths in `crud`, `flags`, `state`
4 //! and `draft` call to return the row they just changed.
5
6 use goingson_core::{Email, EmailId, ProjectId, Result, UserId};
7 use rusqlite::{Connection, params, params_from_iter};
8
9 use crate::utils::{bind_placeholders, query_all, query_opt};
10
11 use super::row::{EMAIL_LIST_CAP, EMAIL_LIST_COLUMNS, EMAIL_SELECT_COLUMNS, EmailRow};
12
13 /// Every email for the user, bodies included, for a backup export.
14 pub(super) fn list_all_for_backup(conn: &Connection, user_id: UserId) -> Result<Vec<Email>> {
15 let query = format!(
16 "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? ORDER BY e.received_at DESC"
17 );
18 let rows = query_all(
19 conn,
20 &query,
21 params![user_id.to_string(), user_id.to_string()],
22 EmailRow::from_row,
23 )?;
24 rows.into_iter().map(Email::try_from).collect()
25 }
26
27 /// Every non-draft email, bodies included.
28 pub(super) fn list_all(
29 conn: &Connection,
30 user_id: UserId,
31 include_archived: bool,
32 ) -> Result<Vec<Email>> {
33 let archived_filter = if include_archived {
34 ""
35 } else {
36 "AND e.is_archived = 0"
37 };
38 let query = format!(
39 "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.is_draft = 0 {archived_filter} ORDER BY e.received_at DESC"
40 );
41 let rows = query_all(
42 conn,
43 &query,
44 params![user_id.to_string(), user_id.to_string()],
45 EmailRow::from_row,
46 )?;
47 rows.into_iter().map(Email::try_from).collect()
48 }
49
50 /// Body-less, capped flat list for the metadata-only list view.
51 pub(super) fn list_metadata(
52 conn: &Connection,
53 user_id: UserId,
54 include_archived: bool,
55 ) -> Result<Vec<Email>> {
56 let archived_filter = if include_archived {
57 ""
58 } else {
59 "AND e.is_archived = 0"
60 };
61 let query = format!(
62 "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.is_draft = 0 {archived_filter} ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
63 );
64 let rows = query_all(
65 conn,
66 &query,
67 params![user_id.to_string(), user_id.to_string()],
68 EmailRow::from_row,
69 )?;
70 rows.into_iter().map(Email::try_from).collect()
71 }
72
73 /// Emails linked to one project.
74 pub(super) fn list_by_project(
75 conn: &Connection,
76 user_id: UserId,
77 project_id: ProjectId,
78 ) -> Result<Vec<Email>> {
79 // Body-less + capped: the project dashboard renders only subject/from/date
80 // and opens the reader (which re-fetches the full body via `get_by_id`) on
81 // click, so this must not materialize every body into RAM (Perf S3, same
82 // rule as `list_metadata`). `EMAIL_LIST_COLUMNS` forces `body_truncated=1`.
83 let query = format!(
84 "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.project_id = ? ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
85 );
86 let rows = query_all(
87 conn,
88 &query,
89 params![
90 user_id.to_string(),
91 user_id.to_string(),
92 project_id.to_string()
93 ],
94 EmailRow::from_row,
95 )?;
96 rows.into_iter().map(Email::try_from).collect()
97 }
98
99 /// Emails sent to or from any of the given addresses.
100 pub(super) fn list_by_addresses(
101 conn: &Connection,
102 user_id: UserId,
103 addresses: &[&str],
104 ) -> Result<Vec<Email>> {
105 if addresses.is_empty() {
106 return Ok(Vec::new());
107 }
108 let placeholders = bind_placeholders(addresses.len());
109 let query = format!(
110 "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND (LOWER(e.from_address) IN ({placeholders}) OR LOWER(e.to_address) IN ({placeholders})) ORDER BY e.received_at DESC LIMIT 200"
111 );
112 let mut binds: Vec<String> = Vec::with_capacity(addresses.len() * 2 + 2);
113 binds.push(user_id.to_string());
114 binds.push(user_id.to_string());
115 // Bind addresses twice (once for from_address IN, once for to_address IN)
116 for _ in 0..2 {
117 binds.extend(addresses.iter().map(|a| a.to_lowercase()));
118 }
119 let rows = query_all(conn, &query, params_from_iter(binds), EmailRow::from_row)?;
120 rows.into_iter().map(Email::try_from).collect()
121 }
122
123 /// Unarchived emails with no project, for the link-to-project picker.
124 pub(super) fn list_unlinked(conn: &Connection, user_id: UserId) -> Result<Vec<Email>> {
125 // Body-less + capped: the sole caller is the "link email to project" picker,
126 // which shows only subject/from. No body needed, and an unbounded mailbox
127 // must not load every body into RAM (Perf S3). `EMAIL_LIST_COLUMNS` forces
128 // `body_truncated=1` so any later reader re-fetches the full body.
129 let query = format!(
130 "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.project_id IS NULL AND e.is_archived = 0 ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
131 );
132 let rows = query_all(
133 conn,
134 &query,
135 params![user_id.to_string(), user_id.to_string()],
136 EmailRow::from_row,
137 )?;
138 rows.into_iter().map(Email::try_from).collect()
139 }
140
141 /// One email by id, body included.
142 pub(super) fn get_by_id(conn: &Connection, id: EmailId, user_id: UserId) -> Result<Option<Email>> {
143 let query = format!(
144 "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.id = ? AND e.user_id = ?"
145 );
146 let row = query_opt(
147 conn,
148 &query,
149 params![user_id.to_string(), id.to_string(), user_id.to_string()],
150 EmailRow::from_row,
151 )?;
152 row.map(Email::try_from).transpose()
153 }
154