1pub mod import;
11pub mod models;
12
13use std::collections::{HashMap, HashSet};
14use std::io::Write as _;
15use std::path::{Path, PathBuf};
16use std::sync::Mutex;
17use std::sync::atomic::{AtomicI64, Ordering};
18use std::time::Duration;
19
20use anyhow::{Context as _, Result};
21use rusqlite::{Connection, OptionalExtension as _, params};
22
23pub use models::{
24 ArtistCard, ArtistDetail, ArtistRef, ArtistsPage, Availability, FederatedTrack, LibraryFilters,
25 LibrarySourceMode, PlaylistCard, PlaylistDetail, ReleaseCard, ReleaseDetail, ReleaseEdit,
26 SearchResults, TrackEdit, TrackItem,
27};
28
29pub const LIKES_PLAYLIST_ID: i64 = -1;
31const NETWORK_ARTIST_CACHE_TTL_MS: i64 = 7 * 24 * 60 * 60 * 1000;
32static NEXT_PENDING_TRACK_ID: AtomicI64 = AtomicI64::new(-1);
33
34type FederatedLikeState = (
35 String,
36 String,
37 String,
38 String,
39 Option<i32>,
40 Option<i64>,
41 Option<String>,
42 Option<i32>,
43 Option<i32>,
44 Option<i64>,
45);
46
47#[derive(Debug, Clone, PartialEq, Eq)]
48pub struct ListenHistoryEntry {
49 pub listen_id: String,
50 pub content_id: String,
51 pub title: String,
52 pub artist: String,
53 pub origin_device_id: String,
54 pub started_at_ms: i64,
55}
56
57const SCHEMA: &str = "
58CREATE TABLE IF NOT EXISTS artists (
59 id INTEGER PRIMARY KEY,
60 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
61 image_path TEXT
62);
63CREATE TABLE IF NOT EXISTS releases (
64 id INTEGER PRIMARY KEY,
65 title TEXT NOT NULL,
66 release_type TEXT NOT NULL DEFAULT 'album',
67 year INTEGER,
68 cover_path TEXT
69);
70CREATE TABLE IF NOT EXISTS release_artists (
71 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
72 artist_id INTEGER NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
73 position INTEGER NOT NULL DEFAULT 0,
74 PRIMARY KEY (release_id, artist_id)
75);
76CREATE TABLE IF NOT EXISTS tracks (
77 id INTEGER PRIMARY KEY,
78 title TEXT NOT NULL,
79 track_number INTEGER,
80 disc_number INTEGER,
81 duration_seconds REAL NOT NULL DEFAULT 0,
82 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
83 file_path TEXT NOT NULL UNIQUE,
84 content_id TEXT,
85 audio_format TEXT,
86 audio_bitrate INTEGER,
87 audio_sample_rate INTEGER,
88 audio_bit_depth INTEGER,
89 file_size_bytes INTEGER,
90 created_at TEXT NOT NULL DEFAULT (datetime('now'))
91);
92CREATE TABLE IF NOT EXISTS track_artists (
93 track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
94 artist_id INTEGER NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
95 role TEXT NOT NULL DEFAULT 'main',
96 position INTEGER NOT NULL DEFAULT 0,
97 PRIMARY KEY (track_id, artist_id, role)
98);
99CREATE TABLE IF NOT EXISTS playlists (
100 id INTEGER PRIMARY KEY,
101 sync_id TEXT UNIQUE,
102 title TEXT NOT NULL,
103 description TEXT,
104 created_at TEXT NOT NULL DEFAULT (datetime('now'))
105);
106CREATE TABLE IF NOT EXISTS playlist_tracks (
107 playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
108 track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
109 position INTEGER NOT NULL,
110 PRIMARY KEY (playlist_id, track_id)
111);
112CREATE TABLE IF NOT EXISTS likes (
113 track_id INTEGER PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
114 liked_at TEXT NOT NULL DEFAULT (datetime('now')),
115 liked_hlc_ms INTEGER
116);
117CREATE TABLE IF NOT EXISTS fed_likes (
118 item_id TEXT PRIMARY KEY,
119 owner TEXT NOT NULL,
120 title TEXT NOT NULL,
121 artist_names TEXT NOT NULL DEFAULT '',
122 featured_artist_names TEXT NOT NULL DEFAULT '',
123 year INTEGER,
124 duration_seconds REAL,
125 content_id TEXT,
126 release_title TEXT,
127 track_number INTEGER,
128 disc_number INTEGER,
129 liked_at TEXT NOT NULL DEFAULT (datetime('now')),
130 liked_hlc_ms INTEGER
131);
132CREATE TABLE IF NOT EXISTS fed_playlist_tracks (
133 playlist_sync_id TEXT NOT NULL,
134 item_id TEXT NOT NULL,
135 owner TEXT NOT NULL,
136 title TEXT NOT NULL,
137 artist_names TEXT NOT NULL DEFAULT '',
138 featured_artist_names TEXT NOT NULL DEFAULT '',
139 year INTEGER,
140 duration_seconds REAL,
141 content_id TEXT NOT NULL,
142 release_title TEXT,
143 track_number INTEGER,
144 disc_number INTEGER,
145 position INTEGER NOT NULL DEFAULT 0,
146 added_at TEXT NOT NULL DEFAULT (datetime('now')),
147 PRIMARY KEY (playlist_sync_id, content_id)
148);
149CREATE TABLE IF NOT EXISTS history (
150 id INTEGER PRIMARY KEY,
151 track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
152 started_at INTEGER,
153 listened_seconds INTEGER NOT NULL DEFAULT 0,
154 completed INTEGER NOT NULL DEFAULT 0,
155 played_at TEXT NOT NULL DEFAULT (datetime('now'))
156);
157CREATE TABLE IF NOT EXISTS listen_events (
158 listen_id TEXT PRIMARY KEY,
159 content_id TEXT NOT NULL,
160 local_track_id INTEGER REFERENCES tracks(id) ON DELETE SET NULL,
161 origin_device_id TEXT NOT NULL,
162 started_at_ms INTEGER NOT NULL,
163 listened_ms INTEGER NOT NULL,
164 track_duration_ms INTEGER,
165 ended_reason TEXT NOT NULL,
166 qualified INTEGER NOT NULL,
167 metadata_json TEXT NOT NULL,
168 created_at TEXT NOT NULL DEFAULT (datetime('now'))
169);
170CREATE INDEX IF NOT EXISTS idx_tracks_release ON tracks(release_id);
171CREATE INDEX IF NOT EXISTS idx_track_artists_artist ON track_artists(artist_id);
172CREATE INDEX IF NOT EXISTS idx_release_artists_artist ON release_artists(artist_id);
173CREATE INDEX IF NOT EXISTS idx_history_track ON history(track_id);
174CREATE INDEX IF NOT EXISTS idx_listen_events_content
175 ON listen_events(content_id, started_at_ms DESC);
176CREATE INDEX IF NOT EXISTS idx_listen_events_local_track
177 ON listen_events(local_track_id, qualified);
178CREATE TRIGGER IF NOT EXISTS reconcile_listen_events_after_track_insert
179AFTER INSERT ON tracks
180WHEN NEW.content_id IS NOT NULL
181BEGIN
182 UPDATE listen_events
183 SET local_track_id = NEW.id
184 WHERE content_id = NEW.content_id;
185END;
186CREATE TRIGGER IF NOT EXISTS reconcile_listen_events_after_track_content_id
187AFTER UPDATE OF content_id ON tracks
188WHEN NEW.content_id IS NOT NULL
189BEGIN
190 UPDATE listen_events
191 SET local_track_id = NEW.id
192 WHERE content_id = NEW.content_id;
193END;
194CREATE INDEX IF NOT EXISTS idx_playlist_tracks_playlist ON playlist_tracks(playlist_id);
195CREATE INDEX IF NOT EXISTS idx_fed_playlist_tracks_playlist
196 ON fed_playlist_tracks(playlist_sync_id, position);
197CREATE TABLE IF NOT EXISTS network_artist_cache (
198 source_id TEXT NOT NULL,
199 source_kind TEXT NOT NULL,
200 artist_key TEXT NOT NULL,
201 name TEXT NOT NULL,
202 image_path TEXT,
203 remote_image_hint TEXT,
204 release_count INTEGER NOT NULL DEFAULT 0,
205 track_count INTEGER NOT NULL DEFAULT 0,
206 seen_at_ms INTEGER NOT NULL,
207 PRIMARY KEY (source_id, artist_key)
208);
209CREATE INDEX IF NOT EXISTS idx_network_artist_cache_kind
210 ON network_artist_cache(source_kind, seen_at_ms);
211CREATE INDEX IF NOT EXISTS idx_network_artist_cache_artist
212 ON network_artist_cache(artist_key);
213CREATE TABLE IF NOT EXISTS similarity_profiles (
214 profile_id TEXT PRIMARY KEY,
215 model_id TEXT NOT NULL,
216 model_version TEXT NOT NULL,
217 model_sha256 TEXT NOT NULL,
218 preprocessing TEXT NOT NULL,
219 dimensions INTEGER NOT NULL,
220 created_at_ms INTEGER NOT NULL
221);
222CREATE TABLE IF NOT EXISTS track_embeddings (
223 track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
224 profile_id TEXT NOT NULL REFERENCES similarity_profiles(profile_id) ON DELETE CASCADE,
225 dimensions INTEGER NOT NULL,
226 vector BLOB NOT NULL,
227 routing_signature BLOB,
228 source_content_id TEXT,
229 computed_at_ms INTEGER NOT NULL,
230 PRIMARY KEY (track_id, profile_id)
231);
232CREATE INDEX IF NOT EXISTS idx_track_embeddings_profile
233 ON track_embeddings(profile_id, track_id);
234";
235
236const TRACK_COLUMNS: &str = "
239 t.id, t.title, t.track_number, t.disc_number, t.duration_seconds,
240 t.release_id, r.title, r.year, r.cover_path,
241 t.file_path, t.audio_format, t.audio_bitrate, t.audio_sample_rate,
242 t.audio_bit_depth, t.file_size_bytes,
243 t.content_id,
244 ((SELECT COUNT(*) FROM history h WHERE h.track_id = t.id AND h.completed = 1)
245 + (SELECT COUNT(*) FROM listen_events le
246 WHERE le.local_track_id = t.id AND le.qualified = 1))
247";
248
249#[derive(Debug)]
252pub struct FederationExport {
253 pub artists: Vec<(i64, String)>,
254 pub releases: Vec<ExportRelease>,
255 pub tracks: Vec<ExportTrack>,
256}
257
258#[derive(Debug, Clone, Default, PartialEq, Eq)]
259pub struct ContentIdBackfillStats {
260 pub checked: usize,
261 pub normalized: usize,
262 pub hashed: usize,
263 pub failed: usize,
264}
265
266impl ContentIdBackfillStats {
267 pub fn updated(&self) -> usize {
268 self.normalized + self.hashed
269 }
270}
271
272#[derive(Debug)]
273pub struct ExportRelease {
274 pub id: i64,
275 pub title: String,
276 pub year: Option<i32>,
277 pub release_type: String,
278 pub artist_names: Vec<String>,
279}
280
281#[derive(Debug)]
282pub struct ExportTrack {
283 pub id: i64,
284 pub title: String,
285 pub year: Option<i32>,
286 pub duration_seconds: f64,
287 pub artist_names: Vec<String>,
288 pub featured_artist_names: Vec<String>,
289 pub release_title: String,
290 pub release_type: String,
291 pub track_number: Option<i32>,
292 pub disc_number: Option<i32>,
293 pub content_id: Option<String>,
294}
295
296#[derive(Debug, Clone)]
297pub struct NetworkArtistPreview {
298 pub artist_key: String,
299 pub name: String,
300 pub image_path: Option<String>,
301 pub release_count: i64,
302 pub track_count: i64,
303}
304
305#[derive(Debug, Clone)]
306pub struct NetworkArtistImageRequest {
307 pub source_id: String,
308 pub artist_key: String,
309 pub name: String,
310}
311
312#[derive(Debug, Clone)]
314pub struct SimilarityTrack {
315 pub id: i64,
316 pub title: String,
317 pub file_path: String,
318 pub content_id: Option<String>,
319 pub duration_seconds: f64,
320}
321
322#[derive(Debug, Clone)]
324pub struct StoredEmbedding {
325 pub track_id: i64,
326 pub vector: Vec<f32>,
327 pub artist_key: String,
328}
329
330#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
331pub struct SimilarityStorageStats {
332 pub total_tracks: usize,
333 pub embedded_tracks: usize,
334 pub stored_vectors: usize,
335 pub stored_bytes: u64,
336}
337
338pub struct Library {
339 conn: Mutex<Connection>,
340 db_path: PathBuf,
341 covers_dir: PathBuf,
343}
344
345#[derive(Debug, Clone, Default, PartialEq, Eq)]
346pub struct LocalLibraryStats {
347 pub artist_count: i64,
348 pub release_count: i64,
349 pub track_count: i64,
350 pub audio_bytes: u64,
351 pub tracks_without_size: i64,
352 pub cover_bytes: u64,
353 pub database_bytes: u64,
354}
355
356#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
357pub struct MusicRelocationStats {
358 pub tracks: usize,
359 pub images: usize,
360}
361
362impl LocalLibraryStats {
363 pub fn total_bytes(&self) -> u64 {
364 self.audio_bytes
365 .saturating_add(self.cover_bytes)
366 .saturating_add(self.database_bytes)
367 }
368}
369
370pub fn default_db_path() -> Result<PathBuf> {
372 let dirs = directories::ProjectDirs::from("", "", "furumi")
373 .context("cannot determine the Furumi data directory")?;
374 Ok(dirs.data_dir().join("library.db"))
375}
376
377impl Library {
378 pub fn open(db_path: &Path) -> Result<Self> {
379 if let Some(parent) = db_path.parent() {
380 std::fs::create_dir_all(parent)
381 .with_context(|| format!("creating {}", parent.display()))?;
382 }
383 let conn = Connection::open(db_path)
384 .with_context(|| format!("opening database {}", db_path.display()))?;
385 conn.busy_timeout(Duration::from_secs(5))?;
386 conn.pragma_update(None, "journal_mode", "WAL")?;
387 conn.pragma_update(None, "foreign_keys", "ON")?;
388 register_norm_function(&conn)?;
389 conn.execute_batch(SCHEMA).context("applying schema")?;
390 ensure_schema_migrations(&conn)?;
391 let covers_dir = db_path
392 .parent()
393 .map(|dir| dir.join("covers"))
394 .unwrap_or_else(|| PathBuf::from("covers"));
395 Ok(Self {
396 conn: Mutex::new(conn),
397 db_path: db_path.to_path_buf(),
398 covers_dir,
399 })
400 }
401
402 pub fn covers_dir(&self) -> &Path {
403 &self.covers_dir
404 }
405
406 pub fn validate_music_directory(path: &Path) -> Result<PathBuf> {
410 anyhow::ensure!(!path.as_os_str().is_empty(), "music directory is empty");
411 std::fs::create_dir_all(path)
412 .with_context(|| format!("creating music directory {}", path.display()))?;
413 let path = std::fs::canonicalize(path)
414 .with_context(|| format!("resolving music directory {}", path.display()))?;
415 anyhow::ensure!(path.is_dir(), "{} is not a directory", path.display());
416
417 let unique = std::time::SystemTime::now()
418 .duration_since(std::time::UNIX_EPOCH)
419 .map(|duration| duration.as_nanos())
420 .unwrap_or(0);
421 let probe = path.join(format!(
422 ".furumi-write-test-{}-{unique}",
423 std::process::id()
424 ));
425 let result = (|| -> Result<()> {
426 let mut file = std::fs::OpenOptions::new()
427 .write(true)
428 .create_new(true)
429 .open(&probe)
430 .with_context(|| format!("no write access to {}", path.display()))?;
431 file.write_all(b"furumi")?;
432 file.sync_all()?;
433 Ok(())
434 })();
435 let _ = std::fs::remove_file(&probe);
436 result?;
437 Ok(path)
438 }
439
440 pub fn relocate_managed_music(
444 &self,
445 old_root: &Path,
446 new_root: &Path,
447 ) -> Result<MusicRelocationStats> {
448 let new_root = Self::validate_music_directory(new_root)?;
449 let old_root = std::fs::canonicalize(old_root)
450 .with_context(|| format!("resolving old music directory {}", old_root.display()))?;
451 anyhow::ensure!(
452 old_root != new_root,
453 "the new music directory is the current directory"
454 );
455 anyhow::ensure!(
456 !old_root.starts_with(&new_root) && !new_root.starts_with(&old_root),
457 "choose a directory outside the current music directory"
458 );
459
460 #[derive(Debug)]
461 struct Row {
462 track_id: i64,
463 track_path: String,
464 release_id: i64,
465 release_title: String,
466 cover_path: Option<String>,
467 artist_id: Option<i64>,
468 artist_name: String,
469 image_path: Option<String>,
470 }
471
472 let rows = {
473 let conn = self.lock();
474 let mut statement = conn.prepare(
475 "SELECT t.id, t.file_path, r.id, r.title, r.cover_path,
476 a.id, COALESCE(a.name, 'Unknown Artist'), a.image_path
477 FROM tracks t
478 JOIN releases r ON r.id = t.release_id
479 LEFT JOIN track_artists ta
480 ON ta.track_id = t.id AND ta.role = 'main' AND ta.position = 0
481 LEFT JOIN artists a ON a.id = ta.artist_id
482 ORDER BY t.id",
483 )?;
484 statement
485 .query_map([], |row| {
486 Ok(Row {
487 track_id: row.get(0)?,
488 track_path: row.get(1)?,
489 release_id: row.get(2)?,
490 release_title: row.get(3)?,
491 cover_path: row.get(4)?,
492 artist_id: row.get(5)?,
493 artist_name: row.get(6)?,
494 image_path: row.get(7)?,
495 })
496 })?
497 .collect::<rusqlite::Result<Vec<_>>>()?
498 };
499
500 #[derive(Debug, Clone)]
501 struct CopyOp {
502 source: PathBuf,
503 destination: PathBuf,
504 }
505 let covers_root =
506 std::fs::canonicalize(&self.covers_dir).unwrap_or_else(|_| self.covers_dir.clone());
507 let managed_art =
508 |path: &Path| path.starts_with(&old_root) || path.starts_with(&covers_root);
509 let mut reserved = HashSet::<PathBuf>::new();
510 let mut copies = Vec::<CopyOp>::new();
511 let mut track_updates = Vec::<(i64, String, String)>::new();
512 let mut release_updates = HashMap::<i64, (String, String)>::new();
513 let mut artist_updates = HashMap::<i64, (String, String)>::new();
514
515 for row in rows {
516 let source = PathBuf::from(&row.track_path);
517 let source = std::fs::canonicalize(&source).unwrap_or(source);
518 if !source.is_file() || !source.starts_with(&old_root) {
519 continue;
520 }
521 let artist_dir = new_root.join(storage_name(&row.artist_name, "Unknown Artist"));
522 let release_dir = artist_dir.join(storage_name(&row.release_title, "Unknown Release"));
523 let filename = source
524 .file_name()
525 .filter(|name| !name.is_empty())
526 .unwrap_or_else(|| std::ffi::OsStr::new("track"));
527 let destination =
528 unique_destination(release_dir.join(filename), row.track_id, &mut reserved);
529 copies.push(CopyOp {
530 source: source.clone(),
531 destination: destination.clone(),
532 });
533 track_updates.push((
534 row.track_id,
535 row.track_path,
536 destination.to_string_lossy().into_owned(),
537 ));
538
539 if let Some(cover) = row.cover_path {
540 let cover_source = PathBuf::from(&cover);
541 let cover_source = std::fs::canonicalize(&cover_source).unwrap_or(cover_source);
542 if cover_source.is_file()
543 && managed_art(&cover_source)
544 && !release_updates.contains_key(&row.release_id)
545 {
546 let extension = cover_source.extension().unwrap_or_default();
547 let mut destination = release_dir.join("cover");
548 destination.set_extension(extension);
549 let destination =
550 unique_destination(destination, row.release_id, &mut reserved);
551 copies.push(CopyOp {
552 source: cover_source,
553 destination: destination.clone(),
554 });
555 release_updates.insert(
556 row.release_id,
557 (cover, destination.to_string_lossy().into_owned()),
558 );
559 }
560 }
561
562 if let (Some(artist_id), Some(image)) = (row.artist_id, row.image_path) {
563 let image_source = PathBuf::from(&image);
564 let image_source = std::fs::canonicalize(&image_source).unwrap_or(image_source);
565 if image_source.is_file()
566 && managed_art(&image_source)
567 && !artist_updates.contains_key(&artist_id)
568 {
569 let extension = image_source.extension().unwrap_or_default();
570 let mut destination = artist_dir.join("artist");
571 destination.set_extension(extension);
572 let destination = unique_destination(destination, artist_id, &mut reserved);
573 copies.push(CopyOp {
574 source: image_source,
575 destination: destination.clone(),
576 });
577 artist_updates.insert(
578 artist_id,
579 (image, destination.to_string_lossy().into_owned()),
580 );
581 }
582 }
583 }
584
585 let mut created = Vec::<PathBuf>::new();
586 let copy_result = (|| -> Result<()> {
587 for op in &copies {
588 let parent = op
589 .destination
590 .parent()
591 .context("music destination has no parent")?;
592 std::fs::create_dir_all(parent)?;
593 copy_file_exclusive(&op.source, &op.destination)?;
594 created.push(op.destination.clone());
595 }
596 Ok(())
597 })();
598 if let Err(error) = copy_result {
599 for path in created.iter().rev() {
600 let _ = std::fs::remove_file(path);
601 }
602 return Err(error.context("copying the existing music library"));
603 }
604
605 let database_result = (|| -> Result<()> {
606 let mut conn = self.lock();
607 let transaction = conn.transaction()?;
608 for (id, old, new) in &track_updates {
609 anyhow::ensure!(
610 transaction.execute(
611 "UPDATE tracks SET file_path = ?3 WHERE id = ?1 AND file_path = ?2",
612 params![id, old, new],
613 )? == 1,
614 "track {id} changed while the music library was moving"
615 );
616 }
617 for (id, (old, new)) in &release_updates {
618 anyhow::ensure!(
619 transaction.execute(
620 "UPDATE releases SET cover_path = ?3 WHERE id = ?1 AND cover_path = ?2",
621 params![id, old, new],
622 )? == 1,
623 "release {id} changed while the music library was moving"
624 );
625 }
626 for (id, (old, new)) in &artist_updates {
627 anyhow::ensure!(
628 transaction.execute(
629 "UPDATE artists SET image_path = ?3 WHERE id = ?1 AND image_path = ?2",
630 params![id, old, new],
631 )? == 1,
632 "artist {id} changed while the music library was moving"
633 );
634 }
635 transaction.commit()?;
636 Ok(())
637 })();
638 if let Err(error) = database_result {
639 for path in created.iter().rev() {
640 let _ = std::fs::remove_file(path);
641 }
642 return Err(error.context("updating music paths in the library"));
643 }
644
645 let mut removed = HashSet::new();
646 for op in &copies {
647 if removed.insert(op.source.clone())
648 && let Err(error) = std::fs::remove_file(&op.source)
649 {
650 tracing::warn!(path = %op.source.display(), %error, "old music copy was not removed");
653 }
654 }
655 remove_empty_directories(&old_root);
656
657 Ok(MusicRelocationStats {
658 tracks: track_updates.len(),
659 images: release_updates.len() + artist_updates.len(),
660 })
661 }
662
663 pub fn local_stats(&self) -> Result<LocalLibraryStats> {
664 let (artist_count, release_count, track_count, audio_bytes, tracks_without_size) = {
665 let conn = self.lock();
666 conn.query_row(
667 "SELECT
668 (SELECT COUNT(*) FROM artists),
669 (SELECT COUNT(*) FROM releases),
670 (SELECT COUNT(*) FROM tracks),
671 (SELECT COALESCE(SUM(CASE
672 WHEN file_size_bytes IS NOT NULL AND file_size_bytes >= 0
673 THEN file_size_bytes ELSE 0 END), 0) FROM tracks),
674 (SELECT COUNT(*) FROM tracks WHERE file_size_bytes IS NULL)
675 ",
676 [],
677 |row| {
678 Ok((
679 row.get::<_, i64>(0)?,
680 row.get::<_, i64>(1)?,
681 row.get::<_, i64>(2)?,
682 row.get::<_, i64>(3)?,
683 row.get::<_, i64>(4)?,
684 ))
685 },
686 )?
687 };
688 Ok(LocalLibraryStats {
689 artist_count,
690 release_count,
691 track_count,
692 audio_bytes: audio_bytes.max(0) as u64,
693 tracks_without_size,
694 cover_bytes: directory_size(&self.covers_dir),
695 database_bytes: sqlite_database_size(&self.db_path),
696 })
697 }
698
699 pub fn backfill_missing_content_ids(&self) -> Result<ContentIdBackfillStats> {
706 let rows = {
707 let conn = self.lock();
708 let mut statement =
709 conn.prepare("SELECT id, content_id, file_path FROM tracks ORDER BY id")?;
710 statement
711 .query_map([], |row| {
712 Ok((
713 row.get::<_, i64>(0)?,
714 row.get::<_, Option<String>>(1)?,
715 row.get::<_, String>(2)?,
716 ))
717 })?
718 .collect::<rusqlite::Result<Vec<_>>>()?
719 };
720
721 let mut stats = ContentIdBackfillStats {
722 checked: rows.len(),
723 ..ContentIdBackfillStats::default()
724 };
725 let mut updates = Vec::new();
726 for (track_id, raw_content_id, file_path) in rows {
727 if let Some(normalized) = raw_content_id
728 .as_deref()
729 .and_then(music_dht::normalize_content_id)
730 {
731 if raw_content_id.as_deref() != Some(normalized.as_str()) {
732 updates.push((track_id, normalized));
733 stats.normalized += 1;
734 }
735 } else if let Some(content_id) = audio_content_id(&file_path) {
736 updates.push((track_id, content_id));
737 stats.hashed += 1;
738 } else {
739 stats.failed += 1;
740 tracing::warn!(
741 track_id,
742 path = %file_path,
743 "content id backfill skipped an unreadable track"
744 );
745 }
746
747 if updates.len() >= 64 {
748 self.write_content_id_updates(&mut updates)?;
749 }
750 }
751 self.write_content_id_updates(&mut updates)?;
752 Ok(stats)
753 }
754
755 fn write_content_id_updates(&self, updates: &mut Vec<(i64, String)>) -> Result<()> {
756 if updates.is_empty() {
757 return Ok(());
758 }
759 let mut conn = self.lock();
760 let tx = conn.transaction()?;
761 for (track_id, content_id) in updates.drain(..) {
762 tx.execute(
763 "UPDATE tracks SET content_id = ?2 WHERE id = ?1",
764 params![track_id, content_id],
765 )?;
766 }
767 tx.commit()?;
768 Ok(())
769 }
770
771 fn lock(&self) -> std::sync::MutexGuard<'_, Connection> {
772 self.conn.lock().unwrap_or_else(|poisoned| {
773 poisoned.into_inner()
775 })
776 }
777
778 pub fn artists(&self, page: i64, limit: i64, filters: LibraryFilters) -> Result<ArtistsPage> {
783 if !filters.source_mode.includes_network() {
784 return self.local_artists(page, limit, filters.hide_featured_only);
785 }
786 self.merged_artists(page, limit, filters)
787 }
788
789 fn local_artists(
790 &self,
791 page: i64,
792 limit: i64,
793 hide_featured_only: bool,
794 ) -> Result<ArtistsPage> {
795 let conn = self.lock();
796 let hide_featured_only = i64::from(hide_featured_only);
797 let total: i64 = conn.query_row(
798 "SELECT COUNT(*)
799 FROM artists a
800 WHERE ?1 = 0
801 OR EXISTS (
802 SELECT 1
803 FROM release_artists ra
804 WHERE ra.artist_id = a.id
805 )",
806 [hide_featured_only],
807 |row| row.get(0),
808 )?;
809 let offset = (page.max(1) - 1) * limit;
810 let mut statement = conn.prepare(
811 "SELECT a.id, a.name, a.image_path,
812 (SELECT COUNT(DISTINCT ra.release_id)
813 FROM release_artists ra
814 WHERE ra.artist_id = a.id) AS release_count,
815 (SELECT COUNT(DISTINCT ta.track_id)
816 FROM track_artists ta
817 WHERE ta.artist_id = a.id) AS track_count
818 FROM artists a
819 WHERE ?1 = 0
820 OR EXISTS (
821 SELECT 1
822 FROM release_artists ra
823 WHERE ra.artist_id = a.id
824 )
825 ORDER BY release_count DESC, track_count DESC, a.name COLLATE NOCASE
826 LIMIT ?2 OFFSET ?3",
827 )?;
828 let items = statement
829 .query_map(params![hide_featured_only, limit, offset], |row| {
830 Ok(ArtistCard {
831 id: row.get(0)?,
832 name: row.get(1)?,
833 image_path: row.get(2)?,
834 release_count: row.get(3)?,
835 track_count: row.get(4)?,
836 availability: Availability::Local,
837 })
838 })?
839 .collect::<rusqlite::Result<Vec<_>>>()?;
840 let has_more = offset + (items.len() as i64) < total;
841 Ok(ArtistsPage {
842 has_more,
843 items,
844 total,
845 page: page.max(1),
846 })
847 }
848
849 fn local_artist_cards(&self, hide_featured_only: bool) -> Result<Vec<ArtistCard>> {
850 let conn = self.lock();
851 let hide_featured_only = i64::from(hide_featured_only);
852 let mut statement = conn.prepare(
853 "SELECT a.id, a.name, a.image_path,
854 (SELECT COUNT(DISTINCT ra.release_id)
855 FROM release_artists ra
856 WHERE ra.artist_id = a.id) AS release_count,
857 (SELECT COUNT(DISTINCT ta.track_id)
858 FROM track_artists ta
859 WHERE ta.artist_id = a.id) AS track_count
860 FROM artists a
861 WHERE ?1 = 0
862 OR EXISTS (
863 SELECT 1
864 FROM release_artists ra
865 WHERE ra.artist_id = a.id
866 )",
867 )?;
868 statement
869 .query_map(params![hide_featured_only], |row| {
870 Ok(ArtistCard {
871 id: row.get(0)?,
872 name: row.get(1)?,
873 image_path: row.get(2)?,
874 release_count: row.get(3)?,
875 track_count: row.get(4)?,
876 availability: Availability::Local,
877 })
878 })?
879 .collect::<rusqlite::Result<Vec<_>>>()
880 .map_err(Into::into)
881 }
882
883 fn merged_artists(
884 &self,
885 page: i64,
886 limit: i64,
887 filters: LibraryFilters,
888 ) -> Result<ArtistsPage> {
889 let mut by_key: HashMap<String, ArtistCard> = HashMap::new();
890 for artist in self.local_artist_cards(filters.hide_featured_only)? {
891 let key = music_dht::normalize_name(&artist.name);
892 if !key.is_empty() {
893 by_key.insert(key, artist);
894 }
895 }
896
897 let conn = self.lock();
898 let cutoff = now_ms_i64().saturating_sub(NETWORK_ARTIST_CACHE_TTL_MS);
899 let source_predicate = if filters.source_mode.includes_global_peers() {
900 "seen_at_ms >= ?1"
901 } else {
902 "seen_at_ms >= ?1 AND source_kind = 'personal'"
903 };
904 let release_predicate = if filters.hide_featured_only {
905 " AND release_count > 0"
906 } else {
907 ""
908 };
909 let sql = format!(
910 "SELECT artist_key,
911 COALESCE(NULLIF(MIN(name), ''), artist_key) AS name,
912 MAX(image_path) AS image_path,
913 MAX(release_count) AS release_count,
914 MAX(track_count) AS track_count
915 FROM network_artist_cache
916 WHERE {source_predicate}{release_predicate}
917 GROUP BY artist_key"
918 );
919 let mut statement = conn.prepare(&sql)?;
920 let rows = statement.query_map([cutoff], |row| {
921 Ok((
922 row.get::<_, String>(0)?,
923 row.get::<_, String>(1)?,
924 row.get::<_, Option<String>>(2)?,
925 row.get::<_, i64>(3)?,
926 row.get::<_, i64>(4)?,
927 ))
928 })?;
929 for row in rows {
930 let (artist_key, name, image_path, release_count, track_count) = row?;
931 if artist_key.trim().is_empty() || track_count <= 0 {
932 continue;
933 }
934 match by_key.get_mut(&artist_key) {
935 Some(local) => {
936 if release_count > local.release_count || track_count > local.track_count {
937 local.availability = Availability::Mixed;
938 }
939 local.release_count = local.release_count.max(release_count);
940 local.track_count = local.track_count.max(track_count);
941 if local.image_path.is_none() {
942 local.image_path = image_path;
943 }
944 }
945 None => {
946 by_key.insert(
947 artist_key.clone(),
948 ArtistCard {
949 id: remote_artist_id(&artist_key),
950 name,
951 image_path,
952 release_count,
953 track_count,
954 availability: Availability::Remote,
955 },
956 );
957 }
958 }
959 }
960
961 let mut items: Vec<ArtistCard> = by_key.into_values().collect();
962 items.sort_by(|left, right| {
963 right
964 .release_count
965 .cmp(&left.release_count)
966 .then_with(|| right.track_count.cmp(&left.track_count))
967 .then_with(|| left.name.to_lowercase().cmp(&right.name.to_lowercase()))
968 });
969 let total = items.len() as i64;
970 let offset = (page.max(1) - 1) * limit;
971 let page_items = items
972 .into_iter()
973 .skip(offset.max(0) as usize)
974 .take(limit.max(0) as usize)
975 .collect::<Vec<_>>();
976 let has_more = offset + (page_items.len() as i64) < total;
977 Ok(ArtistsPage {
978 items: page_items,
979 total,
980 page: page.max(1),
981 has_more,
982 })
983 }
984
985 pub fn artist_preview_slice(
986 &self,
987 offset: usize,
988 limit: usize,
989 ) -> Result<(Vec<NetworkArtistPreview>, Option<String>)> {
990 let conn = self.lock();
991 let mut statement = conn.prepare(
992 "SELECT a.name, a.image_path,
993 (SELECT COUNT(DISTINCT ra.release_id)
994 FROM release_artists ra
995 WHERE ra.artist_id = a.id) AS release_count,
996 (SELECT COUNT(DISTINCT ta.track_id)
997 FROM track_artists ta
998 WHERE ta.artist_id = a.id) AS track_count
999 FROM artists a
1000 WHERE EXISTS (
1001 SELECT 1 FROM track_artists ta WHERE ta.artist_id = a.id
1002 )
1003 ORDER BY release_count DESC, track_count DESC, a.name COLLATE NOCASE
1004 LIMIT ?1 OFFSET ?2",
1005 )?;
1006 let items = statement
1007 .query_map(params![limit as i64 + 1, offset as i64], |row| {
1008 let name: String = row.get(0)?;
1009 Ok(NetworkArtistPreview {
1010 artist_key: music_dht::normalize_name(&name),
1011 name,
1012 image_path: row.get(1)?,
1013 release_count: row.get(2)?,
1014 track_count: row.get(3)?,
1015 })
1016 })?
1017 .collect::<rusqlite::Result<Vec<_>>>()?;
1018 let has_more = items.len() > limit;
1019 let mut items: Vec<_> = items
1020 .into_iter()
1021 .take(limit)
1022 .filter(|artist| !artist.artist_key.is_empty() && artist.track_count > 0)
1023 .collect();
1024 let next = has_more.then(|| (offset + items.len()).to_string());
1025 Ok((std::mem::take(&mut items), next))
1026 }
1027
1028 pub fn replace_network_artist_cache(
1029 &self,
1030 source_id: &str,
1031 source_kind: &str,
1032 artists: &[NetworkArtistPreview],
1033 _replace_source: bool,
1034 ) -> Result<usize> {
1035 let source_id = source_id.trim();
1036 let source_kind = source_kind.trim();
1037 if source_id.is_empty() || source_kind.is_empty() {
1038 return Ok(0);
1039 }
1040 let now = now_ms_i64();
1041 let mut conn = self.lock();
1042 let tx = conn.transaction()?;
1043 let mut inserted = 0usize;
1044 {
1045 let mut stmt = tx.prepare(
1046 "INSERT INTO network_artist_cache
1047 (source_id, source_kind, artist_key, name, image_path, remote_image_hint,
1048 release_count, track_count, seen_at_ms)
1049 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1050 ON CONFLICT(source_id, artist_key) DO UPDATE SET
1051 source_kind = excluded.source_kind,
1052 name = excluded.name,
1053 image_path = COALESCE(network_artist_cache.image_path, excluded.image_path),
1054 remote_image_hint = excluded.remote_image_hint,
1055 release_count = excluded.release_count,
1056 track_count = excluded.track_count,
1057 seen_at_ms = excluded.seen_at_ms",
1058 )?;
1059 for artist in artists {
1060 let artist_key = if artist.artist_key.trim().is_empty() {
1061 music_dht::normalize_name(&artist.name)
1062 } else {
1063 artist.artist_key.clone()
1064 };
1065 if artist_key.is_empty() || artist.track_count <= 0 {
1066 continue;
1067 }
1068 stmt.execute(params![
1069 source_id,
1070 source_kind,
1071 artist_key,
1072 artist.name.trim(),
1073 Option::<&str>::None,
1074 artist.image_path.as_deref(),
1075 artist.release_count.max(0),
1076 artist.track_count.max(0),
1077 now,
1078 ])?;
1079 inserted += 1;
1080 }
1081 }
1082 tx.commit()?;
1083 Ok(inserted)
1084 }
1085
1086 pub fn network_artist_image_requests(
1087 &self,
1088 filters: LibraryFilters,
1089 artist_names: &[String],
1090 limit: usize,
1091 ) -> Result<Vec<NetworkArtistImageRequest>> {
1092 if !filters.source_mode.includes_network() || artist_names.is_empty() || limit == 0 {
1093 return Ok(Vec::new());
1094 }
1095 let conn = self.lock();
1096 let cutoff = now_ms_i64().saturating_sub(NETWORK_ARTIST_CACHE_TTL_MS);
1097 let source_predicate = if filters.source_mode.includes_global_peers() {
1098 "seen_at_ms >= ?2"
1099 } else {
1100 "seen_at_ms >= ?2 AND source_kind = 'personal'"
1101 };
1102 let sql = format!(
1103 "SELECT source_id, artist_key, name
1104 FROM network_artist_cache
1105 WHERE artist_key = ?1
1106 AND {source_predicate}
1107 AND image_path IS NULL
1108 AND remote_image_hint IS NOT NULL
1109 AND remote_image_hint <> ''
1110 ORDER BY CASE source_kind WHEN 'personal' THEN 0 ELSE 1 END,
1111 seen_at_ms DESC
1112 LIMIT 1"
1113 );
1114 let mut statement = conn.prepare(&sql)?;
1115 let mut seen_keys = HashSet::new();
1116 let mut requests = Vec::new();
1117 for name in artist_names {
1118 let artist_key = music_dht::normalize_name(name);
1119 if artist_key.is_empty() || !seen_keys.insert(artist_key.clone()) {
1120 continue;
1121 }
1122 let row = statement
1123 .query_row(params![artist_key, cutoff], |row| {
1124 Ok(NetworkArtistImageRequest {
1125 source_id: row.get(0)?,
1126 artist_key: row.get(1)?,
1127 name: row.get(2)?,
1128 })
1129 })
1130 .optional()?;
1131 if let Some(request) = row {
1132 requests.push(request);
1133 if requests.len() >= limit {
1134 break;
1135 }
1136 }
1137 }
1138 Ok(requests)
1139 }
1140
1141 pub fn set_network_artist_image(
1142 &self,
1143 source_id: &str,
1144 artist_key: &str,
1145 image_path: &str,
1146 ) -> Result<bool> {
1147 let changed = self.lock().execute(
1148 "UPDATE network_artist_cache
1149 SET image_path = ?3
1150 WHERE source_id = ?1 AND artist_key = ?2",
1151 params![source_id, artist_key, image_path],
1152 )?;
1153 Ok(changed > 0)
1154 }
1155
1156 pub fn artist(&self, id: i64) -> Result<ArtistDetail> {
1157 let conn = self.lock();
1158 let (name, image_path): (String, Option<String>) = conn
1159 .query_row(
1160 "SELECT name, image_path FROM artists WHERE id = ?1",
1161 [id],
1162 |row| Ok((row.get(0)?, row.get(1)?)),
1163 )
1164 .context("artist not found")?;
1165 let total_track_count: i64 = conn.query_row(
1166 "SELECT COUNT(*) FROM track_artists WHERE artist_id = ?1 AND role = 'main'",
1167 [id],
1168 |row| row.get(0),
1169 )?;
1170 let total_play_count: i64 = conn.query_row(
1171 "SELECT
1172 (SELECT COUNT(*) FROM history h
1173 WHERE h.completed = 1 AND h.track_id IN
1174 (SELECT track_id FROM track_artists WHERE artist_id = ?1))
1175 +
1176 (SELECT COUNT(*) FROM listen_events le
1177 WHERE le.qualified = 1 AND le.local_track_id IN
1178 (SELECT track_id FROM track_artists WHERE artist_id = ?1))",
1179 [id],
1180 |row| row.get(0),
1181 )?;
1182 let top_tracks = query_tracks(
1183 &conn,
1184 &format!(
1185 "SELECT {TRACK_COLUMNS} FROM tracks t
1186 JOIN releases r ON r.id = t.release_id
1187 JOIN track_artists ta ON ta.track_id = t.id
1188 WHERE ta.artist_id = ?1 AND ta.role = 'main'
1189 ORDER BY 17 DESC, t.title COLLATE NOCASE
1190 LIMIT 10"
1191 ),
1192 params![id],
1193 )?;
1194 let featured_tracks = query_tracks(
1195 &conn,
1196 &format!(
1197 "SELECT {TRACK_COLUMNS} FROM tracks t
1198 JOIN releases r ON r.id = t.release_id
1199 JOIN track_artists ta ON ta.track_id = t.id
1200 WHERE ta.artist_id = ?1 AND ta.role = 'featured'
1201 ORDER BY t.title COLLATE NOCASE"
1202 ),
1203 params![id],
1204 )?;
1205 let mut statement = conn.prepare(
1206 "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1207 (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1208 FROM releases r
1209 JOIN release_artists ra ON ra.release_id = r.id
1210 WHERE ra.artist_id = ?1
1211 ORDER BY r.year IS NULL, r.year DESC, r.title COLLATE NOCASE",
1212 )?;
1213 let releases = statement
1214 .query_map([id], release_card_from_row)?
1215 .collect::<rusqlite::Result<Vec<_>>>()?;
1216 Ok(ArtistDetail {
1217 id,
1218 name,
1219 image_path,
1220 total_track_count,
1221 total_play_count,
1222 top_tracks,
1223 releases,
1224 featured_tracks,
1225 })
1226 }
1227
1228 pub fn releases(&self) -> Result<Vec<ReleaseCard>> {
1230 let conn = self.lock();
1231 let mut statement = conn.prepare(
1232 "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1233 (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1234 FROM releases r
1235 ORDER BY r.year IS NULL, r.year DESC, r.title COLLATE NOCASE, r.id",
1236 )?;
1237 Ok(statement
1238 .query_map([], release_card_from_row)?
1239 .collect::<rusqlite::Result<Vec<_>>>()?)
1240 }
1241
1242 pub fn release(&self, id: i64) -> Result<ReleaseDetail> {
1243 let conn = self.lock();
1244 let (title, release_type, year, cover_path) = conn
1245 .query_row(
1246 "SELECT title, release_type, year, cover_path FROM releases WHERE id = ?1",
1247 [id],
1248 |row| {
1249 Ok((
1250 row.get::<_, String>(0)?,
1251 row.get::<_, String>(1)?,
1252 row.get::<_, Option<i32>>(2)?,
1253 row.get::<_, Option<String>>(3)?,
1254 ))
1255 },
1256 )
1257 .context("release not found")?;
1258 let mut statement = conn.prepare(
1259 "SELECT a.id, a.name FROM artists a
1260 JOIN release_artists ra ON ra.artist_id = a.id
1261 WHERE ra.release_id = ?1 ORDER BY ra.position",
1262 )?;
1263 let artists = statement
1264 .query_map([id], |row| {
1265 Ok(ArtistRef {
1266 id: row.get(0)?,
1267 name: row.get(1)?,
1268 })
1269 })?
1270 .collect::<rusqlite::Result<Vec<_>>>()?;
1271 let tracks = query_tracks(
1272 &conn,
1273 &format!(
1274 "SELECT {TRACK_COLUMNS} FROM tracks t
1275 JOIN releases r ON r.id = t.release_id
1276 WHERE t.release_id = ?1
1277 ORDER BY t.disc_number IS NULL, t.disc_number,
1278 t.track_number IS NULL, t.track_number,
1279 t.title COLLATE NOCASE"
1280 ),
1281 params![id],
1282 )?;
1283 Ok(ReleaseDetail {
1284 id,
1285 title,
1286 release_type,
1287 year,
1288 cover_path,
1289 artists,
1290 tracks,
1291 })
1292 }
1293
1294 pub fn search(&self, query: &str, limit: i64) -> Result<SearchResults> {
1295 let query = query.trim();
1296 if query.is_empty() {
1297 return Ok(SearchResults::default());
1298 }
1299 let pattern = music_dht::normalize_name(query);
1302 if pattern.is_empty() {
1303 return Ok(SearchResults::default());
1305 }
1306 let conn = self.lock();
1307 let mut statement = conn.prepare(
1308 "SELECT a.id, a.name, a.image_path,
1309 (SELECT COUNT(DISTINCT ra.release_id)
1310 FROM release_artists ra
1311 WHERE ra.artist_id = a.id),
1312 (SELECT COUNT(DISTINCT ta.track_id)
1313 FROM track_artists ta
1314 WHERE ta.artist_id = a.id)
1315 FROM artists a WHERE instr(norm(a.name), ?1) > 0
1316 ORDER BY a.name COLLATE NOCASE LIMIT ?2",
1317 )?;
1318 let artists = statement
1319 .query_map(params![pattern, limit], |row| {
1320 Ok(ArtistCard {
1321 id: row.get(0)?,
1322 name: row.get(1)?,
1323 image_path: row.get(2)?,
1324 release_count: row.get(3)?,
1325 track_count: row.get(4)?,
1326 availability: Availability::Local,
1327 })
1328 })?
1329 .collect::<rusqlite::Result<Vec<_>>>()?;
1330 let mut statement = conn.prepare(
1331 "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1332 (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1333 FROM releases r WHERE instr(norm(r.title), ?1) > 0
1334 ORDER BY r.title COLLATE NOCASE LIMIT ?2",
1335 )?;
1336 let releases = statement
1337 .query_map(params![pattern, limit], release_card_from_row)?
1338 .collect::<rusqlite::Result<Vec<_>>>()?;
1339 let tracks = query_tracks(
1340 &conn,
1341 &format!(
1342 "SELECT {TRACK_COLUMNS} FROM tracks t
1343 JOIN releases r ON r.id = t.release_id
1344 WHERE instr(norm(t.title), ?1) > 0
1345 ORDER BY t.title COLLATE NOCASE LIMIT ?2"
1346 ),
1347 params![pattern, limit],
1348 )?;
1349 let mut results = SearchResults {
1350 artists,
1351 releases,
1352 tracks,
1353 };
1354 rank_search_results(&mut results, &pattern);
1355 Ok(results)
1356 }
1357
1358 pub fn federation_export(&self) -> Result<FederationExport> {
1361 let conn = self.lock();
1362 let mut statement = conn.prepare("SELECT id, name FROM artists")?;
1363 let artists = statement
1364 .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
1365 .collect::<rusqlite::Result<Vec<(i64, String)>>>()?;
1366
1367 let mut statement = conn.prepare(
1368 "SELECT ra.release_id, a.name FROM release_artists ra
1369 JOIN artists a ON a.id = ra.artist_id
1370 ORDER BY ra.release_id, ra.position",
1371 )?;
1372 let mut release_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1373 for row in statement.query_map([], |row| {
1374 Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
1375 })? {
1376 let (id, name) = row?;
1377 release_artists.entry(id).or_default().push(name);
1378 }
1379 let mut statement = conn.prepare("SELECT id, title, year, release_type FROM releases")?;
1380 let releases = statement
1381 .query_map([], |row| {
1382 Ok(ExportRelease {
1383 id: row.get(0)?,
1384 title: row.get(1)?,
1385 year: row.get(2)?,
1386 release_type: row.get(3)?,
1387 artist_names: Vec::new(),
1388 })
1389 })?
1390 .collect::<rusqlite::Result<Vec<_>>>()?
1391 .into_iter()
1392 .map(|mut release| {
1393 release.artist_names = release_artists.remove(&release.id).unwrap_or_default();
1394 release
1395 })
1396 .collect();
1397
1398 let mut statement = conn.prepare(
1399 "SELECT ta.track_id, a.name, ta.role FROM track_artists ta
1400 JOIN artists a ON a.id = ta.artist_id
1401 WHERE ta.role IN ('main', 'featured')
1402 ORDER BY ta.track_id,
1403 CASE ta.role WHEN 'main' THEN 0 ELSE 1 END,
1404 ta.position",
1405 )?;
1406 let mut track_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1407 let mut featured_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1408 for row in statement.query_map([], |row| {
1409 Ok((
1410 row.get::<_, i64>(0)?,
1411 row.get::<_, String>(1)?,
1412 row.get::<_, String>(2)?,
1413 ))
1414 })? {
1415 let (id, name, role) = row?;
1416 if role == "featured" {
1417 featured_artists.entry(id).or_default().push(name);
1418 } else {
1419 track_artists.entry(id).or_default().push(name);
1420 }
1421 }
1422 let mut statement = conn.prepare(
1423 "SELECT t.id, t.title, r.year, t.duration_seconds,
1424 r.title, r.release_type, t.track_number, t.disc_number,
1425 t.content_id, t.file_path
1426 FROM tracks t JOIN releases r ON r.id = t.release_id",
1427 )?;
1428 let track_rows = statement
1429 .query_map([], |row| {
1430 Ok((
1431 ExportTrack {
1432 id: row.get(0)?,
1433 title: row.get(1)?,
1434 year: row.get(2)?,
1435 duration_seconds: row.get(3)?,
1436 artist_names: Vec::new(),
1437 featured_artist_names: Vec::new(),
1438 release_title: row.get(4)?,
1439 release_type: row.get(5)?,
1440 track_number: row.get(6)?,
1441 disc_number: row.get(7)?,
1442 content_id: None,
1443 },
1444 row.get::<_, Option<String>>(8)?,
1445 row.get::<_, String>(9)?,
1446 ))
1447 })?
1448 .collect::<rusqlite::Result<Vec<_>>>()?;
1449 drop(statement);
1450 let mut tracks = Vec::with_capacity(track_rows.len());
1451 for (mut track, raw_content_id, file_path) in track_rows {
1452 let mut content_id = raw_content_id
1453 .as_deref()
1454 .and_then(music_dht::normalize_content_id);
1455 if content_id.is_none() {
1456 content_id = audio_content_id(&file_path);
1457 }
1458 if content_id != raw_content_id {
1459 conn.execute(
1460 "UPDATE tracks SET content_id = ?2 WHERE id = ?1",
1461 params![track.id, content_id.as_deref()],
1462 )?;
1463 }
1464 track.artist_names = track_artists.remove(&track.id).unwrap_or_default();
1465 track.featured_artist_names = featured_artists.remove(&track.id).unwrap_or_default();
1466 track.content_id = content_id;
1467 tracks.push(track);
1468 }
1469
1470 Ok(FederationExport {
1471 artists,
1472 releases,
1473 tracks,
1474 })
1475 }
1476
1477 pub fn tracks_by_ids(&self, ids: &[i64]) -> Result<Vec<TrackItem>> {
1478 let conn = self.lock();
1479 let mut tracks = Vec::with_capacity(ids.len());
1480 for &id in ids {
1481 let mut found = query_tracks(
1482 &conn,
1483 &format!(
1484 "SELECT {TRACK_COLUMNS} FROM tracks t
1485 JOIN releases r ON r.id = t.release_id
1486 WHERE t.id = ?1"
1487 ),
1488 params![id],
1489 )?;
1490 tracks.append(&mut found);
1491 }
1492 Ok(tracks)
1493 }
1494
1495 pub fn track_by_content_id(&self, content_id: &str) -> Result<Option<TrackItem>> {
1496 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
1497 return Ok(None);
1498 };
1499 let conn = self.lock();
1500 let mut tracks = query_tracks(
1501 &conn,
1502 &format!(
1503 "SELECT {TRACK_COLUMNS} FROM tracks t
1504 JOIN releases r ON r.id = t.release_id
1505 WHERE t.content_id = ?1
1506 LIMIT 1"
1507 ),
1508 params![content_id],
1509 )?;
1510 Ok(tracks.pop())
1511 }
1512
1513 pub fn ensure_similarity_profile(
1519 &self,
1520 profile_id: &str,
1521 model_id: &str,
1522 model_version: &str,
1523 model_sha256: &str,
1524 preprocessing: &str,
1525 dimensions: usize,
1526 ) -> Result<()> {
1527 let conn = self.lock();
1528 conn.execute(
1529 "INSERT INTO similarity_profiles (
1530 profile_id, model_id, model_version, model_sha256,
1531 preprocessing, dimensions, created_at_ms
1532 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1533 ON CONFLICT(profile_id) DO NOTHING",
1534 params![
1535 profile_id,
1536 model_id,
1537 model_version,
1538 model_sha256,
1539 preprocessing,
1540 dimensions as i64,
1541 now_ms_i64(),
1542 ],
1543 )?;
1544 Ok(())
1545 }
1546
1547 pub fn pending_similarity_tracks(&self, profile_id: &str) -> Result<Vec<SimilarityTrack>> {
1548 let conn = self.lock();
1549 let mut statement = conn.prepare(
1550 "SELECT t.id, t.title, t.file_path, t.content_id, t.duration_seconds
1551 FROM tracks t
1552 WHERE NOT EXISTS (
1553 SELECT 1 FROM track_embeddings e
1554 WHERE e.track_id = t.id
1555 AND e.profile_id = ?1
1556 AND e.source_content_id IS t.content_id
1557 )
1558 ORDER BY t.id",
1559 )?;
1560 Ok(statement
1561 .query_map([profile_id], |row| {
1562 Ok(SimilarityTrack {
1563 id: row.get(0)?,
1564 title: row.get(1)?,
1565 file_path: row.get(2)?,
1566 content_id: row.get(3)?,
1567 duration_seconds: row.get(4)?,
1568 })
1569 })?
1570 .collect::<rusqlite::Result<Vec<_>>>()?)
1571 }
1572
1573 pub fn store_similarity_embedding(
1574 &self,
1575 track: &SimilarityTrack,
1576 profile_id: &str,
1577 vector: &[f32],
1578 ) -> Result<()> {
1579 anyhow::ensure!(!vector.is_empty(), "embedding vector is empty");
1580 anyhow::ensure!(
1581 vector.iter().all(|value| value.is_finite()),
1582 "embedding contains a non-finite value"
1583 );
1584 let bytes = embedding_to_bytes(vector);
1585 let routing_signature = music_dht::similarity_lsh::routing_signature(vector)?;
1586 let conn = self.lock();
1587 conn.execute(
1588 "INSERT INTO track_embeddings (
1589 track_id, profile_id, dimensions, vector, routing_signature,
1590 source_content_id, computed_at_ms
1591 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1592 ON CONFLICT(track_id, profile_id) DO UPDATE SET
1593 dimensions = excluded.dimensions,
1594 vector = excluded.vector,
1595 routing_signature = excluded.routing_signature,
1596 source_content_id = excluded.source_content_id,
1597 computed_at_ms = excluded.computed_at_ms",
1598 params![
1599 track.id,
1600 profile_id,
1601 vector.len() as i64,
1602 bytes,
1603 routing_signature.as_slice(),
1604 track.content_id.as_deref(),
1605 now_ms_i64(),
1606 ],
1607 )?;
1608 Ok(())
1609 }
1610
1611 pub fn similarity_embedding(
1612 &self,
1613 track_id: i64,
1614 profile_id: &str,
1615 ) -> Result<Option<Vec<f32>>> {
1616 let conn = self.lock();
1617 let row = conn
1618 .query_row(
1619 "SELECT e.dimensions, e.vector
1620 FROM track_embeddings e
1621 JOIN tracks t ON t.id = e.track_id
1622 WHERE e.track_id = ?1
1623 AND e.profile_id = ?2
1624 AND e.source_content_id IS t.content_id",
1625 params![track_id, profile_id],
1626 |row| Ok((row.get::<_, i64>(0)?, row.get::<_, Vec<u8>>(1)?)),
1627 )
1628 .optional()?;
1629 row.map(|(dimensions, bytes)| embedding_from_bytes(dimensions, &bytes))
1630 .transpose()
1631 }
1632
1633 pub fn load_similarity_index(&self, profile_id: &str) -> Result<Vec<StoredEmbedding>> {
1634 let conn = self.lock();
1635 let mut statement = conn.prepare(
1636 "SELECT e.track_id, e.dimensions, e.vector,
1637 COALESCE((
1638 SELECT norm(a.name)
1639 FROM track_artists ta
1640 JOIN artists a ON a.id = ta.artist_id
1641 WHERE ta.track_id = e.track_id AND ta.role = 'main'
1642 ORDER BY ta.position LIMIT 1
1643 ), '')
1644 FROM track_embeddings e
1645 JOIN tracks t ON t.id = e.track_id
1646 WHERE e.profile_id = ?1
1647 AND e.source_content_id IS t.content_id
1648 ORDER BY e.track_id",
1649 )?;
1650 let rows = statement.query_map([profile_id], |row| {
1651 Ok((
1652 row.get::<_, i64>(0)?,
1653 row.get::<_, i64>(1)?,
1654 row.get::<_, Vec<u8>>(2)?,
1655 row.get::<_, String>(3)?,
1656 ))
1657 })?;
1658 let mut embeddings = Vec::new();
1659 for row in rows {
1660 let (track_id, dimensions, bytes, artist_key) = row?;
1661 embeddings.push(StoredEmbedding {
1662 track_id,
1663 vector: embedding_from_bytes(dimensions, &bytes)?,
1664 artist_key,
1665 });
1666 }
1667 Ok(embeddings)
1668 }
1669
1670 pub fn similarity_routing_signatures(&self, profile_id: &str) -> Result<Vec<[u8; 32]>> {
1674 let mut conn = self.lock();
1675 let transaction = conn.transaction()?;
1676 let missing = {
1677 let mut statement = transaction.prepare(
1678 "SELECT e.track_id, e.dimensions, e.vector
1679 FROM track_embeddings e
1680 JOIN tracks t ON t.id = e.track_id
1681 WHERE e.profile_id = ?1
1682 AND e.source_content_id IS t.content_id
1683 AND (e.routing_signature IS NULL OR length(e.routing_signature) != 32)
1684 ORDER BY e.track_id",
1685 )?;
1686 statement
1687 .query_map([profile_id], |row| {
1688 Ok((
1689 row.get::<_, i64>(0)?,
1690 row.get::<_, i64>(1)?,
1691 row.get::<_, Vec<u8>>(2)?,
1692 ))
1693 })?
1694 .collect::<rusqlite::Result<Vec<_>>>()?
1695 };
1696 for (track_id, dimensions, vector_bytes) in missing {
1697 let vector = embedding_from_bytes(dimensions, &vector_bytes)?;
1698 let signature = music_dht::similarity_lsh::routing_signature(&vector)?;
1699 transaction.execute(
1700 "UPDATE track_embeddings
1701 SET routing_signature = ?3
1702 WHERE track_id = ?1 AND profile_id = ?2",
1703 params![track_id, profile_id, signature.as_slice()],
1704 )?;
1705 }
1706 transaction.commit()?;
1707
1708 let mut statement = conn.prepare(
1709 "SELECT e.routing_signature
1710 FROM track_embeddings e
1711 JOIN tracks t ON t.id = e.track_id
1712 WHERE e.profile_id = ?1
1713 AND e.source_content_id IS t.content_id
1714 ORDER BY e.track_id",
1715 )?;
1716 let stored = statement
1717 .query_map([profile_id], |row| row.get::<_, Vec<u8>>(0))?
1718 .collect::<rusqlite::Result<Vec<_>>>()?;
1719 let signatures = stored
1720 .into_iter()
1721 .map(|signature| {
1722 <[u8; 32]>::try_from(signature)
1723 .map_err(|_| anyhow::anyhow!("invalid similarity routing signature length"))
1724 })
1725 .collect::<Result<Vec<_>>>()?;
1726 Ok(signatures)
1727 }
1728
1729 pub fn similarity_storage_stats(&self, profile_id: &str) -> Result<SimilarityStorageStats> {
1730 let conn = self.lock();
1731 let total_tracks = conn.query_row("SELECT COUNT(*) FROM tracks", [], |row| {
1732 row.get::<_, i64>(0)
1733 })?;
1734 let embedded_tracks = conn.query_row(
1735 "SELECT COUNT(*)
1736 FROM track_embeddings e JOIN tracks t ON t.id = e.track_id
1737 WHERE e.profile_id = ?1 AND e.source_content_id IS t.content_id",
1738 [profile_id],
1739 |row| row.get::<_, i64>(0),
1740 )?;
1741 let (stored_vectors, stored_bytes) = conn.query_row(
1742 "SELECT COUNT(*), COALESCE(SUM(length(vector)), 0) FROM track_embeddings",
1743 [],
1744 |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?)),
1745 )?;
1746 Ok(SimilarityStorageStats {
1747 total_tracks: total_tracks.max(0) as usize,
1748 embedded_tracks: embedded_tracks.max(0) as usize,
1749 stored_vectors: stored_vectors.max(0) as usize,
1750 stored_bytes: stored_bytes.max(0) as u64,
1751 })
1752 }
1753
1754 pub fn clear_similarity_embeddings(&self) -> Result<()> {
1755 let conn = self.lock();
1756 conn.execute("DELETE FROM track_embeddings", [])?;
1757 conn.execute("DELETE FROM similarity_profiles", [])?;
1758 Ok(())
1759 }
1760
1761 pub fn playlists(&self) -> Result<Vec<PlaylistCard>> {
1766 let conn = self.lock();
1767 let liked: i64 = conn.query_row(
1768 "SELECT (SELECT COUNT(*) FROM likes) + (SELECT COUNT(*) FROM fed_likes)",
1769 [],
1770 |row| row.get(0),
1771 )?;
1772 let mut list = vec![PlaylistCard {
1773 id: LIKES_PLAYLIST_ID,
1774 title: "Liked tracks".to_string(),
1775 track_count: liked,
1776 kind: "likes".to_string(),
1777 }];
1778 let mut statement = conn.prepare(
1779 "SELECT p.id, p.title,
1780 (SELECT COUNT(*) FROM playlist_tracks pt WHERE pt.playlist_id = p.id)
1781 +
1782 (SELECT COUNT(*) FROM fed_playlist_tracks f
1783 WHERE f.playlist_sync_id = p.sync_id
1784 AND NOT EXISTS (
1785 SELECT 1 FROM playlist_tracks pt
1786 JOIN tracks t ON t.id = pt.track_id
1787 WHERE pt.playlist_id = p.id
1788 AND t.content_id = f.content_id
1789 ))
1790 FROM playlists p ORDER BY p.title COLLATE NOCASE",
1791 )?;
1792 let rows = statement
1793 .query_map([], |row| {
1794 Ok(PlaylistCard {
1795 id: row.get(0)?,
1796 title: row.get(1)?,
1797 track_count: row.get(2)?,
1798 kind: "normal".to_string(),
1799 })
1800 })?
1801 .collect::<rusqlite::Result<Vec<_>>>()?;
1802 list.extend(rows);
1803 Ok(list)
1804 }
1805
1806 pub fn playlist(&self, id: i64) -> Result<PlaylistDetail> {
1807 let conn = self.lock();
1808 if id == LIKES_PLAYLIST_ID {
1809 let local_tracks = query_tracks(
1810 &conn,
1811 &format!(
1812 "SELECT {TRACK_COLUMNS} FROM tracks t
1813 JOIN releases r ON r.id = t.release_id
1814 JOIN likes k ON k.track_id = t.id"
1815 ),
1816 params![],
1817 )?;
1818 let mut liked_at_by_track = HashMap::new();
1819 let mut liked_stmt = conn.prepare(
1820 "SELECT track_id,
1821 COALESCE(liked_hlc_ms,
1822 CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
1823 0)
1824 FROM likes",
1825 )?;
1826 let liked_rows = liked_stmt
1827 .query_map([], |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?)))?;
1828 for row in liked_rows {
1829 let (track_id, liked_at) = row?;
1830 liked_at_by_track.insert(track_id, liked_at);
1831 }
1832 drop(liked_stmt);
1833
1834 let mut entries: Vec<(i64, String, TrackItem)> = local_tracks
1835 .into_iter()
1836 .map(|track| {
1837 let liked_at = liked_at_by_track
1838 .get(&track.id)
1839 .copied()
1840 .unwrap_or_default();
1841 let stable_key = track
1842 .content_id
1843 .clone()
1844 .unwrap_or_else(|| format!("local:{:020}", track.id));
1845 (liked_at, stable_key, track)
1846 })
1847 .collect();
1848
1849 let mut fed_stmt = conn.prepare(
1850 "SELECT COALESCE(liked_hlc_ms,
1851 CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
1852 0),
1853 item_id, owner, title, artist_names, featured_artist_names,
1854 year, duration_seconds, content_id, release_title, track_number, disc_number
1855 FROM fed_likes",
1856 )?;
1857 let fed_rows = fed_stmt.query_map([], |row| {
1858 Ok((row.get::<_, i64>(0)?, fed_track_from_offset_row(row, 1)?))
1859 })?;
1860 for row in fed_rows {
1861 let (liked_at, fed) = row?;
1862 let stable_key = fed
1863 .content_id
1864 .clone()
1865 .unwrap_or_else(|| format!("fed:{}", fed.item_id));
1866 entries.push((liked_at, stable_key, pending_track(&fed)));
1867 }
1868 entries.sort_by(|left, right| right.0.cmp(&left.0).then_with(|| left.1.cmp(&right.1)));
1869 return Ok(PlaylistDetail {
1870 id,
1871 title: "Liked tracks".to_string(),
1872 description: None,
1873 tracks: entries.into_iter().map(|(_, _, track)| track).collect(),
1874 });
1875 }
1876 let (title, description, sync_id): (String, Option<String>, Option<String>) = conn
1877 .query_row(
1878 "SELECT title, description, sync_id FROM playlists WHERE id = ?1",
1879 [id],
1880 |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
1881 )
1882 .context("playlist not found")?;
1883 let local_tracks = query_tracks(
1884 &conn,
1885 &format!(
1886 "SELECT {TRACK_COLUMNS} FROM tracks t
1887 JOIN releases r ON r.id = t.release_id
1888 JOIN playlist_tracks pt ON pt.track_id = t.id
1889 WHERE pt.playlist_id = ?1
1890 ORDER BY pt.position"
1891 ),
1892 params![id],
1893 )?;
1894 let mut positions = HashMap::new();
1895 let mut position_stmt =
1896 conn.prepare("SELECT track_id, position FROM playlist_tracks WHERE playlist_id = ?1")?;
1897 let position_rows = position_stmt.query_map([id], |row| {
1898 Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
1899 })?;
1900 for row in position_rows {
1901 let (track_id, position) = row?;
1902 positions.insert(track_id, position);
1903 }
1904 drop(position_stmt);
1905 let mut entries: Vec<(i64, TrackItem)> = local_tracks
1906 .into_iter()
1907 .enumerate()
1908 .map(|(index, track)| {
1909 let position = positions.get(&track.id).copied().unwrap_or(index as i64);
1910 (position, track)
1911 })
1912 .collect();
1913 if let Some(sync_id) = sync_id.as_deref() {
1914 for (position, fed) in fed_playlist_tracks(&conn, id, sync_id)? {
1915 entries.push((position, pending_track(&fed)));
1916 }
1917 }
1918 entries.sort_by(|(left_pos, left_track), (right_pos, right_track)| {
1919 left_pos
1920 .cmp(right_pos)
1921 .then_with(|| left_track.title.cmp(&right_track.title))
1922 });
1923 Ok(PlaylistDetail {
1924 id,
1925 title,
1926 description,
1927 tracks: entries.into_iter().map(|(_, track)| track).collect(),
1928 })
1929 }
1930
1931 pub fn create_playlist(&self, title: &str) -> Result<PlaylistCard> {
1932 let conn = self.lock();
1933 conn.execute("INSERT INTO playlists (title) VALUES (?1)", [title])?;
1934 let id = conn.last_insert_rowid();
1935 let sync_id = make_playlist_sync_id(id, title);
1936 conn.execute(
1937 "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
1938 params![id, sync_id],
1939 )?;
1940 Ok(PlaylistCard {
1941 id,
1942 title: title.to_string(),
1943 track_count: 0,
1944 kind: "normal".to_string(),
1945 })
1946 }
1947
1948 pub fn update_playlist(&self, id: i64, title: &str, description: Option<&str>) -> Result<()> {
1949 let conn = self.lock();
1950 conn.execute(
1951 "UPDATE playlists SET title = ?2, description = ?3 WHERE id = ?1",
1952 params![id, title, description],
1953 )?;
1954 Ok(())
1955 }
1956
1957 pub fn delete_playlist(&self, id: i64) -> Result<()> {
1958 let conn = self.lock();
1959 let sync_id: Option<String> = conn
1960 .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1961 row.get::<_, Option<String>>(0)
1962 })
1963 .optional()?
1964 .flatten();
1965 if let Some(sync_id) = sync_id {
1966 conn.execute(
1967 "DELETE FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
1968 [sync_id],
1969 )?;
1970 }
1971 conn.execute("DELETE FROM playlists WHERE id = ?1", [id])?;
1972 Ok(())
1973 }
1974
1975 pub fn delete_playlist_by_sync_id(&self, sync_id: &str) -> Result<()> {
1976 let conn = self.lock();
1977 conn.execute(
1978 "DELETE FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
1979 [sync_id],
1980 )?;
1981 conn.execute("DELETE FROM playlists WHERE sync_id = ?1", [sync_id])?;
1982 Ok(())
1983 }
1984
1985 pub fn playlist_sync_id(&self, id: i64) -> Result<Option<String>> {
1986 let conn = self.lock();
1987 Ok(conn
1988 .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1989 row.get(0)
1990 })
1991 .optional()?)
1992 }
1993
1994 pub fn ensure_playlist_sync_id(&self, id: i64) -> Result<String> {
1995 let conn = self.lock();
1996 let existing: Option<String> = conn
1997 .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1998 row.get(0)
1999 })
2000 .optional()?
2001 .flatten();
2002 if let Some(sync_id) = existing {
2003 return Ok(sync_id);
2004 }
2005 let title: String =
2006 conn.query_row("SELECT title FROM playlists WHERE id = ?1", [id], |row| {
2007 row.get(0)
2008 })?;
2009 let sync_id = make_playlist_sync_id(id, &title);
2010 conn.execute(
2011 "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
2012 params![id, sync_id],
2013 )?;
2014 Ok(sync_id)
2015 }
2016
2017 pub fn upsert_synced_playlist(&self, sync_id: &str, title: &str) -> Result<i64> {
2018 let conn = self.lock();
2019 if let Some(id) = conn
2020 .query_row(
2021 "SELECT id FROM playlists WHERE sync_id = ?1",
2022 [sync_id],
2023 |row| row.get(0),
2024 )
2025 .optional()?
2026 {
2027 conn.execute(
2028 "UPDATE playlists SET title = ?2 WHERE id = ?1",
2029 params![id, title],
2030 )?;
2031 return Ok(id);
2032 }
2033 conn.execute(
2034 "INSERT INTO playlists (sync_id, title) VALUES (?1, ?2)",
2035 params![sync_id, title],
2036 )?;
2037 Ok(conn.last_insert_rowid())
2038 }
2039
2040 pub fn add_tracks_to_playlist(&self, playlist_id: i64, track_ids: &[i64]) -> Result<()> {
2041 let mut conn = self.lock();
2042 let tx = conn.transaction()?;
2043 let mut next: i64 = tx.query_row(
2044 "SELECT COALESCE(MAX(position), -1) + 1 FROM playlist_tracks WHERE playlist_id = ?1",
2045 [playlist_id],
2046 |row| row.get(0),
2047 )?;
2048 for &track_id in track_ids {
2049 let inserted = tx.execute(
2050 "INSERT OR IGNORE INTO playlist_tracks (playlist_id, track_id, position)
2051 VALUES (?1, ?2, ?3)",
2052 params![playlist_id, track_id, next],
2053 )?;
2054 if inserted > 0 {
2055 next += 1;
2056 }
2057 }
2058 tx.commit()?;
2059 Ok(())
2060 }
2061
2062 pub fn add_fed_tracks_to_playlist(
2063 &self,
2064 playlist_id: i64,
2065 tracks: &[FederatedTrack],
2066 ) -> Result<()> {
2067 if tracks.is_empty() {
2068 return Ok(());
2069 }
2070 let mut conn = self.lock();
2071 let tx = conn.transaction()?;
2072 let playlist_sync_id: Option<String> = tx
2073 .query_row(
2074 "SELECT sync_id FROM playlists WHERE id = ?1",
2075 [playlist_id],
2076 |row| row.get::<_, Option<String>>(0),
2077 )
2078 .optional()?
2079 .flatten();
2080 let playlist_sync_id = match playlist_sync_id {
2081 Some(sync_id) => sync_id,
2082 None => {
2083 let title: String = tx.query_row(
2084 "SELECT title FROM playlists WHERE id = ?1",
2085 [playlist_id],
2086 |row| row.get(0),
2087 )?;
2088 let sync_id = make_playlist_sync_id(playlist_id, &title);
2089 tx.execute(
2090 "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
2091 params![playlist_id, sync_id],
2092 )?;
2093 sync_id
2094 }
2095 };
2096 let local_max: i64 = tx.query_row(
2097 "SELECT COALESCE(MAX(position), -1) FROM playlist_tracks WHERE playlist_id = ?1",
2098 [playlist_id],
2099 |row| row.get(0),
2100 )?;
2101 let fed_max: i64 = tx.query_row(
2102 "SELECT COALESCE(MAX(position), -1) FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
2103 [&playlist_sync_id],
2104 |row| row.get(0),
2105 )?;
2106 let mut next = local_max.max(fed_max) + 1;
2107 for fed in tracks {
2108 let Some(content_id) = fed
2109 .content_id
2110 .as_deref()
2111 .and_then(music_dht::normalize_content_id)
2112 else {
2113 continue;
2114 };
2115 let existing_position: Option<i64> = tx
2116 .query_row(
2117 "SELECT position FROM fed_playlist_tracks
2118 WHERE playlist_sync_id = ?1 AND content_id = ?2",
2119 params![playlist_sync_id, content_id],
2120 |row| row.get(0),
2121 )
2122 .optional()?;
2123 let position = match existing_position {
2124 Some(position) => position,
2125 None => {
2126 let position = next;
2127 next += 1;
2128 position
2129 }
2130 };
2131 tx.execute(
2132 "INSERT INTO fed_playlist_tracks
2133 (playlist_sync_id, item_id, owner, title, artist_names,
2134 featured_artist_names, year, duration_seconds, content_id,
2135 release_title, track_number, disc_number, position)
2136 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
2137 ON CONFLICT(playlist_sync_id, content_id) DO UPDATE SET
2138 item_id = excluded.item_id,
2139 owner = excluded.owner,
2140 title = excluded.title,
2141 artist_names = excluded.artist_names,
2142 featured_artist_names = excluded.featured_artist_names,
2143 year = excluded.year,
2144 duration_seconds = excluded.duration_seconds,
2145 release_title = excluded.release_title,
2146 track_number = excluded.track_number,
2147 disc_number = excluded.disc_number,
2148 position = excluded.position",
2149 params![
2150 playlist_sync_id,
2151 fed.item_id,
2152 fed.owner,
2153 fed.title,
2154 fed.artist_names.join("; "),
2155 fed.featured_artist_names.join("; "),
2156 fed.year,
2157 fed.duration_seconds.map(|d| d as f64),
2158 content_id,
2159 fed.release_title,
2160 fed.track_number,
2161 fed.disc_number,
2162 position,
2163 ],
2164 )?;
2165 }
2166 tx.commit()?;
2167 Ok(())
2168 }
2169
2170 pub fn remove_tracks_from_playlist(&self, playlist_id: i64, track_ids: &[i64]) -> Result<()> {
2171 let conn = self.lock();
2172 for &track_id in track_ids {
2173 conn.execute(
2174 "DELETE FROM playlist_tracks WHERE playlist_id = ?1 AND track_id = ?2",
2175 params![playlist_id, track_id],
2176 )?;
2177 }
2178 Ok(())
2179 }
2180
2181 pub fn remove_content_ids_from_playlist(
2182 &self,
2183 playlist_id: i64,
2184 content_ids: &[String],
2185 ) -> Result<()> {
2186 let conn = self.lock();
2187 let playlist_sync_id: Option<String> = conn
2188 .query_row(
2189 "SELECT sync_id FROM playlists WHERE id = ?1",
2190 [playlist_id],
2191 |row| row.get::<_, Option<String>>(0),
2192 )
2193 .optional()?
2194 .flatten();
2195 for content_id in content_ids {
2196 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2197 continue;
2198 };
2199 conn.execute(
2200 "DELETE FROM playlist_tracks
2201 WHERE playlist_id = ?1
2202 AND track_id IN (
2203 SELECT id FROM tracks WHERE content_id = ?2
2204 )",
2205 params![playlist_id, content_id],
2206 )?;
2207 if let Some(sync_id) = playlist_sync_id.as_deref() {
2208 conn.execute(
2209 "DELETE FROM fed_playlist_tracks
2210 WHERE playlist_sync_id = ?1 AND content_id = ?2",
2211 params![sync_id, content_id],
2212 )?;
2213 }
2214 }
2215 Ok(())
2216 }
2217
2218 pub fn track_content_id_by_id(&self, track_id: i64) -> Result<Option<String>> {
2219 let conn = self.lock();
2220 let content_id: Option<String> = conn
2221 .query_row(
2222 "SELECT content_id FROM tracks WHERE id = ?1",
2223 [track_id],
2224 |row| row.get::<_, Option<String>>(0),
2225 )
2226 .optional()?
2227 .flatten()
2228 .and_then(|value| music_dht::normalize_content_id(&value));
2229 Ok(content_id)
2230 }
2231
2232 pub fn playlist_track_content_positions(
2233 &self,
2234 playlist_id: i64,
2235 track_ids: &[i64],
2236 ) -> Result<Vec<(String, i64)>> {
2237 let conn = self.lock();
2238 let mut out = Vec::new();
2239 for &track_id in track_ids {
2240 let row: Option<(Option<String>, i64)> = conn
2241 .query_row(
2242 "SELECT t.content_id, pt.position
2243 FROM tracks t
2244 JOIN playlist_tracks pt ON pt.track_id = t.id
2245 WHERE t.id = ?1 AND pt.playlist_id = ?2",
2246 params![track_id, playlist_id],
2247 |row| Ok((row.get(0)?, row.get(1)?)),
2248 )
2249 .optional()?;
2250 let Some((content_id, position)) = row else {
2251 continue;
2252 };
2253 if let Some(content_id) = content_id
2254 .as_deref()
2255 .and_then(music_dht::normalize_content_id)
2256 {
2257 out.push((content_id, position));
2258 }
2259 }
2260 Ok(out)
2261 }
2262
2263 pub fn playlist_content_position(
2264 &self,
2265 playlist_id: i64,
2266 content_id: &str,
2267 ) -> Result<Option<i64>> {
2268 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2269 return Ok(None);
2270 };
2271 let conn = self.lock();
2272 let local_position = conn
2273 .query_row(
2274 "SELECT pt.position
2275 FROM playlist_tracks pt
2276 JOIN tracks t ON t.id = pt.track_id
2277 WHERE pt.playlist_id = ?1 AND t.content_id = ?2
2278 LIMIT 1",
2279 params![playlist_id, content_id],
2280 |row| row.get(0),
2281 )
2282 .optional()?;
2283 if local_position.is_some() {
2284 return Ok(local_position);
2285 }
2286 let sync_id: Option<String> = conn
2287 .query_row(
2288 "SELECT sync_id FROM playlists WHERE id = ?1",
2289 [playlist_id],
2290 |row| row.get::<_, Option<String>>(0),
2291 )
2292 .optional()?
2293 .flatten();
2294 let Some(sync_id) = sync_id else {
2295 return Ok(None);
2296 };
2297 Ok(conn
2298 .query_row(
2299 "SELECT position
2300 FROM fed_playlist_tracks
2301 WHERE playlist_sync_id = ?1 AND content_id = ?2
2302 LIMIT 1",
2303 params![sync_id, content_id],
2304 |row| row.get(0),
2305 )
2306 .optional()?)
2307 }
2308
2309 pub fn track_id_by_content_id(&self, content_id: &str) -> Result<Option<i64>> {
2310 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2311 return Ok(None);
2312 };
2313 let conn = self.lock();
2314 Ok(conn
2315 .query_row(
2316 "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2317 [content_id],
2318 |row| row.get(0),
2319 )
2320 .optional()?)
2321 }
2322
2323 pub fn liked_content_ids(&self) -> Result<Vec<String>> {
2324 let conn = self.lock();
2325 let mut statement = conn.prepare(
2326 "SELECT DISTINCT t.content_id
2327 FROM likes k
2328 JOIN tracks t ON t.id = k.track_id
2329 WHERE t.content_id IS NOT NULL",
2330 )?;
2331 let rows = statement
2332 .query_map([], |row| row.get::<_, String>(0))?
2333 .collect::<rusqlite::Result<Vec<_>>>()?;
2334 Ok(rows
2335 .into_iter()
2336 .filter_map(|content_id| music_dht::normalize_content_id(&content_id))
2337 .collect())
2338 }
2339
2340 pub fn local_content_ids(&self) -> Result<Vec<String>> {
2341 let conn = self.lock();
2342 let mut statement = conn.prepare(
2343 "SELECT DISTINCT content_id
2344 FROM tracks
2345 WHERE content_id IS NOT NULL",
2346 )?;
2347 let rows = statement
2348 .query_map([], |row| row.get::<_, String>(0))?
2349 .collect::<rusqlite::Result<Vec<_>>>()?;
2350 Ok(rows
2351 .into_iter()
2352 .filter_map(|content_id| music_dht::normalize_content_id(&content_id))
2353 .collect())
2354 }
2355
2356 pub fn toggle_like_by_content_id(&self, content_id: &str) -> Result<bool> {
2358 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2359 anyhow::bail!("invalid content id");
2360 };
2361 let mut conn = self.lock();
2362 let tx = conn.transaction()?;
2363 let removed_local = tx.execute(
2364 "DELETE FROM likes
2365 WHERE track_id IN (
2366 SELECT id FROM tracks WHERE content_id = ?1
2367 )",
2368 [&content_id],
2369 )?;
2370 let removed_fed =
2371 tx.execute("DELETE FROM fed_likes WHERE content_id = ?1", [&content_id])?;
2372 if removed_local + removed_fed > 0 {
2373 tx.commit()?;
2374 return Ok(false);
2375 }
2376 let track_id: i64 = tx
2377 .query_row(
2378 "SELECT id FROM tracks WHERE content_id = ?1 ORDER BY id LIMIT 1",
2379 [&content_id],
2380 |row| row.get(0),
2381 )
2382 .optional()?
2383 .with_context(|| format!("no local track with content id {content_id}"))?;
2384 tx.execute("INSERT INTO likes (track_id) VALUES (?1)", [track_id])?;
2385 tx.commit()?;
2386 Ok(true)
2387 }
2388
2389 pub fn set_synced_like(&self, track_id: i64, liked: bool, liked_hlc_ms: i64) -> Result<bool> {
2390 let conn = self.lock();
2391 let changed = if liked {
2392 conn.execute(
2393 "INSERT INTO likes (track_id, liked_at, liked_hlc_ms)
2394 VALUES (?1, datetime(?2 / 1000, 'unixepoch'), ?2)
2395 ON CONFLICT(track_id) DO UPDATE SET
2396 liked_at = excluded.liked_at,
2397 liked_hlc_ms = excluded.liked_hlc_ms",
2398 params![track_id, liked_hlc_ms],
2399 )?
2400 } else {
2401 conn.execute("DELETE FROM likes WHERE track_id = ?1", [track_id])?
2402 };
2403 Ok(changed > 0)
2404 }
2405
2406 pub fn upsert_fed_playlist_track(
2407 &self,
2408 playlist_sync_id: &str,
2409 fed: &FederatedTrack,
2410 position: i64,
2411 ) -> Result<bool> {
2412 let Some(content_id) = fed
2413 .content_id
2414 .as_deref()
2415 .and_then(music_dht::normalize_content_id)
2416 else {
2417 return Ok(false);
2418 };
2419 let conn = self.lock();
2420 Ok(conn.execute(
2421 "INSERT INTO fed_playlist_tracks
2422 (playlist_sync_id, item_id, owner, title, artist_names,
2423 featured_artist_names, year, duration_seconds, content_id,
2424 release_title, track_number, disc_number, position)
2425 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
2426 ON CONFLICT(playlist_sync_id, content_id) DO UPDATE SET
2427 item_id = excluded.item_id,
2428 owner = excluded.owner,
2429 title = excluded.title,
2430 artist_names = excluded.artist_names,
2431 featured_artist_names = excluded.featured_artist_names,
2432 year = excluded.year,
2433 duration_seconds = excluded.duration_seconds,
2434 release_title = excluded.release_title,
2435 track_number = excluded.track_number,
2436 disc_number = excluded.disc_number,
2437 position = excluded.position",
2438 params![
2439 playlist_sync_id,
2440 fed.item_id,
2441 fed.owner,
2442 fed.title,
2443 fed.artist_names.join("; "),
2444 fed.featured_artist_names.join("; "),
2445 fed.year,
2446 fed.duration_seconds.map(|d| d as f64),
2447 content_id,
2448 fed.release_title,
2449 fed.track_number,
2450 fed.disc_number,
2451 position,
2452 ],
2453 )? > 0)
2454 }
2455
2456 pub fn fed_playlist_track_by_content_id(
2457 &self,
2458 playlist_sync_id: &str,
2459 content_id: &str,
2460 ) -> Result<Option<FederatedTrack>> {
2461 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2462 return Ok(None);
2463 };
2464 let conn = self.lock();
2465 conn.query_row(
2466 "SELECT item_id, owner, title, artist_names, featured_artist_names,
2467 year, duration_seconds, content_id, release_title, track_number, disc_number
2468 FROM fed_playlist_tracks
2469 WHERE playlist_sync_id = ?1 AND content_id = ?2
2470 LIMIT 1",
2471 params![playlist_sync_id, content_id],
2472 fed_track_from_row,
2473 )
2474 .optional()
2475 .map_err(Into::into)
2476 }
2477
2478 pub fn has_playlist_content_reference(
2479 &self,
2480 playlist_sync_id: &str,
2481 content_id: &str,
2482 ) -> Result<bool> {
2483 if self.track_id_by_content_id(content_id)?.is_some() {
2484 return Ok(true);
2485 }
2486 Ok(self
2487 .fed_playlist_track_by_content_id(playlist_sync_id, content_id)?
2488 .is_some())
2489 }
2490
2491 pub fn add_content_id_to_synced_playlist(
2492 &self,
2493 playlist_sync_id: &str,
2494 content_id: &str,
2495 ) -> Result<bool> {
2496 let Some(track_id) = self.track_id_by_content_id(content_id)? else {
2497 return Ok(false);
2498 };
2499 let conn = self.lock();
2500 let playlist_id: Option<i64> = conn
2501 .query_row(
2502 "SELECT id FROM playlists WHERE sync_id = ?1",
2503 [playlist_sync_id],
2504 |row| row.get::<_, i64>(0),
2505 )
2506 .optional()?;
2507 let Some(playlist_id) = playlist_id else {
2508 return Ok(false);
2509 };
2510 let next: i64 = conn.query_row(
2511 "SELECT COALESCE(MAX(position), -1) + 1 FROM playlist_tracks WHERE playlist_id = ?1",
2512 [playlist_id],
2513 |row| row.get(0),
2514 )?;
2515 conn.execute(
2516 "INSERT OR IGNORE INTO playlist_tracks (playlist_id, track_id, position)
2517 VALUES (?1, ?2, ?3)",
2518 params![playlist_id, track_id, next],
2519 )?;
2520 Ok(true)
2521 }
2522
2523 pub fn remove_content_id_from_synced_playlist(
2524 &self,
2525 playlist_sync_id: &str,
2526 content_id: &str,
2527 ) -> Result<()> {
2528 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2529 return Ok(());
2530 };
2531 let conn = self.lock();
2532 let playlist_id: Option<i64> = conn
2533 .query_row(
2534 "SELECT id FROM playlists WHERE sync_id = ?1",
2535 [playlist_sync_id],
2536 |row| row.get::<_, i64>(0),
2537 )
2538 .optional()?;
2539 if let Some(playlist_id) = playlist_id {
2540 let track_id: Option<i64> = conn
2541 .query_row(
2542 "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2543 [&content_id],
2544 |row| row.get(0),
2545 )
2546 .optional()?;
2547 if let Some(track_id) = track_id {
2548 conn.execute(
2549 "DELETE FROM playlist_tracks WHERE playlist_id = ?1 AND track_id = ?2",
2550 params![playlist_id, track_id],
2551 )?;
2552 }
2553 }
2554 conn.execute(
2555 "DELETE FROM fed_playlist_tracks
2556 WHERE playlist_sync_id = ?1 AND content_id = ?2",
2557 params![playlist_sync_id, content_id],
2558 )?;
2559 Ok(())
2560 }
2561
2562 pub fn fed_like_ids(&self) -> Result<Vec<String>> {
2564 let conn = self.lock();
2565 let mut statement = conn.prepare("SELECT item_id, content_id FROM fed_likes")?;
2566 let rows = statement
2567 .query_map([], |row| {
2568 Ok((row.get::<_, String>(0)?, row.get::<_, Option<String>>(1)?))
2569 })?
2570 .collect::<rusqlite::Result<Vec<_>>>()?;
2571 let mut keys = Vec::new();
2572 for (item_id, content_id) in rows {
2573 keys.push(item_id);
2574 if let Some(content_id) = content_id
2575 .as_deref()
2576 .and_then(music_dht::normalize_content_id)
2577 {
2578 keys.push(content_id);
2579 }
2580 }
2581 Ok(keys)
2582 }
2583
2584 pub fn toggle_fed_like(&self, fed: &FederatedTrack) -> Result<bool> {
2586 let content_id = fed
2587 .content_id
2588 .as_deref()
2589 .and_then(music_dht::normalize_content_id);
2590 let mut conn = self.lock();
2591 let tx = conn.transaction()?;
2592 let removed = match content_id.as_deref() {
2593 Some(content_id) => {
2594 let removed_fed = tx.execute(
2595 "DELETE FROM fed_likes WHERE item_id = ?1 OR content_id = ?2",
2596 params![fed.item_id, content_id],
2597 )?;
2598 let removed_local = tx.execute(
2599 "DELETE FROM likes
2600 WHERE track_id IN (
2601 SELECT id FROM tracks WHERE content_id = ?1
2602 )",
2603 [content_id],
2604 )?;
2605 removed_fed + removed_local
2606 }
2607 None => tx.execute("DELETE FROM fed_likes WHERE item_id = ?1", [&fed.item_id])?,
2608 };
2609 if removed > 0 {
2610 tx.commit()?;
2611 return Ok(false);
2612 }
2613 if let Some(content_id) = content_id.as_deref() {
2614 tx.execute(
2615 "DELETE FROM fed_likes WHERE content_id = ?1 AND item_id != ?2",
2616 params![content_id, fed.item_id],
2617 )?;
2618 }
2619 tx.execute(
2620 "INSERT INTO fed_likes (item_id, owner, title, artist_names,
2621 featured_artist_names, year, duration_seconds, content_id,
2622 release_title, track_number, disc_number)
2623 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
2624 params![
2625 fed.item_id,
2626 fed.owner,
2627 fed.title,
2628 fed.artist_names.join("; "),
2629 fed.featured_artist_names.join("; "),
2630 fed.year,
2631 fed.duration_seconds.map(|d| d as f64),
2632 content_id,
2633 fed.release_title,
2634 fed.track_number,
2635 fed.disc_number,
2636 ],
2637 )?;
2638 tx.commit()?;
2639 Ok(true)
2640 }
2641
2642 pub fn fed_like_by_content_id(&self, content_id: &str) -> Result<Option<FederatedTrack>> {
2643 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2644 return Ok(None);
2645 };
2646 let conn = self.lock();
2647 let mut statement = conn.prepare(
2648 "SELECT item_id, owner, title, artist_names, featured_artist_names,
2649 year, duration_seconds, content_id, release_title, track_number, disc_number
2650 FROM fed_likes
2651 WHERE content_id = ?1
2652 ORDER BY COALESCE(liked_hlc_ms,
2653 CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
2654 0) DESC
2655 LIMIT 1",
2656 )?;
2657 let track = statement
2658 .query_row([content_id], |row| {
2659 let artists: String = row.get(3)?;
2660 Ok(FederatedTrack {
2661 item_id: row.get(0)?,
2662 owner: row.get(1)?,
2663 own: false,
2664 title: row.get(2)?,
2665 artist_names: artists
2666 .split("; ")
2667 .filter(|name| !name.is_empty())
2668 .map(str::to_string)
2669 .collect(),
2670 featured_artist_names: row
2671 .get::<_, String>(4)?
2672 .split("; ")
2673 .filter(|name| !name.is_empty())
2674 .map(str::to_string)
2675 .collect(),
2676 year: row.get(5)?,
2677 duration_seconds: row.get::<_, Option<f64>>(6)?.map(|d| d.round() as i64),
2678 content_id: row.get(7)?,
2679 release_title: row.get(8)?,
2680 track_number: row.get(9)?,
2681 disc_number: row.get(10)?,
2682 })
2683 })
2684 .optional()?;
2685 Ok(track)
2686 }
2687
2688 pub fn upsert_synced_fed_like(&self, fed: &FederatedTrack, liked_hlc_ms: i64) -> Result<bool> {
2689 let Some(content_id) = fed
2690 .content_id
2691 .as_deref()
2692 .and_then(music_dht::normalize_content_id)
2693 else {
2694 return Ok(false);
2695 };
2696 let conn = self.lock();
2697 let duplicate_rows = conn.execute(
2698 "DELETE FROM fed_likes WHERE content_id = ?1 AND item_id != ?2",
2699 params![content_id, fed.item_id],
2700 )?;
2701 let existing: Option<FederatedLikeState> = conn
2702 .query_row(
2703 "SELECT owner, title, artist_names, featured_artist_names,
2704 year, duration_seconds, release_title, track_number, disc_number,
2705 liked_hlc_ms
2706 FROM fed_likes
2707 WHERE item_id = ?1",
2708 [&fed.item_id],
2709 |row| {
2710 Ok((
2711 row.get(0)?,
2712 row.get(1)?,
2713 row.get(2)?,
2714 row.get(3)?,
2715 row.get(4)?,
2716 row.get::<_, Option<f64>>(5)?.map(|d| d.round() as i64),
2717 row.get(6)?,
2718 row.get(7)?,
2719 row.get(8)?,
2720 row.get(9)?,
2721 ))
2722 },
2723 )
2724 .optional()?;
2725 let incoming = (
2726 fed.owner.clone(),
2727 fed.title.clone(),
2728 fed.artist_names.join("; "),
2729 fed.featured_artist_names.join("; "),
2730 fed.year,
2731 fed.duration_seconds,
2732 fed.release_title.clone(),
2733 fed.track_number,
2734 fed.disc_number,
2735 Some(liked_hlc_ms),
2736 );
2737 if duplicate_rows == 0 && existing.as_ref() == Some(&incoming) {
2738 return Ok(false);
2739 }
2740 conn.execute(
2741 "INSERT INTO fed_likes (item_id, owner, title, artist_names,
2742 featured_artist_names, year, duration_seconds, content_id,
2743 release_title, track_number, disc_number, liked_at, liked_hlc_ms)
2744 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11,
2745 datetime(?12 / 1000, 'unixepoch'), ?12)
2746 ON CONFLICT(item_id) DO UPDATE SET
2747 owner = excluded.owner,
2748 title = excluded.title,
2749 artist_names = excluded.artist_names,
2750 featured_artist_names = excluded.featured_artist_names,
2751 year = excluded.year,
2752 duration_seconds = excluded.duration_seconds,
2753 content_id = excluded.content_id,
2754 release_title = excluded.release_title,
2755 track_number = excluded.track_number,
2756 disc_number = excluded.disc_number,
2757 liked_at = excluded.liked_at,
2758 liked_hlc_ms = excluded.liked_hlc_ms",
2759 params![
2760 fed.item_id,
2761 fed.owner,
2762 fed.title,
2763 fed.artist_names.join("; "),
2764 fed.featured_artist_names.join("; "),
2765 fed.year,
2766 fed.duration_seconds.map(|d| d as f64),
2767 content_id,
2768 fed.release_title,
2769 fed.track_number,
2770 fed.disc_number,
2771 liked_hlc_ms,
2772 ],
2773 )?;
2774 Ok(true)
2775 }
2776
2777 pub fn remove_fed_like_by_content_id(&self, content_id: &str) -> Result<bool> {
2778 let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2779 return Ok(false);
2780 };
2781 let conn = self.lock();
2782 Ok(conn.execute("DELETE FROM fed_likes WHERE content_id = ?1", [content_id])? > 0)
2783 }
2784
2785 pub fn transfer_fed_like(&self, item_id: &str, track_id: i64) -> Result<bool> {
2788 let conn = self.lock();
2789 let liked: Option<(String, Option<i64>)> = conn
2790 .query_row(
2791 "SELECT liked_at, liked_hlc_ms FROM fed_likes WHERE item_id = ?1",
2792 [item_id],
2793 |row| Ok((row.get(0)?, row.get(1)?)),
2794 )
2795 .optional()?;
2796 let removed = conn.execute("DELETE FROM fed_likes WHERE item_id = ?1", [item_id])?;
2797 if removed == 0 {
2798 return Ok(false);
2799 }
2800 if let Some((liked_at, liked_hlc_ms)) = liked {
2801 conn.execute(
2802 "INSERT INTO likes (track_id, liked_at, liked_hlc_ms)
2803 VALUES (?1, ?2, ?3)
2804 ON CONFLICT(track_id) DO UPDATE SET
2805 liked_at = excluded.liked_at,
2806 liked_hlc_ms = excluded.liked_hlc_ms",
2807 params![track_id, liked_at, liked_hlc_ms],
2808 )?;
2809 } else {
2810 conn.execute(
2811 "INSERT OR IGNORE INTO likes (track_id) VALUES (?1)",
2812 [track_id],
2813 )?;
2814 }
2815 Ok(true)
2816 }
2817
2818 pub fn apply_listen_event(
2820 &self,
2821 event: &music_dht::device_sync::ListenEvent,
2822 origin_device_id: &str,
2823 ) -> Result<bool> {
2824 if !event.should_record() || origin_device_id.trim().is_empty() {
2825 return Ok(false);
2826 }
2827 let content_id = music_dht::normalize_content_id(&event.content_id)
2828 .context("invalid listen content id")?;
2829 let conn = self.lock();
2830 let local_track_id: Option<i64> = conn
2831 .query_row(
2832 "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2833 [&content_id],
2834 |row| row.get(0),
2835 )
2836 .optional()?;
2837 let inserted = conn.execute(
2838 "INSERT OR IGNORE INTO listen_events
2839 (listen_id, content_id, local_track_id, origin_device_id,
2840 started_at_ms, listened_ms, track_duration_ms, ended_reason,
2841 qualified, metadata_json)
2842 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
2843 params![
2844 event.listen_id,
2845 content_id,
2846 local_track_id,
2847 origin_device_id,
2848 event.started_at_ms,
2849 event.listened_ms,
2850 event.track_duration_ms,
2851 serde_json::to_string(&event.ended_reason)?,
2852 i64::from(event.qualifies_as_play()),
2853 serde_json::to_string(&event.track)?,
2854 ],
2855 )?;
2856 Ok(inserted > 0)
2857 }
2858
2859 pub fn listen_history(&self, limit: usize) -> Result<Vec<ListenHistoryEntry>> {
2862 let conn = self.lock();
2863 let mut stmt = conn.prepare(
2864 "SELECT listen_id, content_id, origin_device_id, started_at_ms, metadata_json
2865 FROM listen_events
2866 WHERE qualified = 1
2867 ORDER BY started_at_ms DESC, listen_id DESC
2868 LIMIT ?1",
2869 )?;
2870 let rows = stmt
2871 .query_map([limit.min(i64::MAX as usize) as i64], |row| {
2872 Ok((
2873 row.get::<_, String>(0)?,
2874 row.get::<_, String>(1)?,
2875 row.get::<_, String>(2)?,
2876 row.get::<_, i64>(3)?,
2877 row.get::<_, String>(4)?,
2878 ))
2879 })?
2880 .collect::<rusqlite::Result<Vec<_>>>()?;
2881 rows.into_iter()
2882 .map(
2883 |(listen_id, content_id, origin_device_id, started_at_ms, metadata_json)| {
2884 let metadata: music_dht::device_sync::ListenTrackMetadata =
2885 serde_json::from_str(&metadata_json)
2886 .context("invalid listen history metadata")?;
2887 let mut artists = metadata.artist_names;
2888 artists.extend(metadata.featured_artist_names);
2889 Ok(ListenHistoryEntry {
2890 listen_id,
2891 content_id,
2892 title: metadata.title,
2893 artist: artists.join(", "),
2894 origin_device_id,
2895 started_at_ms,
2896 })
2897 },
2898 )
2899 .collect()
2900 }
2901
2902 pub fn update_track(&self, id: i64, edit: &TrackEdit) -> Result<()> {
2907 let mut conn = self.lock();
2908 let tx = conn.transaction()?;
2909 tx.execute(
2910 "UPDATE tracks SET title = ?2, track_number = ?3, disc_number = ?4 WHERE id = ?1",
2911 params![id, edit.title, edit.track_number, edit.disc_number],
2912 )?;
2913 tx.execute(
2916 "UPDATE releases SET cover_path = ?2
2917 WHERE id = (SELECT release_id FROM tracks WHERE id = ?1)",
2918 params![id, edit.cover_path],
2919 )?;
2920 tx.execute("DELETE FROM track_artists WHERE track_id = ?1", [id])?;
2921 link_track_artists(&tx, id, &edit.artists, "main")?;
2922 link_track_artists(&tx, id, &edit.featured_artists, "featured")?;
2923 tx.commit()?;
2924 Ok(())
2925 }
2926
2927 pub fn update_release(&self, id: i64, edit: &ReleaseEdit) -> Result<()> {
2928 let mut conn = self.lock();
2929 let tx = conn.transaction()?;
2930 tx.execute(
2931 "UPDATE releases SET title = ?2, release_type = ?3, year = ?4 WHERE id = ?1",
2932 params![id, edit.title, edit.release_type, edit.year],
2933 )?;
2934 if !edit.artists.is_empty() {
2936 tx.execute("DELETE FROM release_artists WHERE release_id = ?1", [id])?;
2937 for (position, name) in edit.artists.iter().enumerate() {
2938 let artist_id = find_or_create_artist(&tx, name)?;
2939 tx.execute(
2940 "INSERT OR IGNORE INTO release_artists (release_id, artist_id, position)
2941 VALUES (?1, ?2, ?3)",
2942 params![id, artist_id, position as i64],
2943 )?;
2944 }
2945 }
2946 tx.commit()?;
2947 Ok(())
2948 }
2949
2950 pub fn update_artist(&self, id: i64, name: &str, image_path: Option<&str>) -> Result<()> {
2951 let conn = self.lock();
2952 let taken: Option<i64> = conn
2953 .query_row(
2954 "SELECT id FROM artists WHERE name = ?1 COLLATE NOCASE AND id != ?2",
2955 params![name, id],
2956 |row| row.get(0),
2957 )
2958 .optional()?;
2959 if taken.is_some() {
2960 anyhow::bail!("an artist named \"{name}\" already exists");
2961 }
2962 conn.execute(
2963 "UPDATE artists SET name = ?2, image_path = ?3 WHERE id = ?1",
2964 params![id, name, image_path],
2965 )?;
2966 Ok(())
2967 }
2968
2969 pub fn artist_id_by_name(&self, name: &str) -> Result<Option<i64>> {
2971 let conn = self.lock();
2972 Ok(conn
2973 .query_row(
2974 "SELECT id FROM artists WHERE norm(name) = norm(?1) LIMIT 1",
2975 [name],
2976 |row| row.get(0),
2977 )
2978 .optional()?)
2979 }
2980
2981 pub fn release_cover_by_names(&self, artist: &str, release: &str) -> Result<Option<String>> {
2984 let conn = self.lock();
2985 Ok(conn
2986 .query_row(
2987 "SELECT r.cover_path FROM releases r
2988 JOIN release_artists ra ON ra.release_id = r.id
2989 JOIN artists a ON a.id = ra.artist_id
2990 WHERE norm(a.name) = norm(?1) AND norm(r.title) = norm(?2)
2991 LIMIT 1",
2992 params![artist, release],
2993 |row| row.get(0),
2994 )
2995 .optional()?
2996 .flatten())
2997 }
2998
2999 pub fn artist_image(&self, artist_id: i64) -> Result<Option<String>> {
3001 let conn = self.lock();
3002 Ok(conn
3003 .query_row(
3004 "SELECT image_path FROM artists WHERE id = ?1",
3005 [artist_id],
3006 |row| row.get(0),
3007 )
3008 .optional()?
3009 .flatten())
3010 }
3011
3012 pub fn artist_image_missing(&self, name: &str) -> Result<bool> {
3014 let conn = self.lock();
3015 let missing: Option<bool> = conn
3016 .query_row(
3017 "SELECT image_path IS NULL FROM artists WHERE norm(name) = norm(?1)",
3018 [name],
3019 |row| row.get(0),
3020 )
3021 .optional()?;
3022 Ok(missing.unwrap_or(false))
3023 }
3024
3025 pub fn set_artist_image_if_missing(&self, name: &str, image_path: &str) -> Result<bool> {
3028 let conn = self.lock();
3029 let changed = conn.execute(
3030 "UPDATE artists SET image_path = ?2
3031 WHERE norm(name) = norm(?1) AND image_path IS NULL",
3032 params![name, image_path],
3033 )?;
3034 Ok(changed > 0)
3035 }
3036
3037 pub fn delete_track(&self, id: i64) -> Result<()> {
3038 let mut conn = self.lock();
3039 let tx = conn.transaction()?;
3040 tx.execute("DELETE FROM tracks WHERE id = ?1", [id])?;
3041 cleanup_empty_releases(&tx)?;
3042 tx.commit()?;
3043 Ok(())
3044 }
3045
3046 pub fn delete_release(&self, id: i64) -> Result<()> {
3047 let conn = self.lock();
3048 conn.execute("DELETE FROM releases WHERE id = ?1", [id])?;
3049 Ok(())
3050 }
3051
3052 pub fn delete_artist(&self, id: i64) -> Result<()> {
3056 let mut conn = self.lock();
3057 let tx = conn.transaction()?;
3058 tx.execute("DELETE FROM artists WHERE id = ?1", [id])?;
3059 tx.execute(
3060 "DELETE FROM tracks WHERE id NOT IN
3061 (SELECT track_id FROM track_artists WHERE role = 'main')",
3062 [],
3063 )?;
3064 cleanup_empty_releases(&tx)?;
3065 tx.commit()?;
3066 Ok(())
3067 }
3068}
3069
3070pub fn storage_name(value: &str, fallback: &str) -> String {
3072 let cleaned: String = value
3073 .chars()
3074 .map(|character| match character {
3075 '/' | '\\' | ':' | '*' | '?' | '"' | '<' | '>' | '|' => '_',
3076 character if character.is_control() => '_',
3077 character => character,
3078 })
3079 .collect();
3080 let cleaned = cleaned.trim().trim_matches('.');
3081 let mut shortened: String = cleaned.chars().take(120).collect();
3082 if shortened.is_empty() {
3083 return fallback.to_string();
3084 }
3085 let windows_base = shortened
3086 .split('.')
3087 .next()
3088 .unwrap_or_default()
3089 .to_ascii_uppercase();
3090 let windows_reserved = matches!(windows_base.as_str(), "CON" | "PRN" | "AUX" | "NUL")
3091 || windows_base
3092 .strip_prefix("COM")
3093 .or_else(|| windows_base.strip_prefix("LPT"))
3094 .is_some_and(|number| number.len() == 1 && matches!(number.as_bytes()[0], b'1'..=b'9'));
3095 if windows_reserved {
3096 shortened.insert(0, '_');
3097 }
3098 shortened
3099}
3100
3101fn unique_destination(requested: PathBuf, id: i64, reserved: &mut HashSet<PathBuf>) -> PathBuf {
3102 if !requested.exists() && reserved.insert(requested.clone()) {
3103 return requested;
3104 }
3105 let stem = requested
3106 .file_stem()
3107 .unwrap_or_else(|| std::ffi::OsStr::new("file"))
3108 .to_string_lossy();
3109 let extension = requested.extension().map(|value| value.to_os_string());
3110 let mut candidate = requested.with_file_name(format!("{stem}-{id}"));
3111 if let Some(extension) = extension {
3112 candidate.set_extension(extension);
3113 }
3114 let mut suffix = 2usize;
3115 while candidate.exists() || !reserved.insert(candidate.clone()) {
3116 candidate = requested.with_file_name(format!("{stem}-{id}-{suffix}"));
3117 if let Some(extension) = requested.extension() {
3118 candidate.set_extension(extension);
3119 }
3120 suffix += 1;
3121 }
3122 candidate
3123}
3124
3125fn copy_file_exclusive(source: &Path, destination: &Path) -> Result<()> {
3126 let mut input =
3127 std::fs::File::open(source).with_context(|| format!("opening {}", source.display()))?;
3128 let mut output = std::fs::OpenOptions::new()
3129 .write(true)
3130 .create_new(true)
3131 .open(destination)
3132 .with_context(|| format!("creating {}", destination.display()))?;
3133 let result = std::io::copy(&mut input, &mut output)
3134 .with_context(|| format!("copying {}", source.display()))
3135 .and_then(|_| {
3136 output
3137 .sync_all()
3138 .with_context(|| format!("syncing {}", destination.display()))
3139 });
3140 if let Err(error) = result {
3141 drop(output);
3142 let _ = std::fs::remove_file(destination);
3143 return Err(error);
3144 }
3145 Ok(())
3146}
3147
3148fn remove_empty_directories(root: &Path) {
3149 let Ok(entries) = std::fs::read_dir(root) else {
3150 return;
3151 };
3152 for entry in entries.flatten() {
3153 let path = entry.path();
3154 if path.is_dir() {
3155 remove_empty_directories(&path);
3156 }
3157 }
3158 let _ = std::fs::remove_dir(root);
3159}
3160
3161fn cleanup_empty_releases(tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
3162 tx.execute(
3163 "DELETE FROM releases WHERE id NOT IN (SELECT release_id FROM tracks)",
3164 [],
3165 )?;
3166 Ok(())
3167}
3168
3169fn link_track_artists(
3170 tx: &rusqlite::Transaction,
3171 track_id: i64,
3172 names: &[String],
3173 role: &str,
3174) -> Result<()> {
3175 for (position, name) in names.iter().enumerate() {
3176 let artist_id = find_or_create_artist(tx, name)?;
3177 tx.execute(
3178 "INSERT OR IGNORE INTO track_artists (track_id, artist_id, role, position)
3179 VALUES (?1, ?2, ?3, ?4)",
3180 params![track_id, artist_id, role, position as i64],
3181 )?;
3182 }
3183 Ok(())
3184}
3185
3186pub(crate) fn find_or_create_artist(conn: &Connection, name: &str) -> Result<i64> {
3187 let name = name.trim();
3188 anyhow::ensure!(!name.is_empty(), "artist name is empty");
3189 if let Some(id) = conn
3190 .query_row(
3191 "SELECT id FROM artists WHERE norm(name) = norm(?1) LIMIT 1",
3192 [name],
3193 |row| row.get(0),
3194 )
3195 .optional()?
3196 {
3197 return Ok(id);
3198 }
3199 conn.execute("INSERT INTO artists (name) VALUES (?1)", [name])?;
3200 Ok(conn.last_insert_rowid())
3201}
3202
3203fn release_card_from_row(row: &rusqlite::Row) -> rusqlite::Result<ReleaseCard> {
3204 Ok(ReleaseCard {
3205 id: row.get(0)?,
3206 title: row.get(1)?,
3207 release_type: row.get(2)?,
3208 year: row.get(3)?,
3209 cover_path: row.get(4)?,
3210 track_count: row.get(5)?,
3211 availability: Availability::Local,
3212 })
3213}
3214
3215fn rank_search_results(results: &mut SearchResults, normalized_query: &str) {
3216 results
3217 .artists
3218 .sort_by_key(|artist| exact_match_rank(&artist.name, normalized_query));
3219 results
3220 .releases
3221 .sort_by_key(|release| exact_match_rank(&release.title, normalized_query));
3222 results
3223 .tracks
3224 .sort_by_key(|track| track_match_rank(track, normalized_query));
3225}
3226
3227fn exact_match_rank(value: &str, normalized_query: &str) -> u8 {
3228 if music_dht::normalize_name(value) == normalized_query {
3229 0
3230 } else {
3231 1
3232 }
3233}
3234
3235fn track_match_rank(track: &TrackItem, normalized_query: &str) -> u8 {
3236 if music_dht::normalize_name(&track.title) == normalized_query {
3237 return 0;
3238 }
3239 if music_dht::normalize_name(&track.release_title) == normalized_query {
3240 return 1;
3241 }
3242 if track
3243 .artists
3244 .iter()
3245 .chain(track.featured_artists.iter())
3246 .any(|artist| music_dht::normalize_name(&artist.name) == normalized_query)
3247 {
3248 return 2;
3249 }
3250 3
3251}
3252
3253fn query_tracks(
3255 conn: &Connection,
3256 sql: &str,
3257 params: &[&dyn rusqlite::types::ToSql],
3258) -> Result<Vec<TrackItem>> {
3259 let mut statement = conn.prepare(sql)?;
3260 let mut tracks = statement
3261 .query_map(params, |row| {
3262 Ok(TrackItem {
3263 id: row.get(0)?,
3264 title: row.get(1)?,
3265 track_number: row.get(2)?,
3266 disc_number: row.get(3)?,
3267 duration_seconds: row.get(4)?,
3268 release_id: row.get(5)?,
3269 release_title: row.get(6)?,
3270 release_year: row.get(7)?,
3271 cover_path: row.get(8)?,
3272 file_path: row.get(9)?,
3273 audio_format: row.get(10)?,
3274 audio_bitrate: row.get(11)?,
3275 audio_sample_rate: row.get(12)?,
3276 audio_bit_depth: row.get(13)?,
3277 file_size_bytes: row.get(14)?,
3278 content_id: row
3279 .get::<_, Option<String>>(15)?
3280 .as_deref()
3281 .and_then(music_dht::normalize_content_id),
3282 play_count: row.get(16)?,
3283 artists: Vec::new(),
3284 featured_artists: Vec::new(),
3285 fed: None,
3286 })
3287 })?
3288 .collect::<rusqlite::Result<Vec<_>>>()?;
3289 let mut artist_statement = conn.prepare(
3290 "SELECT a.id, a.name, ta.role FROM artists a
3291 JOIN track_artists ta ON ta.artist_id = a.id
3292 WHERE ta.track_id = ?1
3293 ORDER BY ta.position",
3294 )?;
3295 for track in &mut tracks {
3296 let rows = artist_statement.query_map([track.id], |row| {
3297 Ok((
3298 ArtistRef {
3299 id: row.get(0)?,
3300 name: row.get(1)?,
3301 },
3302 row.get::<_, String>(2)?,
3303 ))
3304 })?;
3305 for row in rows {
3306 let (artist, role) = row?;
3307 if role == "featured" {
3308 track.featured_artists.push(artist);
3309 } else {
3310 track.artists.push(artist);
3311 }
3312 }
3313 }
3314 Ok(tracks)
3315}
3316
3317fn pending_track(fed: &FederatedTrack) -> TrackItem {
3318 let refs = |names: &[String]| {
3319 names
3320 .iter()
3321 .map(|name| ArtistRef {
3322 id: -1,
3323 name: name.clone(),
3324 })
3325 .collect()
3326 };
3327 TrackItem {
3328 id: NEXT_PENDING_TRACK_ID.fetch_sub(1, Ordering::Relaxed),
3329 title: fed.title.clone(),
3330 track_number: fed.track_number,
3331 disc_number: fed.disc_number,
3332 duration_seconds: fed.duration_seconds.unwrap_or(0) as f64,
3333 artists: refs(&fed.artist_names),
3334 featured_artists: refs(&fed.featured_artist_names),
3335 release_id: -1,
3336 release_title: fed
3337 .release_title
3338 .clone()
3339 .unwrap_or_else(|| format!("federation · {}", fed.owner_short())),
3340 release_year: fed.year,
3341 file_path: String::new(),
3342 content_id: fed.content_id.clone(),
3343 cover_path: None,
3344 audio_format: None,
3345 audio_bitrate: None,
3346 audio_sample_rate: None,
3347 audio_bit_depth: None,
3348 file_size_bytes: None,
3349 play_count: 0,
3350 fed: Some(fed.clone()),
3351 }
3352}
3353
3354fn fed_playlist_tracks(
3355 conn: &Connection,
3356 playlist_id: i64,
3357 playlist_sync_id: &str,
3358) -> Result<Vec<(i64, FederatedTrack)>> {
3359 let mut statement = conn.prepare(
3360 "SELECT position, item_id, owner, title, artist_names, featured_artist_names,
3361 year, duration_seconds, content_id, release_title, track_number, disc_number
3362 FROM fed_playlist_tracks f
3363 WHERE f.playlist_sync_id = ?1
3364 AND NOT EXISTS (
3365 SELECT 1 FROM playlist_tracks pt
3366 JOIN tracks t ON t.id = pt.track_id
3367 WHERE pt.playlist_id = ?2
3368 AND t.content_id = f.content_id
3369 )
3370 ORDER BY position, title COLLATE NOCASE",
3371 )?;
3372 let rows = statement.query_map(params![playlist_sync_id, playlist_id], |row| {
3373 Ok((row.get::<_, i64>(0)?, fed_track_from_offset_row(row, 1)?))
3374 })?;
3375 Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
3376}
3377
3378fn fed_track_from_row(row: &rusqlite::Row<'_>) -> rusqlite::Result<FederatedTrack> {
3379 fed_track_from_offset_row(row, 0)
3380}
3381
3382fn fed_track_from_offset_row(
3383 row: &rusqlite::Row<'_>,
3384 offset: usize,
3385) -> rusqlite::Result<FederatedTrack> {
3386 Ok(FederatedTrack {
3387 item_id: row.get(offset)?,
3388 owner: row.get(offset + 1)?,
3389 own: false,
3390 title: row.get(offset + 2)?,
3391 artist_names: split_joined_names(row.get(offset + 3)?),
3392 featured_artist_names: split_joined_names(row.get(offset + 4)?),
3393 year: row.get(offset + 5)?,
3394 duration_seconds: row
3395 .get::<_, Option<f64>>(offset + 6)?
3396 .map(|d| d.round() as i64),
3397 content_id: row.get(offset + 7)?,
3398 release_title: row.get(offset + 8)?,
3399 track_number: row.get(offset + 9)?,
3400 disc_number: row.get(offset + 10)?,
3401 })
3402}
3403
3404fn split_joined_names(names: String) -> Vec<String> {
3405 names
3406 .split("; ")
3407 .filter(|name| !name.is_empty())
3408 .map(str::to_string)
3409 .collect()
3410}
3411
3412fn register_norm_function(conn: &Connection) -> Result<()> {
3418 use rusqlite::functions::FunctionFlags;
3419 conn.create_scalar_function(
3420 "norm",
3421 1,
3422 FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
3423 |ctx| {
3424 let value: String = ctx.get(0)?;
3425 Ok(music_dht::normalize_name(&value))
3426 },
3427 )?;
3428 Ok(())
3429}
3430
3431pub fn audio_content_id(path: &str) -> Option<String> {
3433 let mut file = std::fs::File::open(path).ok()?;
3434 let mut hasher = blake3::Hasher::new();
3435 std::io::copy(&mut file, &mut hasher).ok()?;
3436 Some(format!("b3:{}", hasher.finalize().to_hex()))
3437}
3438
3439fn directory_size(path: &Path) -> u64 {
3440 let Ok(metadata) = std::fs::metadata(path) else {
3441 return 0;
3442 };
3443 if metadata.is_file() {
3444 return metadata.len();
3445 }
3446 let Ok(entries) = std::fs::read_dir(path) else {
3447 return 0;
3448 };
3449 entries
3450 .filter_map(|entry| entry.ok())
3451 .map(|entry| directory_size(&entry.path()))
3452 .sum()
3453}
3454
3455fn file_size(path: &Path) -> u64 {
3456 std::fs::metadata(path)
3457 .map(|metadata| metadata.len())
3458 .unwrap_or(0)
3459}
3460
3461fn sqlite_database_size(path: &Path) -> u64 {
3462 file_size(path)
3463 .saturating_add(file_size(&path_with_suffix(path, "-wal")))
3464 .saturating_add(file_size(&path_with_suffix(path, "-shm")))
3465}
3466
3467fn path_with_suffix(path: &Path, suffix: &str) -> PathBuf {
3468 let mut value = path.as_os_str().to_os_string();
3469 value.push(suffix);
3470 PathBuf::from(value)
3471}
3472
3473fn now_ms_i64() -> i64 {
3474 std::time::SystemTime::now()
3475 .duration_since(std::time::UNIX_EPOCH)
3476 .map(|duration| duration.as_millis() as i64)
3477 .unwrap_or(0)
3478}
3479
3480fn embedding_to_bytes(vector: &[f32]) -> Vec<u8> {
3481 let mut bytes = Vec::with_capacity(std::mem::size_of_val(vector));
3482 for value in vector {
3483 bytes.extend_from_slice(&value.to_le_bytes());
3484 }
3485 bytes
3486}
3487
3488fn embedding_from_bytes(dimensions: i64, bytes: &[u8]) -> Result<Vec<f32>> {
3489 anyhow::ensure!(dimensions > 0, "stored embedding has invalid dimensions");
3490 let dimensions = dimensions as usize;
3491 anyhow::ensure!(
3492 bytes.len() == dimensions * std::mem::size_of::<f32>(),
3493 "stored embedding byte length does not match its dimensions"
3494 );
3495 let vector: Vec<f32> = bytes
3496 .chunks_exact(4)
3497 .map(|chunk| f32::from_le_bytes([chunk[0], chunk[1], chunk[2], chunk[3]]))
3498 .collect();
3499 anyhow::ensure!(
3500 vector.iter().all(|value| value.is_finite()),
3501 "stored embedding contains a non-finite value"
3502 );
3503 Ok(vector)
3504}
3505
3506fn remote_artist_id(artist_key: &str) -> i64 {
3507 let hash = blake3::hash(artist_key.as_bytes());
3508 let mut bytes = [0u8; 8];
3509 bytes.copy_from_slice(&hash.as_bytes()[..8]);
3510 -(i64::from_be_bytes(bytes) & i64::MAX).max(1)
3511}
3512
3513fn ensure_schema_migrations(conn: &Connection) -> Result<()> {
3514 let fed_like_columns = table_columns(conn, "fed_likes")?;
3515 if !fed_like_columns
3516 .iter()
3517 .any(|column| column == "featured_artist_names")
3518 {
3519 conn.execute(
3520 "ALTER TABLE fed_likes
3521 ADD COLUMN featured_artist_names TEXT NOT NULL DEFAULT ''",
3522 [],
3523 )?;
3524 }
3525 if !fed_like_columns.iter().any(|column| column == "content_id") {
3526 conn.execute("ALTER TABLE fed_likes ADD COLUMN content_id TEXT", [])?;
3527 }
3528 if !fed_like_columns
3529 .iter()
3530 .any(|column| column == "liked_hlc_ms")
3531 {
3532 conn.execute("ALTER TABLE fed_likes ADD COLUMN liked_hlc_ms INTEGER", [])?;
3533 }
3534 let like_columns = table_columns(conn, "likes")?;
3535 if !like_columns.iter().any(|column| column == "liked_hlc_ms") {
3536 conn.execute("ALTER TABLE likes ADD COLUMN liked_hlc_ms INTEGER", [])?;
3537 }
3538 let track_columns = table_columns(conn, "tracks")?;
3539 if !track_columns.iter().any(|column| column == "content_id") {
3540 conn.execute("ALTER TABLE tracks ADD COLUMN content_id TEXT", [])?;
3541 }
3542 let playlist_columns = table_columns(conn, "playlists")?;
3543 if !playlist_columns.iter().any(|column| column == "sync_id") {
3544 conn.execute("ALTER TABLE playlists ADD COLUMN sync_id TEXT", [])?;
3545 conn.execute(
3546 "CREATE UNIQUE INDEX IF NOT EXISTS idx_playlists_sync_id
3547 ON playlists(sync_id)",
3548 [],
3549 )?;
3550 }
3551 let network_artist_columns = table_columns(conn, "network_artist_cache")?;
3552 if !network_artist_columns
3553 .iter()
3554 .any(|column| column == "remote_image_hint")
3555 {
3556 conn.execute(
3557 "ALTER TABLE network_artist_cache ADD COLUMN remote_image_hint TEXT",
3558 [],
3559 )?;
3560 }
3561 let embedding_columns = table_columns(conn, "track_embeddings")?;
3562 if !embedding_columns
3563 .iter()
3564 .any(|column| column == "routing_signature")
3565 {
3566 conn.execute(
3567 "ALTER TABLE track_embeddings ADD COLUMN routing_signature BLOB",
3568 [],
3569 )?;
3570 }
3571 let mut rows = conn.prepare("SELECT id, title FROM playlists WHERE sync_id IS NULL")?;
3572 let missing = rows
3573 .query_map([], |row| {
3574 Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
3575 })?
3576 .collect::<rusqlite::Result<Vec<_>>>()?;
3577 drop(rows);
3578 for (id, title) in missing {
3579 conn.execute(
3580 "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
3581 params![id, make_playlist_sync_id(id, &title)],
3582 )?;
3583 }
3584 Ok(())
3585}
3586
3587fn table_columns(conn: &Connection, table: &str) -> Result<Vec<String>> {
3588 let mut statement = conn.prepare(&format!("PRAGMA table_info({table})"))?;
3589 Ok(statement
3590 .query_map([], |row| row.get::<_, String>(1))?
3591 .collect::<rusqlite::Result<Vec<_>>>()?)
3592}
3593
3594fn make_playlist_sync_id(id: i64, title: &str) -> String {
3595 let now = std::time::SystemTime::now()
3596 .duration_since(std::time::UNIX_EPOCH)
3597 .map(|d| d.as_nanos())
3598 .unwrap_or(0);
3599 let seed = format!("playlist:{id}:{title}:{now}:{}", std::process::id());
3600 format!("pl_{}", &blake3::hash(seed.as_bytes()).to_hex()[..24])
3601}
3602
3603#[cfg(test)]
3604#[path = "tests.rs"]
3605mod tests;