Skip to main content

radio_cli/
store.rs

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