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