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 codes = if kind == 0 {
918 crate::territories::matching_prefix_codes(&prefix).collect::<Vec<_>>()
919 } else {
920 Vec::new()
921 };
922 let localized = if codes.is_empty() {
925 String::new()
926 } else {
927 let placeholders = (4..4 + codes.len())
928 .map(|index| format!("?{index}"))
929 .collect::<Vec<_>>()
930 .join(",");
931 format!(" OR value IN ({placeholders})")
932 };
933 let mut args: Vec<Value> = vec![
934 kind.into(),
935 prefix.into(),
936 (limit.min(i64::MAX as usize) as i64).into(),
937 ];
938 args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
939 let mut stmt = self.conn.prepare_cached(&format!(
940 "SELECT value,n FROM facets_cache WHERE kind=?1
941 AND (substr(value,1,length(?2))=?2{localized}) ORDER BY value LIMIT ?3",
942 ))?;
943 let rows = stmt.query_map(params_from_iter(args), |r| {
944 Ok(Facet {
945 value: r.get(0)?,
946 count: r.get(1)?,
947 })
948 })?;
949 Ok(rows.collect::<rusqlite::Result<_>>()?)
950 }
951
952 pub fn favorite(&self, uuid: &str, add: bool) -> Result<()> {
953 self.station(uuid)?;
954 if add {
955 self.conn.execute(
956 "INSERT OR IGNORE INTO favorites(uuid,created_at) VALUES (?1,?2)",
957 params![uuid, chrono::Utc::now().to_rfc3339()],
958 )?;
959 } else {
960 self.conn
961 .execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
962 }
963 Ok(())
964 }
965
966 pub fn toggle_favorite(&mut self, uuid: &str) -> Result<bool> {
967 let tx = self
968 .conn
969 .transaction_with_behavior(TransactionBehavior::Immediate)?;
970 tx.query_row("SELECT 1 FROM stations WHERE uuid=?1", [uuid], |r| {
971 r.get::<_, u8>(0)
972 })
973 .optional()?
974 .context(DomainError::new(ErrorCode::StationNotFound))?;
975 let removed = tx.execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
976 let added = removed == 0;
977 if added {
978 tx.execute(
979 "INSERT INTO favorites(uuid,created_at) VALUES (?1,?2)",
980 params![uuid, chrono::Utc::now().to_rfc3339()],
981 )?;
982 }
983 tx.commit()?;
984 Ok(added)
985 }
986
987 pub fn played(&self, uuid: &str) -> Result<()> {
988 self.conn.execute(
989 "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
990 params![uuid, chrono::Utc::now().to_rfc3339()],
991 )?;
992 let threshold = self.conn.last_insert_rowid().saturating_sub(1000);
993 self.conn
994 .execute("DELETE FROM history WHERE id <= ?1", [threshold])?;
995 Ok(())
996 }
997
998 pub fn add_backup(&self, uuid: &str, url: &str) -> Result<()> {
999 crate::config::validate_url(url)?;
1000 self.conn.execute(
1001 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1002 params![uuid, url],
1003 )?;
1004 Ok(())
1005 }
1006
1007 pub fn save_custom(&mut self, station: &Station, backups: Option<&[String]>) -> Result<()> {
1008 let tx = self
1009 .conn
1010 .transaction_with_behavior(TransactionBehavior::Immediate)?;
1011 let key = crate::service::normalized_url(station.primary_url())?;
1012 if let Some(uuid) = tx.query_row(
1013 "SELECT uuid FROM stations WHERE active=1 AND normalized_url=?1 AND uuid<>?2 LIMIT 1",
1014 params![key, station.stationuuid], |r| r.get::<_, String>(0),
1015 ).optional()? {
1016 bail!(DomainError::new(ErrorCode::DuplicateStation).with("uuid", uuid));
1017 }
1018 Self::write_station_with_policy(&tx, "stations", station, true, true)?;
1019 if let Some(backups) = backups {
1020 tx.execute("DELETE FROM streams WHERE uuid=?1", [&station.stationuuid])?;
1021 for url in backups {
1022 crate::config::validate_url(url)?;
1023 tx.execute(
1024 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1025 params![station.stationuuid, url],
1026 )?;
1027 }
1028 }
1029 geo::changed(&tx)?;
1030 tx.commit()?;
1031 Ok(())
1032 }
1033
1034 pub fn remove_custom(&mut self, uuid: &str, keep_references: bool) -> Result<bool> {
1035 let tx = self
1036 .conn
1037 .transaction_with_behavior(TransactionBehavior::Immediate)?;
1038 let json: String = tx
1039 .query_row(
1040 "SELECT json FROM stations WHERE uuid=?1 AND custom=1",
1041 [uuid],
1042 |r| r.get(0),
1043 )
1044 .optional()?
1045 .context(DomainError::new(ErrorCode::NotCustom))?;
1046 let mut station: Station = serde_json::from_str(&json)?;
1047 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))?;
1048 tx.execute("DELETE FROM streams WHERE uuid=?1", [uuid])?;
1049 if referenced || keep_references {
1050 station.custom = false;
1051 station.active = false;
1052 station.url.clear();
1053 station.url_resolved.clear();
1054 Self::write_station_with_policy(&tx, "stations", &station, true, true)?;
1055 } else {
1056 let before = facets::station_values(&tx, uuid)?;
1057 tx.execute("DELETE FROM stations WHERE uuid=?1", [uuid])?;
1058 facets::update(&tx, &before, &Default::default())?;
1059 }
1060 geo::changed(&tx)?;
1061 tx.commit()?;
1062 Ok(referenced || keep_references)
1063 }
1064
1065 pub fn update_backups(
1066 &mut self,
1067 uuid: &str,
1068 add: &[String],
1069 remove: &[String],
1070 ) -> Result<Vec<String>> {
1071 self.station(uuid)?;
1072 let tx = self.conn.transaction()?;
1073 for url in remove {
1074 tx.execute(
1075 "DELETE FROM streams WHERE uuid=?1 AND url=?2",
1076 params![uuid, url],
1077 )?;
1078 }
1079 for url in add {
1080 crate::config::validate_url(url)?;
1081 tx.execute(
1082 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1083 params![uuid, url],
1084 )?;
1085 }
1086 tx.commit()?;
1087 self.backups(uuid)
1088 }
1089
1090 pub fn backups(&self, uuid: &str) -> Result<Vec<String>> {
1091 let mut stmt = self
1092 .conn
1093 .prepare("SELECT url FROM streams WHERE uuid=?1 ORDER BY rowid")?;
1094 Ok(stmt
1095 .query_map([uuid], |r| r.get(0))?
1096 .collect::<rusqlite::Result<_>>()?)
1097 }
1098
1099 pub fn save_recording(&self, recording: &Recording) -> Result<()> {
1100 self.conn.execute(
1101 "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",
1102 params![
1103 recording.id,
1104 recording.status,
1105 serde_json::to_string(recording)?,
1106 recording.recovery_pending
1107 ],
1108 )?;
1109 Ok(())
1110 }
1111
1112 pub fn search_count(&self, filter: &Filter) -> Result<usize> {
1113 let (conditions, args, source) = self.conditions(filter);
1114 Ok(self
1115 .conn
1116 .prepare_cached(&format!("SELECT count(*) FROM {source} WHERE {conditions}"))?
1117 .query_row(params_from_iter(args), |r| r.get(0))?)
1118 }
1119
1120 pub fn recordings(&self, limit: usize) -> Result<Vec<Recording>> {
1121 self.recordings_page(limit, 0)
1122 }
1123
1124 pub fn recordings_count(&self) -> Result<usize> {
1125 Ok(self
1126 .conn
1127 .query_row("SELECT count(*) FROM recordings", [], |r| r.get(0))?)
1128 }
1129
1130 pub fn recording(&self, id: &str) -> Result<Recording> {
1131 let row = self
1132 .conn
1133 .query_row(
1134 "SELECT json,recovery_pending FROM recordings WHERE id=?1",
1135 [id],
1136 |r| Ok((r.get(0)?, r.get(1)?)),
1137 )
1138 .optional()?
1139 .context(DomainError::new(ErrorCode::RecordingNotFound))?;
1140 Self::read_recording(row)
1141 }
1142
1143 pub fn remove_recording_index(&self, id: &str) -> Result<()> {
1144 self.conn
1145 .execute("DELETE FROM recordings WHERE id=?1", [id])?;
1146 Ok(())
1147 }
1148
1149 pub fn recordings_page(&self, limit: usize, offset: usize) -> Result<Vec<Recording>> {
1150 let mut stmt = self.conn.prepare_cached(&format!("SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT ?1 OFFSET ?2"))?;
1151 let rows = stmt.query_map(
1152 params![
1153 limit.clamp(1, 1000) as i64,
1154 offset.min(i64::MAX as usize) as i64
1155 ],
1156 |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)),
1157 )?;
1158 rows.map(|row| Self::read_recording(row?)).collect()
1159 }
1160
1161 fn read_recording((json, recovery_pending): (String, bool)) -> Result<Recording> {
1162 let mut record: Recording = serde_json::from_str(&json)?;
1163 record.recovery_pending = recovery_pending;
1164 Ok(record)
1165 }
1166
1167 pub fn unfinished_recordings(&self) -> Result<Vec<Recording>> {
1168 let mut stmt = self
1169 .conn
1170 .prepare("SELECT json,recovery_pending FROM recordings WHERE status IN ('recording','finalizing') OR recovery_pending=1")?;
1171 let rows = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)))?;
1172 rows.map(|row| Self::read_recording(row?)).collect()
1173 }
1174}
1175
1176#[cfg(test)]
1177mod tests {
1178 use super::*;
1179
1180 #[test]
1181 fn recording_order_index_migrates_without_losing_old_or_malformed_entries() {
1182 let dir = tempfile::tempdir().unwrap();
1183 let path = dir.path().join("radio.db");
1184 let store = Store::open(&path).unwrap();
1185 for (id, at) in [
1186 ("a", "2026-01-02"),
1187 ("b", "2026-01-02"),
1188 ("old", "2026-01-01"),
1189 ] {
1190 store
1191 .save_recording(&Recording {
1192 id: id.into(),
1193 stationuuid: "station".into(),
1194 station_name: "Station".into(),
1195 track: None,
1196 source_url_resolved: "http://localhost/live".into(),
1197 started_at: at.into(),
1198 stopped_at: None,
1199 media_duration_ms: Some(1000),
1200 codec: "mp3".into(),
1201 container: "mp3".into(),
1202 path: id.into(),
1203 final_path: None,
1204 size_bytes: 1,
1205 status: "complete".into(),
1206 error: None,
1207 reconnects: 0,
1208 recovery_pending: false,
1209 })
1210 .unwrap();
1211 }
1212 store
1213 .conn
1214 .execute_batch(
1215 "DROP INDEX recording_started; PRAGMA user_version=7;
1216 INSERT INTO recordings(id,status,json) VALUES ('broken','complete','invalid');",
1217 )
1218 .unwrap();
1219 drop(store);
1220 let store = Store::open(&path).unwrap();
1221 assert_eq!(store.recordings_count().unwrap(), 4);
1222 let page = store.recordings_page(2, 0).unwrap();
1223 assert_eq!(
1224 page.iter().map(|r| r.id.as_str()).collect::<Vec<_>>(),
1225 ["b", "a"]
1226 );
1227 assert_eq!(store.recordings_page(1, 2).unwrap()[0].id, "old");
1228 assert!(store.recordings_page(1, 3).is_err());
1229 let plan: Vec<String> = store.conn.prepare(&format!(
1230 "EXPLAIN QUERY PLAN SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT 100"
1231 )).unwrap().query_map([], |r| r.get(3)).unwrap().collect::<rusqlite::Result<_>>().unwrap();
1232 assert!(plan.iter().any(|line| line.contains("recording_started")));
1233 assert!(!plan.iter().any(|line| line.contains("TEMP B-TREE")));
1234 }
1235
1236 #[test]
1237 fn territory_queries_union_indexed_country_and_trigram_candidates() {
1238 let dir = tempfile::tempdir().unwrap();
1239 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1240 let rows: Vec<_> = (0..100)
1241 .map(|i| Station {
1242 stationuuid: uuid::Uuid::new_v4().to_string(),
1243 name: match i {
1244 1 => "爱尔兰音乐".into(),
1245 2 => "爱尔兰广播".into(),
1246 _ => format!("catalog station {i}"),
1247 },
1248 countrycode: if i == 0 || i == 2 { "IE" } else { "FR" }.into(),
1249 clickcount: i,
1250 active: true,
1251 ..Station::default()
1252 })
1253 .collect();
1254 store.bootstrap(&rows, || false).unwrap();
1255 let filter = Filter {
1256 query: " 爱尔兰 ".into(),
1257 limit: 100,
1258 ..Filter::default()
1259 };
1260 let actual: Vec<_> = store
1261 .search(&filter)
1262 .unwrap()
1263 .into_iter()
1264 .map(|row| row.stationuuid)
1265 .collect();
1266 assert_eq!(actual, [2, 1, 0].map(|i| rows[i].stationuuid.clone()));
1267 assert_eq!(store.search_count(&filter).unwrap(), 3);
1268 let (conditions, args, source) = store.conditions(&filter);
1269 let plan: Vec<String> = store
1270 .conn
1271 .prepare(&format!(
1272 "EXPLAIN QUERY PLAN SELECT s.rowid FROM {source} WHERE {conditions}"
1273 ))
1274 .unwrap()
1275 .query_map(params_from_iter(args), |r| r.get(3))
1276 .unwrap()
1277 .collect::<rusqlite::Result<_>>()
1278 .unwrap();
1279 for index in [
1280 "SEARCH s USING INTEGER PRIMARY KEY (rowid=?)",
1281 "station_search VIRTUAL TABLE INDEX",
1282 "station_country (country=?)",
1283 ] {
1284 assert!(plan.iter().any(|step| step.contains(index)), "{plan:?}");
1285 }
1286 }
1287
1288 #[test]
1289 fn search_statistics_collection_allows_a_concurrent_writer() {
1290 use std::sync::{
1291 Arc,
1292 atomic::{AtomicBool, Ordering},
1293 };
1294
1295 let dir = tempfile::tempdir().unwrap();
1296 let path = dir.path().join("radio.db");
1297 let mut store = Store::open(&path).unwrap();
1298 let rows: Vec<_> = (0..256)
1299 .map(|i| Station {
1300 stationuuid: uuid::Uuid::new_v4().to_string(),
1301 name: format!("station {i:05} jazz"),
1302 active: true,
1303 ..Station::default()
1304 })
1305 .collect();
1306 store.bootstrap(&rows, || false).unwrap();
1307 let writer = Connection::open(&path).unwrap();
1308 writer.busy_timeout(std::time::Duration::ZERO).unwrap();
1309 let attempted = Arc::new(AtomicBool::new(false));
1310 let succeeded = Arc::new(AtomicBool::new(false));
1311 let (attempt, success) = (attempted.clone(), succeeded.clone());
1312 store.conn.progress_handler(
1313 1000,
1314 Some(move || {
1315 if !attempt.swap(true, Ordering::SeqCst) {
1316 success.store(
1317 writer
1318 .execute("INSERT INTO meta VALUES ('concurrent','write')", [])
1319 .is_ok(),
1320 Ordering::SeqCst,
1321 );
1322 }
1323 false
1324 }),
1325 );
1326 store.refresh_search_stats().unwrap();
1327 store.conn.progress_handler(0, None::<fn() -> bool>);
1328 assert!(attempted.load(Ordering::SeqCst));
1329 assert!(succeeded.load(Ordering::SeqCst));
1330 let actual: usize = store
1331 .conn
1332 .query_row(
1333 "SELECT n FROM station_search_stats WHERE term=''",
1334 [],
1335 |r| r.get(0),
1336 )
1337 .unwrap();
1338 assert_eq!(actual, rows.len());
1339 let differences: usize = store
1340 .conn
1341 .query_row(
1342 "SELECT count(*) FROM (
1343 SELECT term,doc FROM station_search_terms
1344 EXCEPT SELECT term,n FROM station_search_stats WHERE term<>'')",
1345 [],
1346 |r| r.get(0),
1347 )
1348 .unwrap();
1349 assert_eq!(differences, 0);
1350 }
1351
1352 #[test]
1353 fn failed_statistics_refresh_preserves_hints_and_successful_publication() {
1354 for sync in [false, true] {
1355 let dir = tempfile::tempdir().unwrap();
1356 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1357 let row = |name: &str| Station {
1358 stationuuid: uuid::Uuid::new_v4().to_string(),
1359 name: name.into(),
1360 tags: name.into(),
1361 active: true,
1362 ..Station::default()
1363 };
1364 store.bootstrap(&[row("originalneedle")], || false).unwrap();
1365 let stats = || {
1366 store
1367 .conn
1368 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1369 .unwrap()
1370 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1371 .unwrap()
1372 .collect::<rusqlite::Result<Vec<_>>>()
1373 .unwrap()
1374 };
1375 let before = stats();
1376 store
1377 .conn
1378 .execute_batch(
1379 "CREATE TRIGGER reject_stats BEFORE INSERT ON station_search_stats
1380 BEGIN SELECT RAISE(ABORT,'statistics unavailable'); END;",
1381 )
1382 .unwrap();
1383 let replacement = row("replacementneedle");
1384 if sync {
1385 store.begin_sync().unwrap();
1386 store.stage(&[replacement]).unwrap();
1387 assert_eq!(store.finish_sync().unwrap(), 1);
1388 assert!(store.meta("last_sync").unwrap().is_some());
1389 } else {
1390 assert_eq!(store.bootstrap(&[replacement], || false).unwrap(), 1);
1391 }
1392 assert_eq!(
1393 store
1394 .search_count(&Filter {
1395 query: "replacementneedle".into(),
1396 ..Filter::default()
1397 })
1398 .unwrap(),
1399 1
1400 );
1401 assert_eq!(
1402 store
1403 .facets(crate::model::FacetKind::Tag, "replacement")
1404 .unwrap()[0]
1405 .count,
1406 1
1407 );
1408 let after: Vec<_> = store
1409 .conn
1410 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1411 .unwrap()
1412 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1413 .unwrap()
1414 .collect::<rusqlite::Result<_>>()
1415 .unwrap();
1416 assert_eq!(after, before);
1417 }
1418 }
1419
1420 #[test]
1421 fn read_snapshot_pins_revision_before_a_concurrent_commit() {
1422 let dir = tempfile::tempdir().unwrap();
1423 let path = dir.path().join("radio.db");
1424 let writer = Store::open(&path).unwrap();
1425 let reader = Store::open_reader(&path).unwrap();
1426 let revision = reader
1427 .read_snapshot(|store| {
1428 let revision = store.list_revision(&Filter::default())?;
1429 writer.upsert(&Station {
1430 stationuuid: uuid::Uuid::new_v4().to_string(),
1431 name: "new row".into(),
1432 active: true,
1433 ..Station::default()
1434 })?;
1435 assert_eq!(store.list_revision(&Filter::default())?, revision);
1436 assert_eq!(store.count()?, 0);
1437 Ok(revision)
1438 })
1439 .unwrap();
1440 assert_ne!(reader.list_revision(&Filter::default()).unwrap(), revision);
1441 assert_eq!(reader.count().unwrap(), 1);
1442 }
1443
1444 #[test]
1445 fn cancellation_before_sync_commit_rolls_back_all_derived_data() {
1446 let dir = tempfile::tempdir().unwrap();
1447 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1448 let row = |name: &str, tag: &str| Station {
1449 stationuuid: uuid::Uuid::new_v4().to_string(),
1450 name: name.into(),
1451 tags: tag.into(),
1452 active: true,
1453 ..Station::default()
1454 };
1455 store.upsert(&row("originalneedle", "originaltag")).unwrap();
1456 store.begin_sync().unwrap();
1457 store
1458 .stage(&[row("replacementneedle", "replacementtag")])
1459 .unwrap();
1460 assert!(store.finish_sync_cancellable(|| true).is_err());
1461 assert_eq!(store.count().unwrap(), 1);
1462 for (query, expected) in [("originalneedle", 1), ("replacementneedle", 0)] {
1463 assert_eq!(
1464 store
1465 .search_count(&Filter {
1466 query: query.into(),
1467 ..Filter::default()
1468 })
1469 .unwrap(),
1470 expected
1471 );
1472 }
1473 assert_eq!(
1474 store.facets(crate::model::FacetKind::Tag, "").unwrap()[0].value,
1475 "originaltag"
1476 );
1477 assert!(store.meta("last_sync").unwrap().is_none());
1478 }
1479
1480 #[test]
1481 fn sync_writes_only_changed_stations_and_facet_memberships() {
1482 let dir = tempfile::tempdir().unwrap();
1483 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1484 let row = |name: &str| Station {
1485 stationuuid: uuid::Uuid::new_v4().to_string(),
1486 name: name.into(),
1487 tags: "jazz,jazz".into(),
1488 language: "english,en".into(),
1489 countrycode: "US".into(),
1490 active: true,
1491 ..Station::default()
1492 };
1493 let unchanged = row("unchanged");
1494 let mut changed = row("changed");
1495 let removed = row("removed");
1496 let mut revived = row("revived");
1497 revived.active = false;
1498 let mut custom = row("custom");
1499 custom.custom = true;
1500 for station in [&unchanged, &changed, &removed, &revived, &custom] {
1501 store.upsert(station).unwrap();
1502 }
1503 store
1504 .conn
1505 .execute_batch(
1506 "CREATE TABLE station_writes(uuid TEXT);
1507 CREATE TRIGGER audit_station_writes AFTER UPDATE ON stations
1508 BEGIN INSERT INTO station_writes VALUES(new.uuid); END;",
1509 )
1510 .unwrap();
1511 changed.tags = "rock,rock".into();
1512 changed.countrycode = "FR".into();
1513 revived.active = true;
1514 let mut collision = custom.clone();
1515 collision.custom = false;
1516 collision.tags = "wrong".into();
1517 let incoming = [unchanged.clone(), changed, revived, collision, row("new")];
1518 store.begin_sync().unwrap();
1519 store.stage(&incoming).unwrap();
1520 store.finish_sync().unwrap();
1521 let writes: Vec<String> = store
1522 .conn
1523 .prepare("SELECT uuid FROM station_writes")
1524 .unwrap()
1525 .query_map([], |r| r.get(0))
1526 .unwrap()
1527 .collect::<rusqlite::Result<_>>()
1528 .unwrap();
1529 assert_eq!(writes.len(), 3);
1530 assert!(!writes.contains(&unchanged.stationuuid));
1531 assert!(!writes.contains(&custom.stationuuid));
1532 assert!(!store.station(&removed.stationuuid).unwrap().active);
1533 assert_eq!(
1534 store.station(&custom.stationuuid).unwrap().tags,
1535 "jazz,jazz"
1536 );
1537 let facets = || {
1538 store
1539 .conn
1540 .prepare("SELECT kind,value,n FROM facets_cache ORDER BY kind,value")
1541 .unwrap()
1542 .query_map([], |r| {
1543 Ok((
1544 r.get::<_, u8>(0)?,
1545 r.get::<_, String>(1)?,
1546 r.get::<_, i64>(2)?,
1547 ))
1548 })
1549 .unwrap()
1550 .collect::<rusqlite::Result<Vec<_>>>()
1551 .unwrap()
1552 };
1553 let delta_counts = facets();
1554 facets::rebuild(&store.conn).unwrap();
1555 assert_eq!(facets(), delta_counts);
1556 let geo_revision = store.geo_revision().unwrap();
1557 store.begin_sync().unwrap();
1558 store.stage(&incoming).unwrap();
1559 let before = store.conn.total_changes();
1560 store.finish_sync().unwrap();
1561 assert_eq!(store.conn.total_changes() - before, 1);
1564 assert_eq!(store.geo_revision().unwrap(), geo_revision);
1565 }
1566}