Skip to main content

max / goingson

7.9 KB · 267 lines History Blame Raw
1 //! SQLite implementation of the `ContextRepository`.
2 //!
3 //! Contexts are stored as spans and read per day, so every query here is a
4 //! range overlap and none of them is per-day. See
5 //! [`goingson_core::Context`] for why that is the model rather than a
6 //! convenience.
7
8 use chrono::{NaiveDate, Utc};
9 use goingson_core::{
10 Context, ContextId, ContextKind, ContextRepository, CoreError, Result, UserId,
11 };
12 use rusqlite::params;
13
14 use crate::Db;
15 use crate::utils::{
16 execute, format_datetime, parse_datetime, parse_uuid, parse_uuid_opt, query_all,
17 };
18
19 /// The columns every context query selects.
20 const CONTEXT_COLUMNS: &str =
21 "id, user_id, label, kind, starts_on, ends_on, migrated_from_event_id, created_at, updated_at";
22
23 const DATE: &str = "%Y-%m-%d";
24
25 pub struct SqliteContextRepository {
26 db: Db,
27 }
28
29 impl SqliteContextRepository {
30 #[tracing::instrument(skip_all)]
31 pub fn new(db: Db) -> Self {
32 Self { db }
33 }
34 }
35
36 fn parse_date(value: &str) -> Result<NaiveDate> {
37 NaiveDate::parse_from_str(value, DATE).map_err(|_| CoreError::parse("Invalid date"))
38 }
39
40 fn from_row(
41 row: &rusqlite::Row<'_>,
42 ) -> rusqlite::Result<(
43 String,
44 String,
45 String,
46 String,
47 String,
48 String,
49 Option<String>,
50 String,
51 String,
52 )> {
53 Ok((
54 row.get("id")?,
55 row.get("user_id")?,
56 row.get("label")?,
57 row.get("kind")?,
58 row.get("starts_on")?,
59 row.get("ends_on")?,
60 row.get("migrated_from_event_id")?,
61 row.get("created_at")?,
62 row.get("updated_at")?,
63 ))
64 }
65
66 type Row = (
67 String,
68 String,
69 String,
70 String,
71 String,
72 String,
73 Option<String>,
74 String,
75 String,
76 );
77
78 fn into_context(row: Row) -> Result<Context> {
79 let (id, user_id, label, kind, starts_on, ends_on, migrated_from, created_at, updated_at) = row;
80 Ok(Context {
81 id: parse_uuid(&id)?.into(),
82 user_id: parse_uuid(&user_id)?.into(),
83 label,
84 kind: ContextKind::parse(&kind),
85 starts_on: parse_date(&starts_on)?,
86 ends_on: parse_date(&ends_on)?,
87 migrated_from_event_id: parse_uuid_opt(migrated_from.as_deref())?.map(Into::into),
88 created_at: parse_datetime(&created_at)?,
89 updated_at: parse_datetime(&updated_at)?,
90 })
91 }
92
93 impl ContextRepository for SqliteContextRepository {
94 #[tracing::instrument(skip(self))]
95 fn list_overlapping(
96 &self,
97 user_id: UserId,
98 from: NaiveDate,
99 to: NaiveDate,
100 ) -> Result<Vec<Context>> {
101 let conn = self.db.conn()?;
102 // Both ends inclusive on both sides: a context overlaps the window when
103 // it starts before the window ends and ends after the window starts.
104 let rows: Vec<Row> = query_all(
105 &conn,
106 &format!(
107 "SELECT {CONTEXT_COLUMNS} FROM contexts
108 WHERE user_id = ?1 AND starts_on <= ?3 AND ends_on >= ?2
109 ORDER BY starts_on, label"
110 ),
111 params![
112 user_id.to_string(),
113 from.format(DATE).to_string(),
114 to.format(DATE).to_string()
115 ],
116 from_row,
117 )?;
118 rows.into_iter().map(into_context).collect()
119 }
120
121 #[tracing::instrument(skip(self))]
122 fn list_all(&self, user_id: UserId) -> Result<Vec<Context>> {
123 let conn = self.db.conn()?;
124 let rows: Vec<Row> = query_all(
125 &conn,
126 &format!(
127 "SELECT {CONTEXT_COLUMNS} FROM contexts WHERE user_id = ?1 ORDER BY starts_on, label"
128 ),
129 params![user_id.to_string()],
130 from_row,
131 )?;
132 rows.into_iter().map(into_context).collect()
133 }
134
135 #[tracing::instrument(skip(self))]
136 fn create(
137 &self,
138 user_id: UserId,
139 label: &str,
140 kind: ContextKind,
141 starts_on: NaiveDate,
142 ends_on: NaiveDate,
143 ) -> Result<Context> {
144 let (starts_on, ends_on) = ordered(starts_on, ends_on);
145 // Truncated to what the column holds, so the record handed back is the
146 // record a re-read gives. Returning a sub-second `now()` that the store
147 // rounded away is how a struct comes to disagree with its own row.
148 let now = stored_now();
149 let context = Context {
150 id: ContextId::new(),
151 user_id,
152 label: label.to_owned(),
153 kind,
154 starts_on,
155 ends_on,
156 migrated_from_event_id: None,
157 created_at: now,
158 updated_at: now,
159 };
160
161 let conn = self.db.conn()?;
162 execute(
163 &conn,
164 "INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on, created_at, updated_at)
165 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
166 params![
167 context.id.to_string(),
168 user_id.to_string(),
169 context.label,
170 kind.as_str(),
171 starts_on.format(DATE).to_string(),
172 ends_on.format(DATE).to_string(),
173 format_datetime(&now),
174 format_datetime(&now),
175 ],
176 )?;
177 Ok(context)
178 }
179
180 #[tracing::instrument(skip(self))]
181 fn update(
182 &self,
183 user_id: UserId,
184 id: ContextId,
185 label: &str,
186 kind: ContextKind,
187 starts_on: NaiveDate,
188 ends_on: NaiveDate,
189 ) -> Result<Context> {
190 let (starts_on, ends_on) = ordered(starts_on, ends_on);
191 let now = stored_now();
192 let conn = self.db.conn()?;
193 let changed = execute(
194 &conn,
195 "UPDATE contexts SET label = ?3, kind = ?4, starts_on = ?5, ends_on = ?6, updated_at = ?7
196 WHERE id = ?1 AND user_id = ?2",
197 params![
198 id.to_string(),
199 user_id.to_string(),
200 label,
201 kind.as_str(),
202 starts_on.format(DATE).to_string(),
203 ends_on.format(DATE).to_string(),
204 format_datetime(&now),
205 ],
206 )?;
207 if changed == 0 {
208 return Err(CoreError::not_found("Context", id));
209 }
210
211 let rows: Vec<Row> = query_all(
212 &conn,
213 &format!("SELECT {CONTEXT_COLUMNS} FROM contexts WHERE id = ?1"),
214 params![id.to_string()],
215 from_row,
216 )?;
217 rows.into_iter()
218 .next()
219 .ok_or_else(|| CoreError::not_found("Context", id))
220 .and_then(into_context)
221 }
222
223 #[tracing::instrument(skip(self))]
224 fn delete(&self, user_id: UserId, id: ContextId) -> Result<()> {
225 let conn = self.db.conn()?;
226 // The event this was migrated from comes back to the timeline. That is
227 // the whole of what makes migration 067's guess reversible, and it is
228 // one statement because the migration hid the event rather than
229 // deleting it.
230 execute(
231 &conn,
232 "UPDATE events SET converted_to_context_id = NULL WHERE converted_to_context_id = ?1",
233 params![id.to_string()],
234 )?;
235 let changed = execute(
236 &conn,
237 "DELETE FROM contexts WHERE id = ?1 AND user_id = ?2",
238 params![id.to_string(), user_id.to_string()],
239 )?;
240 if changed == 0 {
241 return Err(CoreError::not_found("Context", id));
242 }
243 Ok(())
244 }
245 }
246
247 /// Now, at the precision the store keeps.
248 ///
249 /// SQLite holds `YYYY-MM-DD HH:MM:SS`, so a `DateTime` with sub-second
250 /// precision is not what comes back out.
251 fn stored_now() -> chrono::DateTime<Utc> {
252 let now = Utc::now();
253 now - chrono::Duration::nanoseconds(i64::from(now.timestamp_subsec_nanos()))
254 }
255
256 /// The two ends, earliest first.
257 ///
258 /// A span typed backwards is a slip rather than a question: the days it names
259 /// are unambiguous, so it is straightened instead of refused.
260 fn ordered(starts_on: NaiveDate, ends_on: NaiveDate) -> (NaiveDate, NaiveDate) {
261 if ends_on < starts_on {
262 (ends_on, starts_on)
263 } else {
264 (starts_on, ends_on)
265 }
266 }
267