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