trane/
practice_stats.rs

1//! Defines how the results of exercise trials are stored for used during scheduling.
2//!
3//! Currently, only the score and the timestamp are stored. From the results and timestamps of
4//! previous trials, a score for the exercise (in the range 0.0 to 5.0) is calculated. See the
5//! documentation in [exercise_scorer](crate::exercise_scorer) for more details.
6
7use anyhow::{Context, Ok, Result};
8use r2d2::Pool;
9use r2d2_sqlite::SqliteConnectionManager;
10use rusqlite::params;
11use rusqlite_migration::{M, Migrations};
12use ustr::Ustr;
13
14use crate::{
15    data::{ExerciseTrial, MasteryScore},
16    error::PracticeStatsError,
17    utils,
18};
19
20/// Contains functions to retrieve and record the scores from each exercise trial.
21pub trait PracticeStats {
22    /// Retrieves the last `num_scores` scores of a particular exercise. The scores are returned in
23    /// descending order according to the timestamp.
24    fn get_scores(
25        &self,
26        exercise_id: Ustr,
27        num_scores: usize,
28    ) -> Result<Vec<ExerciseTrial>, PracticeStatsError>;
29
30    /// Records the score assigned to the exercise in a particular trial. Therefore, the score is a
31    /// value of the `MasteryScore` enum instead of a float. Only units of type `UnitType::Exercise`
32    /// should have scores recorded. However, the enforcement of this requirement is left to the
33    /// caller.
34    fn record_exercise_score(
35        &mut self,
36        exercise_id: Ustr,
37        score: MasteryScore,
38        timestamp: i64,
39    ) -> Result<(), PracticeStatsError>;
40
41    /// Deletes all the exercise trials except for the last `num_scores` with the aim of keeping the
42    /// storage size under check.
43    fn trim_scores(&mut self, num_scores: usize) -> Result<(), PracticeStatsError>;
44
45    /// Removes all the scores from the units that match the given prefix.
46    fn remove_scores_with_prefix(&mut self, prefix: &str) -> Result<(), PracticeStatsError>;
47}
48
49/// An implementation of [`PracticeStats`] backed by `SQLite`.
50pub struct LocalPracticeStats {
51    /// A pool of connections to the database.
52    pool: Pool<SqliteConnectionManager>,
53}
54
55impl LocalPracticeStats {
56    /// Returns all the migrations needed to set up the database.
57    fn migrations() -> Migrations<'static> {
58        Migrations::new(vec![
59            // Create a table with a mapping of unit IDs to a unique integer ID. The purpose of this
60            // table is to save space when storing the exercise trials by not having to store the
61            // entire ID of the unit.
62            M::up("CREATE TABLE uids(unit_uid INTEGER PRIMARY KEY, unit_id TEXT NOT NULL UNIQUE);")
63                .down("DROP TABLE uids;"),
64            // Create a table storing all the exercise trials.
65            M::up(
66                "CREATE TABLE practice_stats(
67                id INTEGER PRIMARY KEY,
68                unit_uid INTEGER NOT NULL REFERENCES uids(unit_uid),
69                score REAL, timestamp INTEGER);",
70            )
71            .down("DROP TABLE practice_stats"),
72            // Create an index of `unit_ids`.
73            M::up("CREATE INDEX unit_ids ON uids (unit_id);").down("DROP INDEX unit_ids"),
74            //@<lp-example-6
75            // Originally the trials were indexed solely by `unit_uid`. This index was replaced so
76            // this migration is immediately canceled by the one right below. They cannot be removed
77            // from the migration list without breaking databases created in an earlier version than
78            // the one which removes them, so they are kept here for now.
79            M::up("CREATE INDEX unit_scores ON practice_stats (unit_uid);")
80                .down("DROP INDEX unit_scores"),
81            M::up("DROP INDEX unit_scores")
82                .down("CREATE INDEX unit_scores ON practice_stats (unit_uid);"),
83            //>@lp-example-6
84            // Create a combined index of `unit_uid` and `timestamp` for fast trial retrieval.
85            M::up("CREATE INDEX trials ON practice_stats (unit_uid, timestamp);")
86                .down("DROP INDEX trials"),
87        ])
88    }
89
90    /// Initializes the database by running the migrations. If the migrations have been applied
91    /// already, they will have no effect on the database.
92    fn init(&mut self) -> Result<()> {
93        let mut connection = self.pool.get()?;
94        let migrations = Self::migrations();
95        migrations
96            .to_latest(&mut connection)
97            .context("failed to initialize practice stats DB")
98    }
99
100    /// Creates a connection pool and initializes the database.
101    fn new(connection_manager: SqliteConnectionManager) -> Result<LocalPracticeStats> {
102        let pool = utils::new_connection_pool(connection_manager)?;
103        let mut stats = LocalPracticeStats { pool };
104        stats.init()?;
105        Ok(stats)
106    }
107
108    /// A constructor taking the path to a database file.
109    pub fn new_from_disk(db_path: &str) -> Result<LocalPracticeStats> {
110        Self::new(utils::new_connection_manager(db_path))
111    }
112
113    /// Helper function to retrieve scores from the database.
114    fn get_scores_helper(
115        &self,
116        exercise_id: Ustr,
117        num_scores: usize,
118    ) -> Result<Vec<ExerciseTrial>> {
119        // Retrieve the exercise trials from the database.
120        let connection = self.pool.get()?;
121        let mut stmt = connection.prepare_cached(
122            "SELECT score, timestamp from practice_stats WHERE unit_uid = (
123                SELECT unit_uid FROM uids WHERE unit_id = $1)
124                ORDER BY timestamp DESC LIMIT ?2;",
125        )?;
126
127        // Convert the results into a vector of `ExerciseTrial` objects.
128        #[allow(clippy::let_and_return)]
129        let rows = stmt
130            .query_map(params![exercise_id.as_str(), num_scores], |row| {
131                let score = row.get(0)?;
132                let timestamp = row.get(1)?;
133                rusqlite::Result::Ok(ExerciseTrial { score, timestamp })
134            })?
135            .map(|r| r.context("failed to retrieve scores from practice stats DB"))
136            .collect::<Result<Vec<ExerciseTrial>, _>>()?;
137        Ok(rows)
138    }
139
140    /// Helper function to record a score to the database.
141    fn record_exercise_score_helper(
142        &mut self,
143        exercise_id: Ustr,
144        score: &MasteryScore,
145        timestamp: i64,
146    ) -> Result<()> {
147        // Update the mapping of unit ID to unique integer ID.
148        let connection = self.pool.get()?;
149        let mut uid_stmt =
150            connection.prepare_cached("INSERT OR IGNORE INTO uids(unit_id) VALUES ($1);")?;
151        uid_stmt.execute(params![exercise_id.as_str()])?;
152
153        // Insert the exercise trial into the database.
154        let mut stmt = connection.prepare_cached(
155            "INSERT INTO practice_stats (unit_uid, score, timestamp) VALUES (
156                (SELECT unit_uid FROM uids WHERE unit_id = $1), $2, $3);",
157        )?;
158        stmt.execute(params![
159            exercise_id.as_str(),
160            score.float_score(),
161            timestamp
162        ])?;
163        Ok(())
164    }
165
166    /// Helper function to trim the number of scores for each exercise.
167    fn trim_scores_helper(&mut self, num_scores: usize) -> Result<()> {
168        // Get all the UIDs from the database.
169        let connection = self.pool.get()?;
170        let mut uid_stmt = connection.prepare_cached("SELECT unit_uid from uids")?;
171        let uids = uid_stmt
172            .query_map([], |row| row.get(0))?
173            .map(|r| r.context("failed to retrieve UIDs from practice stats DB"))
174            .collect::<Result<Vec<i64>, _>>()?;
175
176        // Delete the oldest trials for each UID but keep the most recent `num_scores` trials.
177        for uid in uids {
178            let mut stmt = connection.prepare_cached(
179                "DELETE FROM practice_stats WHERE unit_uid = $1 AND timestamp NOT IN (
180                    SELECT timestamp FROM practice_stats WHERE unit_uid = $1
181                    ORDER BY timestamp DESC LIMIT ?2);",
182            )?;
183            let _ = stmt.execute(params![uid, num_scores])?;
184        }
185
186        // Call the `VACUUM` command to reclaim the space freed by the deleted trials.
187        connection.execute_batch("VACUUM;")?;
188        Ok(())
189    }
190
191    /// Helper function to remove all the scores from units that match the given prefix.
192    fn remove_scores_with_prefix_helper(&mut self, prefix: &str) -> Result<()> {
193        // Get all the UIDs for the units that match the prefix.
194        let connection = self.pool.get()?;
195        let mut uid_stmt =
196            connection.prepare_cached("SELECT unit_uid FROM uids WHERE unit_id LIKE $1;")?;
197        let uids = uid_stmt
198            .query_map(params![format!("{}%", prefix)], |row| row.get(0))?
199            .map(|r| r.context("failed to retrieve UIDs from practice stats DB"))
200            .collect::<Result<Vec<i64>, _>>()?;
201
202        // Delete all the trials for those units.
203        for uid in uids {
204            let mut stmt =
205                connection.prepare_cached("DELETE FROM practice_stats WHERE unit_uid = $1;")?;
206            let _ = stmt.execute(params![uid])?;
207        }
208
209        // Call the `VACUUM` command to reclaim the space freed by the deleted trials.
210        connection.execute_batch("VACUUM;")?;
211        Ok(())
212    }
213}
214
215impl PracticeStats for LocalPracticeStats {
216    fn get_scores(
217        &self,
218        exercise_id: Ustr,
219        num_scores: usize,
220    ) -> Result<Vec<ExerciseTrial>, PracticeStatsError> {
221        self.get_scores_helper(exercise_id, num_scores)
222            .map_err(|e| PracticeStatsError::GetScores(exercise_id, e))
223    }
224
225    fn record_exercise_score(
226        &mut self,
227        exercise_id: Ustr,
228        score: MasteryScore,
229        timestamp: i64,
230    ) -> Result<(), PracticeStatsError> {
231        self.record_exercise_score_helper(exercise_id, &score, timestamp)
232            .map_err(|e| PracticeStatsError::RecordScore(exercise_id, e))
233    }
234
235    fn trim_scores(&mut self, num_scores: usize) -> Result<(), PracticeStatsError> {
236        self.trim_scores_helper(num_scores)
237            .map_err(PracticeStatsError::TrimScores)
238    }
239
240    fn remove_scores_with_prefix(&mut self, prefix: &str) -> Result<(), PracticeStatsError> {
241        self.remove_scores_with_prefix_helper(prefix)
242            .map_err(|e| PracticeStatsError::RemovePrefix(prefix.to_string(), e))
243    }
244}
245
246#[cfg(test)]
247#[cfg_attr(coverage, coverage(off))]
248mod test {
249    use anyhow::{Ok, Result};
250    use r2d2_sqlite::SqliteConnectionManager;
251    use ustr::Ustr;
252
253    use crate::{
254        data::{ExerciseTrial, MasteryScore},
255        practice_stats::{LocalPracticeStats, PracticeStats},
256    };
257
258    fn new_tests_stats() -> Result<Box<dyn PracticeStats>> {
259        let connection_manager = SqliteConnectionManager::memory();
260        let practice_stats = LocalPracticeStats::new(connection_manager)?;
261        Ok(Box::new(practice_stats))
262    }
263
264    fn assert_scores(expected: &[f32], actual: &[ExerciseTrial]) {
265        let only_scores: Vec<f32> = actual.iter().map(|t| t.score).collect();
266        assert_eq!(expected, only_scores);
267        let timestamp_sorted = actual
268            .iter()
269            .enumerate()
270            .map(|(i, _)| {
271                if i == 0 {
272                    return true;
273                }
274                actual[i - 1].timestamp >= actual[i].timestamp
275            })
276            .all(|b| b);
277        assert!(timestamp_sorted);
278    }
279
280    /// Verifies setting and retrieving a single score for an exercise.
281    #[test]
282    fn basic() -> Result<()> {
283        let mut stats = new_tests_stats()?;
284        let exercise_id = Ustr::from("ex_123");
285        stats.record_exercise_score(exercise_id, MasteryScore::Five, 1)?;
286        let scores = stats.get_scores(exercise_id, 1)?;
287        assert_scores(&[5.0], &scores);
288        Ok(())
289    }
290
291    /// Verifies setting and retrieving multiple scores for an exercise.
292    #[test]
293    fn multiple_records() -> Result<()> {
294        let mut stats = new_tests_stats()?;
295        let exercise_id = Ustr::from("ex_123");
296        stats.record_exercise_score(exercise_id, MasteryScore::Three, 1)?;
297        stats.record_exercise_score(exercise_id, MasteryScore::Four, 2)?;
298        stats.record_exercise_score(exercise_id, MasteryScore::Five, 3)?;
299
300        let one_score = stats.get_scores(exercise_id, 1)?;
301        assert_scores(&[5.0], &one_score);
302
303        let three_scores = stats.get_scores(exercise_id, 3)?;
304        assert_scores(&[5.0, 4.0, 3.0], &three_scores);
305
306        let more_scores = stats.get_scores(exercise_id, 10)?;
307        assert_scores(&[5.0, 4.0, 3.0], &more_scores);
308        Ok(())
309    }
310
311    /// Verifies retrieving an empty list of scores for an exercise with no previous scores.
312    #[test]
313    fn no_records() -> Result<()> {
314        let stats = new_tests_stats()?;
315        let scores = stats.get_scores(Ustr::from("ex_123"), 10)?;
316        assert_scores(&[], &scores);
317        Ok(())
318    }
319
320    /// Verifies trimming all but the most recent scores.
321    #[test]
322    fn trim_scores_some_scores_removed() -> Result<()> {
323        let mut stats = new_tests_stats()?;
324        let exercise1_id = Ustr::from("exercise1");
325        stats.record_exercise_score(exercise1_id, MasteryScore::Three, 1)?;
326        stats.record_exercise_score(exercise1_id, MasteryScore::Four, 2)?;
327        stats.record_exercise_score(exercise1_id, MasteryScore::Five, 3)?;
328
329        let exercise2_id = Ustr::from("exercise2");
330        stats.record_exercise_score(exercise2_id, MasteryScore::One, 1)?;
331        stats.record_exercise_score(exercise2_id, MasteryScore::One, 2)?;
332        stats.record_exercise_score(exercise2_id, MasteryScore::Three, 3)?;
333
334        stats.trim_scores(2)?;
335
336        let scores = stats.get_scores(exercise1_id, 10)?;
337        assert_scores(&[5.0, 4.0], &scores);
338        let scores = stats.get_scores(exercise2_id, 10)?;
339        assert_scores(&[3.0, 1.0], &scores);
340        Ok(())
341    }
342
343    /// Verifies trimming no scores when the number of scores is less than the limit.
344    #[test]
345    fn trim_scores_no_scores_removed() -> Result<()> {
346        let mut stats = new_tests_stats()?;
347        let exercise1_id = Ustr::from("exercise1");
348        stats.record_exercise_score(exercise1_id, MasteryScore::Three, 1)?;
349        stats.record_exercise_score(exercise1_id, MasteryScore::Four, 2)?;
350        stats.record_exercise_score(exercise1_id, MasteryScore::Five, 3)?;
351
352        let exercise2_id = Ustr::from("exercise2");
353        stats.record_exercise_score(exercise2_id, MasteryScore::One, 1)?;
354        stats.record_exercise_score(exercise2_id, MasteryScore::One, 2)?;
355        stats.record_exercise_score(exercise2_id, MasteryScore::Three, 3)?;
356
357        stats.trim_scores(10)?;
358
359        let scores = stats.get_scores(exercise1_id, 10)?;
360        assert_scores(&[5.0, 4.0, 3.0], &scores);
361        let scores = stats.get_scores(exercise2_id, 10)?;
362        assert_scores(&[3.0, 1.0, 1.0], &scores);
363        Ok(())
364    }
365
366    /// Verifies removing the trials for units that match the given prefix.
367    #[test]
368    fn remove_scores_with_prefix() -> Result<()> {
369        let mut stats = new_tests_stats()?;
370        let exercise1_id = Ustr::from("exercise1");
371        stats.record_exercise_score(exercise1_id, MasteryScore::Three, 1)?;
372        stats.record_exercise_score(exercise1_id, MasteryScore::Four, 2)?;
373        stats.record_exercise_score(exercise1_id, MasteryScore::Five, 3)?;
374
375        let exercise2_id = Ustr::from("exercise2");
376        stats.record_exercise_score(exercise2_id, MasteryScore::One, 1)?;
377        stats.record_exercise_score(exercise2_id, MasteryScore::One, 2)?;
378        stats.record_exercise_score(exercise2_id, MasteryScore::Three, 3)?;
379
380        let exercise3_id = Ustr::from("exercise3");
381        stats.record_exercise_score(exercise3_id, MasteryScore::One, 1)?;
382        stats.record_exercise_score(exercise3_id, MasteryScore::One, 2)?;
383        stats.record_exercise_score(exercise3_id, MasteryScore::Three, 3)?;
384
385        // Remove the prefix "exercise1".
386        stats.remove_scores_with_prefix("exercise1")?;
387        let scores = stats.get_scores(exercise1_id, 10)?;
388        assert_scores(&[], &scores);
389        let scores = stats.get_scores(exercise2_id, 10)?;
390        assert_scores(&[3.0, 1.0, 1.0], &scores);
391        let scores = stats.get_scores(exercise3_id, 10)?;
392        assert_scores(&[3.0, 1.0, 1.0], &scores);
393
394        // Remove the prefix "exercise". All the scores should be removed.
395        stats.remove_scores_with_prefix("exercise")?;
396        let scores = stats.get_scores(exercise1_id, 10)?;
397        assert_scores(&[], &scores);
398        let scores = stats.get_scores(exercise2_id, 10)?;
399        assert_scores(&[], &scores);
400        let scores = stats.get_scores(exercise3_id, 10)?;
401        assert_scores(&[], &scores);
402
403        Ok(())
404    }
405}
trane/practice_stats.rs

trane/
practice_stats.rs
