koan_core/db/queries/
bookmarks.rs

1use rusqlite::{Connection, params};
2
3use super::auth::resolve_user;
4
5// A bookmark is a place to resume a track from: one per account and track,
6// with an optional note. Subsonic clients make them for audiobooks, podcasts
7// and long mixes. It names a row, and follows it through merges.
8
9#[derive(Debug, Clone, PartialEq, Eq)]
10pub struct Bookmark {
11    pub track_id: i64,
12    pub position_ms: i64,
13    pub comment: Option<String>,
14    /// Seconds since the epoch.
15    pub created_at: i64,
16    pub changed_at: i64,
17}
18
19fn now() -> i64 {
20    std::time::SystemTime::now()
21        .duration_since(std::time::UNIX_EPOCH)
22        .unwrap_or_default()
23        .as_secs() as i64
24}
25
26/// Save where `user` is in a track, replacing the bookmark already there.
27pub fn save_bookmark(
28    conn: &Connection,
29    user: i64,
30    track_id: i64,
31    position_ms: i64,
32    comment: Option<&str>,
33) -> rusqlite::Result<()> {
34    conn.prepare_cached(
35        "INSERT INTO bookmarks (user_id, track_id, position_ms, comment, created_at, changed_at)
36         VALUES (?1, ?2, ?3, ?4, ?5, ?5)
37         ON CONFLICT (user_id, track_id) DO UPDATE SET
38             position_ms = excluded.position_ms,
39             comment = excluded.comment,
40             changed_at = excluded.changed_at",
41    )?
42    .execute(params![
43        resolve_user(conn, user)?,
44        track_id,
45        position_ms.max(0),
46        comment,
47        now()
48    ])?;
49    Ok(())
50}
51
52/// Remove `user`'s bookmark in a track. Returns whether there was one.
53pub fn delete_bookmark(conn: &Connection, user: i64, track_id: i64) -> rusqlite::Result<bool> {
54    let removed = conn
55        .prepare_cached("DELETE FROM bookmarks WHERE user_id = ?1 AND track_id = ?2")?
56        .execute(params![resolve_user(conn, user)?, track_id])?;
57    Ok(removed > 0)
58}
59
60/// `user`'s bookmarks, most recently changed first.
61pub fn bookmarks(conn: &Connection, user: i64) -> rusqlite::Result<Vec<Bookmark>> {
62    let mut stmt = conn.prepare_cached(
63        "SELECT track_id, position_ms, comment, created_at, changed_at FROM bookmarks
64         WHERE user_id = ?1 ORDER BY changed_at DESC, track_id",
65    )?;
66    let rows = stmt.query_map([resolve_user(conn, user)?], |r| {
67        Ok(Bookmark {
68            track_id: r.get(0)?,
69            position_ms: r.get(1)?,
70            comment: r.get(2)?,
71            created_at: r.get(3)?,
72            changed_at: r.get(4)?,
73        })
74    })?;
75    rows.collect()
76}
77
78#[cfg(test)]
79mod tests {
80    use super::*;
81    use crate::db::queries::LOCAL_USER;
82
83    /// A library of two tracks, ids 1 and 2.
84    fn conn() -> Connection {
85        let conn = Connection::open_in_memory().unwrap();
86        conn.pragma_update(None, "foreign_keys", "on").unwrap();
87        crate::db::schema::create_tables(&conn).unwrap();
88        conn.execute_batch("INSERT INTO tracks (id, title) VALUES (1, 'Side A'), (2, 'Side B');")
89            .unwrap();
90        conn
91    }
92
93    #[test]
94    fn save_replaces_and_delete_removes() {
95        let conn = &conn();
96        save_bookmark(conn, LOCAL_USER, 1, 60_000, Some("chapter 2")).unwrap();
97        save_bookmark(conn, LOCAL_USER, 1, 90_000, None).unwrap();
98        let saved = bookmarks(conn, LOCAL_USER).unwrap();
99        assert_eq!(saved.len(), 1);
100        assert_eq!(saved[0].position_ms, 90_000);
101        assert_eq!(saved[0].comment, None);
102        assert!(delete_bookmark(conn, LOCAL_USER, 1).unwrap());
103        assert!(!delete_bookmark(conn, LOCAL_USER, 1).unwrap());
104        assert!(bookmarks(conn, LOCAL_USER).unwrap().is_empty());
105    }
106
107    #[test]
108    fn a_bookmark_needs_its_track() {
109        let conn = &conn();
110        assert!(save_bookmark(conn, LOCAL_USER, 99, 0, None).is_err());
111        save_bookmark(conn, LOCAL_USER, 2, 1_000, None).unwrap();
112        conn.execute("DELETE FROM tracks WHERE id = 2", []).unwrap();
113        assert!(bookmarks(conn, LOCAL_USER).unwrap().is_empty());
114    }
115}
koan_core/db/queries/bookmarks.rs

koan_core/db/queries/
bookmarks.rs
