termusiclib/library_db/
mod.rs

1use crate::config::v2::server::ScanDepth;
2/**
3 * MIT License
4 *
5 * termusic - Copyright (c) 2021 Larry Hao
6 *
7 * Permission is hereby granted, free of charge, to any person obtaining a copy
8 * of this software and associated documentation files (the "Software"), to deal
9 * in the Software without restriction, including without limitation the rights
10 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
11 * copies of the Software, and to permit persons to whom the Software is
12 * furnished to do so, subject to the following conditions:
13 *
14 * The above copyright notice and this permission notice shall be included in all
15 * copies or substantial portions of the Software.
16 *
17 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
18 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
19 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
21 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
22 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
23 * SOFTWARE.
24 */
25use crate::config::ServerOverlay;
26use crate::track::Track;
27use crate::utils::{filetype_supported, get_app_config_path, get_pin_yin};
28use anyhow::Context;
29use parking_lot::Mutex;
30use rusqlite::{params, Connection, Error, Result};
31use std::path::Path;
32use std::sync::Arc;
33use std::time::{Duration, UNIX_EPOCH};
34use track_db::TrackDBInsertable;
35
36mod migration;
37mod track_db;
38
39pub use track_db::{const_unknown, Indexable, TrackDB};
40
41pub struct DataBase {
42    conn: Arc<Mutex<Connection>>,
43    max_depth: ScanDepth,
44}
45
46#[derive(Debug, Clone, Copy, PartialEq, Eq)]
47pub enum SearchCriteria {
48    Artist,
49    Album,
50
51    // TODO: the values below are current unused
52    Genre,
53    Directory,
54    Playlist,
55}
56
57impl From<usize> for SearchCriteria {
58    fn from(u_index: usize) -> Self {
59        match u_index {
60            1 => Self::Album,
61            2 => Self::Genre,
62            3 => Self::Directory,
63            4 => Self::Playlist,
64            /* 0 | */ _ => Self::Artist,
65        }
66    }
67}
68
69impl std::fmt::Display for SearchCriteria {
70    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
71        match self {
72            Self::Artist => write!(f, "artist"),
73            Self::Album => write!(f, "album"),
74            Self::Genre => write!(f, "genre"),
75            Self::Directory => write!(f, "directory"),
76            Self::Playlist => write!(f, "playlist"),
77        }
78    }
79}
80
81impl DataBase {
82    /// # Panics
83    ///
84    /// - if app config path creation fails
85    /// - if any required database operation fails
86    pub fn new(config: &ServerOverlay) -> anyhow::Result<Self> {
87        let mut db_path = get_app_config_path().context("failed to get app configuration path")?;
88        db_path.push("library.db");
89        let conn = Connection::open(db_path).context("open/create database")?;
90
91        migration::migrate(&conn).context("Database creation / migration")?;
92
93        let max_depth = config.get_library_scan_depth();
94
95        let conn = Arc::new(Mutex::new(conn));
96        Ok(Self { conn, max_depth })
97    }
98
99    /// Insert multiple tracks into the database
100    fn add_records(conn: &Arc<Mutex<Connection>>, tracks: Vec<Track>) -> Result<()> {
101        let mut conn = conn.lock();
102        let tx = conn.transaction()?;
103
104        for track in tracks {
105            TrackDBInsertable::from(&track).insert_track(&tx)?;
106        }
107
108        tx.commit()?;
109        Ok(())
110    }
111
112    /// Check if the given path's track needs to be updated in the database by comparing `last_modified` times
113    fn need_update(conn: &Arc<Mutex<Connection>>, path: &Path) -> Result<bool> {
114        let conn = conn.lock();
115        let filename = path
116            .file_name()
117            .ok_or_else(|| Error::InvalidParameterName("file name missing".to_string()))?
118            .to_string_lossy();
119        let mut stmt = conn.prepare("SELECT last_modified FROM tracks WHERE name = ?")?;
120        let rows = stmt.query_map([filename], |row| {
121            let last_modified: String = row.get(0)?;
122
123            Ok(last_modified)
124        })?;
125
126        for r in rows.flatten() {
127            let r_u64: u64 = r.parse().unwrap();
128            let timestamp = path.metadata().unwrap().modified().unwrap();
129            let timestamp_u64 = timestamp.duration_since(UNIX_EPOCH).unwrap().as_secs();
130            if timestamp_u64 <= r_u64 {
131                return Ok(false);
132            }
133        }
134
135        Ok(true)
136    }
137
138    /// Get all Track Paths from the database which dont exist on disk anymore
139    fn need_delete(conn: &Arc<Mutex<Connection>>) -> Result<Vec<String>> {
140        let conn = conn.lock();
141        let mut stmt = conn.prepare("SELECT * FROM tracks")?;
142
143        let track_vec: Vec<String> = stmt
144            .query_map([], TrackDB::try_from_row_named)?
145            .flatten()
146            .filter_map(|record| {
147                let path = Path::new(&record.file);
148                if path.exists() {
149                    None
150                } else {
151                    Some(record.file)
152                }
153            })
154            .collect();
155
156        Ok(track_vec)
157    }
158
159    /// Delete Tracks from the database by the full file path
160    fn delete_records(conn: &Arc<Mutex<Connection>>, tracks: Vec<String>) -> Result<()> {
161        let mut conn = conn.lock();
162        let tx = conn.transaction()?;
163
164        for track in tracks {
165            tx.execute("DELETE FROM tracks WHERE file = ?", params![track])?;
166        }
167
168        tx.commit()?;
169        Ok(())
170    }
171
172    /// Synchronize the database with the on-disk paths (insert, update, remove), limited to `path` root
173    pub fn sync_database(&mut self, path: &Path) {
174        // add updated records
175        let conn = self.conn.clone();
176        let all_items = {
177            let mut walker = walkdir::WalkDir::new(path).follow_links(true);
178
179            if let ScanDepth::Limited(limit) = self.max_depth {
180                walker = walker.max_depth(usize::try_from(limit).unwrap_or(usize::MAX));
181            }
182
183            walker
184        };
185
186        std::thread::spawn(move || -> Result<()> {
187            let mut need_updates: Vec<Track> = vec![];
188
189            for record in all_items
190                .into_iter()
191                .filter_map(std::result::Result::ok)
192                .filter(|f| f.file_type().is_file())
193                .filter(|f| filetype_supported(&f.path().to_string_lossy()))
194            {
195                match Self::need_update(&conn, record.path()) {
196                    Ok(true) => {
197                        if let Ok(track) = Track::read_from_path(record.path(), true) {
198                            need_updates.push(track);
199                        }
200                    }
201                    Ok(false) => {}
202                    Err(e) => {
203                        error!("Error in need_update: {e}");
204                    }
205                }
206            }
207            if !need_updates.is_empty() {
208                Self::add_records(&conn, need_updates)?;
209            }
210
211            // delete records where local file are missing
212
213            match Self::need_delete(&conn) {
214                Ok(string_vec) => {
215                    if !string_vec.is_empty() {
216                        Self::delete_records(&conn, string_vec)?;
217                    }
218                }
219                Err(e) => {
220                    error!("Error in need_delete: {e}");
221                }
222            }
223
224            Ok(())
225        });
226    }
227
228    /// Get all Tracks in the database at once
229    pub fn get_all_records(&mut self) -> Result<Vec<TrackDB>> {
230        let conn = self.conn.lock();
231        let mut stmt = conn.prepare("SELECT * FROM tracks")?;
232        let vec: Vec<TrackDB> = stmt
233            .query_map([], TrackDB::try_from_row_named)?
234            .flatten()
235            .collect();
236        Ok(vec)
237    }
238
239    /// Get Tracks by [`SearchCriteria`]
240    pub fn get_record_by_criteria(
241        &mut self,
242        criteria_val: &str,
243        criteria: &SearchCriteria,
244    ) -> Result<Vec<TrackDB>> {
245        let search_str = format!("SELECT * FROM tracks WHERE {criteria} = ?");
246        let conn = self.conn.lock();
247        let mut stmt = conn.prepare(&search_str)?;
248
249        let mut vec_records: Vec<(String, TrackDB)> = stmt
250            .query_map([criteria_val], TrackDB::try_from_row_named)?
251            .flatten()
252            .map(|v| (get_pin_yin(&v.name), v))
253            .collect();
254
255        // Left for debug
256        // error!("criteria_val: {}", criteria_val);
257        // error!("criteria: {}", criteria);
258        // error!("vec: {:?}", vec_records);
259
260        // TODO: if SearchCriteria is "Album", maybe we should sort by album track index
261        // TODO: should we really do the search here in the libary?
262        vec_records.sort_by(|a, b| alphanumeric_sort::compare_str(&a.0, &b.0));
263
264        let vec_records = vec_records.into_iter().map(|v| v.1).collect();
265        Ok(vec_records)
266    }
267
268    /// Get a list of available distinct [`SearchCriteria`] (ie get Artist names deduplicated)
269    pub fn get_criterias(&mut self, criteria: &SearchCriteria) -> Result<Vec<String>> {
270        let search_str = format!("SELECT DISTINCT {criteria} FROM tracks");
271        let conn = self.conn.lock();
272        let mut stmt = conn.prepare(&search_str)?;
273
274        // tuple.0 is the sort key, and tuple.1 is the actual value
275        let mut vec: Vec<(String, String)> = stmt
276            .query_map([], |row| {
277                let criteria: String = row.get(0)?;
278                Ok(criteria)
279            })?
280            .flatten()
281            .map(|v| (get_pin_yin(&v), v))
282            .collect();
283
284        // TODO: should we really do the search here in the libary?
285        vec.sort_by(|a, b| alphanumeric_sort::compare_str(&a.0, &b.0));
286
287        let vec = vec.into_iter().map(|v| v.1).collect();
288        Ok(vec)
289    }
290
291    /// Get the stored `last_position` of a given track
292    pub fn get_last_position(&mut self, track: &Track) -> Result<Duration> {
293        let filename = track
294            .name()
295            .ok_or_else(|| Error::InvalidParameterName("file name missing".to_string()))?;
296        let query = "SELECT last_position FROM tracks WHERE name = ?1";
297
298        let mut last_position: Duration = Duration::from_secs(0);
299        let conn = self.conn.lock();
300        conn.query_row(query, params![filename], |row| {
301            let last_position_u64: u64 = row.get(0)?;
302            // error!("last_position_u64 is {last_position_u64}");
303            last_position = Duration::from_secs(last_position_u64);
304            Ok(last_position)
305        })?;
306        // error!("get last pos as {}", last_position.as_secs());
307        Ok(last_position)
308    }
309
310    /// Set the stored `last_position` of a given track
311    pub fn set_last_position(&mut self, track: &Track, last_position: Duration) -> Result<()> {
312        let filename = track
313            .name()
314            .ok_or_else(|| Error::InvalidParameterName("file name missing".to_string()))?;
315        let query = "UPDATE tracks SET last_position = ?1 WHERE name = ?2";
316        let conn = self.conn.lock();
317        conn.execute(query, params![last_position.as_secs(), filename,])?;
318        // error!("set last position as {}", last_position.as_secs());
319        Ok(())
320    }
321
322    /// Get a Track by the given full file path
323    pub fn get_record_by_path(&mut self, file_path: &str) -> Result<TrackDB> {
324        let search_str = "SELECT * FROM tracks WHERE file = ?";
325        let conn = self.conn.lock();
326        let mut stmt = conn.prepare(search_str)?;
327
328        let maybe_record: Option<TrackDB> = stmt
329            .query_map([file_path], TrackDB::try_from_row_named)?
330            .flatten()
331            .next();
332
333        if let Some(record) = maybe_record {
334            return Ok(record);
335        }
336
337        Err(Error::QueryReturnedNoRows)
338    }
339}
340
341#[cfg(test)]
342mod test_utils {
343    use rusqlite::Connection;
344
345    /// Open a new In-Memory sqlite database
346    pub fn gen_database() -> Connection {
347        Connection::open_in_memory().expect("open db failed")
348    }
349}
termusiclib/library_db/mod.rs

termusiclib/library_db/
mod.rs
