koan_core/db/queries/
search.rs

1use std::collections::HashMap;
2use std::sync::Arc;
3
4use rusqlite::{Connection, params};
5
6use crate::db::connection::DbError;
7
8use super::TrackRow;
9
10/// Sanitize a user query for FTS5 MATCH: escapes double-quotes and wraps in
11/// a quoted phrase so FTS5 special characters are treated as literals.
12pub(crate) fn sanitize_fts_query(query: &str) -> String {
13    let trimmed = query.trim();
14    if trimmed.is_empty() {
15        return String::new();
16    }
17    // Escape double quotes for FTS5 literal matching
18    let escaped = trimmed.replace('"', "\"\"");
19    format!("\"{}\"*", escaped)
20}
21
22/// Full-text search across track title, artist, album, genre.
23pub fn search_tracks(conn: &Connection, query: &str) -> Result<Vec<TrackRow>, DbError> {
24    search_tracks_paged(conn, query, 100, 0)
25}
26
27/// Full-text search with configurable limit and offset for pagination.
28pub fn search_tracks_paged(
29    conn: &Connection,
30    query: &str,
31    limit: u32,
32    offset: u32,
33) -> Result<Vec<TrackRow>, DbError> {
34    // FTS5 query — sanitize input and append * for prefix matching.
35    let fts_query = sanitize_fts_query(query);
36
37    let mut stmt = conn.prepare(
38        "SELECT t.id, t.album_id, t.artist_id, a.name, aa.name, al.title,
39                t.disc, t.track_number, t.title, t.duration_ms, t.path,
40                t.codec, t.sample_rate, t.bit_depth, t.channels, t.bitrate,
41                t.genre, t.source, t.remote_id, t.cached_path
42         FROM tracks_fts f
43         JOIN tracks t ON t.id = f.rowid
44         LEFT JOIN artists a ON t.artist_id = a.id
45         LEFT JOIN albums al ON t.album_id = al.id
46         LEFT JOIN artists aa ON al.artist_id = aa.id
47         WHERE tracks_fts MATCH ?1
48         ORDER BY a.name COLLATE LIBRARY, al.date, al.title COLLATE LIBRARY, t.disc, t.track_number
49         LIMIT ?2 OFFSET ?3",
50    )?;
51
52    let rows = stmt
53        .query_map(params![fts_query, limit, offset], super::row_to_track_row)?
54        .collect::<Result<Vec<_>, _>>()?;
55
56    Ok(rows)
57}
58
59/// What a fuzzy match runs over.
60#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash)]
61pub enum CorpusKind {
62    Track,
63    Album,
64    Artist,
65}
66
67/// Each row's id, and the text a fuzzy match reads.
68pub type Corpus = Arc<Vec<(i64, String)>>;
69
70/// Every row of `kind` as its id and the text a fuzzy match reads: "artist —
71/// album — title" for a track, "artist — album" for an album, the name for an
72/// album artist.
73///
74/// Unordered, and only the matched columns: the matcher ranks what it finds,
75/// so sorting on the LIBRARY collation — a Rust callback per comparison, the
76/// most expensive part of reading the whole library — bought nothing.
77pub fn fuzzy_corpus(conn: &Connection, kind: CorpusKind) -> Result<Vec<(i64, String)>, DbError> {
78    let mut stmt = conn.prepare_cached(match kind {
79        CorpusKind::Track => {
80            "SELECT t.id, COALESCE(a.name, '') || ' — ' || COALESCE(al.title, '') || ' — ' || t.title
81             FROM tracks t
82             LEFT JOIN artists a ON t.artist_id = a.id
83             LEFT JOIN albums al ON t.album_id = al.id"
84        }
85        CorpusKind::Album => {
86            "SELECT al.id, COALESCE(a.name, '') || ' — ' || al.title
87             FROM albums al
88             LEFT JOIN artists a ON al.artist_id = a.id"
89        }
90        CorpusKind::Artist => {
91            "SELECT a.id, a.name FROM artists a
92             WHERE EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = a.id)"
93        }
94    })?;
95    let rows = stmt.query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?;
96    rows.collect::<Result<Vec<_>, _>>().map_err(Into::into)
97}
98
99/// How closely `column` matches a search, as SQL: 0 when it is the query, 1
100/// when it starts with it, 2 when a later word does, 3 otherwise. Takes the
101/// three parameters [`match_rank_binds`] gives, in order.
102pub(crate) fn match_rank(column: &str) -> String {
103    format!(
104        "CASE WHEN {column} LIKE ? ESCAPE '\\' THEN 0
105              WHEN {column} LIKE ? ESCAPE '\\' THEN 1
106              WHEN {column} LIKE ? ESCAPE '\\' THEN 2 ELSE 3 END"
107    )
108}
109
110pub(crate) fn match_rank_binds(query: &str) -> [String; 3] {
111    let q = super::artists::escape_like(query.trim());
112    [q.clone(), format!("{q}%"), format!("% {q}%")]
113}
114
115/// How far below the best fuzzy score a match may fall and still be listed.
116///
117/// Nucleo matches any run of the query's letters in order, however scattered:
118/// "Black Country, New Road" holds k, r, e and w. Scores are dominated by how
119/// contiguous a match is and whether it starts words, so a fraction of the
120/// best keeps the near misses of a typo and drops the scattered ones.
121const FUZZY_FLOOR: f64 = 0.6;
122
123/// At most how many fuzzy matches a search lists: plenty for a typo, and a
124/// bound on what the listing joins and orders.
125const FUZZY_LIMIT: usize = 200;
126
127/// The rows of `kind` closest to `query` by fuzzy match, best first, at most
128/// [`FUZZY_LIMIT`], those scoring under [`FUZZY_FLOOR`] of the best left out.
129///
130/// What a search falls back on when nothing holds the query as typed, so a
131/// typo still finds what was meant. The query is matched literally: nucleo's
132/// operators (`!`, `^`, `$`, `'`) are characters someone typed, not syntax.
133///
134/// The corpus is kept until the database changes, and the last answer for
135/// each kind with it: a search page lists and counts each kind, and both
136/// fall back alike.
137pub fn fuzzy_ids(conn: &Connection, kind: CorpusKind, query: &str) -> Result<Vec<i64>, DbError> {
138    use nucleo::pattern::{AtomKind, CaseMatching, Normalization, Pattern};
139    use nucleo::{Config, Matcher, Utf32Str};
140
141    let stamp = Stamp::of(conn)?;
142    let corpus = {
143        let slots = FALLBACK.lock();
144        match slots.get(&kind) {
145            Some(slot) if slot.stamp == stamp => {
146                if let Some((q, ids)) = &slot.last
147                    && q == query
148                {
149                    return Ok(ids.clone());
150                }
151                Some(Arc::clone(&slot.corpus))
152            }
153            _ => None,
154        }
155    };
156    let corpus = match corpus {
157        Some(corpus) => corpus,
158        None => Arc::new(fuzzy_corpus(conn, kind)?),
159    };
160
161    // Case-insensitive: a phone keyboard capitalises the first letter.
162    let pattern = Pattern::new(
163        query,
164        CaseMatching::Ignore,
165        Normalization::Smart,
166        AtomKind::Fuzzy,
167    );
168    let mut matcher = Matcher::new(Config::DEFAULT);
169    let mut buf = Vec::new();
170    let mut scored: Vec<(u32, usize, i64)> = corpus
171        .iter()
172        .filter_map(|(id, text)| {
173            pattern
174                .score(Utf32Str::new(text, &mut buf), &mut matcher)
175                .map(|score| (score, text.len(), *id))
176        })
177        .collect();
178    // Ties to the shorter text: the closer of two equal matches.
179    scored.sort_by_key(|&(score, len, id)| (std::cmp::Reverse(score), len, id));
180    let ids: Vec<i64> = match scored.first() {
181        Some(&(best, ..)) => {
182            let floor = (best as f64 * FUZZY_FLOOR) as u32;
183            scored
184                .into_iter()
185                .take_while(|&(score, ..)| score >= floor)
186                .take(FUZZY_LIMIT)
187                .map(|(.., id)| id)
188                .collect()
189        }
190        None => Vec::new(),
191    };
192    FALLBACK.lock().insert(
193        kind,
194        Fallback {
195            stamp,
196            corpus,
197            last: Some((query.to_owned(), ids.clone())),
198        },
199    );
200    Ok(ids)
201}
202
203/// A kind's corpus as of a [`Stamp`], and the last fuzzy answer read from it.
204struct Fallback {
205    stamp: Stamp,
206    corpus: Corpus,
207    last: Option<(String, Vec<i64>)>,
208}
209
210static FALLBACK: std::sync::LazyLock<parking_lot::Mutex<HashMap<CorpusKind, Fallback>>> =
211    std::sync::LazyLock::new(Default::default);
212
213/// Which connection, and the database as it saw it. Two stamps are equal only
214/// when nothing has been written in between: `data_version` moves when
215/// another connection commits, `total_changes` when this one writes.
216#[derive(Debug, Clone, Copy, PartialEq, Eq)]
217struct Stamp {
218    connection: i64,
219    data_version: i64,
220    changes: u64,
221}
222
223impl Stamp {
224    fn of(conn: &Connection) -> Result<Self, DbError> {
225        // A random id in a temporary table, which lives and dies with the
226        // connection: an address could be reused by the next one opened.
227        conn.execute_batch(
228            "CREATE TEMP TABLE IF NOT EXISTS koan_connection (id INTEGER NOT NULL);
229             INSERT INTO koan_connection
230                SELECT random() WHERE NOT EXISTS (SELECT 1 FROM koan_connection);",
231        )?;
232        let connection = conn.query_row("SELECT id FROM koan_connection", [], |r| r.get(0))?;
233        let data_version = conn.query_row("PRAGMA data_version", [], |r| r.get(0))?;
234        Ok(Self {
235            connection,
236            data_version,
237            changes: conn.total_changes(),
238        })
239    }
240}
241
242/// A corpus per kind, read again only when the library has moved.
243///
244/// A search field asks on every keystroke, and the library is the same between
245/// them.
246#[derive(Default)]
247pub struct CorpusCache {
248    slots: parking_lot::Mutex<HashMap<CorpusKind, (u64, Corpus)>>,
249}
250
251impl CorpusCache {
252    /// The corpus for `kind` as of `version`, a number the caller changes
253    /// whenever library rows may have.
254    pub fn get(
255        &self,
256        conn: &Connection,
257        kind: CorpusKind,
258        version: u64,
259    ) -> Result<Corpus, DbError> {
260        if let Some((at, corpus)) = self.slots.lock().get(&kind)
261            && *at == version
262        {
263            return Ok(Arc::clone(corpus));
264        }
265        // Read without the lock held: a slow read for one kind is no reason to
266        // hold up a cached answer for another.
267        let corpus: Corpus = Arc::new(fuzzy_corpus(conn, kind)?);
268        self.slots
269            .lock()
270            .insert(kind, (version, Arc::clone(&corpus)));
271        Ok(corpus)
272    }
273}
274
275#[cfg(test)]
276mod tests {
277    use super::*;
278    use crate::db::connection::Database;
279    use crate::db::queries::{sample_meta, upsert_track};
280
281    fn test_db() -> Database {
282        let conn = rusqlite::Connection::open_in_memory().unwrap();
283        conn.pragma_update(None, "foreign_keys", "on").unwrap();
284        crate::db::schema::create_tables(&conn).unwrap();
285        Database { conn }
286    }
287
288    #[test]
289    fn test_search_fts() {
290        let db = test_db();
291        upsert_track(&db.conn, &sample_meta("Vordhosbn", "Aphex Twin", "Drukqs")).unwrap();
292        upsert_track(
293            &db.conn,
294            &sample_meta("Roygbiv", "Boards of Canada", "MHTRTC"),
295        )
296        .unwrap();
297        upsert_track(
298            &db.conn,
299            &sample_meta("Tha", "Aphex Twin", "Selected Ambient Works"),
300        )
301        .unwrap();
302
303        // Search by artist.
304        let results = search_tracks(&db.conn, "Aphex").unwrap();
305        assert_eq!(results.len(), 2);
306
307        // Search by title.
308        let results = search_tracks(&db.conn, "Roygbiv").unwrap();
309        assert_eq!(results.len(), 1);
310        assert_eq!(results[0].title, "Roygbiv");
311
312        // Search by album.
313        let results = search_tracks(&db.conn, "Drukqs").unwrap();
314        assert_eq!(results.len(), 1);
315    }
316}
koan_core/db/queries/search.rs

koan_core/db/queries/
search.rs
