From d347d89dfeef54d648ffdea054897617c0016509 Mon Sep 17 00:00:00 2001 From: salmonik Date: Sat, 6 Jun 2026 19:48:22 +0300 Subject: correctly working auto completion for db --- src/database.rs | 259 ++++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 206 insertions(+), 53 deletions(-) (limited to 'src/database.rs') diff --git a/src/database.rs b/src/database.rs index 00d970a..01bee92 100644 --- a/src/database.rs +++ b/src/database.rs @@ -63,7 +63,29 @@ impl Database { fs::create_dir_all(&data_dir).ok(); let db_path = data_dir.join("library.db"); - let conn = Connection::open(&db_path)?; + let debug_log = std::env::temp_dir().join("rsp-completion-debug.log"); + macro_rules! debug_log { + ($($arg:tt)*) => { + let _ = std::fs::OpenOptions::new() + .create(true) + .append(true) + .open(&debug_log) + .and_then(|mut f| { + use std::io::Write; + writeln!(f, $($arg)*) + }); + }; + } + debug_log!("[Database::open] db_path: {:?}", db_path); + debug_log!("[Database::open] db exists: {}", db_path.exists()); + + let conn = match Connection::open(&db_path) { + Ok(c) => c, + Err(e) => { + debug_log!("[Database::open] Connection::open failed: {e}"); + return Err(e); + } + }; let db = Database { conn }; db.init_schema()?; Ok(db) @@ -273,51 +295,174 @@ impl Database { self.get_album_tracks_after(album_id, track_number, &path) } - pub fn complete_artists(&self, prefix: &str) -> SqlResult> { - let pattern = format!("%{}%", prefix); - let mut stmt = self - .conn - .prepare("SELECT name FROM artists WHERE name LIKE ?1 ORDER BY name LIMIT 25")?; - let rows = stmt.query_map([&pattern], |row| row.get::<_, String>(0))?; - rows.collect() - } + pub fn complete(&self, query: &str) -> SqlResult> { + let mut results: Vec = Vec::new(); - pub fn complete_albums(&self, prefix: &str) -> SqlResult> { - let (artist, album_prefix) = match prefix.split_once('/') { - Some((a, ap)) => (a, ap), - None => return Ok(Vec::new()), - }; - let pattern = format!("{}%", album_prefix); - let mut stmt = self.conn.prepare( - "SELECT a.title FROM albums a - JOIN artists ar ON a.artist_id = ar.id - WHERE ar.name = ?1 AND a.title LIKE ?2 - ORDER BY a.title LIMIT 25", - )?; - let rows = stmt.query_map(rusqlite::params![artist, pattern], |row| { - row.get::<_, String>(0) - })?; - rows.collect() - } + if query.is_empty() { + let mut stmt = self + .conn + .prepare("SELECT name FROM artists ORDER BY name LIMIT 25")?; + let rows = stmt.query_map([], |row| row.get::<_, String>(0))?; + for name in rows.flatten() { + results.push(format!("{name}/")); + } + return Ok(results); + } - pub fn complete_tracks(&self, prefix: &str) -> SqlResult> { - let parts: Vec<&str> = prefix.splitn(3, '/').collect(); - if parts.len() < 3 { - return Ok(Vec::new()); + let has_trailing_slash = query.ends_with('/'); + + if has_trailing_slash { + let prefix = query.trim_end_matches('/'); + let segments: Vec<&str> = prefix.split('/').filter(|s| !s.is_empty()).collect(); + + match segments.len() { + 1 => { + let artist = segments[0]; + let mut stmt = self.conn.prepare( + "SELECT a.title FROM albums a + JOIN artists ar ON a.artist_id = ar.id + WHERE ar.name LIKE ?1 + ORDER BY a.title LIMIT 25", + )?; + let rows = stmt.query_map([artist], |row| row.get::<_, String>(0))?; + for album in rows.flatten() { + results.push(format!("{artist}/{album}/")); + } + } + 2 => { + let artist = segments[0]; + let album = segments[1]; + let mut stmt = self.conn.prepare( + "SELECT t.title FROM tracks t + JOIN albums a ON t.album_id = a.id + JOIN artists ar ON a.artist_id = ar.id + WHERE ar.name LIKE ?1 AND a.title LIKE ?2 + ORDER BY t.track_number, t.title LIMIT 25", + )?; + let rows = stmt.query_map(rusqlite::params![artist, album], |row| { + row.get::<_, String>(0) + })?; + for track in rows.flatten() { + results.push(format!("{artist}/{album}/{track}")); + } + } + _ => {} + } + } else { + let segments: Vec<&str> = query.split('/').collect(); + + match segments.len() { + 1 => { + let prefix = segments[0]; + let like_pattern = format!("%{prefix}%"); + let prefix_pattern = format!("{prefix}%"); + + let mut stmt = self.conn.prepare( + "SELECT name FROM artists + WHERE name LIKE ?1 + ORDER BY + CASE WHEN name LIKE ?2 THEN 0 ELSE 1 END, + name + LIMIT 25", + )?; + let rows = stmt + .query_map(rusqlite::params![&like_pattern, &prefix_pattern], |row| { + row.get::<_, String>(0) + })?; + for name in rows.flatten() { + results.push(format!("{name}/")); + } + } + 2 => { + let artist = segments[0]; + let album_prefix = segments[1]; + if artist.is_empty() { + return Ok(results); + } + + let artist_pattern = format!("%{artist}%"); + let artist_prefix = format!("{artist}%"); + let album_pattern = format!("%{album_prefix}%"); + let album_prefix_pattern = format!("{album_prefix}%"); + + let mut stmt = self.conn.prepare( + "SELECT ar.name, a.title FROM albums a + JOIN artists ar ON a.artist_id = ar.id + WHERE ar.name LIKE ?1 AND a.title LIKE ?2 + ORDER BY + CASE WHEN ar.name LIKE ?3 THEN 0 ELSE 1 END, + CASE WHEN a.title LIKE ?4 THEN 0 ELSE 1 END, + ar.name, a.title + LIMIT 25", + )?; + let rows = stmt.query_map( + rusqlite::params![ + &artist_pattern, + &album_pattern, + &artist_prefix, + &album_prefix_pattern, + ], + |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)), + )?; + for (name, album) in rows.flatten() { + results.push(format!("{name}/{album}/")); + } + } + 3 => { + let artist = segments[0]; + let album = segments[1]; + let track_prefix = segments[2]; + if artist.is_empty() || album.is_empty() { + return Ok(results); + } + + let artist_pattern = format!("%{artist}%"); + let artist_prefix = format!("{artist}%"); + let album_pattern = format!("%{album}%"); + let album_prefix = format!("{album}%"); + let track_pattern = format!("%{track_prefix}%"); + let track_prefix_pattern = format!("{track_prefix}%"); + + let mut stmt = self.conn.prepare( + "SELECT ar.name, a.title, t.title FROM tracks t + JOIN albums a ON t.album_id = a.id + JOIN artists ar ON a.artist_id = ar.id + WHERE ar.name LIKE ?1 AND a.title LIKE ?2 AND t.title LIKE ?3 + ORDER BY + CASE WHEN ar.name LIKE ?4 THEN 0 ELSE 1 END, + CASE WHEN a.title LIKE ?5 THEN 0 ELSE 1 END, + CASE WHEN t.title LIKE ?6 THEN 0 ELSE 1 END, + t.track_number, t.title + LIMIT 25", + )?; + let rows = stmt.query_map( + rusqlite::params![ + &artist_pattern, + &album_pattern, + &track_pattern, + &artist_prefix, + &album_prefix, + &track_prefix_pattern, + ], + |row| { + Ok(( + row.get::<_, String>(0)?, + row.get::<_, String>(1)?, + row.get::<_, String>(2)?, + )) + }, + )?; + for (name, album_title, track) in rows.flatten() { + results.push(format!("{name}/{album_title}/{track}")); + } + } + _ => {} + } } - let (artist, album, track_prefix) = (parts[0], parts[1], parts[2]); - let pattern = format!("{}%", track_prefix); - let mut stmt = self.conn.prepare( - "SELECT t.title FROM tracks t - JOIN albums a ON t.album_id = a.id - JOIN artists ar ON a.artist_id = ar.id - WHERE ar.name = ?1 AND a.title = ?2 AND t.title LIKE ?3 - ORDER BY t.track_number, t.title LIMIT 25", - )?; - let rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| { - row.get::<_, String>(0) - })?; - rows.collect() + + results.truncate(25); + + Ok(results) } #[allow(dead_code)] @@ -328,14 +473,14 @@ impl Database { Ok(db) } - /// Find all tracks in an album by exact artist name and album title. + /// Find all tracks in an album by case-insensitive artist and album match. pub fn find_tracks_by_album(&self, artist: &str, album: &str) -> SqlResult> { let mut stmt = self.conn.prepare( "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title FROM tracks t JOIN albums a ON t.album_id = a.id JOIN artists ar ON a.artist_id = ar.id - WHERE ar.name = ?1 AND a.title = ?2 + WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2 ORDER BY t.track_number", )?; let rows = stmt.query_map(rusqlite::params![artist, album], |row| { @@ -367,7 +512,7 @@ impl Database { FROM tracks t JOIN albums a ON t.album_id = a.id JOIN artists ar ON a.artist_id = ar.id - WHERE ar.name = ?1 AND a.title = ?2 AND t.title LIKE ?3 + WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2 AND t.title LIKE ?3 ORDER BY t.track_number LIMIT 1", )?; let mut rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| { @@ -462,11 +607,19 @@ pub fn extract_metadata(path: &Path) -> (String, String, String, Option, Op .and_then(|s| s.to_str()) .unwrap_or("Unknown"); - ( - grandparent.to_string(), - parent.to_string(), - file_stem.to_string(), - None, - None, - ) + let fs_artist = grandparent.to_string(); + let fs_album = parent.to_string(); + let fs_title = file_stem.to_string(); + + if let Some(audio_meta) = crate::queue::extract_metadata(path) { + ( + audio_meta.artist.unwrap_or(fs_artist), + audio_meta.album.unwrap_or(fs_album), + audio_meta.title.unwrap_or(fs_title), + audio_meta.track_number, + audio_meta.duration_secs.map(|d| d as u64), + ) + } else { + (fs_artist, fs_album, fs_title, None, None) + } } -- cgit v1.2.3