Something went wrong. Try again.
[READ-ONLY] Mirror of https://github.com/kasper9n/ferrum. Music library app for Mac, Linux and Windows ferrum.kasper.space
Something went wrong. Try again.
16 kB · 622 lines
Rust
at sqlite
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623use crate::library::{Paths, embedded_sql};use crate::migrate::old_library::{Library, TrackList, TrackLists, load_library_json};use anyhow::{Context, Result};use rusqlite::{Connection, OpenFlags, params};use std::collections::HashMap;use std::path::PathBuf;use std::time::Instant;use tempfile::TempDir;
pub async fn migrate_to_sqlite(paths: &Paths) -> Result<()> { let now = Instant::now();
let library_dir = PathBuf::from(&paths.library_dir); let library_json = library_dir.join("Library.json");
let library_json = match load_library_json(&library_json)? { None => { return Ok(()); } Some(library_json) => library_json, };
let tmp_dir = TempDir::new().context("failed to create temp dir")?; let tmp_db = tmp_dir.path().join("Library.sqlite");
let mut db = Connection::open_with_flags( &tmp_db, OpenFlags::SQLITE_OPEN_CREATE | OpenFlags::SQLITE_OPEN_READ_WRITE, ) .context("Could not create library database")?; db.execute_batch("PRAGMA foreign_keys = ON;") .context("Error enabling foreign keys")?;
embedded_sql::migrations::runner() .run(&mut db) .context("Could not run database migrations")?;
insert_library_into_db(&library_json, &mut db) .context("Could not insert Library.json into database")?;
db.close() .map_err(|(_, e)| e) .context("Could not save/close database")?;
std::fs::rename(&tmp_db, &paths.library_sqlite) .context("Failed to finalize sqlite database")?;
println!("Migrated to SQLite: {}ms", now.elapsed().as_millis());
// Move Library.json to Library.json.bak println!("TODO: Re-enable backing up Library.json"); // let library_json_backup = library_dir.join("Library backup.json"); // std::fs::rename(&paths.library_json, library_json_backup) // .context("Failed to rename Library.json to Library backup.json")?;
Ok(())}
fn insert_library_into_db(library: &Library, db: &mut Connection) -> anyhow::Result<()> { let tx = db.transaction().context("Failed to begin transaction")?; // Defer foreign key checks for track list parent_id tx.execute_batch("PRAGMA defer_foreign_keys = ON;")?;
let mut new_ids: HashMap<&str, u32> = HashMap::new();
for (i, (text_id, track)) in library.tracks.iter().enumerate() { let track_id: u32 = i.try_into().unwrap(); let removed = new_ids.insert(text_id, track_id); assert!(removed.is_none()); tx.execute( " INSERT INTO tracks ( id, text_id, filesize, duration_s, bitrate, sample_rate, file, modified_at, added_at, title, artist, imported_from, original_id, composer, sort_title, sort_artist, sort_composer, genre, rating_pct, year, bpm, comments, grouping, liked, disliked, disabled, compilation, album_title, album_artist, sort_album_title, sort_album_artist, track_num, track_count, disc_num, disc_count, imported_at, play_count, skip_count, volume ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ", params![ &track_id, &text_id, track.size, track.duration, track.bitrate, track.sampleRate, &track.file, track.dateModified, track.dateAdded, &track.name, &track.artist, &track.importedFrom, &track.originalId, &track.composer, &track.sortName, &track.sortArtist, &track.sortComposer, &track.genre, track.rating, track.year, track.bpm, &track.comments, &track.grouping, track.liked, track.disliked, track.disabled, track.compilation, &track.albumName, &track.albumArtist, &track.sortAlbumName, &track.sortAlbumArtist, track.trackNum, track.trackCount, track.discNum, track.discCount, track.dateImported, track.playCount.unwrap_or(0), track.skipCount.unwrap_or(0), track.volume, ], ) .with_context(|| format!("Failed to insert track {track_id}"))?;
if let Some(plays) = &track.plays { for &date in plays { tx.execute( "INSERT INTO plays (date, track_id) VALUES (?, ?)", params![date, &track_id], ) .with_context(|| format!("Failed to insert plays with date {}", date))?; } }
if let Some(imported) = &track.playsImported { for co in imported { tx.execute( "INSERT INTO plays_imported (date_range_from, date_range_to, count, track_id) VALUES (?, ?, ?, ?)", params![ co.fromDate, co.toDate, co.count, &track_id, ])
.with_context(|| format!("Failed to insert plays_imported with fromDate {}", co.fromDate))?; } }
if let Some(skips) = &track.skips { for &date in skips { tx.execute( "INSERT INTO skips (date, track_id) VALUES (?, ?)", params![date, &track_id], ) .with_context(|| format!("Failed to insert skips with date {}", date))?; } }
if let Some(imported) = &track.skipsImported { for co in imported { tx.execute( "INSERT INTO skips_imported (date_range_from, date_range_to, count, track_id) VALUES (?, ?, ?, ?)", params![ co.fromDate, co.toDate, co.count, &track_id, ])
.with_context(|| format!("Failed to insert skips_imported with fromDate {}", co.fromDate))?; } } }
// Playtimes were incorrect and overwritten after relaunch, so nothing to keep
let parent_map = build_parent_map(&library.trackLists);
for (list_id, tracklist) in &library.trackLists { match tracklist { TrackList::Special(special) => { let name = special.name.get_name_str(); tx.execute( " INSERT INTO track_lists (id, kind, name, description, created_at) VALUES (?, ?, ?, ?, ?) ", params![ &special.id, "special", special.name.get_name_str(), "", special.dateCreated, ], ) .with_context(|| format!("Failed to insert special playlist {name}"))?; } TrackList::Folder(folder) => { let (index, parent_id) = parent_map .get(list_id.as_str()) .with_context(|| format!("Parent of folder {} not found", folder.name))?; tx.execute( " INSERT INTO track_lists (id, kind, parent_id, item_pos, name, description, liked, disliked, imported_from, original_id, imported_at, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ", params![ &folder.id, "folder", parent_id, index, &folder.name, folder.description.as_deref().unwrap_or(""), folder.liked, folder.disliked, &folder.importedFrom, &folder.originalId, folder.dateImported, folder.dateCreated, ], ) .with_context(|| format!("Failed to insert playlist folder {}", folder.name))?; } TrackList::Playlist(playlist) => { let (index, parent_id) = parent_map .get(list_id.as_str()) .with_context(|| format!("Parent of playlist {} not found", playlist.name))?; tx.execute( " INSERT INTO track_lists (id, kind, parent_id, item_pos, name, description, liked, disliked, imported_from, original_id, imported_at, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ", params![ &playlist.id, "playlist", parent_id, index, &playlist.name, playlist.description.as_deref().unwrap_or(""), playlist.liked, playlist.disliked, &playlist.importedFrom, &playlist.originalId, playlist.dateImported, playlist.dateCreated, ], ) .with_context(|| format!("Failed to insert playlist {}", playlist.name))?;
// playlist_tracks rows for (i, text_id) in playlist.tracks.iter().enumerate() { let track_id = new_ids.get(text_id.as_str()).unwrap(); let i: u32 = i.try_into().unwrap(); tx.execute( "INSERT INTO playlist_tracks (track_list_id, track_id, item_pos) VALUES (?, ?, ?)", params![&playlist.id, track_id, i], ) .with_context(|| { format!( "Failed to insert track {} in playlist {}", track_id, playlist.name ) })?; } } } }
tx.commit().context("Failed to commit transaction")?; Ok(())}
/// Returns a map of playlist -> (index, parent_id) for every tracklist entry.fn build_parent_map(track_lists: &TrackLists) -> std::collections::HashMap<String, (i64, String)> { let mut map = std::collections::HashMap::new(); for (parent_id, tl) in track_lists { let children = match tl { TrackList::Folder(f) => &f.children, TrackList::Special(s) => &s.children, TrackList::Playlist(_) => continue, }; for (i, child_id) in children.iter().enumerate() { // SQLite does not support u64 let i: i64 = i.try_into().unwrap(); assert!(i >= 0); map.insert(child_id.clone(), (i, parent_id.clone())); } } map}
mod old_library { #![allow(non_snake_case)]
use anyhow::{Context, Result}; use linked_hash_map::LinkedHashMap; use serde::Deserialize; use serde_json::{Value, json}; use std::fs::File; use std::io::{ErrorKind, Read, Seek, SeekFrom}; use std::path::PathBuf;
pub fn load_library_json(library_json: &PathBuf) -> Result<Option<Library>> { let mut library_file = match File::open(&library_json) { Ok(file) => file, Err(err) => match err.kind() { ErrorKind::NotFound => return Ok(None), _ => return Err(err).context("Error opening library file"), }, };
let mut json_bytes = Vec::new(); library_file .read_to_end(&mut json_bytes) .context("Error reading library file")?;
let versioned_library: VersionedLibrary = match simd_json::from_slice(&mut json_bytes) { Ok(lib) => lib, Err(_) => { library_file .seek(SeekFrom::Start(0)) .context("Error seeking to start of library file")?; let versioned_library = parse_old_versionless_library_json(&mut library_file)?; versioned_library } };
let library = versioned_library.upgrade(); Ok(Some(library)) }
fn parse_old_versionless_library_json(library_file: &mut File) -> Result<VersionedLibrary> { let mut json_str = String::new(); library_file .read_to_string(&mut json_str) .context("Error reading library file")?;
let mut value: Value = serde_json::from_str(&mut json_str).context("Error parsing library file")?; // Migrate version number to string if let Some(obj) = value.as_object_mut() { if let Some(version_field) = obj.get_mut("version") { if let Some(version) = version_field.as_number() { if version.as_u64() == Some(1) { *version_field = json!("1"); } else if version.as_u64() == Some(2) { *version_field = json!("2"); } } } }
let versioned_library: VersionedLibrary = serde_json::from_value(value).context("Error parsing library file")?; Ok(versioned_library) }
pub type Library = V2Library;
#[derive(Deserialize, Clone, Debug)] #[serde(deny_unknown_fields)] pub struct V2Library { pub tracks: LinkedHashMap<TrackID, Track>, pub trackLists: TrackLists, // Playtimes were incorrect and overwritten after relaunch, so nothing to keep #[allow(unused)] v1PlayTime: Vec<PlayTime>, #[allow(unused)] playTime: Vec<PlayTime>, }
#[derive(Deserialize, Clone, Debug)] #[serde(tag = "version", deny_unknown_fields)] enum VersionedLibrary { #[serde(rename = "1")] V1(V1Library), #[serde(rename = "2")] V2(V2Library), } impl VersionedLibrary { pub fn upgrade(self) -> V2Library { match self { VersionedLibrary::V1(v1) => v1.upgrade(), VersionedLibrary::V2(v2) => v2, } } }
#[derive(Deserialize, Clone, Debug)] #[serde(deny_unknown_fields)] struct V1Library { tracks: LinkedHashMap<TrackID, Track>, trackLists: TrackLists, playTime: Vec<PlayTime>, } impl V1Library { fn upgrade<'a>(self) -> V2Library { V2Library { tracks: self.tracks, trackLists: self.trackLists, v1PlayTime: self.playTime, playTime: Vec::new(), } } }
type TrackID = String; type TrackListID = String; type MsSinceUnixEpoch = i64; /// Should be 0-100 type PercentInteger = u8; pub type TrackLists = LinkedHashMap<TrackListID, TrackList>;
/// (track id, start time, duration) type PlayTime = (TrackID, MsSinceUnixEpoch, i64);
#[derive(Deserialize, Clone, Debug)] pub struct Track { pub size: i64, pub duration: f64, pub bitrate: f64, pub sampleRate: f64, pub file: String, pub dateModified: MsSinceUnixEpoch, pub dateAdded: MsSinceUnixEpoch, pub name: String, #[serde(default)] pub importedFrom: Option<String>, /// Imported ID, like iTunes Persistent ID #[serde(default)] pub originalId: Option<String>, #[serde(default)] pub artist: String, #[serde(default)] pub composer: Option<String>, #[serde(default)] pub sortName: Option<String>, #[serde(default)] pub sortArtist: Option<String>, #[serde(default)] pub sortComposer: Option<String>, #[serde(default)] pub genre: Option<String>, #[serde(default)] pub rating: Option<PercentInteger>, #[serde(default)] pub year: Option<i64>, #[serde(default)] pub bpm: Option<f64>, #[serde(default)] pub comments: Option<String>, #[serde(default)] pub grouping: Option<String>, #[serde(default)] pub liked: Option<bool>, #[serde(default)] pub disliked: Option<bool>, #[serde(default)] pub disabled: Option<bool>, #[serde(default)] pub compilation: Option<bool>, #[serde(default)] pub albumName: Option<String>, #[serde(default)] pub albumArtist: Option<String>, #[serde(default)] pub sortAlbumName: Option<String>, #[serde(default)] pub sortAlbumArtist: Option<String>, #[serde(default)] pub trackNum: Option<u32>, #[serde(default)] pub trackCount: Option<u32>, #[serde(default)] pub discNum: Option<u32>, #[serde(default)] pub discCount: Option<u32>, #[serde(default)] pub dateImported: Option<MsSinceUnixEpoch>, #[serde(default)] pub playCount: Option<u32>, #[serde(default)] pub plays: Option<Vec<MsSinceUnixEpoch>>, #[serde(default)] pub playsImported: Option<Vec<CountObject>>, #[serde(default)] pub skipCount: Option<u32>, #[serde(default)] pub skips: Option<Vec<MsSinceUnixEpoch>>, #[serde(default)] pub skipsImported: Option<Vec<CountObject>>, /// -100 to 100 #[serde(default)] pub volume: Option<i8>, }
#[derive(Deserialize, Clone, Debug)] pub struct CountObject { pub count: i64, pub fromDate: MsSinceUnixEpoch, pub toDate: MsSinceUnixEpoch, }
#[derive(Deserialize, Clone, Debug)] #[serde(tag = "type")] pub enum TrackList { #[serde(rename = "playlist")] Playlist(Playlist), #[serde(rename = "folder")] Folder(Folder), #[serde(rename = "special")] Special(Special), }
#[derive(Deserialize, Clone, Debug)] pub struct Playlist { pub id: TrackListID, pub name: String, #[serde(default)] pub description: Option<String>, #[serde(default)] pub liked: bool, #[serde(default)] pub disliked: bool, #[serde(default)] pub importedFrom: Option<String>, #[serde(default)] pub originalId: Option<String>, #[serde(default)] pub dateImported: Option<MsSinceUnixEpoch>, #[serde(default)] pub dateCreated: Option<MsSinceUnixEpoch>, pub tracks: Vec<TrackID>, }
#[derive(Deserialize, Clone, Debug)] pub struct Folder { pub id: TrackListID, pub name: String, #[serde(default)] pub description: Option<String>, #[serde(default)] pub liked: bool, #[serde(default)] pub disliked: bool, /// For example "itunes" #[serde(default)] pub importedFrom: Option<String>, /// For example iTunes Persistent ID #[serde(default)] pub originalId: Option<String>, #[serde(default)] pub dateImported: Option<MsSinceUnixEpoch>, #[serde(default)] pub dateCreated: Option<MsSinceUnixEpoch>, pub children: Vec<TrackListID>, }
#[derive(Deserialize, Clone, Debug)] pub struct Special { pub id: TrackListID, pub name: SpecialTrackListName, pub dateCreated: MsSinceUnixEpoch, pub children: Vec<TrackListID>, }
#[derive(Deserialize, Clone, Debug)] pub enum SpecialTrackListName { Root, } impl SpecialTrackListName { pub fn get_name_str(&self) -> &str { match self { SpecialTrackListName::Root => "root", } } }}