1mod facets;
2
3use crate::error::{DomainError, ErrorCode};
4use crate::model::{Filter, Recording, Station};
5use anyhow::{Context, Result, bail};
6use rusqlite::{
7 Connection, OptionalExtension, TransactionBehavior, params, params_from_iter, types::Value,
8};
9use std::path::Path;
10
11pub struct Store {
12 conn: Connection,
13}
14
15impl Store {
16 #[cfg(test)]
17 pub(crate) fn test_connection(&self) -> &Connection {
18 &self.conn
19 }
20
21 pub(crate) fn open_reader(path: &Path) -> Result<Self> {
22 let conn = Connection::open_with_flags(path, rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY)?;
23 conn.busy_timeout(std::time::Duration::from_millis(100))?;
24 conn.execute_batch("PRAGMA cache_size=-4096;")?;
25 Ok(Self { conn })
26 }
27
28 pub(crate) fn interrupt_handle(&self) -> rusqlite::InterruptHandle {
29 self.conn.get_interrupt_handle()
30 }
31
32 pub(crate) fn cancel_queries_when(&self, cancel: impl FnMut() -> bool + Send + 'static) {
33 self.conn.progress_handler(1000, Some(cancel));
34 }
35
36 pub(crate) fn read_snapshot<T>(&self, read: impl FnOnce(&Self) -> Result<T>) -> Result<T> {
37 let tx = self.conn.unchecked_transaction()?;
38 tx.query_row("SELECT 1 FROM sqlite_schema LIMIT 1", [], |_| Ok(()))?;
41 let value = read(self)?;
42 tx.commit()?;
43 Ok(value)
44 }
45
46 pub fn open(path: &Path) -> Result<Self> {
47 let mut conn = Connection::open(path)?;
48 conn.busy_timeout(std::time::Duration::from_secs(5))?;
49 conn.execute_batch(
50 "PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL;
51 PRAGMA cache_size=-4096; PRAGMA synchronous=NORMAL;",
52 )?;
53 let version: u32 = conn.pragma_query_value(None, "user_version", |r| r.get(0))?;
54 if version > 5 {
55 bail!(DomainError::new(ErrorCode::DatabaseTooNew));
56 }
57 if version < 5 {
58 let tx = conn.transaction_with_behavior(TransactionBehavior::Immediate)?;
59 let version: u32 = tx.pragma_query_value(None, "user_version", |r| r.get(0))?;
61 match version {
62 0 => tx.execute_batch(
63 "CREATE TABLE stations (
64 uuid TEXT PRIMARY KEY, name TEXT NOT NULL, search TEXT NOT NULL,
65 country TEXT NOT NULL, languages TEXT NOT NULL, tags TEXT NOT NULL,
66 bitrate INTEGER NOT NULL, popularity INTEGER NOT NULL,
67 custom INTEGER NOT NULL, active INTEGER NOT NULL, json TEXT NOT NULL);
68 CREATE INDEX station_country ON stations(country, bitrate);
69 CREATE INDEX station_popularity ON stations(popularity DESC);
70 CREATE TABLE favorites (
71 uuid TEXT PRIMARY KEY REFERENCES stations(uuid), created_at TEXT NOT NULL);
72 CREATE TABLE history (
73 id INTEGER PRIMARY KEY, uuid TEXT NOT NULL REFERENCES stations(uuid), played_at TEXT NOT NULL);
74 CREATE INDEX history_station ON history(uuid);
75 CREATE TABLE streams (
76 uuid TEXT NOT NULL REFERENCES stations(uuid), url TEXT NOT NULL, PRIMARY KEY(uuid,url));
77 CREATE TABLE recordings (
78 id TEXT PRIMARY KEY, status TEXT NOT NULL, json TEXT NOT NULL,
79 recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1)));
80 CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);"
81 )?,
82 1 => tx.execute_batch(
83 "ALTER TABLE recordings ADD COLUMN recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1));
84 -- Translate legacy diagnostics once, never during normal queries.
85 UPDATE recordings SET recovery_pending=1 WHERE status='interrupted' AND json_extract(json,'$.error') LIKE '恢复失败:%';"
86 )?,
87 2..=5 => {},
88 _ => bail!(DomainError::new(ErrorCode::DatabaseTooNew)),
89 }
90 if version < 3 {
91 tx.execute_batch(
92 "ALTER TABLE stations ADD COLUMN normalized_url TEXT;
93 CREATE INDEX station_url ON stations(normalized_url) WHERE active=1;",
94 )?;
95 let mut select = tx.prepare("SELECT uuid,json FROM stations")?;
96 let mut update =
97 tx.prepare("UPDATE stations SET normalized_url=?2 WHERE uuid=?1")?;
98 let rows = select
99 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)))?;
100 for row in rows {
101 let (uuid, json) = row?;
102 let station: Station = serde_json::from_str(&json)?;
103 update.execute(params![
104 uuid,
105 crate::service::normalized_url(station.primary_url()).ok()
106 ])?;
107 }
108 }
109 if version < 4 {
110 tx.execute_batch(
111 "CREATE TABLE facets_cache (
112 kind INTEGER NOT NULL,value TEXT NOT NULL,n INTEGER NOT NULL CHECK(n>=0),
113 PRIMARY KEY(kind,value)) WITHOUT ROWID;",
114 )?;
115 facets::rebuild(&tx)?;
116 }
117 if version < 5 {
118 tx.execute_batch(
119 "CREATE INDEX station_sort ON stations(popularity DESC,name,uuid,active,custom,bitrate);
120 CREATE INDEX station_name ON stations(name COLLATE NOCASE,uuid,active,custom,bitrate);
121 CREATE INDEX station_bitrate ON stations(bitrate DESC,name,uuid,active,custom);
122 CREATE INDEX station_active ON stations(active) WHERE active=1;
123 CREATE INDEX station_directory ON stations(active) WHERE active=1 AND custom=0;
124 DROP INDEX IF EXISTS station_popularity;
125 CREATE VIRTUAL TABLE station_search USING fts5(search,
126 content='stations',content_rowid='rowid',columnsize=0,detail=none,
127 tokenize='trigram case_sensitive 1');
128 INSERT INTO station_search(station_search) VALUES ('rebuild');
129 CREATE VIRTUAL TABLE station_search_terms USING fts5vocab(station_search,'row');
130 CREATE TABLE station_search_stats(term TEXT PRIMARY KEY,n INTEGER NOT NULL) WITHOUT ROWID;
131 INSERT INTO station_search_stats SELECT term,doc FROM station_search_terms;
132 INSERT INTO station_search_stats VALUES ('',(SELECT count(*) FROM stations));
133 CREATE TRIGGER station_search_insert AFTER INSERT ON stations BEGIN
134 INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
135 END;
136 CREATE TRIGGER station_search_delete AFTER DELETE ON stations BEGIN
137 INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
138 END;
139 CREATE TRIGGER station_search_update AFTER UPDATE OF search ON stations
140 WHEN old.search IS NOT new.search BEGIN
141 INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
142 INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
143 END;
144 ANALYZE;",
145 )?;
146 }
147 tx.pragma_update(None, "user_version", 5)?;
148 tx.commit()?;
149 }
150 Ok(Self { conn })
151 }
152
153 pub fn upsert(&self, station: &Station) -> Result<()> {
154 let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
155 Self::write_station(&tx, "stations", station)?;
156 tx.commit()?;
157 Ok(())
158 }
159
160 pub fn bootstrap(
161 &mut self,
162 rows: &[Station],
163 mut cancelled: impl FnMut() -> bool,
164 ) -> Result<usize> {
165 let tx = self.conn.transaction()?;
166 for row in rows {
167 if cancelled() {
168 bail!(DomainError::new(ErrorCode::Cancelled));
169 }
170 Self::write_station_with_policy(&tx, "stations", row, false, false)?;
171 }
172 if cancelled() {
173 bail!(DomainError::new(ErrorCode::Cancelled));
174 }
175 if rows.is_empty() {
176 bail!(DomainError::new(ErrorCode::EmptyCatalog));
177 }
178 facets::rebuild(&tx)?;
179 if cancelled() {
180 bail!(DomainError::new(ErrorCode::Cancelled));
181 }
182 tx.commit()?;
183 let _ = self.refresh_search_stats();
185 Ok(rows.len())
186 }
187
188 fn write_station(conn: &Connection, table: &str, s: &Station) -> Result<()> {
189 Self::write_station_with_policy(conn, table, s, false, table == "stations")
190 }
191
192 fn write_station_with_policy(
193 conn: &Connection,
194 table: &str,
195 s: &Station,
196 custom: bool,
197 maintain_facets: bool,
198 ) -> Result<()> {
199 if s.stationuuid.is_empty() || s.name.trim().is_empty() {
200 bail!(DomainError::new(ErrorCode::InvalidStation));
201 }
202 uuid::Uuid::parse_str(&s.stationuuid).context(DomainError::new(ErrorCode::InvalidUuid))?;
203 let before = if maintain_facets {
204 facets::station_values(conn, &s.stationuuid)?
205 } else {
206 Default::default()
207 };
208 let sql = format!("INSERT INTO {table} (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url)
209 VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)
210 ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
211 country=excluded.country,languages=excluded.languages,tags=excluded.tags,
212 bitrate=excluded.bitrate,popularity=excluded.popularity,custom=excluded.custom,active=excluded.active,json=excluded.json,normalized_url=excluded.normalized_url
213 WHERE {table}.custom=0 OR {custom}");
214 let normalize = |value: &str| {
218 value
219 .split(',')
220 .map(|v| v.trim().to_lowercase())
221 .filter(|v| !v.is_empty())
222 .collect::<Vec<_>>()
223 .join(",")
224 };
225 let languages = normalize(&format!("{},{}", s.language, s.languagecodes));
226 conn.prepare_cached(&sql)?.execute(params![
227 s.stationuuid,
228 s.name,
229 s.search_text(),
230 s.countrycode.to_uppercase(),
231 format!(",{languages},"),
232 format!(",{},", normalize(&s.tags)),
233 s.bitrate,
234 s.clickcount.min(i64::MAX as u64),
235 s.custom,
236 s.active,
237 serde_json::to_string(s)?,
238 crate::service::normalized_url(s.primary_url()).ok()
239 ])?;
240 if maintain_facets {
241 facets::update(
242 conn,
243 &before,
244 &facets::station_values(conn, &s.stationuuid)?,
245 )?;
246 }
247 Ok(())
248 }
249
250 pub fn set_meta(&self, key: &str, value: &str) -> Result<()> {
251 self.conn.execute(
252 "INSERT INTO meta(key,value) VALUES (?1,?2) ON CONFLICT(key) DO UPDATE SET value=excluded.value WHERE meta.value IS NOT excluded.value",
253 params![key, value],
254 )?;
255 Ok(())
256 }
257
258 pub fn directory_count(&self) -> Result<usize> {
259 Ok(self.conn.query_row(
260 "SELECT count(*) FROM stations WHERE custom=0 AND active=1",
261 [],
262 |r| r.get(0),
263 )?)
264 }
265
266 pub fn incoming_count(&self) -> Result<usize> {
267 Ok(self
268 .conn
269 .query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?)
270 }
271
272 pub fn begin_sync(&self) -> Result<()> {
273 self.conn.execute_batch(
274 "DROP TABLE IF EXISTS temp.incoming;
275 CREATE TEMP TABLE incoming AS SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url FROM stations WHERE 0;
276 CREATE UNIQUE INDEX temp.incoming_uuid ON incoming(uuid);",
277 )?;
278 Ok(())
279 }
280
281 pub fn stage(&mut self, rows: &[Station]) -> Result<()> {
282 let tx = self.conn.transaction()?;
283 for row in rows {
284 let mut s = row.clone();
285 s.active = true;
286 s.custom = false;
287 Self::write_station(&tx, "incoming", &s)?;
288 }
289 tx.commit()?;
290 Ok(())
291 }
292
293 pub fn finish_sync(&mut self) -> Result<usize> {
294 self.finish_sync_cancellable(|| false)
295 }
296
297 pub(crate) fn finish_sync_cancellable(
298 &mut self,
299 mut cancelled: impl FnMut() -> bool,
300 ) -> Result<usize> {
301 let tx = self.conn.transaction()?;
302 let count: usize = tx.query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?;
303 if count == 0 {
304 bail!(DomainError::new(ErrorCode::EmptyCatalog));
305 }
306 tx.execute("UPDATE stations SET active=0 WHERE custom=0", [])?;
307 tx.execute_batch(
308 "INSERT INTO stations (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url) SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url FROM incoming WHERE true
309 ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
310 country=excluded.country,languages=excluded.languages,tags=excluded.tags,
311 bitrate=excluded.bitrate,popularity=excluded.popularity,active=1,json=excluded.json,normalized_url=excluded.normalized_url
312 WHERE stations.custom=0;",
313 )?;
314 tx.execute(
315 "INSERT OR REPLACE INTO meta(key,value) VALUES ('last_sync',?1)",
316 [chrono::Utc::now().to_rfc3339()],
317 )?;
318 facets::rebuild(&tx)?;
319 if cancelled() {
320 bail!(DomainError::new(ErrorCode::Cancelled));
321 }
322 tx.commit()?;
323 let _ = self.refresh_search_stats();
326 let _ = self.conn.execute_batch("PRAGMA optimize;");
327 Ok(count)
328 }
329
330 pub fn revision(&self) -> Result<(u64, u64)> {
331 let external = self
332 .conn
333 .pragma_query_value(None, "data_version", |r| r.get(0))?;
334 Ok((external, self.conn.total_changes()))
335 }
336
337 pub fn meta(&self, key: &str) -> Result<Option<String>> {
338 Ok(self
339 .conn
340 .query_row("SELECT value FROM meta WHERE key=?1", [key], |r| r.get(0))
341 .optional()?)
342 }
343
344 pub fn count(&self) -> Result<usize> {
345 Ok(self
346 .conn
347 .query_row("SELECT count(*) FROM stations WHERE active=1", [], |r| {
348 r.get(0)
349 })?)
350 }
351
352 pub fn station(&self, uuid: &str) -> Result<Station> {
353 let row = self
354 .conn
355 .query_row(
356 "SELECT s.json,s.active,f.uuid IS NOT NULL FROM stations s
357 LEFT JOIN favorites f ON f.uuid=s.uuid WHERE s.uuid=?1",
358 [uuid],
359 |r| {
360 Ok((
361 r.get::<_, String>(0)?,
362 r.get::<_, bool>(1)?,
363 r.get::<_, bool>(2)?,
364 ))
365 },
366 )
367 .optional()?
368 .context(DomainError::new(ErrorCode::StationNotFound))?;
369 let mut s: Station = serde_json::from_str(&row.0)?;
370 s.active = row.1;
371 s.favorite = row.2;
372 Ok(s)
373 }
374
375 fn conditions(&self, filter: &Filter) -> (String, Vec<Value>, &'static str) {
376 let mut clauses = vec![
377 "(s.active=1 OR s.custom=1 OR EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid))"
378 .to_owned(),
379 ];
380 let mut args = vec![];
381 let mut source =
384 if filter.country.is_none() && (filter.language.is_some() || filter.tag.is_some()) {
385 "stations s NOT INDEXED"
386 } else {
387 "stations s"
388 };
389 if filter.favorites {
390 clauses.push("EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)".into());
391 }
392 if filter.custom {
393 clauses.push("s.custom=1".into());
394 }
395 if filter.history {
396 clauses[0] = "EXISTS(SELECT 1 FROM history h WHERE h.uuid=s.uuid)".into();
397 }
398 let query = filter.query.trim();
399 if !query.is_empty() {
400 let codes: Vec<_> = crate::territories::matching_codes(query).collect();
403 let normalized = query.to_lowercase();
404 source = "stations s NOT INDEXED";
405 if let Some(candidate) = self.trigram_candidate(&normalized) {
406 let mut candidates =
409 "SELECT rowid FROM station_search WHERE station_search MATCH ?".to_owned();
410 args.push(candidate.into());
411 if !codes.is_empty() {
412 candidates.push_str(&format!(
413 " UNION SELECT rowid FROM stations WHERE country IN ({})",
414 vec!["?"; codes.len()].join(",")
415 ));
416 args.extend(codes.iter().map(|code| Value::from((*code).to_owned())));
417 }
418 clauses.push(if codes.is_empty() {
419 source = "station_search CROSS JOIN stations s ON s.rowid=station_search.rowid";
420 "station_search MATCH ?".to_owned()
421 } else {
422 format!("s.rowid IN ({candidates})")
423 });
424 }
425 if codes.is_empty() {
426 clauses.push("instr(s.search,?)>0".into());
427 } else {
428 let placeholders = vec!["?"; codes.len()].join(",");
429 clauses.push(format!(
430 "(instr(s.search,?)>0 OR s.country IN ({placeholders}))"
431 ));
432 }
433 args.push(normalized.into());
434 args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
435 }
436 if let Some(country) = &filter.country {
437 clauses.push("s.country=?".into());
438 args.push(country.to_uppercase().into());
439 }
440 for (col, value) in [("languages", &filter.language), ("tags", &filter.tag)] {
441 if let Some(value) = value {
442 clauses.push(format!("instr(s.{col},?)>0"));
443 args.push(format!(",{},", value.trim().to_lowercase()).into());
444 }
445 }
446 clauses.push("s.bitrate>=?".into());
447 args.push((filter.min_bitrate as i64).into());
448 (clauses.join(" AND "), args, source)
449 }
450
451 fn trigram_candidate(&self, normalized: &str) -> Option<String> {
454 if normalized.chars().count() < 3 || normalized.contains('\0') {
455 return None;
456 }
457 let chars: Vec<_> = normalized.chars().collect();
458 let stride = ((chars.len() - 2) / 16).max(1);
459 let mut best = (usize::MAX, chars[..3].iter().collect::<String>());
460 let terms: std::collections::BTreeSet<String> = chars
462 .windows(3)
463 .step_by(stride)
464 .take(16)
465 .map(|part| part.iter().collect())
466 .collect();
467 for term in terms {
468 let frequency = self
469 .conn
470 .prepare_cached("SELECT n FROM station_search_stats WHERE term=?1")
471 .and_then(|mut s| s.query_row([&term], |r| r.get::<_, usize>(0)).optional());
472 let frequency = match frequency {
475 Ok(Some(n)) => n,
476 Ok(None) => 0,
477 Err(_) => continue,
478 };
479 if frequency < best.0 {
480 best = (frequency, term);
481 }
482 if frequency == 0 {
483 break;
484 }
485 }
486 let total: usize = self
487 .conn
488 .query_row(
489 "SELECT n FROM station_search_stats WHERE term=''",
490 [],
491 |r| r.get(0),
492 )
493 .unwrap_or(0);
494 if total > 0 && best.0 > total / 4 {
497 return None;
498 }
499 Some(format!("\"{}\"", best.1.replace('"', "\"\"")))
500 }
501
502 fn refresh_search_stats(&self) -> Result<()> {
503 let stats = {
507 let mut stmt = self.conn.prepare(
508 "SELECT term,doc FROM station_search_terms
509 UNION ALL SELECT '',count(*) FROM stations",
510 )?;
511 stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))?
512 .collect::<rusqlite::Result<Vec<_>>>()?
513 };
514 let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
517 tx.execute("DELETE FROM station_search_stats", [])?;
518 {
519 let mut stmt = tx.prepare("INSERT INTO station_search_stats VALUES (?1,?2)")?;
520 for (term, count) in stats {
521 stmt.execute(params![term, count])?;
522 }
523 }
524 tx.commit()?;
525 Ok(())
526 }
527
528 fn search_order(filter: &Filter) -> &'static str {
529 if filter.history {
530 "(SELECT max(id) FROM history WHERE uuid=s.uuid) DESC"
531 } else {
532 match filter.sort {
533 crate::model::Sort::Name => "s.name COLLATE NOCASE,s.uuid",
534 crate::model::Sort::Bitrate => "s.bitrate DESC,s.name,s.uuid",
535 crate::model::Sort::Votes => "json_extract(s.json,'$.votes') DESC,s.name,s.uuid",
536 crate::model::Sort::Clickcount => "s.popularity DESC,s.name,s.uuid",
537 crate::model::Sort::Random => "random()",
538 }
539 }
540 }
541
542 pub fn station_position(&self, uuid: &str, filter: &Filter) -> Result<Option<usize>> {
544 use crate::model::Sort;
545 let (conditions, mut args, source) = self.conditions(filter);
546 if !filter.history && matches!(filter.sort, Sort::Clickcount | Sort::Bitrate | Sort::Name) {
547 let tx = self.conn.unchecked_transaction()?;
548 let mut target_args = args.clone();
549 target_args.push(uuid.to_owned().into());
550 let target = tx
551 .query_row(
552 &format!(
553 "SELECT s.popularity,s.bitrate,s.name FROM {source}
554 WHERE {conditions} AND s.uuid=?"
555 ),
556 params_from_iter(target_args),
557 |r| {
558 Ok((
559 r.get::<_, i64>(0)?,
560 r.get::<_, i64>(1)?,
561 r.get::<_, String>(2)?,
562 ))
563 },
564 )
565 .optional()?;
566 let Some((popularity, bitrate, name)) = target else {
567 return Ok(None);
568 };
569 let count = |predicate: &str, values: Vec<Value>| -> Result<usize> {
570 let mut values_with_filter = args.clone();
571 values_with_filter.extend(values);
572 Ok(tx
573 .prepare_cached(&format!(
574 "SELECT count(*) FROM {source}
575 WHERE {conditions} AND ({predicate})"
576 ))?
577 .query_row(params_from_iter(values_with_filter), |r| r.get(0))?)
578 };
579 let position = if filter.sort == Sort::Name {
582 count("s.name COLLATE NOCASE < ?", vec![name.clone().into()])?
583 + count(
584 "s.name COLLATE NOCASE = ? AND s.uuid < ?",
585 vec![name.into(), uuid.to_owned().into()],
586 )?
587 } else {
588 let (column, value) = if filter.sort == Sort::Bitrate {
589 ("bitrate", bitrate)
590 } else {
591 ("popularity", popularity)
592 };
593 count(&format!("s.{column}>?"), vec![value.into()])?
594 + count(
595 &format!("s.{column}=? AND (s.name,s.uuid)<(?,?)"),
596 vec![value.into(), name.into(), uuid.to_owned().into()],
597 )?
598 };
599 tx.commit()?;
600 return Ok(Some(position));
601 }
602 let order = Self::search_order(filter);
603 let sql = format!(
604 "WITH ranked AS (
605 SELECT s.uuid,row_number() OVER (ORDER BY {order})-1 AS position
606 FROM {source} WHERE {conditions}
607 ) SELECT position FROM ranked WHERE uuid=?"
608 );
609 args.push(uuid.to_owned().into());
610 Ok(self
611 .conn
612 .query_row(&sql, params_from_iter(args), |row| row.get::<_, i64>(0))
613 .optional()?
614 .map(|position| position as usize))
615 }
616
617 pub fn search(&self, filter: &Filter) -> Result<Vec<Station>> {
618 let (conditions, mut args, source) = self.conditions(filter);
619 let order = Self::search_order(filter);
620 let page_order = if !filter.history && filter.sort == crate::model::Sort::Random {
623 String::new()
624 } else {
625 format!(" ORDER BY {order}")
626 };
627 let sql = format!(
629 "WITH page AS MATERIALIZED (
630 SELECT s.rowid AS id FROM {source}
631 WHERE {conditions} ORDER BY {order} LIMIT ? OFFSET ?)
632 SELECT s.json,s.active,EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)
633 FROM page JOIN stations s ON s.rowid=page.id{page_order}"
634 );
635 args.push((filter.limit.clamp(1, 1000) as i64).into());
636 args.push((filter.offset.min(i64::MAX as usize) as i64).into());
637 let mut stmt = self.conn.prepare_cached(&sql)?;
638 let rows = stmt.query_map(params_from_iter(args), |r| {
639 Ok((
640 r.get::<_, String>(0)?,
641 r.get::<_, bool>(1)?,
642 r.get::<_, bool>(2)?,
643 ))
644 })?;
645 rows.map(|row| {
646 let (json, active, favorite) = row?;
647 let mut s: Station = serde_json::from_str(&json)?;
648 s.active = active;
649 s.favorite = favorite;
650 Ok(s)
651 })
652 .collect()
653 }
654
655 pub fn random(&self, filter: &Filter, exclude: &[String]) -> Result<Option<Station>> {
656 let (mut conditions, mut args, source) = self.conditions(filter);
657 if !exclude.is_empty() {
658 let placeholders = vec!["?"; exclude.len()].join(",");
659 conditions.push_str(&format!(" AND s.uuid NOT IN ({placeholders})"));
660 args.extend(exclude.iter().cloned().map(Value::Text));
661 }
662 let sql = format!(
663 "SELECT s.uuid FROM {source}
664 WHERE {conditions} ORDER BY random() LIMIT 1"
665 );
666 let selected = self
667 .conn
668 .query_row(&sql, params_from_iter(args), |r| r.get::<_, String>(0))
669 .optional()?;
670 selected.map(|id| self.station(&id)).transpose()
671 }
672
673 pub fn facets(
674 &self,
675 kind: crate::model::FacetKind,
676 prefix: &str,
677 ) -> Result<Vec<crate::model::Facet>> {
678 self.facets_limited(kind, prefix, usize::MAX)
679 }
680
681 pub fn facets_limited(
682 &self,
683 kind: crate::model::FacetKind,
684 prefix: &str,
685 limit: usize,
686 ) -> Result<Vec<crate::model::Facet>> {
687 use crate::model::{Facet, FacetKind};
688 let (kind, prefix) = match kind {
689 FacetKind::Country => (0, prefix.trim().to_uppercase()),
690 FacetKind::Language => (1, prefix.trim().to_lowercase()),
691 FacetKind::Tag => (2, prefix.trim().to_lowercase()),
692 };
693 let mut stmt = self.conn.prepare_cached(
694 "SELECT value,n FROM facets_cache WHERE kind=?1 AND substr(value,1,length(?2))=?2 ORDER BY value LIMIT ?3",
695 )?;
696 let rows = stmt.query_map(
697 params![kind, prefix, limit.min(i64::MAX as usize) as i64],
698 |r| {
699 Ok(Facet {
700 value: r.get(0)?,
701 count: r.get(1)?,
702 })
703 },
704 )?;
705 Ok(rows.collect::<rusqlite::Result<_>>()?)
706 }
707
708 pub fn favorite(&self, uuid: &str, add: bool) -> Result<()> {
709 self.station(uuid)?;
710 if add {
711 self.conn.execute(
712 "INSERT OR IGNORE INTO favorites(uuid,created_at) VALUES (?1,?2)",
713 params![uuid, chrono::Utc::now().to_rfc3339()],
714 )?;
715 } else {
716 self.conn
717 .execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
718 }
719 Ok(())
720 }
721
722 pub fn toggle_favorite(&mut self, uuid: &str) -> Result<bool> {
723 let tx = self
724 .conn
725 .transaction_with_behavior(TransactionBehavior::Immediate)?;
726 tx.query_row("SELECT 1 FROM stations WHERE uuid=?1", [uuid], |r| {
727 r.get::<_, u8>(0)
728 })
729 .optional()?
730 .context(DomainError::new(ErrorCode::StationNotFound))?;
731 let removed = tx.execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
732 let added = removed == 0;
733 if added {
734 tx.execute(
735 "INSERT INTO favorites(uuid,created_at) VALUES (?1,?2)",
736 params![uuid, chrono::Utc::now().to_rfc3339()],
737 )?;
738 }
739 tx.commit()?;
740 Ok(added)
741 }
742
743 pub fn played(&self, uuid: &str) -> Result<()> {
744 self.conn.execute(
745 "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
746 params![uuid, chrono::Utc::now().to_rfc3339()],
747 )?;
748 let threshold = self.conn.last_insert_rowid().saturating_sub(1000);
749 self.conn
750 .execute("DELETE FROM history WHERE id <= ?1", [threshold])?;
751 Ok(())
752 }
753
754 pub fn add_backup(&self, uuid: &str, url: &str) -> Result<()> {
755 crate::config::validate_url(url)?;
756 self.conn.execute(
757 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
758 params![uuid, url],
759 )?;
760 Ok(())
761 }
762
763 pub fn save_custom(&mut self, station: &Station, backups: Option<&[String]>) -> Result<()> {
764 let tx = self
765 .conn
766 .transaction_with_behavior(TransactionBehavior::Immediate)?;
767 let key = crate::service::normalized_url(station.primary_url())?;
768 if let Some(uuid) = tx.query_row(
769 "SELECT uuid FROM stations WHERE active=1 AND normalized_url=?1 AND uuid<>?2 LIMIT 1",
770 params![key, station.stationuuid], |r| r.get::<_, String>(0),
771 ).optional()? {
772 bail!(DomainError::new(ErrorCode::DuplicateStation).with("uuid", uuid));
773 }
774 Self::write_station_with_policy(&tx, "stations", station, true, true)?;
775 if let Some(backups) = backups {
776 tx.execute("DELETE FROM streams WHERE uuid=?1", [&station.stationuuid])?;
777 for url in backups {
778 crate::config::validate_url(url)?;
779 tx.execute(
780 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
781 params![station.stationuuid, url],
782 )?;
783 }
784 }
785 tx.commit()?;
786 Ok(())
787 }
788
789 pub fn remove_custom(&mut self, uuid: &str, keep_references: bool) -> Result<bool> {
790 let tx = self
791 .conn
792 .transaction_with_behavior(TransactionBehavior::Immediate)?;
793 let json: String = tx
794 .query_row(
795 "SELECT json FROM stations WHERE uuid=?1 AND custom=1",
796 [uuid],
797 |r| r.get(0),
798 )
799 .optional()?
800 .context(DomainError::new(ErrorCode::NotCustom))?;
801 let mut station: Station = serde_json::from_str(&json)?;
802 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))?;
803 tx.execute("DELETE FROM streams WHERE uuid=?1", [uuid])?;
804 if referenced || keep_references {
805 station.custom = false;
806 station.active = false;
807 station.url.clear();
808 station.url_resolved.clear();
809 Self::write_station_with_policy(&tx, "stations", &station, true, true)?;
810 } else {
811 let before = facets::station_values(&tx, uuid)?;
812 tx.execute("DELETE FROM stations WHERE uuid=?1", [uuid])?;
813 facets::update(&tx, &before, &Default::default())?;
814 }
815 tx.commit()?;
816 Ok(referenced || keep_references)
817 }
818
819 pub fn update_backups(
820 &mut self,
821 uuid: &str,
822 add: &[String],
823 remove: &[String],
824 ) -> Result<Vec<String>> {
825 self.station(uuid)?;
826 let tx = self.conn.transaction()?;
827 for url in remove {
828 tx.execute(
829 "DELETE FROM streams WHERE uuid=?1 AND url=?2",
830 params![uuid, url],
831 )?;
832 }
833 for url in add {
834 crate::config::validate_url(url)?;
835 tx.execute(
836 "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
837 params![uuid, url],
838 )?;
839 }
840 tx.commit()?;
841 self.backups(uuid)
842 }
843
844 pub fn backups(&self, uuid: &str) -> Result<Vec<String>> {
845 let mut stmt = self
846 .conn
847 .prepare("SELECT url FROM streams WHERE uuid=?1 ORDER BY rowid")?;
848 Ok(stmt
849 .query_map([uuid], |r| r.get(0))?
850 .collect::<rusqlite::Result<_>>()?)
851 }
852
853 pub fn save_recording(&self, recording: &Recording) -> Result<()> {
854 self.conn.execute(
855 "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",
856 params![
857 recording.id,
858 recording.status,
859 serde_json::to_string(recording)?,
860 recording.recovery_pending
861 ],
862 )?;
863 Ok(())
864 }
865
866 pub fn search_count(&self, filter: &Filter) -> Result<usize> {
867 let (conditions, args, source) = self.conditions(filter);
868 Ok(self
869 .conn
870 .prepare_cached(&format!("SELECT count(*) FROM {source} WHERE {conditions}"))?
871 .query_row(params_from_iter(args), |r| r.get(0))?)
872 }
873
874 pub fn recordings(&self, limit: usize) -> Result<Vec<Recording>> {
875 self.recordings_page(limit, 0)
876 }
877
878 pub fn recordings_count(&self) -> Result<usize> {
879 Ok(self
880 .conn
881 .query_row("SELECT count(*) FROM recordings", [], |r| r.get(0))?)
882 }
883
884 pub fn recording(&self, id: &str) -> Result<Recording> {
885 let row = self
886 .conn
887 .query_row(
888 "SELECT json,recovery_pending FROM recordings WHERE id=?1",
889 [id],
890 |r| Ok((r.get(0)?, r.get(1)?)),
891 )
892 .optional()?
893 .context(DomainError::new(ErrorCode::RecordingNotFound))?;
894 Self::read_recording(row)
895 }
896
897 pub fn remove_recording_index(&self, id: &str) -> Result<()> {
898 self.conn
899 .execute("DELETE FROM recordings WHERE id=?1", [id])?;
900 Ok(())
901 }
902
903 pub fn recordings_page(&self, limit: usize, offset: usize) -> Result<Vec<Recording>> {
904 let mut stmt = self.conn.prepare("SELECT json,recovery_pending FROM recordings ORDER BY json_extract(json,'$.started_at') DESC,id DESC LIMIT ?1 OFFSET ?2")?;
905 let rows = stmt.query_map(
906 params![
907 limit.clamp(1, 1000) as i64,
908 offset.min(i64::MAX as usize) as i64
909 ],
910 |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)),
911 )?;
912 rows.map(|row| Self::read_recording(row?)).collect()
913 }
914
915 fn read_recording((json, recovery_pending): (String, bool)) -> Result<Recording> {
916 let mut record: Recording = serde_json::from_str(&json)?;
917 record.recovery_pending = recovery_pending;
918 Ok(record)
919 }
920
921 pub fn unfinished_recordings(&self) -> Result<Vec<Recording>> {
922 let mut stmt = self
923 .conn
924 .prepare("SELECT json,recovery_pending FROM recordings WHERE status IN ('recording','finalizing') OR recovery_pending=1")?;
925 let rows = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)))?;
926 rows.map(|row| Self::read_recording(row?)).collect()
927 }
928}
929
930#[cfg(test)]
931mod tests {
932 use super::*;
933
934 #[test]
935 fn territory_queries_union_indexed_country_and_trigram_candidates() {
936 let dir = tempfile::tempdir().unwrap();
937 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
938 let rows: Vec<_> = (0..100)
939 .map(|i| Station {
940 stationuuid: uuid::Uuid::new_v4().to_string(),
941 name: match i {
942 1 => "爱尔兰音乐".into(),
943 2 => "爱尔兰广播".into(),
944 _ => format!("catalog station {i}"),
945 },
946 countrycode: if i == 0 || i == 2 { "IE" } else { "FR" }.into(),
947 clickcount: i,
948 active: true,
949 ..Station::default()
950 })
951 .collect();
952 store.bootstrap(&rows, || false).unwrap();
953 let filter = Filter {
954 query: " 爱尔兰 ".into(),
955 limit: 100,
956 ..Filter::default()
957 };
958 let actual: Vec<_> = store
959 .search(&filter)
960 .unwrap()
961 .into_iter()
962 .map(|row| row.stationuuid)
963 .collect();
964 assert_eq!(actual, [2, 1, 0].map(|i| rows[i].stationuuid.clone()));
965 assert_eq!(store.search_count(&filter).unwrap(), 3);
966 let (conditions, args, source) = store.conditions(&filter);
967 let plan: Vec<String> = store
968 .conn
969 .prepare(&format!(
970 "EXPLAIN QUERY PLAN SELECT s.rowid FROM {source} WHERE {conditions}"
971 ))
972 .unwrap()
973 .query_map(params_from_iter(args), |r| r.get(3))
974 .unwrap()
975 .collect::<rusqlite::Result<_>>()
976 .unwrap();
977 for index in [
978 "SEARCH s USING INTEGER PRIMARY KEY (rowid=?)",
979 "station_search VIRTUAL TABLE INDEX",
980 "station_country (country=?)",
981 ] {
982 assert!(plan.iter().any(|step| step.contains(index)), "{plan:?}");
983 }
984 }
985
986 #[test]
987 fn search_statistics_collection_allows_a_concurrent_writer() {
988 use std::sync::{
989 Arc,
990 atomic::{AtomicBool, Ordering},
991 };
992
993 let dir = tempfile::tempdir().unwrap();
994 let path = dir.path().join("radio.db");
995 let mut store = Store::open(&path).unwrap();
996 let rows: Vec<_> = (0..256)
997 .map(|i| Station {
998 stationuuid: uuid::Uuid::new_v4().to_string(),
999 name: format!("station {i:05} jazz"),
1000 active: true,
1001 ..Station::default()
1002 })
1003 .collect();
1004 store.bootstrap(&rows, || false).unwrap();
1005 let writer = Connection::open(&path).unwrap();
1006 writer.busy_timeout(std::time::Duration::ZERO).unwrap();
1007 let attempted = Arc::new(AtomicBool::new(false));
1008 let succeeded = Arc::new(AtomicBool::new(false));
1009 let (attempt, success) = (attempted.clone(), succeeded.clone());
1010 store.conn.progress_handler(
1011 1000,
1012 Some(move || {
1013 if !attempt.swap(true, Ordering::SeqCst) {
1014 success.store(
1015 writer
1016 .execute("INSERT INTO meta VALUES ('concurrent','write')", [])
1017 .is_ok(),
1018 Ordering::SeqCst,
1019 );
1020 }
1021 false
1022 }),
1023 );
1024 store.refresh_search_stats().unwrap();
1025 store.conn.progress_handler(0, None::<fn() -> bool>);
1026 assert!(attempted.load(Ordering::SeqCst));
1027 assert!(succeeded.load(Ordering::SeqCst));
1028 let actual: usize = store
1029 .conn
1030 .query_row(
1031 "SELECT n FROM station_search_stats WHERE term=''",
1032 [],
1033 |r| r.get(0),
1034 )
1035 .unwrap();
1036 assert_eq!(actual, rows.len());
1037 let differences: usize = store
1038 .conn
1039 .query_row(
1040 "SELECT count(*) FROM (
1041 SELECT term,doc FROM station_search_terms
1042 EXCEPT SELECT term,n FROM station_search_stats WHERE term<>'')",
1043 [],
1044 |r| r.get(0),
1045 )
1046 .unwrap();
1047 assert_eq!(differences, 0);
1048 }
1049
1050 #[test]
1051 fn failed_statistics_refresh_preserves_hints_and_successful_publication() {
1052 for sync in [false, true] {
1053 let dir = tempfile::tempdir().unwrap();
1054 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1055 let row = |name: &str| Station {
1056 stationuuid: uuid::Uuid::new_v4().to_string(),
1057 name: name.into(),
1058 tags: name.into(),
1059 active: true,
1060 ..Station::default()
1061 };
1062 store.bootstrap(&[row("originalneedle")], || false).unwrap();
1063 let stats = || {
1064 store
1065 .conn
1066 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1067 .unwrap()
1068 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1069 .unwrap()
1070 .collect::<rusqlite::Result<Vec<_>>>()
1071 .unwrap()
1072 };
1073 let before = stats();
1074 store
1075 .conn
1076 .execute_batch(
1077 "CREATE TRIGGER reject_stats BEFORE INSERT ON station_search_stats
1078 BEGIN SELECT RAISE(ABORT,'statistics unavailable'); END;",
1079 )
1080 .unwrap();
1081 let replacement = row("replacementneedle");
1082 if sync {
1083 store.begin_sync().unwrap();
1084 store.stage(&[replacement]).unwrap();
1085 assert_eq!(store.finish_sync().unwrap(), 1);
1086 assert!(store.meta("last_sync").unwrap().is_some());
1087 } else {
1088 assert_eq!(store.bootstrap(&[replacement], || false).unwrap(), 1);
1089 }
1090 assert_eq!(
1091 store
1092 .search_count(&Filter {
1093 query: "replacementneedle".into(),
1094 ..Filter::default()
1095 })
1096 .unwrap(),
1097 1
1098 );
1099 assert_eq!(
1100 store
1101 .facets(crate::model::FacetKind::Tag, "replacement")
1102 .unwrap()[0]
1103 .count,
1104 1
1105 );
1106 let after: Vec<_> = store
1107 .conn
1108 .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1109 .unwrap()
1110 .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1111 .unwrap()
1112 .collect::<rusqlite::Result<_>>()
1113 .unwrap();
1114 assert_eq!(after, before);
1115 }
1116 }
1117
1118 #[test]
1119 fn read_snapshot_pins_revision_before_a_concurrent_commit() {
1120 let dir = tempfile::tempdir().unwrap();
1121 let path = dir.path().join("radio.db");
1122 let writer = Store::open(&path).unwrap();
1123 let reader = Store::open_reader(&path).unwrap();
1124 let revision = reader
1125 .read_snapshot(|store| {
1126 let revision = store.revision()?;
1127 writer.upsert(&Station {
1128 stationuuid: uuid::Uuid::new_v4().to_string(),
1129 name: "new row".into(),
1130 active: true,
1131 ..Station::default()
1132 })?;
1133 assert_eq!(store.revision()?, revision);
1134 assert_eq!(store.count()?, 0);
1135 Ok(revision)
1136 })
1137 .unwrap();
1138 assert_ne!(reader.revision().unwrap(), revision);
1139 assert_eq!(reader.count().unwrap(), 1);
1140 }
1141
1142 #[test]
1143 fn cancellation_before_sync_commit_rolls_back_all_derived_data() {
1144 let dir = tempfile::tempdir().unwrap();
1145 let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1146 let row = |name: &str, tag: &str| Station {
1147 stationuuid: uuid::Uuid::new_v4().to_string(),
1148 name: name.into(),
1149 tags: tag.into(),
1150 active: true,
1151 ..Station::default()
1152 };
1153 store.upsert(&row("originalneedle", "originaltag")).unwrap();
1154 store.begin_sync().unwrap();
1155 store
1156 .stage(&[row("replacementneedle", "replacementtag")])
1157 .unwrap();
1158 assert!(store.finish_sync_cancellable(|| true).is_err());
1159 assert_eq!(store.count().unwrap(), 1);
1160 for (query, expected) in [("originalneedle", 1), ("replacementneedle", 0)] {
1161 assert_eq!(
1162 store
1163 .search_count(&Filter {
1164 query: query.into(),
1165 ..Filter::default()
1166 })
1167 .unwrap(),
1168 expected
1169 );
1170 }
1171 assert_eq!(
1172 store.facets(crate::model::FacetKind::Tag, "").unwrap()[0].value,
1173 "originaltag"
1174 );
1175 assert!(store.meta("last_sync").unwrap().is_none());
1176 }
1177}