Skip to main content

radio_cli/
store.rs

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        // BEGIN is deferred. Pin the WAL snapshot before reading data_version
39        // so a count cached for an older snapshot cannot be reused for new rows.
40        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            // Another process may have migrated while we waited for the lock.
60            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        // Advisory maintenance must not fail an already published catalog.
184        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        // Facet queries compare normalized prefixes with binary SQL equality.
215        // Keep country codes uppercase and language/tag tokens Unicode lowercase
216        // here, for both catalog imports and custom stations.
217        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        // Planner maintenance cannot turn a successful publication into a
324        // reported failure after its transaction has already committed.
325        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        // Token filters still use instr. Without a country index to narrow
382        // them, avoid random table lookups through a sorting index.
383        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            // Resolve localized names at query time so existing catalogs do not
401            // need a resync or a rewrite of their original station metadata.
402            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                // A literal trigram narrows candidates; instr below checks
407                // the complete query without decoding positional postings.
408                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    /// One rare trigram supplies candidates; instr verifies the full literal.
452    /// Avoid positional phrase decoding for every row of a broad match.
453    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        // Bound vocabulary lookups for unusually long command-line queries.
461        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            // If statistics are unavailable, the literal remains a valid
473            // candidate. Query errors are reported by the actual FTS query.
474            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        // Statistics only choose a query strategy. Stale hints after a single
495        // station mutation never change the exact substring predicate.
496        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        // A single read statement pins a consistent snapshot for the vocabulary
504        // and row count. Collect before taking the main database's write lock:
505        // even a small vocabulary requires scanning FTS postings to count docs.
506        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        // Publish all hints atomically. Concurrent station changes may make
515        // them stale, which only affects the choice of search strategy.
516        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    /// Locate a station without loading every preceding page into memory.
543    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            // Separate ranges let each count use the leading sort key. DESC
580            // on the numeric key and ASC on ties cannot use one tuple '<'.
581            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        // The random page has already been shuffled. Other sorts still need
621        // their outer order, including history which overrides filter.sort.
622        let page_order = if !filter.history && filter.sort == crate::model::Sort::Random {
623            String::new()
624        } else {
625            format!(" ORDER BY {order}")
626        };
627        // Sort compact row references, fetching JSON only for the selected page.
628        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}