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    /// Each artist the credits name, where the source names them one by one.
249    pub credits: Credits,
250}
251
252/// The artists a track's and its album's credits name, one by one: `ARTISTS`
253/// and `ALBUMARTISTS` with their MusicBrainz ids in a file, OpenSubsonic's
254/// `artists` and `albumArtists` from a server. `artist` and `album_artist`
255/// stay the credit as it reads ("A × B"); these are what lists the record
256/// under each artist. Empty when the source names only the credit.
257#[derive(Debug, Clone, Default, PartialEq, serde::Serialize, serde::Deserialize)]
258pub struct Credits {
259    #[serde(default, skip_serializing_if = "Vec::is_empty")]
260    pub artists: Vec<Credit>,
261    #[serde(default, skip_serializing_if = "Vec::is_empty")]
262    pub album_artists: Vec<Credit>,
263}
264
265#[derive(Debug, Clone, Default, PartialEq, serde::Serialize, serde::Deserialize)]
266pub struct Credit {
267    pub name: String,
268    #[serde(default, skip_serializing_if = "Option::is_none")]
269    pub mbid: Option<String>,
270    /// The server's id for the artist.
271    #[serde(default, skip_serializing_if = "Option::is_none")]
272    pub remote_id: Option<String>,
273}
274
275impl Credits {
276    pub fn is_empty(&self) -> bool {
277        self.artists.is_empty() && self.album_artists.is_empty()
278    }
279
280    /// Names paired with ids by position, as taggers write them; ids that do
281    /// not line up with the names are dropped rather than guessed at.
282    pub fn pair(names: Vec<String>, mbids: Vec<String>) -> Vec<Credit> {
283        let aligned = names.len() == mbids.len();
284        names
285            .into_iter()
286            .enumerate()
287            .map(|(i, name)| Credit {
288                name,
289                mbid: aligned.then(|| mbids[i].clone()),
290                remote_id: None,
291            })
292            .collect()
293    }
294}
295
296impl TrackMeta {
297    /// What this source says, labelled for an info view, empty values left
298    /// out. The stream URL carries the account's credentials, so it is left out.
299    pub fn info_fields(self) -> Vec<(&'static str, String)> {
300        let text = |v: Option<String>| v.filter(|v| !v.is_empty());
301        let number = |v: Option<i32>| v.map(|v| v.to_string());
302        let length = self.duration_ms.map(|ms| {
303            let s = ms / 1000;
304            format!("{}:{:02}", s / 60, s % 60)
305        });
306        let modified = self
307            .mtime
308            .and_then(|t| chrono::DateTime::from_timestamp(t, 0))
309            .map(|t| t.to_rfc3339());
310        [
311            ("Title", Some(self.title)),
312            ("Artist", Some(self.artist)),
313            ("Album artist", text(self.album_artist)),
314            ("Album", Some(self.album)),
315            ("Date", text(self.date)),
316            ("Disc", number(self.disc)),
317            ("Track", number(self.track_number)),
318            ("Genre", text(self.genre)),
319            ("Label", text(self.label)),
320            ("Length", length),
321            ("Codec", text(self.codec)),
322            ("Sample rate", self.sample_rate.map(|r| format!("{r} Hz"))),
323            ("Bit depth", self.bit_depth.map(|b| format!("{b} bit"))),
324            ("Channels", number(self.channels)),
325            ("Bitrate", self.bitrate.map(|b| format!("{b} kbps"))),
326            ("Size", self.size_bytes.map(|b| format!("{b} bytes"))),
327            ("Modified", modified),
328            ("Path", text(self.path)),
329            ("Server id", text(self.remote_id)),
330            ("Server album id", text(self.album_remote_id)),
331            ("Server artist id", text(self.artist_remote_id)),
332            ("MusicBrainz recording", text(self.mbid)),
333            ("MusicBrainz release", text(self.album_mbid)),
334            ("Album added", text(self.album_added_at)),
335        ]
336        .into_iter()
337        .filter_map(|(name, value)| Some((name, value?)))
338        .collect()
339    }
340}
341
342/// Test helper: build a sample TrackMeta for use in tests across sub-modules.
343#[cfg(test)]
344pub fn sample_meta(title: &str, artist: &str, album: &str) -> TrackMeta {
345    TrackMeta {
346        title: title.into(),
347        artist: artist.into(),
348        album_artist: Some(artist.into()),
349        album: album.into(),
350        date: Some("2024".into()),
351        disc: Some(1),
352        track_number: Some(1),
353        genre: Some("Electronic".into()),
354        label: None,
355        duration_ms: Some(240_000),
356        codec: Some("FLAC".into()),
357        sample_rate: Some(44100),
358        bit_depth: Some(16),
359        channels: Some(2),
360        bitrate: Some(1000),
361        size_bytes: Some(30_000_000),
362        mtime: Some(1700000000),
363        path: Some(format!("/music/{}/{}.flac", album, title)),
364        source: "local".into(),
365        remote_id: None,
366        album_remote_id: None,
367        artist_remote_id: None,
368        mbid: None,
369        album_mbid: None,
370        remote_url: None,
371        album_added_at: None,
372        credits: Default::default(),
373    }
374}
375
376#[cfg(test)]
377mod write_transaction_tests {
378    use std::time::Duration;
379
380    fn open(path: &std::path::Path) -> rusqlite::Connection {
381        let conn = rusqlite::Connection::open(path).unwrap();
382        conn.pragma_update(None, "journal_mode", "wal").unwrap();
383        conn.busy_timeout(Duration::from_secs(5)).unwrap();
384        conn
385    }
386
387    /// A deferred transaction that read before another connection committed
388    /// cannot write at all, however long the busy timeout; `write_transaction`
389    /// waits its turn instead. This is what a sync page meeting another writer
390    /// ran into.
391    #[test]
392    fn a_write_transaction_waits_where_a_deferred_one_fails() {
393        let dir = tempfile::tempdir().unwrap();
394        let path = dir.path().join("t.db");
395        open(&path)
396            .execute_batch("CREATE TABLE t (x INTEGER)")
397            .unwrap();
398
399        let deferred = open(&path);
400        let tx = deferred.unchecked_transaction().unwrap();
401        let _: i64 = tx
402            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
403            .unwrap();
404        open(&path).execute("INSERT INTO t VALUES (1)", []).unwrap();
405        let err = tx.execute("INSERT INTO t VALUES (2)", []).unwrap_err();
406        assert!(
407            err.to_string().contains("locked") || err.to_string().contains("busy"),
408            "{err}"
409        );
410        drop(tx);
411
412        let path_for_writer = path.clone();
413        let writer = std::thread::spawn(move || {
414            let conn = open(&path_for_writer);
415            conn.execute_batch("BEGIN IMMEDIATE; INSERT INTO t VALUES (3)")
416                .unwrap();
417            std::thread::sleep(Duration::from_millis(200));
418            conn.execute_batch("COMMIT").unwrap();
419        });
420        std::thread::sleep(Duration::from_millis(50));
421        let conn = open(&path);
422        let tx = super::write_transaction(&conn).unwrap();
423        let _: i64 = tx
424            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
425            .unwrap();
426        tx.execute("INSERT INTO t VALUES (4)", []).unwrap();
427        tx.commit().unwrap();
428        writer.join().unwrap();
429
430        let n: i64 = conn
431            .query_row("SELECT COUNT(*) FROM t", [], |r| r.get(0))
432            .unwrap();
433        assert_eq!(n, 3);
434    }
435}
koan_core/db/queries/mod.rs

koan_core/db/queries/
mod.rs
