koan_core/db/queries/
mod.rs

1mod albums;
2pub mod api_keys;
3pub mod app_passwords;
4mod artists;
5pub mod auth;
6pub mod batch;
7mod bookmarks;
8pub mod dsp;
9mod favourites;
10pub mod history;
11pub mod lyrics;
12mod play_queues;
13pub mod playback_state;
14pub mod playlists;
15mod ratings;
16mod scan_cache;
17pub mod scrobbling;
18mod search;
19pub mod shares;
20pub mod smart;
21pub(crate) mod sources;
22mod stats;
23pub mod tracks;
24pub mod uids;
25
26use std::path::PathBuf;
27
28// Re-exported so callers can `use queries::*`.
29pub use albums::*;
30pub use artists::*;
31pub use auth::LOCAL_USER;
32pub use batch::*;
33pub use bookmarks::*;
34pub use favourites::*;
35pub use history::*;
36pub use lyrics::*;
37pub use play_queues::*;
38pub use playback_state::*;
39pub use playlists::*;
40pub use ratings::*;
41pub use scan_cache::*;
42pub use search::*;
43pub use sources::sources_of_track;
44pub use stats::*;
45pub use tracks::*;
46pub use uids::*;
47
48/// A write transaction that holds the write lock from its first statement.
49///
50/// What `Connection::unchecked_transaction` gives is deferred: it takes the
51/// lock at its first write, and if another connection wrote since its first
52/// read, SQLite refuses that upgrade outright rather than waiting, since
53/// waiting could deadlock. A scan chunk or a sync page that met another writer
54/// therefore failed every statement in it. Immediate waits for the lock the way
55/// any other statement does.
56pub fn write_transaction(
57    conn: &rusqlite::Connection,
58) -> rusqlite::Result<rusqlite::Transaction<'_>> {
59    rusqlite::Transaction::new_unchecked(conn, rusqlite::TransactionBehavior::Immediate)
60}
61
62/// Run `f` as one unit: its writes land together or not at all, readers never
63/// see them half done, and the write lock is taken once rather than per
64/// statement.
65///
66/// Inside a caller's transaction it is a savepoint, so it nests. Outside one it
67/// begins `IMMEDIATE`: a deferred transaction that reads before it writes
68/// fails outright, without waiting, when another connection wrote in between.
69pub fn atomically<T, E: From<rusqlite::Error>>(
70    conn: &rusqlite::Connection,
71    f: impl FnOnce() -> Result<T, E>,
72) -> Result<T, E> {
73    let (begin, commit, rollback) = if conn.is_autocommit() {
74        ("BEGIN IMMEDIATE", "COMMIT", "ROLLBACK")
75    } else {
76        (
77            "SAVEPOINT atomically",
78            "RELEASE atomically",
79            "ROLLBACK TO atomically; RELEASE atomically",
80        )
81    };
82    conn.execute_batch(begin)?;
83    let result = f();
84    match &result {
85        Ok(_) => conn.execute_batch(commit)?,
86        // The work's own error is what the caller needs. A rollback that
87        // fails, as one does after something inside ended the transaction
88        // already, is logged rather than put in its place.
89        Err(_) => {
90            if let Err(e) = conn.execute_batch(rollback) {
91                log::warn!("rollback after a failed transaction: {e}");
92            }
93        }
94    }
95    result
96}
97
98/// A list as one JSON array, for `IN (SELECT value FROM json_each(?))`.
99///
100/// One placeholder per item stops at SQLite's limit of 32,766 parameters, and
101/// a queue or a playlist may be longer than that. One parameter is not limited.
102pub fn json_list<T: serde::Serialize>(items: &[T]) -> String {
103    serde_json::to_string(items).unwrap_or_else(|_| "[]".into())
104}
105
106/// The half-open range of paths under a folder, for `path >= .0 AND path < .1`.
107///
108/// A prefix match on an indexed column, rather than `LIKE 'folder/%'` — which
109/// SQLite answers by reading every row, because a pattern is opaque to an
110/// index until it has been evaluated. It also takes the pattern out of the
111/// path: `LIKE` reads `_` as "any character" and folds ASCII case, so
112/// `/Volumes/My_Music` would match `/Volumes/My Music` and `/volumes/my_music`
113/// alike.
114///
115/// The trailing separator is what keeps `/Volumes/Music` out of
116/// `/Volumes/Music Backup`; the upper bound is the highest code point, so
117/// every path under the folder sorts below it.
118pub fn folder_prefix_range(folder: &std::path::Path) -> (String, String) {
119    let prefix = format!(
120        "{}{}",
121        folder
122            .to_string_lossy()
123            .trim_end_matches(std::path::MAIN_SEPARATOR),
124        std::path::MAIN_SEPARATOR
125    );
126    let upper = format!("{prefix}\u{10FFFF}");
127    (prefix, upper)
128}
129
130// --- Row types ---
131
132#[derive(Debug, Clone)]
133pub struct ArtistRow {
134    pub id: i64,
135    pub name: String,
136    pub sort_name: Option<String>,
137    pub remote_id: Option<String>,
138    /// Albums credited to this artist, and tracks across them. Aggregated in
139    /// the same query as the row itself — a count per artist would be one
140    /// query per row in a list thousands long.
141    pub album_count: i64,
142    pub track_count: i64,
143}
144
145#[derive(Debug, Clone)]
146pub struct AlbumRow {
147    pub id: i64,
148    pub title: String,
149    pub artist_id: i64,
150    pub artist_name: String,
151    pub date: Option<String>,
152    pub total_discs: Option<i32>,
153    pub total_tracks: Option<i32>,
154    pub codec: Option<String>,
155    pub label: Option<String>,
156    pub remote_id: Option<String>,
157    /// When the album entered the library — the server's `created` for remote
158    /// albums, otherwise the time it was first indexed.
159    pub added_at: Option<String>,
160    /// How much of it can play here, when the listing was narrowed to this
161    /// device or ordered by it.
162    pub on_device: Option<OnDevice>,
163}
164
165#[derive(Debug, Clone)]
166pub struct TrackRow {
167    pub id: i64,
168    pub album_id: Option<i64>,
169    pub artist_id: Option<i64>,
170    pub artist_name: String,
171    pub album_artist_name: String,
172    pub album_title: String,
173    pub disc: Option<i32>,
174    pub track_number: Option<i32>,
175    pub title: String,
176    pub duration_ms: Option<i64>,
177    pub path: Option<String>,
178    pub codec: Option<String>,
179    pub sample_rate: Option<i32>,
180    pub bit_depth: Option<i32>,
181    pub channels: Option<i32>,
182    pub bitrate: Option<i32>,
183    pub genre: Option<String>,
184    pub source: String,
185    pub remote_id: Option<String>,
186    pub cached_path: Option<String>,
187}
188
189/// Where to get audio data for playback. Local always wins.
190#[derive(Debug, Clone)]
191pub enum PlaybackSource {
192    Local(PathBuf),
193    Cached(PathBuf),
194    Remote(String),
195}
196
197#[derive(Debug, Clone, Default)]
198pub struct LibraryStats {
199    pub total_tracks: i64,
200    pub local_tracks: i64,
201    pub remote_tracks: i64,
202    pub cached_tracks: i64,
203    pub total_albums: i64,
204    pub total_artists: i64,
205}
206
207/// Metadata for inserting/updating a track.
208#[derive(Debug, Clone, PartialEq)]
209pub struct TrackMeta {
210    pub title: String,
211    pub artist: String,
212    pub album_artist: Option<String>,
213    pub album: String,
214    pub date: Option<String>,
215    pub disc: Option<i32>,
216    pub track_number: Option<i32>,
217    pub genre: Option<String>,
218    pub label: Option<String>,
219    pub duration_ms: Option<i64>,
220    pub codec: Option<String>,
221    pub sample_rate: Option<i32>,
222    pub bit_depth: Option<i32>,
223    pub channels: Option<i32>,
224    pub bitrate: Option<i32>,
225    pub size_bytes: Option<i64>,
226    pub mtime: Option<i64>,
227    pub path: Option<String>,
228    pub source: String,
229    pub remote_id: Option<String>,
230    pub remote_url: Option<String>,
231    /// The server's ids for the album and its artist.
232    ///
233    /// Carried alongside the track's own, because the server keys stars,
234    /// shares and cover art off them — a library synced without these has
235    /// albums and artists it can name but cannot refer to.
236    pub album_remote_id: Option<String>,
237    pub artist_remote_id: Option<String>,
238    /// MusicBrainz recording and release ids — `MUSICBRAINZ_TRACKID` and
239    /// `MUSICBRAINZ_ALBUMID` in a file's tags, `musicBrainzId` on a server's
240    /// song and album. Together they name one track whatever each source calls
241    /// the album; the recording alone recurs on every compilation it is on.
242    pub mbid: Option<String>,
243    pub album_mbid: Option<String>,
244    /// When the album this track belongs to entered the library. Remote sync
245    /// supplies the server's `created`; anything else leaves it and the album
246    /// is stamped with the time it was first seen.
247    pub album_added_at: Option<String>,
248}
249
250/// Test helper: build a sample TrackMeta for use in tests across sub-modules.
251#[cfg(test)]
252pub fn sample_meta(title: &str, artist: &str, album: &str) -> TrackMeta {
253    TrackMeta {
254        title: title.into(),
255        artist: artist.into(),
256        album_artist: Some(artist.into()),
257        album: album.into(),
258        date: Some("2024".into()),
259        disc: Some(1),
260        track_number: Some(1),
261        genre: Some("Electronic".into()),
262        label: None,
263        duration_ms: Some(240_000),
264        codec: Some("FLAC".into()),
265        sample_rate: Some(44100),
266        bit_depth: Some(16),
267        channels: Some(2),
268        bitrate: Some(1000),
269        size_bytes: Some(30_000_000),
270        mtime: Some(1700000000),
271        path: Some(format!("/music/{}/{}.flac", album, title)),
272        source: "local".into(),
273        remote_id: None,
274        album_remote_id: None,
275        artist_remote_id: None,
276        mbid: None,
277        album_mbid: None,
278        remote_url: None,
279        album_added_at: None,
280    }
281}
282
283#[cfg(test)]
284mod write_transaction_tests {
285    use std::time::Duration;
286
287    fn open(path: &std::path::Path) -> rusqlite::Connection {
288        let conn = rusqlite::Connection::open(path).unwrap();
289        conn.pragma_update(None, "journal_mode", "wal").unwrap();
290        conn.busy_timeout(Duration::from_secs(5)).unwrap();
291        conn
292    }
293
294    /// A deferred transaction that read before another connection committed
295    /// cannot write at all, however long the busy timeout; `write_transaction`
296    /// waits its turn instead. This is what a sync page meeting another writer
297    /// ran into.
298    #[test]
299    fn a_write_transaction_waits_where_a_deferred_one_fails() {
300        let dir = tempfile::tempdir().unwrap();
301        let path = dir.path().join("t.db");
302        open(&path)
303            .execute_batch("CREATE TABLE t (x INTEGER)")
304            .unwrap();
305
306        let deferred = open(&path);
307        let tx = deferred.unchecked_transaction().unwrap();
308        let _: i64 = tx
309            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
310            .unwrap();
311        open(&path).execute("INSERT INTO t VALUES (1)", []).unwrap();
312        let err = tx.execute("INSERT INTO t VALUES (2)", []).unwrap_err();
313        assert!(
314            err.to_string().contains("locked") || err.to_string().contains("busy"),
315            "{err}"
316        );
317        drop(tx);
318
319        let path_for_writer = path.clone();
320        let writer = std::thread::spawn(move || {
321            let conn = open(&path_for_writer);
322            conn.execute_batch("BEGIN IMMEDIATE; INSERT INTO t VALUES (3)")
323                .unwrap();
324            std::thread::sleep(Duration::from_millis(200));
325            conn.execute_batch("COMMIT").unwrap();
326        });
327        std::thread::sleep(Duration::from_millis(50));
328        let conn = open(&path);
329        let tx = super::write_transaction(&conn).unwrap();
330        let _: i64 = tx
331            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
332            .unwrap();
333        tx.execute("INSERT INTO t VALUES (4)", []).unwrap();
334        tx.commit().unwrap();
335        writer.join().unwrap();
336
337        let n: i64 = conn
338            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
339            .unwrap();
340        assert_eq!(n, 3);
341    }
342}
koan_core/db/queries/mod.rs

koan_core/db/queries/
mod.rs
