koan_core/db/
connection.rs

1use std::cell::RefCell;
2use std::collections::HashMap;
3use std::path::Path;
4use std::rc::Rc;
5
6use rusqlite::Connection;
7use thiserror::Error;
8
9use super::schema;
10use crate::config;
11
12#[derive(Debug, Error)]
13pub enum DbError {
14    #[error("sqlite error: {0}")]
15    Sqlite(#[from] rusqlite::Error),
16    #[error("io error: {0}")]
17    Io(#[from] std::io::Error),
18    /// A bulk delete looked like a mount failure rather than an intentional
19    /// deletion, so it was refused. The library is untouched.
20    #[error("refused unsafe bulk delete: {0}")]
21    UnsafeBulkDelete(String),
22}
23
24/// Wrapper around a SQLite connection with koan's schema applied.
25pub struct Database {
26    pub conn: Connection,
27}
28
29impl Database {
30    /// Open (or create) a database at the given path, applying the schema and
31    /// pending migrations.
32    ///
33    /// This is the once-per-process path: it creates the parent directory,
34    /// tightens file permissions, checkpoints the WAL and runs the ~30-statement
35    /// DDL batch. Anything opening a connection per request wants
36    /// [`Database::open_existing`] instead.
37    pub fn open(path: &Path) -> Result<Self, DbError> {
38        if let Some(parent) = path.parent() {
39            std::fs::create_dir_all(parent)?;
40        }
41
42        let conn = Connection::open(path)?;
43
44        #[cfg(unix)]
45        {
46            use std::os::unix::fs::PermissionsExt;
47            let _ = std::fs::set_permissions(path, std::fs::Permissions::from_mode(0o600));
48        }
49
50        configure(&conn)?;
51
52        // Attempt a passive WAL checkpoint on open. This is non-blocking — it
53        // moves WAL pages back to the main DB file only if no readers/writers
54        // are active, preventing unbounded WAL growth across sessions.
55        let _ = conn.execute_batch("PRAGMA wal_checkpoint(PASSIVE)");
56
57        schema::create_tables(&conn)?;
58
59        Ok(Self { conn })
60    }
61
62    /// Open an additional connection to a database whose schema is already
63    /// applied — pragmas only, no DDL, no checkpoint, no permission syscall.
64    ///
65    /// Callers are responsible for having run [`Database::open`] at least once
66    /// against the same path first.
67    pub fn open_existing(path: &Path) -> Result<Self, DbError> {
68        let conn = Connection::open(path)?;
69        configure(&conn)?;
70        Ok(Self { conn })
71    }
72
73    /// Open the default database at the standard data directory.
74    pub fn open_default() -> Result<Self, DbError> {
75        Self::open(&config::db_path())
76    }
77}
78
79/// Connection-scoped pragmas. Every connection needs these; none of them touch
80/// the file on disk, so they are cheap enough to repeat per connection.
81fn configure(conn: &Connection) -> Result<(), DbError> {
82    // WAL mode for concurrent reads + single writer.
83    conn.pragma_update(None, "journal_mode", "wal")?;
84    conn.pragma_update(None, "foreign_keys", "on")?;
85    // Long enough to outlast a scan chunk: a writer that gives up mid-scan
86    // silently loses favourites, queue state and play counts.
87    conn.pragma_update(None, "busy_timeout", 30000)?;
88    // Slightly faster at the cost of durability on power loss (acceptable for a media DB).
89    conn.pragma_update(None, "synchronous", "normal")?;
90    // Here rather than with the schema: `open_existing` skips the DDL, and a
91    // connection without this collation fails every ORDER BY that uses it.
92    register_library_collation(conn)?;
93    Ok(())
94}
95
96/// A collation for names the way a person reads them.
97///
98/// SQLite's default is a byte comparison, which sorts every capital before
99/// every lowercase and every accented letter after the whole ASCII range — so
100/// an artist list ran `Zebra`, then `aphex twin`, and put `Âme` at the end
101/// where nobody would look for it.
102///
103/// Case is folded, accents are folded onto their base letter (`Âme` sorts with
104/// `Ame`), and runs of digits compare by value so `Track 2` precedes
105/// `Track 10`. Ties fall back to the raw bytes, so two names that differ only
106/// in case or accent still have a stable order rather than being treated as
107/// equal.
108/// Registered by `create_tables`, so every connection has it — a query using
109/// `COLLATE LIBRARY` on a connection that skipped this fails outright rather
110/// than quietly sorting some other way.
111pub(crate) fn register_library_collation(conn: &Connection) -> rusqlite::Result<()> {
112    conn.create_collation("LIBRARY", |a, b| {
113        cached_sort_key(a).cmp(&cached_sort_key(b)).then(a.cmp(b))
114    })
115}
116
117thread_local! {
118    /// Sort keys, kept for the life of the thread.
119    ///
120    /// A collation sees the same name once per level of the sort — around two
121    /// dozen times in a five-thousand-row list — and building a key means an
122    /// NFD pass and a `Vec` of freshly allocated `String`s. Cached, each name is
123    /// folded once per thread instead of once per comparison.
124    static SORT_KEYS: RefCell<HashMap<Box<str>, Rc<[Chunk]>>> = RefCell::new(HashMap::new());
125}
126
127fn cached_sort_key(s: &str) -> Rc<[Chunk]> {
128    SORT_KEYS.with_borrow_mut(|cache| {
129        if let Some(key) = cache.get(s) {
130            return Rc::clone(key);
131        }
132        // A library's worth of names is tens of thousands of entries. Anything
133        // beyond that is a query sorting something other than names, and it
134        // should not grow this without bound.
135        if cache.len() >= 50_000 {
136            cache.clear();
137        }
138        let key: Rc<[Chunk]> = sort_key(s).into();
139        cache.insert(s.into(), Rc::clone(&key));
140        key
141    })
142}
143
144/// One comparable chunk of a name: either a run of digits, as a number, or a
145/// run of folded characters.
146#[derive(PartialEq, Eq, PartialOrd, Ord)]
147enum Chunk {
148    Number(u128),
149    Text(String),
150}
151
152fn sort_key(s: &str) -> Vec<Chunk> {
153    use unicode_normalization::UnicodeNormalization;
154
155    // NFD splits an accented letter into its base plus a combining mark; dropping
156    // the marks leaves the base letter to sort on.
157    let folded: String = s
158        .nfd()
159        .filter(|c| !matches!(*c as u32, 0x0300..=0x036F))
160        .flat_map(char::to_lowercase)
161        .collect();
162
163    let mut chunks = Vec::new();
164    let mut rest = folded.as_str();
165    while !rest.is_empty() {
166        let digits = rest
167            .find(|c: char| !c.is_ascii_digit())
168            .unwrap_or(rest.len());
169        if digits > 0 && rest.starts_with(|c: char| c.is_ascii_digit()) {
170            // Absurdly long digit runs are not numbers anyone sorts by.
171            match rest[..digits].parse::<u128>() {
172                Ok(n) => chunks.push(Chunk::Number(n)),
173                Err(_) => chunks.push(Chunk::Text(rest[..digits].to_string())),
174            }
175            rest = &rest[digits..];
176            continue;
177        }
178        let text = rest
179            .find(|c: char| c.is_ascii_digit())
180            .unwrap_or(rest.len())
181            .max(1);
182        chunks.push(Chunk::Text(rest[..text].to_string()));
183        rest = &rest[text..];
184    }
185    chunks
186}
187
188#[cfg(test)]
189mod collation_tests {
190    use super::*;
191
192    fn sorted(names: &[&str]) -> Vec<String> {
193        let conn = Connection::open_in_memory().unwrap();
194        crate::db::schema::create_tables(&conn).unwrap();
195        conn.execute_batch("CREATE TABLE t (name TEXT)").unwrap();
196        for n in names {
197            conn.execute("INSERT INTO t VALUES (?1)", [n]).unwrap();
198        }
199        let mut stmt = conn
200            .prepare("SELECT name FROM t ORDER BY name COLLATE LIBRARY")
201            .unwrap();
202        let rows = stmt.query_map([], |r| r.get::<_, String>(0)).unwrap();
203        rows.map(Result::unwrap).collect()
204    }
205
206    #[test]
207    fn lowercase_does_not_sort_after_everything() {
208        assert_eq!(
209            sorted(&["Zebra", "aphex twin", "Boards of Canada"]),
210            ["aphex twin", "Boards of Canada", "Zebra"]
211        );
212    }
213
214    #[test]
215    fn accents_sort_with_their_base_letter() {
216        // Byte order puts every non-ASCII name after `z`, which is where nobody
217        // looks for Âme.
218        assert_eq!(
219            sorted(&["Zomby", "Âme", "Alva Noto"]),
220            ["Alva Noto", "Âme", "Zomby"]
221        );
222    }
223
224    #[test]
225    fn digit_runs_compare_as_numbers() {
226        assert_eq!(
227            sorted(&["Track 10", "Track 2", "Track 1"]),
228            ["Track 1", "Track 2", "Track 10"]
229        );
230    }
231
232    #[test]
233    fn names_differing_only_in_case_keep_a_stable_order() {
234        // Folding must not make them equal, or the order flips between runs.
235        assert_eq!(
236            sorted(&["kraftwerk", "Kraftwerk"]),
237            ["Kraftwerk", "kraftwerk"]
238        );
239    }
240}
koan_core/db/connection.rs

koan_core/db/
connection.rs
