Skip to main content

max / ripgrow

10.6 KB · 301 lines History Blame Raw
1 //! SQLite wrapper with versioned migrations for one ripgrow profile.
2 //!
3 //! Migrations are tracked with `PRAGMA user_version`. Each step runs inside a
4 //! transaction so the schema change and version bump are atomic. Pattern is
5 //! borrowed from audiofiles-core; kept minimal here because no migrations
6 //! have shipped yet.
7
8 use std::path::Path;
9
10 use rusqlite::{Connection, OptionalExtension, params};
11
12 use crate::error::Error;
13 use crate::values::LoadUnit;
14
15 const MIGRATIONS: &[&str] = &[MIGRATION_001, MIGRATION_002, MIGRATION_003];
16
17 // Historically `Unit` lived here as the profile's weight-unit preference.
18 // It moved into `values` as `LoadUnit` (same type, better name — it also
19 // describes each exercise's load column). Older imports still get `Unit`
20 // via `crate::Unit`.
21
22 pub struct Db {
23 conn: Connection,
24 }
25
26 impl Db {
27 /// Open (or create) a profile database at `path` and run migrations.
28 /// Does not populate the `meta` row; call [`Db::init_profile`] once on
29 /// first-run to record the profile name and unit choice.
30 pub fn open(path: impl AsRef<Path>) -> Result<Self, Error> {
31 let conn = Connection::open(path)?;
32 conn.pragma_update(None, "foreign_keys", "ON")?;
33 conn.pragma_update(None, "journal_mode", "WAL")?;
34 let mut db = Self { conn };
35 db.migrate()?;
36 Ok(db)
37 }
38
39 /// In-memory database for tests. Kept public so downstream crates can
40 /// exercise their TUI logic without touching the filesystem.
41 pub fn open_in_memory() -> Result<Self, Error> {
42 let conn = Connection::open_in_memory()?;
43 conn.pragma_update(None, "foreign_keys", "ON")?;
44 let mut db = Self { conn };
45 db.migrate()?;
46 Ok(db)
47 }
48
49 /// Insert the `meta` row on a freshly-created profile. Idempotent guard:
50 /// returns Ok without touching the row if one already exists.
51 pub fn init_profile(&self, profile_name: &str, unit: LoadUnit) -> Result<(), Error> {
52 let exists: bool = self
53 .conn
54 .query_row("SELECT 1 FROM meta LIMIT 1", [], |_| Ok(true))
55 .optional()?
56 .unwrap_or(false);
57 if exists {
58 return Ok(());
59 }
60 self.conn.execute(
61 "INSERT INTO meta (profile_name, unit, created) \
62 VALUES (?1, ?2, strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))",
63 params![profile_name, unit.as_str()],
64 )?;
65 Ok(())
66 }
67
68 pub fn profile_name(&self) -> Result<Option<String>, Error> {
69 Ok(self
70 .conn
71 .query_row("SELECT profile_name FROM meta LIMIT 1", [], |row| row.get(0))
72 .optional()?)
73 }
74
75 pub fn unit(&self) -> Result<Option<LoadUnit>, Error> {
76 let s: Option<String> = self
77 .conn
78 .query_row("SELECT unit FROM meta LIMIT 1", [], |row| row.get(0))
79 .optional()?;
80 s.map(|v| LoadUnit::parse(&v)).transpose()
81 }
82
83 pub fn conn(&self) -> &Connection {
84 &self.conn
85 }
86
87 fn migrate(&mut self) -> Result<(), Error> {
88 let version: i32 = self
89 .conn
90 .query_row("PRAGMA user_version", [], |row| row.get(0))?;
91
92 for (i, sql) in MIGRATIONS.iter().enumerate() {
93 let target = (i + 1) as i32;
94 if version < target {
95 let batch =
96 format!("BEGIN;\n{sql}\nPRAGMA user_version = {target};\nCOMMIT;");
97 self.conn.execute_batch(&batch)?;
98 }
99 }
100 Ok(())
101 }
102 }
103
104 const MIGRATION_001: &str = r#"
105 CREATE TABLE meta (
106 id INTEGER PRIMARY KEY CHECK (id = 1),
107 profile_name TEXT NOT NULL,
108 unit TEXT NOT NULL CHECK (unit IN ('kg', 'lb')),
109 created TEXT NOT NULL
110 );
111
112 CREATE TABLE tags (
113 id INTEGER PRIMARY KEY,
114 name TEXT NOT NULL UNIQUE
115 );
116
117 CREATE TABLE exercises (
118 id INTEGER PRIMARY KEY,
119 name TEXT NOT NULL UNIQUE,
120 resistance_type TEXT NOT NULL CHECK (resistance_type IN (
121 'bodyweight', 'machine', 'freeweight',
122 'cardio_time', 'cardio_distance'
123 )),
124 load_unit TEXT NOT NULL,
125 increment REAL NOT NULL,
126 notes TEXT NOT NULL DEFAULT ''
127 );
128
129 CREATE TABLE exercise_tags (
130 exercise_id INTEGER NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
131 tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
132 weight REAL NOT NULL DEFAULT 1.0,
133 PRIMARY KEY (exercise_id, tag_id)
134 );
135
136 CREATE TABLE sets (
137 id INTEGER PRIMARY KEY,
138 session_date TEXT NOT NULL,
139 exercise_id INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
140 set_index INTEGER NOT NULL,
141 load REAL NOT NULL,
142 reps INTEGER NOT NULL,
143 rpe INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
144 UNIQUE (session_date, exercise_id, set_index)
145 );
146
147 CREATE INDEX idx_sets_session_date ON sets(session_date);
148 CREATE INDEX idx_sets_exercise_date ON sets(exercise_id, session_date);
149
150 CREATE TABLE progression_state (
151 exercise_id INTEGER PRIMARY KEY REFERENCES exercises(id) ON DELETE CASCADE,
152 state TEXT NOT NULL CHECK (state IN (
153 'progressing', 'probation', 'deloading', 'rebuilding'
154 )),
155 since_date TEXT NOT NULL
156 );
157
158 CREATE TABLE readiness (
159 date TEXT NOT NULL,
160 tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
161 state TEXT NOT NULL CHECK (state IN ('ready', 'tired', 'sore')),
162 PRIMARY KEY (date, tag_id)
163 );
164 "#;
165
166 const MIGRATION_002: &str = r#"
167 ALTER TABLE sets ADD COLUMN is_diagnostic INTEGER NOT NULL DEFAULT 0
168 CHECK (is_diagnostic IN (0, 1));
169 CREATE INDEX idx_sets_exercise_diagnostic ON sets(exercise_id, is_diagnostic);
170 "#;
171
172 // Phase 4a: split effort into per-kind tables. Rename `sets` to
173 // `reps_sets` (that's what all existing rows are), stamp every existing
174 // exercise as effort_kind = 'reps', and create empty tables for the
175 // timed and distance kinds so their DB layers can drop in without another
176 // migration. The state machine, the log/history/diagnostic screens, and
177 // the estimator continue to work off reps_sets exactly like they did
178 // off sets; the only rewrite required here is s/sets/reps_sets/ in the
179 // SQL of a handful of methods (done in the same commit).
180 const MIGRATION_003: &str = r#"
181 ALTER TABLE exercises ADD COLUMN effort_kind TEXT NOT NULL
182 DEFAULT 'reps'
183 CHECK (effort_kind IN ('reps', 'timed', 'distance'));
184
185 ALTER TABLE sets RENAME TO reps_sets;
186
187 CREATE TABLE timed_sets (
188 id INTEGER PRIMARY KEY,
189 session_date TEXT NOT NULL,
190 exercise_id INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
191 set_index INTEGER NOT NULL,
192 duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
193 rpe INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
194 is_diagnostic INTEGER NOT NULL DEFAULT 0 CHECK (is_diagnostic IN (0, 1)),
195 UNIQUE (session_date, exercise_id, set_index)
196 );
197
198 CREATE INDEX idx_timed_sets_session_date ON timed_sets(session_date);
199 CREATE INDEX idx_timed_sets_exercise_date ON timed_sets(exercise_id, session_date);
200 CREATE INDEX idx_timed_sets_exercise_diagnostic
201 ON timed_sets(exercise_id, is_diagnostic);
202
203 CREATE TABLE distance_sets (
204 id INTEGER PRIMARY KEY,
205 session_date TEXT NOT NULL,
206 exercise_id INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
207 set_index INTEGER NOT NULL,
208 distance_meters REAL NOT NULL CHECK (distance_meters >= 0),
209 duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
210 rpe INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
211 is_diagnostic INTEGER NOT NULL DEFAULT 0 CHECK (is_diagnostic IN (0, 1)),
212 UNIQUE (session_date, exercise_id, set_index)
213 );
214
215 CREATE INDEX idx_distance_sets_session_date ON distance_sets(session_date);
216 CREATE INDEX idx_distance_sets_exercise_date
217 ON distance_sets(exercise_id, session_date);
218 CREATE INDEX idx_distance_sets_exercise_diagnostic
219 ON distance_sets(exercise_id, is_diagnostic);
220 "#;
221
222 #[cfg(test)]
223 mod tests {
224 use super::*;
225
226 #[test]
227 fn open_runs_migrations_and_sets_user_version() {
228 let db = Db::open_in_memory().unwrap();
229 let v: i32 = db
230 .conn
231 .query_row("PRAGMA user_version", [], |row| row.get(0))
232 .unwrap();
233 assert_eq!(v, MIGRATIONS.len() as i32);
234 }
235
236 #[test]
237 fn init_profile_writes_meta_row_and_is_idempotent() {
238 let db = Db::open_in_memory().unwrap();
239 db.init_profile("self", LoadUnit::Kg).unwrap();
240 db.init_profile("self", LoadUnit::Kg).unwrap();
241 assert_eq!(db.profile_name().unwrap().as_deref(), Some("self"));
242 assert_eq!(db.unit().unwrap(), Some(LoadUnit::Kg));
243 }
244
245 #[test]
246 fn rpe_check_constraint_rejects_out_of_range() {
247 let db = Db::open_in_memory().unwrap();
248 db.init_profile("self", LoadUnit::Kg).unwrap();
249 db.conn
250 .execute(
251 "INSERT INTO exercises (name, resistance_type, load_unit, increment) \
252 VALUES ('squat', 'freeweight', 'kg', 2.5)",
253 [],
254 )
255 .unwrap();
256 let err = db.conn.execute(
257 "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
258 VALUES ('2026-07-18', 1, 1, 100.0, 5, 6)",
259 [],
260 );
261 assert!(err.is_err(), "rpe=6 should violate CHECK constraint");
262 }
263
264 #[test]
265 fn unit_check_constraint_rejects_bogus_unit() {
266 let db = Db::open_in_memory().unwrap();
267 let err = db.conn.execute(
268 "INSERT INTO meta (id, profile_name, unit, created) \
269 VALUES (1, 'x', 'stone', '2026-07-18T00:00:00Z')",
270 [],
271 );
272 assert!(err.is_err(), "unit='stone' should violate CHECK constraint");
273 }
274
275 #[test]
276 fn sets_unique_constraint_on_session_exercise_set() {
277 let db = Db::open_in_memory().unwrap();
278 db.init_profile("self", LoadUnit::Kg).unwrap();
279 db.conn
280 .execute(
281 "INSERT INTO exercises (name, resistance_type, load_unit, increment) \
282 VALUES ('bench', 'freeweight', 'kg', 2.5)",
283 [],
284 )
285 .unwrap();
286 db.conn
287 .execute(
288 "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
289 VALUES ('2026-07-18', 1, 1, 80.0, 5, 3)",
290 [],
291 )
292 .unwrap();
293 let err = db.conn.execute(
294 "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
295 VALUES ('2026-07-18', 1, 1, 82.5, 5, 3)",
296 [],
297 );
298 assert!(err.is_err(), "duplicate (date, exercise, set_index) should fail");
299 }
300 }
301