koan_core/db/
connection.rs

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

koan_core/db/
connection.rs
