1mod facets;
2mod geo;
3mod revisions;
4pub use revisions::ListRevision;
5
6use crate::error::{DomainError, ErrorCode};
7use crate::model::{Filter, Recording, Station};
8use anyhow::{Context, Result, bail};
9use rusqlite::{
10 Connection, OptionalExtension, TransactionBehavior, params, params_from_iter, types::Value,
11};
12use std::path::Path;
13
14const RECORDING_ORDER: &str =
15 "CASE WHEN json_valid(json) THEN json_extract(json,'$.started_at') END DESC,id DESC";
16
17pub struct Store {
18 pub(crate) conn: Connection,
19}
20
21impl Store {
22 pub(crate) fn open_writer(path: &Path) -> Result<Self> {
23 let conn = Connection::open(path)?;
26 crate::podcast::register_functions(&conn)?;
27 conn.busy_timeout(std::time::Duration::from_millis(100))?;
28 conn.execute_batch("PRAGMA foreign_keys=ON; PRAGMA synchronous=NORMAL;")?;
29 Ok(Self { conn })
30 }
31
32 #[cfg(test)]
33 pub(crate) fn test_connection(&self) -> &Connection {
34 &self.conn
35 }
36
37 #[cfg(test)]
38 pub(crate) fn test_connection_mut(&mut self) -> &mut Connection {
39 &mut self.conn
40 }
41
42 pub(crate) fn open_reader(path: &Path) -> Result<Self> {
43 let conn = Connection::open_with_flags(path, rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY)?;
44 crate::podcast::register_functions(&conn)?;
45 conn.busy_timeout(std::time::Duration::from_millis(100))?;
46 conn.execute_batch("PRAGMA cache_size=-4096;")?;
47 Ok(Self { conn })
48 }
49
50 pub(crate) fn interrupt_handle(&self) -> rusqlite::InterruptHandle {
51 self.conn.get_interrupt_handle()
52 }
53
54 pub(crate) fn cancel_queries_when(
55 &mut self,
56 cancel: impl FnMut() -> bool + Send + 'static,
57 ) -> Result<()> {
58 Ok(self.conn.progress_handler(1000, Some(cancel))?)
59 }
60
61 pub(crate) fn read_snapshot<T>(&self, read: impl FnOnce(&Self) -> Result<T>) -> Result<T> {
62 let tx = self.conn.unchecked_transaction()?;
63 tx.query_row("SELECT 1 FROM sqlite_schema LIMIT 1", [], |_| Ok(()))?;
66 let value = read(self)?;
67 tx.commit()?;
68 Ok(value)
69 }
70
71 pub fn open(path: &Path) -> Result<Self> {
72 let mut conn = Connection::open(path)?;
73 crate::podcast::register_functions(&conn)?;
74 conn.busy_timeout(std::time::Duration::from_secs(5))?;
75 conn.execute_batch(
76 "PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL;
77 PRAGMA cache_size=-4096; PRAGMA synchronous=NORMAL;",
78 )?;
79 let version: u32 = conn.pragma_query_value(None, "user_version", |r| r.get(0))?;
80 if version > 10 {
81 bail!(DomainError::new(ErrorCode::DatabaseTooNew));
82 }
83 if version < 10 {
84 let tx = conn.transaction_with_behavior(TransactionBehavior::Immediate)?;
85 let version: u32 = tx.pragma_query_value(None, "user_version", |r| r.get(0))?;
87 match version {
88 0 => tx.execute_batch(
89 "CREATE TABLE stations (
90 uuid TEXT PRIMARY KEY, name TEXT NOT NULL, search TEXT NOT NULL,
91 country TEXT NOT NULL, languages TEXT NOT NULL, tags TEXT NOT NULL,
92 bitrate INTEGER NOT NULL, popularity INTEGER NOT NULL,
93 custom INTEGER NOT NULL, active INTEGER NOT NULL, json TEXT NOT NULL);
94 CREATE INDEX station_country ON stations(country, bitrate);
95 CREATE INDEX station_popularity ON stations(popularity DESC);
96 CREATE TABLE favorites (
97 uuid TEXT PRIMARY KEY REFERENCES stations(uuid), created_at TEXT NOT NULL);
98 CREATE TABLE history (
99 id INTEGER PRIMARY KEY, uuid TEXT NOT NULL REFERENCES stations(uuid), played_at TEXT NOT NULL);
100 CREATE INDEX history_station ON history(uuid);
101 CREATE TABLE streams (
102 uuid TEXT NOT NULL REFERENCES stations(uuid), url TEXT NOT NULL, PRIMARY KEY(uuid,url));
103 CREATE TABLE recordings (
104 id TEXT PRIMARY KEY, status TEXT NOT NULL, json TEXT NOT NULL,
105 recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1)));
106 CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);"
107 )?,
108 1 => tx.execute_batch(
109 "ALTER TABLE recordings ADD COLUMN recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1));
110 -- Translate legacy diagnostics once, never during normal queries.
111 UPDATE recordings SET recovery_pending=1 WHERE status='interrupted' AND json_extract(json,'$.error') LIKE '恢复失败:%';"
112 )?,
113 2..=10 => {},
114 _ => bail!(DomainError::new(ErrorCode::DatabaseTooNew)),
115 }
116 if version < 3 {
117 tx.execute_batch(
118 "ALTER TABLE stations ADD COLUMN normalized_url TEXT;
119 CREATE INDEX station_url ON stations(normalized_url) WHERE active=1;",
120 )?;
121 let mut select = tx.prepare("SELECT uuid,json FROM stations")?;
122 let mut update =
123 tx.prepare("UPDATE stations SET normalized_url=?2 WHERE uuid=?1")?;
124 let rows = select
125 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)))?;
126 for row in rows {
127 let (uuid, json) = row?;
128 let station: Station = serde_json::from_str(&json)?;
129 update.execute(params![
130 uuid,
131 crate::service::normalized_url(station.primary_url()).ok()
132 ])?;
133 }
134 }
135 if version < 4 {
136 tx.execute_batch(
137 "CREATE TABLE facets_cache (
138 kind INTEGER NOT NULL,value TEXT NOT NULL,n INTEGER NOT NULL CHECK(n>=0),
139 PRIMARY KEY(kind,value)) WITHOUT ROWID;",
140 )?;
141 facets::rebuild(&tx)?;
142 }
143 if version < 5 {
144 tx.execute_batch(
145 "CREATE INDEX station_sort ON stations(popularity DESC,name,uuid,active,custom,bitrate);
146 CREATE INDEX station_name ON stations(name COLLATE NOCASE,uuid,active,custom,bitrate);
147 CREATE INDEX station_bitrate ON stations(bitrate DESC,name,uuid,active,custom);
148 CREATE INDEX station_active ON stations(active) WHERE active=1;
149 CREATE INDEX station_directory ON stations(active) WHERE active=1 AND custom=0;
150 DROP INDEX IF EXISTS station_popularity;
151 CREATE VIRTUAL TABLE station_search USING fts5(search,
152 content='stations',content_rowid='rowid',columnsize=0,detail=none,
153 tokenize='trigram case_sensitive 1');
154 INSERT INTO station_search(station_search) VALUES ('rebuild');
155 CREATE VIRTUAL TABLE station_search_terms USING fts5vocab(station_search,'row');
156 CREATE TABLE station_search_stats(term TEXT PRIMARY KEY,n INTEGER NOT NULL) WITHOUT ROWID;
157 INSERT INTO station_search_stats SELECT term,doc FROM station_search_terms;
158 INSERT INTO station_search_stats VALUES ('',(SELECT count(*) FROM stations));
159 CREATE TRIGGER station_search_insert AFTER INSERT ON stations BEGIN
160 INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
161 END;
162 CREATE TRIGGER station_search_delete AFTER DELETE ON stations BEGIN
163 INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
164 END;
165 CREATE TRIGGER station_search_update AFTER UPDATE OF search ON stations
166 WHEN old.search IS NOT new.search BEGIN
167 INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
168 INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
169 END;
170 ANALYZE;",
171 )?;
172 }
173 if version < 6 {
174 geo::migrate(&tx)?;
175 }
176 if version < 7 {
177 revisions::migrate(&tx)?;
178 }
179 if version < 8 {
180 tx.execute_batch(&format!(
181 "CREATE INDEX IF NOT EXISTS recording_started ON recordings({RECORDING_ORDER});"
182 ))?;
183 }
184 if version < 9 {
185 crate::podcast::migrate(&tx)?;
186 }
187 if version < 10 {
188 crate::podcast::migrate_library(&tx)?;
189 }
190 tx.pragma_update(None, "user_version", 10)?;
191 tx.commit()?;
192 }
193 Ok(Self { conn })
194 }
195
196 pub fn upsert(&self, station: &Station) -> Result<()> {
197 let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
198 Self::write_station(&tx, "stations", station)?;
199 geo::changed(&tx)?;
200 tx.commit()?;
201 Ok(())
202 }
203
204 pub fn bootstrap(
205 &mut self,
206 rows: &[Station],
207 mut cancelled: impl FnMut() -> bool,
208 ) -> Result<usize> {
209 let tx = self.conn.transaction()?;
210 for row in rows {
211 if cancelled() {
212 bail!(DomainError::new(ErrorCode::Cancelled));
213 }
214 Self::write_station_with_policy(&tx, "stations", row, false, false)?;
215 }
216 if cancelled() {
217 bail!(DomainError::new(ErrorCode::Cancelled));
218 }
219 if rows.is_empty() {
220 bail!(DomainError::new(ErrorCode::EmptyCatalog));
221 }
222 facets::rebuild(&tx)?;
223 if cancelled() {
224 bail!(DomainError::new(ErrorCode::Cancelled));
225 }
226 geo::changed(&tx)?;
227 tx.commit()?;
228 let _ = self.refresh_search_stats();
230 Ok(rows.len())
231 }
232
233 fn write_station(conn: &Connection, table: &str, s: &Station) -> Result<()> {
234 Self::write_station_with_policy(conn, table, s, false, table == "stations")
235 }
236
237 fn write_station_with_policy(
238 conn: &Connection,
239 table: &str,
240 s: &Station,
241 custom: bool,
242 maintain_facets: bool,
243 ) -> Result<()> {
244 if s.stationuuid.is_empty() || s.name.trim().is_empty() {
245 bail!(DomainError::new(ErrorCode::InvalidStation));
246 }
247 uuid::Uuid::parse_str(&s.stationuuid).context(DomainError::new(ErrorCode::InvalidUuid))?;
248 let before = if maintain_facets {
249 facets::station_values(conn, &s.stationuuid)?
250 } else {
251 Default::default()
252 };
253 let sql = format!("INSERT INTO {table} (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y)
254 VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14)
255 ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
256 country=excluded.country,languages=excluded.languages,tags=excluded.tags,
257 bitrate=excluded.bitrate,popularity=excluded.popularity,custom=excluded.custom,active=excluded.active,json=excluded.json,normalized_url=excluded.normalized_url,geo_x=excluded.geo_x,geo_y=excluded.geo_y
258 WHERE {table}.custom=0 OR {custom}");
259 let normalize = |value: &str| {
263 value
264 .split(',')
265 .map(|v| v.trim().to_lowercase())
266 .filter(|v| !v.is_empty())
267 .collect::<Vec<_>>()
268 .join(",")
269 };
270 let languages = normalize(&format!("{},{}", s.language, s.languagecodes));
271 let coordinates = crate::geo::coordinates(s.geo_lat, s.geo_long);
272 conn.prepare_cached(&sql)?.execute(params![
273 s.stationuuid,
274 s.name,
275 s.search_text(),
276 s.countrycode.to_uppercase(),
277 format!(",{languages},"),
278 format!(",{},", normalize(&s.tags)),
279 s.bitrate,
280 s.clickcount.min(i64::MAX as u64),
281 s.custom,
282 s.active,
283 serde_json::to_string(s)?,
284 crate::service::normalized_url(s.primary_url()).ok(),
285 coordinates.map(|p| p.0),
286 coordinates.map(|p| p.1)
287 ])?;
288 if maintain_facets {
289 facets::update(
290 conn,
291 &before,
292 &facets::station_values(conn, &s.stationuuid)?,
293 )?;
294 }
295 Ok(())
296 }
297
298 pub fn set_meta(&self, key: &str, value: &str) -> Result<()> {
299 Self::write_meta(&self.conn, key, value)
300 }
301
302 fn write_meta(conn: &Connection, key: &str, value: &str) -> Result<()> {
303 conn.execute(
304 "INSERT INTO meta(key,value) VALUES (?1,?2) ON CONFLICT(key) DO UPDATE SET value=excluded.value WHERE meta.value IS NOT excluded.value",
305 params![key, value],
306 )?;
307 Ok(())
308 }
309
310 pub(crate) fn save_session(
311 &mut self,
312 preferences: &std::collections::BTreeMap<String, String>,
313 plays: &[(String, String)],
314 ) -> Result<()> {
315 let tx = self
316 .conn
317 .transaction_with_behavior(TransactionBehavior::Immediate)?;
318 for (uuid, at) in plays {
319 tx.execute(
320 "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
321 params![uuid, at],
322 )?;
323 let threshold = tx.last_insert_rowid().saturating_sub(1000);
324 tx.execute("DELETE FROM history WHERE id<=?1", [threshold])?;
325 Self::write_meta(&tx, "pref_last_station", uuid)?;
326 Self::write_meta(&tx, "pref_last_media", "station")?;
327 }
328 for (key, value) in preferences {
329 if let Some(id) = key.strip_prefix("podcast_progress:") {
330 Self::write_podcast_progress(&tx, id, &serde_json::from_str(value)?)?;
331 continue;
332 }
333 if let Some(id) = key.strip_prefix("podcast_played:") {
334 tx.execute(
335 "INSERT INTO podcast_history(episode_id,played_at) VALUES (?1,?2)",
336 params![id, value],
337 )?;
338 Self::write_meta(&tx, "pref_last_episode", id)?;
339 Self::write_meta(&tx, "pref_last_media", "episode")?;
340 continue;
341 }
342 Self::write_meta(&tx, key, value)?;
343 }
344 tx.commit()?;
345 Ok(())
346 }
347
348 pub fn directory_count(&self) -> Result<usize> {
349 Ok(self.conn.query_row(
350 "SELECT count(*) FROM stations WHERE custom=0 AND active=1",
351 [],
352 |r| r.get(0),
353 )?)
354 }
355
356 pub fn incoming_count(&self) -> Result<usize> {
357 Ok(self
358 .conn
359 .query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?)
360 }
361
362 pub fn begin_sync(&self) -> Result<()> {
363 self.conn.execute_batch(
364 "DROP TABLE IF EXISTS temp.incoming;
365 CREATE TEMP TABLE incoming AS SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y FROM stations WHERE 0;
366 CREATE UNIQUE INDEX temp.incoming_uuid ON incoming(uuid);",
367 )?;
368 Ok(())
369 }
370
371 pub fn stage(&mut self, rows: &[Station]) -> Result<()> {
372 let tx = self.conn.transaction()?;
373 for row in rows {
374 let mut s = row.clone();
375 s.active = true;
376 s.custom = false;
377 Self::write_station(&tx, "incoming", &s)?;
378 }
379 tx.commit()?;
380 Ok(())
381 }
382
383 pub fn finish_sync(&mut self) -> Result<usize> {
384 self.finish_sync_cancellable(|| false)
385 }
386
387 pub(crate) fn finish_sync_cancellable(
388 &mut self,
389 mut cancelled: impl FnMut() -> bool,
390 ) -> Result<usize> {
391 let tx = self
392 .conn
393 .transaction_with_behavior(TransactionBehavior::Immediate)?;
394 let count: usize = tx.query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?;
395 if count == 0 {
396 bail!(DomainError::new(ErrorCode::EmptyCatalog));
397 }
398 let search_changed: bool = tx.query_row(
399 "SELECT EXISTS(SELECT 1 FROM incoming i LEFT JOIN stations s ON s.uuid=i.uuid
400 WHERE s.uuid IS NULL OR (s.custom=0 AND s.search IS NOT i.search))",
401 [],
402 |r| r.get(0),
403 )?;
404 let facet_delta = facets::publication_delta(&tx)?;
405 if cancelled() {
406 bail!(DomainError::new(ErrorCode::Cancelled));
407 }
408 let removed = tx.execute(
409 "UPDATE stations SET active=0 WHERE custom=0 AND active=1
410 AND NOT EXISTS(SELECT 1 FROM incoming i WHERE i.uuid=stations.uuid)",
411 [],
412 )?;
413 let updated = tx.execute(
414 "INSERT INTO stations (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y) SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y FROM incoming WHERE true
415 ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
416 country=excluded.country,languages=excluded.languages,tags=excluded.tags,
417 bitrate=excluded.bitrate,popularity=excluded.popularity,active=1,json=excluded.json,normalized_url=excluded.normalized_url,geo_x=excluded.geo_x,geo_y=excluded.geo_y
418 WHERE stations.custom=0 AND (
419 stations.active<>1 OR stations.json IS NOT excluded.json OR
420 stations.name IS NOT excluded.name OR stations.search IS NOT excluded.search OR
421 stations.country IS NOT excluded.country OR stations.languages IS NOT excluded.languages OR
422 stations.tags IS NOT excluded.tags OR stations.bitrate IS NOT excluded.bitrate OR
423 stations.popularity IS NOT excluded.popularity OR stations.normalized_url IS NOT excluded.normalized_url OR
424 stations.geo_x IS NOT excluded.geo_x OR stations.geo_y IS NOT excluded.geo_y)", [],
425 )?;
426 tx.execute(
427 "INSERT OR REPLACE INTO meta(key,value) VALUES ('last_sync',?1)",
428 [chrono::Utc::now().to_rfc3339()],
429 )?;
430 facets::apply_delta(&tx, facet_delta)?;
431 if cancelled() {
432 bail!(DomainError::new(ErrorCode::Cancelled));
433 }
434 if removed + updated > 0 {
435 geo::changed(&tx)?;
436 }
437 tx.commit()?;
438 if search_changed {
441 let _ = self.refresh_search_stats();
442 }
443 if removed + updated > 0 {
444 let _ = self.conn.execute_batch("PRAGMA optimize;");
445 }
446 Ok(count)
447 }
448
449 pub fn revision(&self) -> Result<(u64, u64)> {
453 let external = self
454 .conn
455 .pragma_query_value(None, "data_version", |r| r.get(0))?;
456 Ok((external, self.conn.total_changes()))
457 }
458
459 pub fn meta(&self, key: &str) -> Result<Option<String>> {
460 Ok(self
461 .conn
462 .query_row("SELECT value FROM meta WHERE key=?1", [key], |r| r.get(0))
463 .optional()?)
464 }
465
466 pub fn count(&self) -> Result<usize> {
467 Ok(self
468 .conn
469 .query_row("SELECT count(*) FROM stations WHERE active=1", [], |r| {
470 r.get(0)
471 })?)
472 }
473
474 pub fn station(&self, uuid: &str) -> Result<Station> {
475 let row = self
476 .conn
477 .query_row(
478 "SELECT s.json,s.active,f.uuid IS NOT NULL FROM stations s
479 LEFT JOIN favorites f ON f.uuid=s.uuid WHERE s.uuid=?1",
480 [uuid],
481 |r| {
482 Ok((
483 r.get::<_, String>(0)?,
484 r.get::<_, bool>(1)?,
485 r.get::<_, bool>(2)?,
486 ))
487 },
488 )
489 .optional()?
490 .context(DomainError::new(ErrorCode::StationNotFound))?;
491 let mut s: Station = serde_json::from_str(&row.0)?;
492 s.active = row.1;
493 s.favorite = row.2;
494 Ok(s)
495 }
496
497 fn conditions(&self, filter: &Filter) -> (String, Vec<Value>, &'static str) {
498 self.conditions_with_spatial(filter, true)
499 }
500
501 fn conditions_with_spatial(
502 &self,
503 filter: &Filter,
504 spatial: bool,
505 ) -> (String, Vec<Value>, &'static str) {
506 let mut clauses = vec![
507 "(s.active=1 OR s.custom=1 OR EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid))"
508 .to_owned(),
509 ];
510 let mut args = vec![];
511 let mut source =
514 if filter.country.is_none() && (filter.language.is_some() || filter.tag.is_some()) {
515 "stations s NOT INDEXED"
516 } else {
517 "stations s"
518 };
519 if filter.favorites {
520 clauses.push("EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)".into());
521 }
522 if filter.custom {
523 clauses.push("s.custom=1".into());
524 }
525 if filter.history {
526 clauses[0] = "1".into();
527 }
528 let mut text_candidates = false;
529 let query = filter.query.trim();
530 if !query.is_empty() {
531 let codes: Vec<_> = crate::territories::matching_codes(query).collect();
534 let normalized = query.to_lowercase();
535 source = "stations s NOT INDEXED";
536 if !filter.history
537 && let Some(candidate) = self.trigram_candidate(&normalized)
538 {
539 text_candidates = true;
540 let mut candidates =
543 "SELECT rowid FROM station_search WHERE station_search MATCH ?".to_owned();
544 args.push(candidate.into());
545 if !codes.is_empty() {
546 candidates.push_str(&format!(
547 " UNION SELECT rowid FROM stations WHERE country IN ({})",
548 vec!["?"; codes.len()].join(",")
549 ));
550 args.extend(codes.iter().map(|code| Value::from((*code).to_owned())));
551 }
552 clauses.push(
553 if codes.is_empty()
554 && !matches!(filter.geo, Some(crate::geo::GeoFilter::Cell(_)))
555 {
556 source =
557 "station_search CROSS JOIN stations s ON s.rowid=station_search.rowid";
558 "station_search MATCH ?".to_owned()
559 } else {
560 format!("s.rowid IN ({candidates})")
561 },
562 );
563 }
564 if codes.is_empty() {
565 clauses.push("instr(s.search,?)>0".into());
566 } else {
567 let placeholders = vec!["?"; codes.len()].join(",");
568 clauses.push(format!(
569 "(instr(s.search,?)>0 OR s.country IN ({placeholders}))"
570 ));
571 }
572 args.push(normalized.into());
573 args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
574 }
575 if let Some(country) = &filter.country {
576 clauses.push("s.country=?".into());
577 args.push(country.to_uppercase().into());
578 }
579 for (col, value) in [("languages", &filter.language), ("tags", &filter.tag)] {
580 if let Some(value) = value {
581 clauses.push(format!("instr(s.{col},?)>0"));
582 args.push(format!(",{},", value.trim().to_lowercase()).into());
583 }
584 }
585 if filter.min_bitrate > 0 {
586 clauses.push("s.bitrate>=?".into());
587 args.push((filter.min_bitrate as i64).into());
588 }
589 if let Some(geo) = filter.geo {
590 clauses[0] = "s.active=1".into();
591 match geo {
592 crate::geo::GeoFilter::Missing => clauses.push("s.geo_x IS NULL".into()),
593 crate::geo::GeoFilter::Cell(bounds) if spatial => {
594 clauses.push(geo::predicate(bounds, &mut args));
595 if (!text_candidates && bounds.width < crate::geo::WORLD_WIDTH)
598 || geo::plain(filter)
599 {
600 source = "stations s INDEXED BY station_geo";
601 }
602 if filter.query.trim().is_empty()
603 && let Some(country) = filter.country.as_ref().filter(|c| !c.is_empty())
604 && self
605 .conn
606 .prepare_cached("SELECT n FROM facets_cache WHERE kind=0 AND value=?1")
607 .and_then(|mut stmt| {
608 stmt.query_row([country.to_uppercase()], |r| r.get::<_, usize>(0))
609 .optional()
610 })
611 .is_ok_and(|n| n.unwrap_or(0) <= geo::SORT_THRESHOLD)
612 {
613 source = "stations s INDEXED BY station_country";
616 }
617 }
618 crate::geo::GeoFilter::Cell(_) => {}
619 }
620 }
621 if filter.history {
622 source = "(SELECT uuid,max(id) AS last_id FROM history GROUP BY uuid) recent
626 CROSS JOIN stations s ON s.uuid=recent.uuid";
627 }
628 (clauses.join(" AND "), args, source)
629 }
630
631 fn trigram_candidate(&self, normalized: &str) -> Option<String> {
634 if normalized.chars().count() < 3 || normalized.contains('\0') {
635 return None;
636 }
637 let chars: Vec<_> = normalized.chars().collect();
638 let stride = ((chars.len() - 2) / 16).max(1);
639 let mut best = (usize::MAX, chars[..3].iter().collect::<String>());
640 let terms: std::collections::BTreeSet<String> = chars
642 .windows(3)
643 .step_by(stride)
644 .take(16)
645 .map(|part| part.iter().collect())
646 .collect();
647 for term in terms {
648 let frequency = self
649 .conn
650 .prepare_cached("SELECT n FROM station_search_stats WHERE term=?1")
651 .and_then(|mut s| s.query_row([&term], |r| r.get::<_, usize>(0)).optional());
652 let frequency = match frequency {
655 Ok(Some(n)) => n,
656 Ok(None) => 0,
657 Err(_) => continue,
658 };
659 if frequency < best.0 {
660 best = (frequency, term);
661 }
662 if frequency == 0 {
663 break;
664 }
665 }
666 let total: usize = self
667 .conn
668 .query_row(
669 "SELECT n FROM station_search_stats WHERE term=''",
670 [],
671 |r| r.get(0),
672 )
673 .unwrap_or(0);
674 if total > 0 && best.0 > total / 4 {
677 return None;
678 }
679 Some(format!("\"{}\"", best.1.replace('"', "\"\"")))
680 }
681
682 fn refresh_search_stats(&self) -> Result<()> {
683 let stats = {
687 let mut stmt = self.conn.prepare(
688 "SELECT term,doc FROM station_search_terms
689 UNION ALL SELECT '',count(*) FROM stations",
690 )?;
691 stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))?
692 .collect::<rusqlite::Result<Vec<_>>>()?
693 };
694 let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
697 tx.execute("DELETE FROM station_search_stats", [])?;
698 {
699 let mut stmt = tx.prepare("INSERT INTO station_search_stats VALUES (?1,?2)")?;
700 for (term, count) in stats {
701 stmt.execute(params![term, count])?;
702 }
703 }
704 tx.commit()?;
705 Ok(())
706 }
707
708 fn search_order(filter: &Filter) -> &'static str {
709 if filter.history {
710 "recent.last_id DESC"
711 } else {
712 match filter.sort {
713 crate::model::Sort::Name => "s.name COLLATE NOCASE,s.uuid",
714 crate::model::Sort::Bitrate => "s.bitrate DESC,s.name,s.uuid",
715 crate::model::Sort::Votes => "json_extract(s.json,'$.votes') DESC,s.name,s.uuid",
716 crate::model::Sort::Clickcount => "s.popularity DESC,s.name,s.uuid",
717 crate::model::Sort::Random => "random()",
718 }
719 }
720 }
721
722 pub fn station_position(&self, uuid: &str, filter: &Filter) -> Result<Option<usize>> {
724 use crate::model::Sort;
725 let (conditions, mut args, source) = self.conditions(filter);
726 if !filter.history && matches!(filter.sort, Sort::Clickcount | Sort::Bitrate | Sort::Name) {
727 let tx = self.conn.unchecked_transaction()?;
728 let mut target_args = args.clone();
729 target_args.push(uuid.to_owned().into());
730 let target = tx
731 .query_row(
732 &format!(
733 "SELECT s.popularity,s.bitrate,s.name FROM {source}
734 WHERE {conditions} AND s.uuid=?"
735 ),
736 params_from_iter(target_args),
737 |r| {
738 Ok((
739 r.get::<_, i64>(0)?,
740 r.get::<_, i64>(1)?,
741 r.get::<_, String>(2)?,
742 ))
743 },
744 )
745 .optional()?;
746 let Some((popularity, bitrate, name)) = target else {
747 return Ok(None);
748 };
749 let count = |predicate: &str, values: Vec<Value>| -> Result<usize> {
750 let mut values_with_filter = args.clone();
751 values_with_filter.extend(values);
752 Ok(tx
753 .prepare_cached(&format!(
754 "SELECT count(*) FROM {source}
755 WHERE {conditions} AND ({predicate})"
756 ))?
757 .query_row(params_from_iter(values_with_filter), |r| r.get(0))?)
758 };
759 let position = if filter.sort == Sort::Name {
762 count("s.name COLLATE NOCASE < ?", vec![name.clone().into()])?
763 + count(
764 "s.name COLLATE NOCASE = ? AND s.uuid < ?",
765 vec![name.into(), uuid.to_owned().into()],
766 )?
767 } else {
768 let (column, value) = if filter.sort == Sort::Bitrate {
769 ("bitrate", bitrate)
770 } else {
771 ("popularity", popularity)
772 };
773 count(&format!("s.{column}>?"), vec![value.into()])?
774 + count(
775 &format!("s.{column}=? AND (s.name,s.uuid)<(?,?)"),
776 vec![value.into(), name.into(), uuid.to_owned().into()],
777 )?
778 };
779 tx.commit()?;
780 return Ok(Some(position));
781 }
782 let order = Self::search_order(filter);
783 let sql = format!(
784 "WITH ranked AS (
785 SELECT s.uuid,row_number() OVER (ORDER BY {order})-1 AS position
786 FROM {source} WHERE {conditions}
787 ) SELECT position FROM ranked WHERE uuid=?"
788 );
789 args.push(uuid.to_owned().into());
790 Ok(self
791 .conn
792 .query_row(&sql, params_from_iter(args), |row| row.get::<_, i64>(0))
793 .optional()?
794 .map(|position| position as usize))
795 }
796
797 pub fn search(&self, filter: &Filter) -> Result<Vec<Station>> {
798 let _snapshot = if filter.geo.is_some() && self.conn.is_autocommit() {
801 Some(self.conn.unchecked_transaction()?)
802 } else {
803 None
804 };
805 let (mut conditions, mut args, mut source) = self.conditions(filter);
806 if let Some(crate::geo::GeoFilter::Cell(bounds)) = filter.geo
811 && !filter.history
812 && let Some(index) = match filter.sort {
813 crate::model::Sort::Clickcount => Some("stations s INDEXED BY station_sort"),
814 crate::model::Sort::Name => Some("stations s INDEXED BY station_name"),
815 crate::model::Sort::Bitrate => Some("stations s INDEXED BY station_bitrate"),
816 _ => None,
817 }
818 {
819 let probe = format!(
820 "SELECT s.rowid FROM {source} WHERE {conditions} LIMIT {}",
821 geo::SORT_THRESHOLD + 1
822 );
823 let ids = if geo::plain(filter) {
826 None
827 } else {
828 Some(
829 self.conn
830 .prepare_cached(&probe)?
831 .query_map(params_from_iter(&args), |r| r.get::<_, i64>(0))?
832 .collect::<rusqlite::Result<Vec<_>>>()?,
833 )
834 };
835 let n = if let Some(ids) = &ids {
836 ids.len()
837 } else {
838 self.conn
839 .prepare_cached(&format!("SELECT count(*) FROM ({probe})"))?
840 .query_row(params_from_iter(&args), |r| r.get::<_, usize>(0))?
841 };
842 if n == 0 {
843 return Ok(vec![]);
844 }
845 if n <= geo::SORT_THRESHOLD
846 && let Some(ids) = ids
847 {
848 conditions = format!("s.rowid IN ({})", vec!["?"; ids.len()].join(","));
849 args = ids.into_iter().map(Value::Integer).collect();
850 source = "stations s";
851 } else if n > geo::SORT_THRESHOLD {
852 let mut spatial_args = vec![];
853 let spatial = geo::predicate(bounds, &mut spatial_args);
854 let candidates = format!(
855 "SELECT s.rowid FROM stations s INDEXED BY station_geo WHERE s.active=1 AND {spatial}"
856 );
857 (conditions, args, _) = self.conditions_with_spatial(filter, false);
861 conditions = format!("s.rowid IN ({candidates}) AND {conditions}");
862 spatial_args.extend(args);
863 args = spatial_args;
864 source = index;
865 }
866 }
867 let order = Self::search_order(filter);
868 let page_order = if filter.history {
871 " ORDER BY page.last_id DESC".to_owned()
872 } else if filter.sort == crate::model::Sort::Random {
873 String::new()
874 } else {
875 format!(" ORDER BY {order}")
876 };
877 let history_column = if filter.history {
878 ",recent.last_id"
879 } else {
880 ""
881 };
882 let sql = format!(
884 "WITH page AS MATERIALIZED (
885 SELECT s.rowid AS id{history_column} FROM {source}
886 WHERE {conditions} ORDER BY {order} LIMIT ? OFFSET ?)
887 SELECT s.json,s.active,EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)
888 FROM page JOIN stations s ON s.rowid=page.id{page_order}"
889 );
890 args.push((filter.limit.clamp(1, 1000) as i64).into());
891 args.push((filter.offset.min(i64::MAX as usize) as i64).into());
892 let mut stmt = self.conn.prepare_cached(&sql)?;
893 let rows = stmt.query_map(params_from_iter(args), |r| {
894 Ok((
895 r.get::<_, String>(0)?,
896 r.get::<_, bool>(1)?,
897 r.get::<_, bool>(2)?,
898 ))
899 })?;
900 rows.map(|row| {
901 let (json, active, favorite) = row?;
902 let mut s: Station = serde_json::from_str(&json)?;
903 s.active = active;
904 s.favorite = favorite;
905 Ok(s)
906 })
907 .collect()
908 }
909
910 pub fn random(&self, filter: &Filter, exclude: &[String]) -> Result<Option<Station>> {
911 let (mut conditions, mut args, source) = self.conditions(filter);
912 if !exclude.is_empty() {
913 let placeholders = vec!["?"; exclude.len()].join(",");
914 conditions.push_str(&format!(" AND s.uuid NOT IN ({placeholders})"));
915 args.extend(exclude.iter().cloned().map(Value::Text));
916 }
917 let sql = format!(
918 "SELECT s.uuid FROM {source}
919 WHERE {conditions} ORDER BY random() LIMIT 1"
920 );
921 let selected = self
922 .conn
923 .query_row(&sql, params_from_iter(args), |r| r.get::<_, String>(0))
924 .optional()?;
925 selected.map(|id| self.station(&id)).transpose()
926 }
927
928 pub fn facets(
929 &self,
930 kind: crate::model::FacetKind,
931 prefix: &str,
932 ) -> Result<Vec<crate::model::Facet>> {
933 self.facets_limited(kind, prefix, usize::MAX)
934 }
935
936 pub fn facets_limited(
937 &self,
938 kind: crate::model::FacetKind,
939 prefix: &str,
940 limit: usize,
941 ) -> Result<Vec<crate::model::Facet>> {
942 use crate::model::{Facet, FacetKind};
943 let (kind, prefix) = match kind {
944 FacetKind::Country => (0, prefix.trim().to_uppercase()),
945 FacetKind::Language => (1, prefix.trim().to_lowercase()),
946 FacetKind::Tag => (2, prefix.trim().to_lowercase()),
947 };
948 let codes = if kind == 0 {
949 crate::territories::matching_prefix_codes(&prefix).collect::<Vec<_>>()
950 } else {
951 Vec::new()
952 };
953 let localized = if codes.is_empty() {
956 String::new()
957 } else {
958 let placeholders = (4..4 + codes.len())
959 .map(|index| format!("?{index}"))
960 .collect::<Vec<_>>()
961 .join(",");
962 format!(" OR value IN ({placeholders})")
963 };
964 let mut args: Vec<Value> = vec![
965 kind.into(),
966 prefix.into(),
967 (limit.min(i64::MAX as usize) as i64).into(),
968 ];
969 args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
970 let mut stmt = self.conn.prepare_cached(&format!(
971 "SELECT value,n FROM facets_cache WHERE kind=?1
972 AND (substr(value,1,length(?2))=?2{localized}) ORDER BY value LIMIT ?3",
973 ))?;
974 let rows = stmt.query_map(params_from_iter(args), |r| {
975 Ok(Facet {
976 value: r.get(0)?,
977 count: r.get(1)?,
978 })
979 })?;
980 Ok(rows.collect::<rusqlite::Result<_>>()?)
981 }
982
983 pub fn favorite(&self, uuid: &str, add: bool) -> Result<()> {
984 self.station(uuid)?;
985 if add {
986 self.conn.execute(
987 "INSERT OR IGNORE INTO favorites(uuid,created_at) VALUES (?1,?2)",
988 params![uuid, chrono::Utc::now().to_rfc3339()],
989 )?;
990 } else {
991 self.conn
992 .execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
993 }
994 Ok(())
995 }
996
997 pub fn toggle_favorite(&mut self, uuid: &str) -> Result<bool> {
998 let tx = self
999 .conn
1000 .transaction_with_behavior(TransactionBehavior::Immediate)?;
1001 tx.query_row("SELECT 1 FROM stations WHERE uuid=?1", [uuid], |r| {
1002 r.get::<_, u8>(0)
1003 })
1004 .optional()?
1005 .context(DomainError::new(ErrorCode::StationNotFound))?;
1006 let removed = tx.execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
1007 let added = removed == 0;
1008 if added {
1009 tx.execute(
1010 "INSERT INTO favorites(uuid,created_at) VALUES (?1,?2)",
1011 params![uuid, chrono::Utc::now().to_rfc3339()],
1012 )?;
1013 }
1014 tx.commit()?;
1015 Ok(added)
1016 }
1017
1018 pub fn played(&self, uuid: &str) -> Result<()> {
1019 self.conn.execute(
1020 "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
1021 params![uuid, chrono::Utc::now().to_rfc3339()],
1022 )?;
1023 let threshold = self.conn.last_insert_rowid().saturating_sub(1000);
1024 self.conn
1025 .execute("DELETE FROM history WHERE id <= ?1", [threshold])?;
1026 Ok(())
1027 }
1028
1029 pub fn add_backup(&self, uuid: &str, url: &str) -> Result<()> {
1030 crate::config::validate_url(url)?;
1031 self.conn.execute(
1032 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1033 params![uuid, url],
1034 )?;
1035 Ok(())
1036 }
1037
1038 pub fn save_custom(&mut self, station: &Station, backups: Option<&[String]>) -> Result<()> {
1039 let tx = self
1040 .conn
1041 .transaction_with_behavior(TransactionBehavior::Immediate)?;
1042 let key = crate::service::normalized_url(station.primary_url())?;
1043 if let Some(uuid) = tx.query_row(
1044 "SELECT uuid FROM stations WHERE active=1 AND normalized_url=?1 AND uuid<>?2 LIMIT 1",
1045 params![key, station.stationuuid], |r| r.get::<_, String>(0),
1046 ).optional()? {
1047 bail!(DomainError::new(ErrorCode::DuplicateStation).with("uuid", uuid));
1048 }
1049 Self::write_station_with_policy(&tx, "stations", station, true, true)?;
1050 if let Some(backups) = backups {
1051 tx.execute("DELETE FROM streams WHERE uuid=?1", [&station.stationuuid])?;
1052 for url in backups {
1053 crate::config::validate_url(url)?;
1054 tx.execute(
1055 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1056 params![station.stationuuid, url],
1057 )?;
1058 }
1059 }
1060 geo::changed(&tx)?;
1061 tx.commit()?;
1062 Ok(())
1063 }
1064
1065 pub fn remove_custom(&mut self, uuid: &str, keep_references: bool) -> Result<bool> {
1066 let tx = self
1067 .conn
1068 .transaction_with_behavior(TransactionBehavior::Immediate)?;
1069 let json: String = tx
1070 .query_row(
1071 "SELECT json FROM stations WHERE uuid=?1 AND custom=1",
1072 [uuid],
1073 |r| r.get(0),
1074 )
1075 .optional()?
1076 .context(DomainError::new(ErrorCode::NotCustom))?;
1077 let mut station: Station = serde_json::from_str(&json)?;
1078 let referenced: bool = tx.query_row("SELECT EXISTS(SELECT 1 FROM favorites WHERE uuid=?1) OR EXISTS(SELECT 1 FROM history WHERE uuid=?1) OR EXISTS(SELECT 1 FROM recordings WHERE json_extract(json,'$.stationuuid')=?1)", [uuid], |r| r.get(0))?;
1079 tx.execute("DELETE FROM streams WHERE uuid=?1", [uuid])?;
1080 if referenced || keep_references {
1081 station.custom = false;
1082 station.active = false;
1083 station.url.clear();
1084 station.url_resolved.clear();
1085 Self::write_station_with_policy(&tx, "stations", &station, true, true)?;
1086 } else {
1087 let before = facets::station_values(&tx, uuid)?;
1088 tx.execute("DELETE FROM stations WHERE uuid=?1", [uuid])?;
1089 facets::update(&tx, &before, &Default::default())?;
1090 }
1091 geo::changed(&tx)?;
1092 tx.commit()?;
1093 Ok(referenced || keep_references)
1094 }
1095
1096 pub fn update_backups(
1097 &mut self,
1098 uuid: &str,
1099 add: &[String],
1100 remove: &[String],
1101 ) -> Result<Vec<String>> {
1102 self.station(uuid)?;
1103 let tx = self.conn.transaction()?;
1104 for url in remove {
1105 tx.execute(
1106 "DELETE FROM streams WHERE uuid=?1 AND url=?2",
1107 params![uuid, url],
1108 )?;
1109 }
1110 for url in add {
1111 crate::config::validate_url(url)?;
1112 tx.execute(
1113 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1114 params![uuid, url],
1115 )?;
1116 }
1117 tx.commit()?;
1118 self.backups(uuid)
1119 }
1120
1121 pub fn backups(&self, uuid: &str) -> Result<Vec<String>> {
1122 let mut stmt = self
1123 .conn
1124 .prepare("SELECT url FROM streams WHERE uuid=?1 ORDER BY rowid")?;
1125 Ok(stmt
1126 .query_map([uuid], |r| r.get(0))?
1127 .collect::<rusqlite::Result<_>>()?)
1128 }
1129
1130 pub fn save_recording(&self, recording: &Recording) -> Result<()> {
1131 self.conn.execute(
1132 "INSERT INTO recordings (id,status,json,recovery_pending) VALUES (?1,?2,?3,?4) ON CONFLICT(id) DO UPDATE SET status=excluded.status,json=excluded.json,recovery_pending=excluded.recovery_pending",
1133 params![
1134 recording.id,
1135 recording.status,
1136 serde_json::to_string(recording)?,
1137 recording.recovery_pending
1138 ],
1139 )?;
1140 Ok(())
1141 }
1142
1143 pub fn search_count(&self, filter: &Filter) -> Result<usize> {
1144 let (conditions, args, source) = self.conditions(filter);
1145 Ok(self
1146 .conn
1147 .prepare_cached(&format!("SELECT count(*) FROM {source} WHERE {conditions}"))?
1148 .query_row(params_from_iter(args), |r| r.get(0))?)
1149 }
1150
1151 pub fn recordings(&self, limit: usize) -> Result<Vec<Recording>> {
1152 self.recordings_page(limit, 0)
1153 }
1154
1155 pub fn recordings_count(&self) -> Result<usize> {
1156 Ok(self
1157 .conn
1158 .query_row("SELECT count(*) FROM recordings", [], |r| r.get(0))?)
1159 }
1160
1161 pub fn recording(&self, id: &str) -> Result<Recording> {
1162 let row = self
1163 .conn
1164 .query_row(
1165 "SELECT json,recovery_pending FROM recordings WHERE id=?1",
1166 [id],
1167 |r| Ok((r.get(0)?, r.get(1)?)),
1168 )
1169 .optional()?
1170 .context(DomainError::new(ErrorCode::RecordingNotFound))?;
1171 Self::read_recording(row)
1172 }
1173
1174 pub fn remove_recording_index(&self, id: &str) -> Result<()> {
1175 self.conn
1176 .execute("DELETE FROM recordings WHERE id=?1", [id])?;
1177 Ok(())
1178 }
1179
1180 pub fn recordings_page(&self, limit: usize, offset: usize) -> Result<Vec<Recording>> {
1181 let mut stmt = self.conn.prepare_cached(&format!("SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT ?1 OFFSET ?2"))?;
1182 let rows = stmt.query_map(
1183 params![
1184 limit.clamp(1, 1000) as i64,
1185 offset.min(i64::MAX as usize) as i64
1186 ],
1187 |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)),
1188 )?;
1189 rows.map(|row| Self::read_recording(row?)).collect()
1190 }
1191
1192 fn read_recording((json, recovery_pending): (String, bool)) -> Result<Recording> {
1193 let mut record: Recording = serde_json::from_str(&json)?;
1194 record.recovery_pending = recovery_pending;
1195 Ok(record)
1196 }
1197
1198 pub fn unfinished_recordings(&self) -> Result<Vec<Recording>> {
1199 let mut stmt = self
1200 .conn
1201 .prepare("SELECT json,recovery_pending FROM recordings WHERE status IN ('recording','finalizing') OR recovery_pending=1")?;
1202 let rows = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)))?;
1203 rows.map(|row| Self::read_recording(row?)).collect()
1204 }
1205}
1206
1207#[cfg(test)]
1208mod tests {
1209 use super::*;
1210
1211 #[test]
1212 fn recording_order_index_migrates_without_losing_old_or_malformed_entries() {
1213 let dir = tempfile::tempdir().unwrap();
1214 let path = dir.path().join("radio.db");
1215 let store = Store::open(&path).unwrap();
1216 for (id, at) in [
1217 ("a", "2026-01-02"),
1218 ("b", "2026-01-02"),
1219 ("old", "2026-01-01"),
1220 ] {
1221 store
1222 .save_recording(&Recording {
1223 id: id.into(),
1224 stationuuid: "station".into(),
1225 station_name: "Station".into(),
1226 track: None,
1227 source_url_resolved: "http://localhost/live".into(),
1228 started_at: at.into(),
1229 stopped_at: None,
1230 media_duration_ms: Some(1000),
1231 codec: "mp3".into(),
1232 container: "mp3".into(),
1233 path: id.into(),
1234 final_path: None,
1235 size_bytes: 1,
1236 status: "complete".into(),
1237 error: None,
1238 reconnects: 0,
1239 recovery_pending: false,
1240 })
1241 .unwrap();
1242 }
1243 store
1244 .conn
1245 .execute_batch(
1246 "DROP INDEX recording_started; PRAGMA user_version=7;
1247 INSERT INTO recordings(id,status,json) VALUES ('broken','complete','invalid');",
1248 )
1249 .unwrap();
1250 drop(store);
1251 let store = Store::open(&path).unwrap();
1252 assert_eq!(store.recordings_count().unwrap(), 4);
1253 let page = store.recordings_page(2, 0).unwrap();
1254 assert_eq!(
1255 page.iter().map(|r| r.id.as_str()).collect::<Vec<_>>(),
1256 ["b", "a"]
1257 );
1258 assert_eq!(store.recordings_page(1, 2).unwrap()[0].id, "old");
1259 assert!(store.recordings_page(1, 3).is_err());
1260 let plan: Vec<String> = store.conn.prepare(&format!(
1261 "EXPLAIN QUERY PLAN SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT 100"
1262 )).unwrap().query_map([], |r| r.get(3)).unwrap().collect::<rusqlite::Result<_>>().unwrap();
1263 assert!(plan.iter().any(|line| line.contains("recording_started")));
1264 assert!(!plan.iter().any(|line| line.contains("TEMP B-TREE")));
1265 }
1266
1267 #[test]
1268 fn territory_queries_union_indexed_country_and_trigram_candidates() {
1269 let dir = tempfile::tempdir().unwrap();
1270 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1271 let rows: Vec<_> = (0..100)
1272 .map(|i| Station {
1273 stationuuid: uuid::Uuid::new_v4().to_string(),
1274 name: match i {
1275 1 => "爱尔兰音乐".into(),
1276 2 => "爱尔兰广播".into(),
1277 _ => format!("catalog station {i}"),
1278 },
1279 countrycode: if i == 0 || i == 2 { "IE" } else { "FR" }.into(),
1280 clickcount: i,
1281 active: true,
1282 ..Station::default()
1283 })
1284 .collect();
1285 store.bootstrap(&rows, || false).unwrap();
1286 let filter = Filter {
1287 query: " 爱尔兰 ".into(),
1288 limit: 100,
1289 ..Filter::default()
1290 };
1291 let actual: Vec<_> = store
1292 .search(&filter)
1293 .unwrap()
1294 .into_iter()
1295 .map(|row| row.stationuuid)
1296 .collect();
1297 assert_eq!(actual, [2, 1, 0].map(|i| rows[i].stationuuid.clone()));
1298 assert_eq!(store.search_count(&filter).unwrap(), 3);
1299 let (conditions, args, source) = store.conditions(&filter);
1300 let plan: Vec<String> = store
1301 .conn
1302 .prepare(&format!(
1303 "EXPLAIN QUERY PLAN SELECT s.rowid FROM {source} WHERE {conditions}"
1304 ))
1305 .unwrap()
1306 .query_map(params_from_iter(args), |r| r.get(3))
1307 .unwrap()
1308 .collect::<rusqlite::Result<_>>()
1309 .unwrap();
1310 for index in [
1311 "SEARCH s USING INTEGER PRIMARY KEY (rowid=?)",
1312 "station_search VIRTUAL TABLE INDEX",
1313 "station_country (country=?)",
1314 ] {
1315 assert!(plan.iter().any(|step| step.contains(index)), "{plan:?}");
1316 }
1317 }
1318
1319 #[test]
1320 fn search_statistics_collection_allows_a_concurrent_writer() {
1321 use std::sync::{
1322 Arc,
1323 atomic::{AtomicBool, Ordering},
1324 };
1325
1326 let dir = tempfile::tempdir().unwrap();
1327 let path = dir.path().join("radio.db");
1328 let mut store = Store::open(&path).unwrap();
1329 let rows: Vec<_> = (0..256)
1330 .map(|i| Station {
1331 stationuuid: uuid::Uuid::new_v4().to_string(),
1332 name: format!("station {i:05} jazz"),
1333 active: true,
1334 ..Station::default()
1335 })
1336 .collect();
1337 store.bootstrap(&rows, || false).unwrap();
1338 let writer = Connection::open(&path).unwrap();
1339 writer.busy_timeout(std::time::Duration::ZERO).unwrap();
1340 let attempted = Arc::new(AtomicBool::new(false));
1341 let succeeded = Arc::new(AtomicBool::new(false));
1342 let (attempt, success) = (attempted.clone(), succeeded.clone());
1343 store
1344 .conn
1345 .progress_handler(
1346 1000,
1347 Some(move || {
1348 if !attempt.swap(true, Ordering::SeqCst) {
1349 success.store(
1350 writer
1351 .execute("INSERT INTO meta VALUES ('concurrent','write')", [])
1352 .is_ok(),
1353 Ordering::SeqCst,
1354 );
1355 }
1356 false
1357 }),
1358 )
1359 .unwrap();
1360 store.refresh_search_stats().unwrap();
1361 store
1362 .conn
1363 .progress_handler(0, None::<fn() -> bool>)
1364 .unwrap();
1365 assert!(attempted.load(Ordering::SeqCst));
1366 assert!(succeeded.load(Ordering::SeqCst));
1367 let actual: usize = store
1368 .conn
1369 .query_row(
1370 "SELECT n FROM station_search_stats WHERE term=''",
1371 [],
1372 |r| r.get(0),
1373 )
1374 .unwrap();
1375 assert_eq!(actual, rows.len());
1376 let differences: usize = store
1377 .conn
1378 .query_row(
1379 "SELECT count(*) FROM (
1380 SELECT term,doc FROM station_search_terms
1381 EXCEPT SELECT term,n FROM station_search_stats WHERE term<>'')",
1382 [],
1383 |r| r.get(0),
1384 )
1385 .unwrap();
1386 assert_eq!(differences, 0);
1387 }
1388
1389 #[test]
1390 fn failed_statistics_refresh_preserves_hints_and_successful_publication() {
1391 for sync in [false, true] {
1392 let dir = tempfile::tempdir().unwrap();
1393 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1394 let row = |name: &str| Station {
1395 stationuuid: uuid::Uuid::new_v4().to_string(),
1396 name: name.into(),
1397 tags: name.into(),
1398 active: true,
1399 ..Station::default()
1400 };
1401 store.bootstrap(&[row("originalneedle")], || false).unwrap();
1402 let stats = || {
1403 store
1404 .conn
1405 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1406 .unwrap()
1407 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1408 .unwrap()
1409 .collect::<rusqlite::Result<Vec<_>>>()
1410 .unwrap()
1411 };
1412 let before = stats();
1413 store
1414 .conn
1415 .execute_batch(
1416 "CREATE TRIGGER reject_stats BEFORE INSERT ON station_search_stats
1417 BEGIN SELECT RAISE(ABORT,'statistics unavailable'); END;",
1418 )
1419 .unwrap();
1420 let replacement = row("replacementneedle");
1421 if sync {
1422 store.begin_sync().unwrap();
1423 store.stage(&[replacement]).unwrap();
1424 assert_eq!(store.finish_sync().unwrap(), 1);
1425 assert!(store.meta("last_sync").unwrap().is_some());
1426 } else {
1427 assert_eq!(store.bootstrap(&[replacement], || false).unwrap(), 1);
1428 }
1429 assert_eq!(
1430 store
1431 .search_count(&Filter {
1432 query: "replacementneedle".into(),
1433 ..Filter::default()
1434 })
1435 .unwrap(),
1436 1
1437 );
1438 assert_eq!(
1439 store
1440 .facets(crate::model::FacetKind::Tag, "replacement")
1441 .unwrap()[0]
1442 .count,
1443 1
1444 );
1445 let after: Vec<_> = store
1446 .conn
1447 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1448 .unwrap()
1449 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1450 .unwrap()
1451 .collect::<rusqlite::Result<_>>()
1452 .unwrap();
1453 assert_eq!(after, before);
1454 }
1455 }
1456
1457 #[test]
1458 fn read_snapshot_pins_revision_before_a_concurrent_commit() {
1459 let dir = tempfile::tempdir().unwrap();
1460 let path = dir.path().join("radio.db");
1461 let writer = Store::open(&path).unwrap();
1462 let reader = Store::open_reader(&path).unwrap();
1463 let revision = reader
1464 .read_snapshot(|store| {
1465 let revision = store.list_revision(&Filter::default())?;
1466 writer.upsert(&Station {
1467 stationuuid: uuid::Uuid::new_v4().to_string(),
1468 name: "new row".into(),
1469 active: true,
1470 ..Station::default()
1471 })?;
1472 assert_eq!(store.list_revision(&Filter::default())?, revision);
1473 assert_eq!(store.count()?, 0);
1474 Ok(revision)
1475 })
1476 .unwrap();
1477 assert_ne!(reader.list_revision(&Filter::default()).unwrap(), revision);
1478 assert_eq!(reader.count().unwrap(), 1);
1479 }
1480
1481 #[test]
1482 fn cancellation_before_sync_commit_rolls_back_all_derived_data() {
1483 let dir = tempfile::tempdir().unwrap();
1484 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1485 let row = |name: &str, tag: &str| Station {
1486 stationuuid: uuid::Uuid::new_v4().to_string(),
1487 name: name.into(),
1488 tags: tag.into(),
1489 active: true,
1490 ..Station::default()
1491 };
1492 store.upsert(&row("originalneedle", "originaltag")).unwrap();
1493 store.begin_sync().unwrap();
1494 store
1495 .stage(&[row("replacementneedle", "replacementtag")])
1496 .unwrap();
1497 assert!(store.finish_sync_cancellable(|| true).is_err());
1498 assert_eq!(store.count().unwrap(), 1);
1499 for (query, expected) in [("originalneedle", 1), ("replacementneedle", 0)] {
1500 assert_eq!(
1501 store
1502 .search_count(&Filter {
1503 query: query.into(),
1504 ..Filter::default()
1505 })
1506 .unwrap(),
1507 expected
1508 );
1509 }
1510 assert_eq!(
1511 store.facets(crate::model::FacetKind::Tag, "").unwrap()[0].value,
1512 "originaltag"
1513 );
1514 assert!(store.meta("last_sync").unwrap().is_none());
1515 }
1516
1517 #[test]
1518 fn sync_writes_only_changed_stations_and_facet_memberships() {
1519 let dir = tempfile::tempdir().unwrap();
1520 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1521 let row = |name: &str| Station {
1522 stationuuid: uuid::Uuid::new_v4().to_string(),
1523 name: name.into(),
1524 tags: "jazz,jazz".into(),
1525 language: "english,en".into(),
1526 countrycode: "US".into(),
1527 active: true,
1528 ..Station::default()
1529 };
1530 let unchanged = row("unchanged");
1531 let mut changed = row("changed");
1532 let removed = row("removed");
1533 let mut revived = row("revived");
1534 revived.active = false;
1535 let mut custom = row("custom");
1536 custom.custom = true;
1537 for station in [&unchanged, &changed, &removed, &revived, &custom] {
1538 store.upsert(station).unwrap();
1539 }
1540 store
1541 .conn
1542 .execute_batch(
1543 "CREATE TABLE station_writes(uuid TEXT);
1544 CREATE TRIGGER audit_station_writes AFTER UPDATE ON stations
1545 BEGIN INSERT INTO station_writes VALUES(new.uuid); END;",
1546 )
1547 .unwrap();
1548 changed.tags = "rock,rock".into();
1549 changed.countrycode = "FR".into();
1550 revived.active = true;
1551 let mut collision = custom.clone();
1552 collision.custom = false;
1553 collision.tags = "wrong".into();
1554 let incoming = [unchanged.clone(), changed, revived, collision, row("new")];
1555 store.begin_sync().unwrap();
1556 store.stage(&incoming).unwrap();
1557 store.finish_sync().unwrap();
1558 let writes: Vec<String> = store
1559 .conn
1560 .prepare("SELECT uuid FROM station_writes")
1561 .unwrap()
1562 .query_map([], |r| r.get(0))
1563 .unwrap()
1564 .collect::<rusqlite::Result<_>>()
1565 .unwrap();
1566 assert_eq!(writes.len(), 3);
1567 assert!(!writes.contains(&unchanged.stationuuid));
1568 assert!(!writes.contains(&custom.stationuuid));
1569 assert!(!store.station(&removed.stationuuid).unwrap().active);
1570 assert_eq!(
1571 store.station(&custom.stationuuid).unwrap().tags,
1572 "jazz,jazz"
1573 );
1574 let facets = || {
1575 store
1576 .conn
1577 .prepare("SELECT kind,value,n FROM facets_cache ORDER BY kind,value")
1578 .unwrap()
1579 .query_map([], |r| {
1580 Ok((
1581 r.get::<_, u8>(0)?,
1582 r.get::<_, String>(1)?,
1583 r.get::<_, i64>(2)?,
1584 ))
1585 })
1586 .unwrap()
1587 .collect::<rusqlite::Result<Vec<_>>>()
1588 .unwrap()
1589 };
1590 let delta_counts = facets();
1591 facets::rebuild(&store.conn).unwrap();
1592 assert_eq!(facets(), delta_counts);
1593 let geo_revision = store.geo_revision().unwrap();
1594 store.begin_sync().unwrap();
1595 store.stage(&incoming).unwrap();
1596 let before = store.conn.total_changes();
1597 store.finish_sync().unwrap();
1598 assert_eq!(store.conn.total_changes() - before, 1);
1601 assert_eq!(store.geo_revision().unwrap(), geo_revision);
1602 }
1603}