Skip to main content

radio_cli/podcast/
storage.rs

1use super::*;
2use crate::store::Store;
3use anyhow::Result;
4use rusqlite::{OptionalExtension, TransactionBehavior, params};
5
6pub(crate) fn register_functions(conn: &rusqlite::Connection) -> Result<()> {
7    conn.create_scalar_function(
8        "podcast_lower",
9        1,
10        rusqlite::functions::FunctionFlags::SQLITE_UTF8
11            | rusqlite::functions::FunctionFlags::SQLITE_DETERMINISTIC,
12        |context| Ok(context.get::<String>(0)?.to_lowercase()),
13    )?;
14    Ok(())
15}
16
17pub(crate) fn migrate(conn: &rusqlite::Connection) -> Result<()> {
18    conn.execute_batch("CREATE TABLE IF NOT EXISTS podcast_feeds(id TEXT PRIMARY KEY,url TEXT NOT NULL UNIQUE,subscribed INTEGER NOT NULL,json TEXT NOT NULL);
19    CREATE TABLE IF NOT EXISTS podcast_aliases(url TEXT PRIMARY KEY,feed_id TEXT NOT NULL REFERENCES podcast_feeds(id));
20    CREATE TABLE IF NOT EXISTS podcast_episodes(id TEXT PRIMARY KEY,feed_id TEXT NOT NULL REFERENCES podcast_feeds(id),identity TEXT NOT NULL,json TEXT NOT NULL,UNIQUE(feed_id,identity));
21    CREATE TABLE IF NOT EXISTS podcast_progress(episode_id TEXT PRIMARY KEY REFERENCES podcast_episodes(id),position_ms INTEGER NOT NULL,duration_ms INTEGER,completed INTEGER NOT NULL,updated_at TEXT NOT NULL);
22    CREATE TABLE IF NOT EXISTS podcast_history(id INTEGER PRIMARY KEY,episode_id TEXT NOT NULL REFERENCES podcast_episodes(id),played_at TEXT NOT NULL);
23    CREATE TABLE IF NOT EXISTS podcast_downloads(episode_id TEXT PRIMARY KEY REFERENCES podcast_episodes(id),json TEXT NOT NULL);
24    CREATE TABLE IF NOT EXISTS podcast_queue(episode_id TEXT PRIMARY KEY REFERENCES podcast_episodes(id),position INTEGER NOT NULL);
25    CREATE INDEX IF NOT EXISTS podcast_feed_episodes ON podcast_episodes(feed_id);")?;
26    Ok(())
27}
28
29pub(crate) fn migrate_library(conn: &rusqlite::Connection) -> Result<()> {
30    conn.execute_batch("CREATE TABLE IF NOT EXISTS podcast_bookmarks(id TEXT PRIMARY KEY,episode_id TEXT NOT NULL REFERENCES podcast_episodes(id),position_ms INTEGER NOT NULL,note TEXT NOT NULL);
31    CREATE INDEX IF NOT EXISTS podcast_bookmark_episode ON podcast_bookmarks(episode_id,position_ms);
32    CREATE TABLE IF NOT EXISTS podcast_saved(episode_id TEXT PRIMARY KEY REFERENCES podcast_episodes(id));
33    CREATE TABLE IF NOT EXISTS podcast_inbox(episode_id TEXT PRIMARY KEY REFERENCES podcast_episodes(id));")?;
34    conn.execute_batch("CREATE TABLE IF NOT EXISTS podcast_revision(n INTEGER NOT NULL); INSERT INTO podcast_revision SELECT 0 WHERE NOT EXISTS(SELECT 1 FROM podcast_revision);
35        CREATE INDEX IF NOT EXISTS podcast_episode_dates ON podcast_episodes(feed_id,CASE WHEN json_valid(json) THEN json_extract(json,'$.published') END DESC,CASE WHEN json_valid(json) THEN json_extract(json,'$.source_order') END,id);
36        CREATE INDEX IF NOT EXISTS podcast_history_episode ON podcast_history(episode_id,played_at DESC);")?;
37    for table in [
38        "podcast_feeds",
39        "podcast_episodes",
40        "podcast_progress",
41        "podcast_downloads",
42        "podcast_queue",
43        "podcast_saved",
44        "podcast_inbox",
45        "podcast_bookmarks",
46        "podcast_history",
47    ] {
48        for operation in ["INSERT", "UPDATE", "DELETE"] {
49            conn.execute_batch(&format!("CREATE TRIGGER IF NOT EXISTS changed_{table}_{operation} AFTER {operation} ON {table} BEGIN UPDATE podcast_revision SET n=n+1; END;"))?;
50        }
51    }
52    Ok(())
53}
54impl Store {
55    pub fn podcast_revision(&self) -> Result<u64> {
56        Ok(self
57            .conn
58            .query_row("SELECT n FROM podcast_revision", [], |r| r.get(0))?)
59    }
60    pub(crate) fn podcast_unplayed_count(&self, feed: &str) -> Result<usize> {
61        Ok(self.conn.query_row("SELECT count(*) FROM podcast_episodes e LEFT JOIN podcast_progress p ON p.episode_id=e.id WHERE e.feed_id=?1 AND COALESCE(p.completed,0)=0", [feed], |r| r.get(0))?)
62    }
63    pub fn podcast_feeds(&self) -> Result<Vec<Feed>> {
64        let mut stmt = self.conn.prepare("SELECT json FROM podcast_feeds WHERE subscribed=1 ORDER BY json_extract(json,'$.title'),id")?;
65        let rows = stmt.query_map([], |r| r.get::<_, String>(0))?;
66        rows.map(|r| Ok(serde_json::from_str(&r?)?)).collect()
67    }
68    pub fn podcast_feed_id(&self, value: &str) -> Result<String> {
69        let mut stmt = self.conn.prepare("SELECT id FROM podcast_feeds WHERE id=?1 OR json_extract(json,'$.title')=?1 ORDER BY id=?1 DESC,id")?;
70        let ids = stmt
71            .query_map([value], |r| r.get::<_, String>(0))?
72            .collect::<rusqlite::Result<Vec<_>>>()?;
73        match ids.as_slice() {
74            [id] => Ok(id.clone()),
75            [id, ..] if id == value => Ok(id.clone()),
76            [] => Err(error(crate::t!("podcast_not_found"))),
77            _ => Err(error(&format!(
78                "{}: {}",
79                crate::t!("podcast_ambiguous"),
80                ids.join(", ")
81            ))),
82        }
83    }
84    pub fn podcast_episode_id(&self, value: &str) -> Result<String> {
85        let exists: bool = self.conn.query_row(
86            "SELECT EXISTS(SELECT 1 FROM podcast_episodes WHERE id=?1)",
87            [value],
88            |r| r.get(0),
89        )?;
90        if exists {
91            return Ok(value.into());
92        }
93        let (feed, title) = value
94            .split_once("::")
95            .ok_or_else(|| error(crate::t!("podcast_not_found")))?;
96        let feed = self.podcast_feed_id(feed)?;
97        let mut stmt = self.conn.prepare("SELECT id FROM podcast_episodes WHERE feed_id=?1 AND json_extract(json,'$.title')=?2 ORDER BY id")?;
98        let ids = stmt
99            .query_map(params![feed, title], |r| r.get::<_, String>(0))?
100            .collect::<rusqlite::Result<Vec<_>>>()?;
101        match ids.as_slice() {
102            [id] => Ok(id.clone()),
103            [] => Err(error(crate::t!("podcast_not_found"))),
104            _ => Err(error(&format!(
105                "{}: {}",
106                crate::t!("podcast_ambiguous"),
107                ids.join(", ")
108            ))),
109        }
110    }
111    pub fn podcast_feed(&self, id: &str) -> Result<Feed> {
112        let id = self.podcast_feed_id(id)?;
113        let json: Option<String> = self
114            .conn
115            .query_row("SELECT json FROM podcast_feeds WHERE id=?1", [id], |r| {
116                r.get(0)
117            })
118            .optional()?;
119        Ok(serde_json::from_str(
120            &json.ok_or_else(|| error(crate::t!("podcast_not_found")))?,
121        )?)
122    }
123    pub fn podcast_by_url(&self, url: &str) -> Result<Option<Feed>> {
124        let id: Option<String> = self.conn.query_row("SELECT id FROM podcast_feeds WHERE url=?1 UNION SELECT feed_id FROM podcast_aliases WHERE url=?1 LIMIT 1",[url],|r|r.get(0)).optional()?;
125        id.map(|id| self.podcast_feed(&id)).transpose()
126    }
127    pub fn podcast_subscribe(&self, preview: &Preview, requested: &str) -> Result<Feed> {
128        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
129        let mut feed = self
130            .podcast_by_url(requested)?
131            .or(self.podcast_by_url(&preview.feed.url)?)
132            .unwrap_or_else(|| Feed {
133                id: uuid::Uuid::new_v4().to_string(),
134                ..Default::default()
135            });
136        let id = feed.id.clone();
137        let revision = feed.revision.saturating_add(1);
138        feed = preview.feed.clone();
139        feed.revision = revision;
140        feed.id = id;
141        feed.subscribed = true;
142        self.podcast_write(&feed)?;
143        for url in [requested, &feed.url] {
144            self.conn.execute(
145                "INSERT INTO podcast_aliases VALUES (?1,?2) ON CONFLICT(url) DO NOTHING",
146                params![url, feed.id],
147            )?;
148        }
149        self.podcast_merge(&feed.id, &preview.episodes, false)?;
150        tx.commit()?;
151        Ok(feed)
152    }
153    pub(crate) fn podcast_write(&self, feed: &Feed) -> Result<()> {
154        self.conn.execute("INSERT INTO podcast_feeds VALUES (?1,?2,?3,?4) ON CONFLICT(id) DO UPDATE SET url=excluded.url,subscribed=excluded.subscribed,json=excluded.json",params![feed.id,feed.url,feed.subscribed,serde_json::to_string(feed)?])?;
155        Ok(())
156    }
157    pub(crate) fn podcast_refresh_error(&self, f: &Feed) -> Result<()> {
158        self.conn.execute("UPDATE podcast_feeds SET json=?2 WHERE id=?1 AND subscribed=1 AND json_extract(json,'$.revision')=?3",params![f.id,serde_json::to_string(f)?,f.revision])?;
159        Ok(())
160    }
161    pub fn podcast_refresh_merge(&self, old: &Feed, preview: Option<Preview>) -> Result<()> {
162        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
163        let current = self.podcast_feed(&old.id)?;
164        if !current.subscribed || current.revision != old.revision {
165            return Ok(());
166        }
167        let mut feed = preview.as_ref().map(|p| p.feed.clone()).unwrap_or(current);
168        feed.id = old.id.clone();
169        feed.subscribed = true;
170        feed.error = None;
171        feed.failures = 0;
172        feed.revision = old.revision.saturating_add(1);
173        feed.refreshed_at = Some(chrono::Utc::now().to_rfc3339());
174        if self
175            .podcast_by_url(&feed.url)?
176            .is_some_and(|owner| owner.id != feed.id)
177        {
178            return Err(error(crate::t!("podcast_url_conflict")));
179        }
180        self.podcast_write(&feed)?;
181        self.conn.execute(
182            "INSERT OR IGNORE INTO podcast_aliases VALUES (?1,?2)",
183            params![feed.url, feed.id],
184        )?;
185        if let Some(p) = preview {
186            self.podcast_merge(&feed.id, &p.episodes, true)?;
187        }
188        tx.commit()?;
189        Ok(())
190    }
191    fn podcast_merge(&self, feed_id: &str, episodes: &[Episode], baseline: bool) -> Result<()> {
192        let settings = self.podcast_settings(feed_id)?;
193        for (order, item) in episodes.iter().enumerate() {
194            let identity = item
195                .guid
196                .as_ref()
197                .map(|g| format!("guid:{g}"))
198                .unwrap_or_else(|| format!("url:{}", item.url));
199            let old: Option<String> = self
200                .conn
201                .query_row(
202                    "SELECT json FROM podcast_episodes WHERE feed_id=?1 AND identity=?2",
203                    params![feed_id, identity],
204                    |r| r.get(0),
205                )
206                .optional()?;
207            let mut e = item.clone();
208            e.source_order = e.source_order.or(Some(order as u64));
209            e.feed_id = feed_id.into();
210            let new_episode = old.is_none();
211            e.id = if let Some(old) = old {
212                let old: Episode = serde_json::from_str(&old)?;
213                if (old.bytes.is_some() && e.bytes.is_some() && old.bytes != e.bytes)
214                    || (old.duration_ms.is_some()
215                        && e.duration_ms.is_some()
216                        && old.duration_ms != e.duration_ms)
217                {
218                    self.conn.execute(
219                        "UPDATE podcast_progress SET completed=0 WHERE episode_id=?1",
220                        [&old.id],
221                    )?;
222                }
223                old.id
224            } else {
225                uuid::Uuid::new_v4().to_string()
226            };
227            self.conn.execute("INSERT INTO podcast_episodes VALUES (?1,?2,?3,?4) ON CONFLICT(feed_id,identity) DO UPDATE SET json=excluded.json",params![e.id,feed_id,identity,serde_json::to_string(&e)?])?;
228            if baseline && new_episode {
229                if settings.auto_download && !e.completed {
230                    self.podcast_save_download(&Download {
231                        episode_id: e.id.clone(),
232                        state: "queued".into(),
233                        enqueued_at: Some(chrono::Utc::now().to_rfc3339()),
234                        ..Default::default()
235                    })?;
236                }
237                self.conn
238                    .execute("INSERT OR IGNORE INTO podcast_inbox VALUES (?1)", [&e.id])?;
239            }
240        }
241        Ok(())
242    }
243    pub fn podcast_unsubscribe(&self, id: &str) -> Result<()> {
244        let mut f = self.podcast_feed(id)?;
245        f.subscribed = false;
246        f.revision = f.revision.saturating_add(1);
247        self.podcast_write(&f)
248    }
249    pub fn podcast_episode(&self, id: &str) -> Result<Episode> {
250        let resolved = self.podcast_episode_id(id)?;
251        let id = resolved.as_str();
252        let json: Option<String> = self
253            .conn
254            .query_row("SELECT json FROM podcast_episodes WHERE id=?1", [id], |r| {
255                r.get(0)
256            })
257            .optional()?;
258        let mut e: Episode =
259            serde_json::from_str(&json.ok_or_else(|| error(crate::t!("podcast_not_found")))?)?;
260        let p=self.conn.query_row("SELECT position_ms,duration_ms,completed FROM podcast_progress WHERE episode_id=?1",[id],|r|Ok((r.get(0)?,r.get(1)?,r.get(2)?))).optional()?;
261        if let Some((pos, dur, done)) = p {
262            e.position_ms = pos;
263            e.observed_duration_ms = dur;
264            e.completed = done;
265        }
266        Ok(e)
267    }
268    pub fn podcast_episodes(&self, id: &str) -> Result<Vec<Episode>> {
269        let feed = self.podcast_feed(id)?;
270        self.podcast_episode_rows(&feed.id, "", -1, 0)
271    }
272    pub(crate) fn podcast_episode_position(&self, feed: &str, id: &str) -> Result<Option<usize>> {
273        Ok(self
274            .conn
275            .query_row(
276                "SELECT position FROM (
277                SELECT id,row_number() OVER (
278                    ORDER BY json_extract(json,'$.published') DESC,COALESCE(json_extract(json,'$.source_order'),rowid),id
279                )-1 AS position FROM podcast_episodes WHERE feed_id=?1
280             ) WHERE id=?2",
281                params![feed, id],
282                |r| r.get(0),
283            )
284            .optional()?)
285    }
286    pub fn podcast_episodes_page(
287        &self,
288        id: &str,
289        query: &str,
290        page: &crate::cli::Page,
291    ) -> Result<(Vec<Episode>, usize)> {
292        let feed = self.podcast_feed(id)?;
293        let id = feed.id.as_str();
294        let query = query.to_lowercase();
295        self.read_snapshot(|store| {
296            store.podcast_feed(id)?;
297            let total = store.conn.query_row(
298                "SELECT count(*) FROM podcast_episodes WHERE feed_id=?1
299                 AND (?2='' OR instr(podcast_lower(json_extract(json,'$.title')),?2)>0)",
300                params![id, query],
301                |r| r.get(0),
302            )?;
303            let items = store.podcast_episode_rows(
304                id,
305                &query,
306                if page.all { -1 } else { i64::from(page.limit) },
307                page.offset.min(i64::MAX as usize) as i64,
308            )?;
309            Ok((items, total))
310        })
311    }
312    fn podcast_episode_rows(
313        &self,
314        id: &str,
315        query: &str,
316        limit: i64,
317        offset: i64,
318    ) -> Result<Vec<Episode>> {
319        let mut stmt = self.conn.prepare(
320            "SELECT e.json,p.position_ms,p.duration_ms,p.completed FROM podcast_episodes e
321             LEFT JOIN podcast_progress p ON p.episode_id=e.id WHERE e.feed_id=?1
322             AND (?2='' OR instr(podcast_lower(json_extract(e.json,'$.title')),?2)>0)
323             ORDER BY json_extract(e.json,'$.published') DESC,COALESCE(json_extract(e.json,'$.source_order'),e.rowid),e.id LIMIT ?3 OFFSET ?4",
324        )?;
325        let rows = stmt.query_map(params![id, query, limit, offset], |r| {
326            Ok((
327                r.get::<_, String>(0)?,
328                r.get::<_, Option<u64>>(1)?,
329                r.get::<_, Option<u64>>(2)?,
330                r.get::<_, Option<bool>>(3)?,
331            ))
332        })?;
333        rows.map(|row| {
334            let (json, position, duration, completed) = row?;
335            let mut episode: Episode = serde_json::from_str(&json)?;
336            if let Some(position) = position {
337                episode.position_ms = position;
338                episode.observed_duration_ms = duration;
339                episode.completed = completed.unwrap_or(false);
340            }
341            Ok(episode)
342        })
343        .collect()
344    }
345    /// Save progress only while the supplied media snapshot is still current.
346    pub fn podcast_progress(
347        &self,
348        episode: &Episode,
349        pos: u64,
350        duration: Option<u64>,
351        completed: bool,
352    ) -> Result<()> {
353        self.podcast_save_progress(
354            &episode.id,
355            &EpisodeProgress {
356                media: episode.media(),
357                position_ms: pos,
358                observed_duration_ms: duration,
359                completed,
360            },
361        )
362    }
363    pub(crate) fn podcast_save_progress(&self, id: &str, progress: &EpisodeProgress) -> Result<()> {
364        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
365        Self::write_podcast_progress(&tx, id, progress)?;
366        tx.commit()?;
367        Ok(())
368    }
369    pub(crate) fn write_podcast_progress(
370        tx: &rusqlite::Transaction<'_>,
371        id: &str,
372        progress: &EpisodeProgress,
373    ) -> Result<()> {
374        let json: Option<String> = tx
375            .query_row("SELECT json FROM podcast_episodes WHERE id=?1", [id], |r| {
376                r.get(0)
377            })
378            .optional()?;
379        let Some(json) = json else { return Ok(()) };
380        let episode: Episode = serde_json::from_str(&json)?;
381        // The write transaction also excludes refreshes between this check and
382        // the UPSERT. An old media snapshot must not undo a refresh's reset.
383        if episode.media() != progress.media {
384            return Ok(());
385        }
386        tx.execute("INSERT INTO podcast_progress VALUES (?1,?2,?3,?4,?5) ON CONFLICT(episode_id) DO UPDATE SET position_ms=excluded.position_ms,duration_ms=COALESCE(excluded.duration_ms,duration_ms),completed=excluded.completed,updated_at=excluded.updated_at",params![id,progress.position_ms.min(i64::MAX as u64),progress.observed_duration_ms.map(|d|d.min(i64::MAX as u64)),progress.completed,chrono::Utc::now().to_rfc3339()])?;
387        Ok(())
388    }
389    pub fn podcast_played(&self, id: &str) -> Result<()> {
390        self.conn.execute(
391            "INSERT INTO podcast_history(episode_id,played_at) VALUES (?1,?2)",
392            params![id, chrono::Utc::now().to_rfc3339()],
393        )?;
394        self.set_meta("pref_last_episode", id)?;
395        self.set_meta("pref_last_media", "episode")
396    }
397    pub fn podcast_download(&self, id: &str) -> Result<Option<Download>> {
398        let json: Option<String> = self
399            .conn
400            .query_row(
401                "SELECT json FROM podcast_downloads WHERE episode_id=?1",
402                [id],
403                |r| r.get(0),
404            )
405            .optional()?;
406        json.map(|j| Ok(serde_json::from_str(&j)?)).transpose()
407    }
408    pub(crate) fn podcast_claim_download(&self, expected: &Download) -> Result<Option<Download>> {
409        let json: Option<String> = self
410            .conn
411            .query_row(
412                "UPDATE podcast_downloads
413                 SET json=json_set(json,'$.state','downloading','$.error',NULL,'$.generation',?4)
414                 WHERE episode_id=?1 AND json_extract(json,'$.state')=?2
415                 AND json_extract(json,'$.state') IN ('queued','downloading')
416                 AND COALESCE(json_extract(json,'$.generation'),0)=?3
417                 RETURNING json",
418                params![
419                    expected.episode_id,
420                    expected.state,
421                    expected.generation,
422                    expected.generation.saturating_add(1)
423                ],
424                |r| r.get(0),
425            )
426            .optional()?;
427        json.map(|j| Ok(serde_json::from_str(&j)?)).transpose()
428    }
429    pub(crate) fn podcast_download_transaction<T>(
430        &self,
431        id: &str,
432        update: impl FnOnce(Option<Download>) -> Result<T>,
433    ) -> Result<T> {
434        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
435        let value = update(self.podcast_download(id)?)?;
436        tx.commit()?;
437        Ok(value)
438    }
439    pub(crate) fn podcast_download_update(&self, d: &Download) -> Result<()> {
440        let changed=self.conn.execute("UPDATE podcast_downloads SET json=?2 WHERE episode_id=?1 AND COALESCE(json_extract(json,'$.generation'),0)=?3",params![d.episode_id,serde_json::to_string(d)?,d.generation])?;
441        if changed == 0 {
442            return Err(DomainError::new(ErrorCode::Cancelled).into());
443        }
444        Ok(())
445    }
446    pub fn podcast_save_download(&self, d: &Download) -> Result<()> {
447        self.conn.execute("INSERT INTO podcast_downloads VALUES (?1,?2) ON CONFLICT(episode_id) DO UPDATE SET json=excluded.json",params![d.episode_id,serde_json::to_string(d)?])?;
448        Ok(())
449    }
450    pub fn podcast_downloads(&self) -> Result<Vec<Download>> {
451        let mut stmt = self
452            .conn
453            .prepare("SELECT json FROM podcast_downloads ORDER BY COALESCE(json_extract(json,'$.enqueued_at'),''),rowid")?;
454        stmt.query_map([], |r| r.get::<_, String>(0))?
455            .map(|j| Ok(serde_json::from_str(&j?)?))
456            .collect()
457    }
458    pub fn podcast_queue(&self) -> Result<Vec<Episode>> {
459        let mut stmt = self
460            .conn
461            .prepare("SELECT episode_id FROM podcast_queue ORDER BY position,rowid")?;
462        stmt.query_map([], |r| r.get::<_, String>(0))?
463            .map(|id| self.podcast_episode(&id?))
464            .collect()
465    }
466    pub fn podcast_queue_set(&self, ids: &[String]) -> Result<()> {
467        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
468        self.podcast_queue_write(ids)?;
469        tx.commit()?;
470        Ok(())
471    }
472    pub fn podcast_queue_update(&self, action: QueueAction) -> Result<Vec<Episode>> {
473        self.podcast_queue_update_revision(action)
474            .map(|(items, _)| items)
475    }
476    pub(crate) fn podcast_queue_revision(&self) -> Result<u64> {
477        Ok(self
478            .meta("podcast_queue_revision")?
479            .and_then(|v| v.parse().ok())
480            .unwrap_or(0))
481    }
482    pub(crate) fn podcast_queue_snapshot(&self) -> Result<(Vec<Episode>, u64)> {
483        self.read_snapshot(|store| Ok((store.podcast_queue()?, store.podcast_queue_revision()?)))
484    }
485    pub(crate) fn podcast_queue_update_revision(
486        &self,
487        action: QueueAction,
488    ) -> Result<(Vec<Episode>, u64)> {
489        if matches!(action, QueueAction::List) {
490            return self.podcast_queue_snapshot();
491        }
492        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
493        let mut ids = self
494            .conn
495            .prepare("SELECT episode_id FROM podcast_queue ORDER BY position,rowid")?
496            .query_map([], |r| r.get::<_, String>(0))?
497            .collect::<rusqlite::Result<Vec<_>>>()?;
498        match action {
499            QueueAction::Add { id } => {
500                self.podcast_episode(&id)?;
501                if !ids.contains(&id) {
502                    ids.push(id);
503                }
504            }
505            QueueAction::Next { id, after } => {
506                self.podcast_episode(&id)?;
507                if after.as_ref() == Some(&id) {
508                    return Err(error(crate::t!("podcast_invalid_time")));
509                }
510                ids.retain(|v| v != &id);
511                let position = after
512                    .and_then(|after| ids.iter().position(|v| v == &after))
513                    .map_or(0, |p| p + 1);
514                ids.insert(position, id);
515            }
516            QueueAction::Remove { id } => ids.retain(|v| v != &id),
517            QueueAction::Clear => ids.clear(),
518            QueueAction::Move { id, position } => {
519                if position == 0 || position > ids.len() || !ids.contains(&id) {
520                    return Err(error(crate::t!("podcast_invalid_time")));
521                }
522                ids.retain(|v| v != &id);
523                ids.insert(position - 1, id);
524            }
525            QueueAction::Play { .. } | QueueAction::Start { .. } => {
526                return Err(error(crate::t!("podcast_playback_only")));
527            }
528            QueueAction::List => unreachable!(),
529        }
530        self.podcast_queue_write(&ids)?;
531        let items = self.podcast_queue()?;
532        let revision = self.podcast_queue_revision()?;
533        tx.commit()?;
534        Ok((items, revision))
535    }
536    pub(crate) fn podcast_queue_complete(
537        &self,
538        id: &str,
539    ) -> Result<(Vec<Episode>, u64, Vec<String>)> {
540        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
541        let before = self.podcast_queue()?;
542        let earlier = before
543            .iter()
544            .take_while(|e| e.id != id)
545            .map(|e| e.id.clone())
546            .collect();
547        let ids = before
548            .into_iter()
549            .filter(|e| e.id != id)
550            .map(|e| e.id)
551            .collect::<Vec<_>>();
552        self.podcast_queue_write(&ids)?;
553        let items = self.podcast_queue()?;
554        let revision = self.podcast_queue_revision()?;
555        tx.commit()?;
556        Ok((items, revision, earlier))
557    }
558    fn podcast_queue_write(&self, ids: &[String]) -> Result<()> {
559        self.conn.execute("DELETE FROM podcast_queue", [])?;
560        for (i, id) in ids.iter().enumerate() {
561            self.conn.execute(
562                "INSERT OR IGNORE INTO podcast_queue VALUES (?1,?2)",
563                params![id, i],
564            )?;
565        }
566        self.conn.execute(
567            "INSERT INTO meta(key,value) VALUES ('podcast_queue_revision','1')
568            ON CONFLICT(key) DO UPDATE SET value=CAST(value AS INTEGER)+1",
569            [],
570        )?;
571        Ok(())
572    }
573}
574
575#[cfg(test)]
576mod tests {
577    use super::*;
578    use std::sync::{Arc, Barrier, atomic::AtomicBool};
579
580    fn queue_fixture() -> (tempfile::TempDir, crate::config::Config, Vec<Episode>) {
581        let dir = tempfile::tempdir().unwrap();
582        let config = crate::test_support::config(dir.path());
583        let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
584        let preview = Preview {
585            feed: Feed {
586                url: "https://example.test/rss".into(),
587                ..Default::default()
588            },
589            episodes: (0..2)
590                .map(|i| Episode {
591                    guid: Some(i.to_string()),
592                    url: format!("https://example.test/{i}.mp3"),
593                    ..Default::default()
594                })
595                .collect(),
596        };
597        let feed = store
598            .podcast_subscribe(&preview, &preview.feed.url)
599            .unwrap();
600        let episodes = store.podcast_episodes(&feed.id).unwrap();
601        (dir, config, episodes)
602    }
603
604    #[test]
605    fn cli_marks_cannot_overwrite_progress_after_a_concurrent_media_refresh() {
606        use rusqlite::hooks::{AuthAction, AuthContext, Authorization, TransactionOperation};
607        use std::{sync::mpsc, time::Duration};
608
609        for state in ["played", "unplayed"] {
610            for changed in ["duration", "bytes", "url", "title", "unchanged"] {
611                let dir = tempfile::tempdir().unwrap();
612                let config = crate::test_support::config(dir.path());
613                let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
614                let mut preview = Preview {
615                    feed: Feed {
616                        url: "https://example.test/rss".into(),
617                        ..Default::default()
618                    },
619                    episodes: vec![Episode {
620                        guid: Some("episode".into()),
621                        url: "https://example.test/audio".into(),
622                        duration_ms: Some(20000),
623                        bytes: Some(100),
624                        ..Default::default()
625                    }],
626                };
627                let feed = store
628                    .podcast_subscribe(&preview, &preview.feed.url)
629                    .unwrap();
630                let episode = store.podcast_episodes(&feed.id).unwrap().remove(0);
631                store
632                    .podcast_progress(&episode, 15000, Some(20000), true)
633                    .unwrap();
634
635                let (ready, wait) = mpsc::channel();
636                let (release, go) = mpsc::channel();
637                let id = episode.id.clone();
638                let worker = std::thread::spawn(move || {
639                    let mut store = Store::open(&config.data_dir.join("radio.db")).unwrap();
640                    let mut synchronized = false;
641                    // Mark has read its media snapshot before requesting the
642                    // write transaction. The INSERT hook catches the old path
643                    // which bypassed that transaction and its media guard.
644                    store
645                        .conn
646                        .authorizer(Some(move |context: AuthContext<'_>| {
647                            if !synchronized
648                                && matches!(
649                                    context.action,
650                                    AuthAction::Transaction {
651                                        operation: TransactionOperation::Begin
652                                    } | AuthAction::Insert {
653                                        table_name: "podcast_progress"
654                                    }
655                                )
656                            {
657                                synchronized = true;
658                                ready.send(()).unwrap();
659                                go.recv_timeout(Duration::from_secs(3)).unwrap();
660                            }
661                            Authorization::Allow
662                        }))
663                        .unwrap();
664                    manage(
665                        &config,
666                        &store,
667                        Action::Mark {
668                            id,
669                            state: state.into(),
670                        },
671                        &Arc::new(AtomicBool::new(false)),
672                    )
673                    .unwrap()
674                });
675                wait.recv_timeout(Duration::from_secs(3)).unwrap();
676                match changed {
677                    "duration" => preview.episodes[0].duration_ms = Some(40000),
678                    "bytes" => preview.episodes[0].bytes = Some(200),
679                    "url" => preview.episodes[0].url = "https://example.test/new-audio".into(),
680                    "title" => preview.episodes[0].title = "Updated title".into(),
681                    "unchanged" => {}
682                    _ => unreachable!(),
683                }
684                store.podcast_refresh_merge(&feed, Some(preview)).unwrap();
685                let mut expected = store.podcast_episode(&episode.id).unwrap();
686                if matches!(changed, "title" | "unchanged") {
687                    expected.completed = state == "played";
688                    expected.position_ms = if expected.completed { 20000 } else { 0 };
689                }
690                release.send(()).unwrap();
691                let result = worker.join().unwrap();
692                assert_eq!(
693                    store.podcast_episode(&episode.id).unwrap(),
694                    expected,
695                    "{state}: {changed}"
696                );
697                assert_eq!(result, serde_json::to_value(&expected).unwrap());
698            }
699        }
700    }
701
702    #[test]
703    fn download_controls_read_the_generation_after_a_concurrent_claim() {
704        use rusqlite::hooks::{AuthAction, AuthContext, Authorization, TransactionOperation};
705        use std::sync::mpsc;
706        for control in ["pause", "cancel", "enqueue"] {
707            let (_dir, config, episodes) = queue_fixture();
708            let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
709            let snapshot = Download {
710                episode_id: episodes[0].id.clone(),
711                state: "queued".into(),
712                generation: 7,
713                ..Default::default()
714            };
715            store.podcast_save_download(&snapshot).unwrap();
716            let (ready, wait) = mpsc::channel();
717            let (release, go) = mpsc::channel();
718            let id = snapshot.episode_id.clone();
719            let config = config.clone();
720            let worker = std::thread::spawn(move || {
721                let mut store = Store::open(&config.data_dir.join("radio.db")).unwrap();
722                let mut synchronized = false;
723                // Pause immediately before acquiring the write lock. Without
724                // a transaction this hook runs at INSERT, after the stale read.
725                store
726                    .conn
727                    .authorizer(Some(move |context: AuthContext<'_>| {
728                        if !synchronized
729                            && matches!(
730                                context.action,
731                                AuthAction::Transaction {
732                                    operation: TransactionOperation::Begin
733                                } | AuthAction::Insert {
734                                    table_name: "podcast_downloads"
735                                }
736                            )
737                        {
738                            synchronized = true;
739                            ready.send(()).unwrap();
740                            go.recv_timeout(std::time::Duration::from_secs(3)).unwrap();
741                        }
742                        Authorization::Allow
743                    }))
744                    .unwrap();
745                if control == "enqueue" {
746                    Runtime::enqueue(&store, &id).unwrap();
747                } else {
748                    manage(
749                        &config,
750                        &store,
751                        Action::Downloads {
752                            action: if control == "pause" {
753                                DownloadAction::Pause { id: id.clone() }
754                            } else {
755                                DownloadAction::Cancel { id: id.clone() }
756                            },
757                        },
758                        &Arc::new(AtomicBool::new(false)),
759                    )
760                    .unwrap();
761                }
762            });
763            wait.recv_timeout(std::time::Duration::from_secs(3))
764                .unwrap();
765            let mut claimed = store.podcast_claim_download(&snapshot).unwrap().unwrap();
766            claimed.bytes = 123;
767            store.podcast_download_update(&claimed).unwrap();
768            release.send(()).unwrap();
769            worker.join().unwrap();
770            let current = store
771                .podcast_download(&snapshot.episode_id)
772                .unwrap()
773                .unwrap();
774            assert_eq!(current.generation, claimed.generation + 1, "{control}");
775            assert_eq!(current.bytes, 123);
776            assert_eq!(
777                current.state,
778                match control {
779                    "pause" => "paused",
780                    "cancel" => "cancelled",
781                    _ => "queued",
782                }
783            );
784            assert!(store.podcast_download_update(&claimed).is_err());
785        }
786    }
787
788    #[test]
789    fn concurrent_queue_additions_preserve_both_updates() {
790        use rusqlite::hooks::{AuthAction, AuthContext, Authorization, TransactionOperation};
791        let (_dir, config, episodes) = queue_fixture();
792        let barrier = Arc::new(Barrier::new(2));
793        let workers: Vec<_> = episodes
794            .iter()
795            .map(|e| {
796                let config = config.clone();
797                let id = e.id.clone();
798                let barrier = barrier.clone();
799                std::thread::spawn(move || {
800                    let mut store = Store::open(&config.data_dir.join("radio.db")).unwrap();
801                    let mut synchronized = false;
802                    // Align the first write transactions. Reading before BEGIN
803                    // makes both workers overwrite the same old queue snapshot.
804                    store
805                        .conn
806                        .authorizer(Some(move |context: AuthContext<'_>| {
807                            if !synchronized
808                                && matches!(
809                                    context.action,
810                                    AuthAction::Transaction {
811                                        operation: TransactionOperation::Begin
812                                    }
813                                )
814                            {
815                                synchronized = true;
816                                barrier.wait();
817                            }
818                            Authorization::Allow
819                        }))
820                        .unwrap();
821                    manage(
822                        &config,
823                        &store,
824                        Action::Queue {
825                            action: QueueAction::Add { id },
826                        },
827                        &Arc::new(AtomicBool::new(false)),
828                    )
829                    .unwrap();
830                })
831            })
832            .collect();
833        for worker in workers {
834            worker.join().unwrap();
835        }
836        let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
837        let mut actual: Vec<_> = store
838            .podcast_queue()
839            .unwrap()
840            .into_iter()
841            .map(|e| e.id)
842            .collect();
843        let mut expected: Vec<_> = episodes.into_iter().map(|e| e.id).collect();
844        actual.sort();
845        expected.sort();
846        assert_eq!(actual, expected);
847    }
848
849    #[test]
850    fn listing_the_queue_is_read_only() {
851        let (_dir, config, episodes) = queue_fixture();
852        let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
853        store.podcast_queue_set(&[episodes[0].id.clone()]).unwrap();
854        store.conn.execute_batch("PRAGMA query_only=ON").unwrap();
855        let value = manage(
856            &config,
857            &store,
858            Action::Queue {
859                action: QueueAction::List,
860            },
861            &Arc::new(AtomicBool::new(false)),
862        )
863        .unwrap();
864        assert_eq!(value[0]["id"], episodes[0].id);
865    }
866
867    #[test]
868    fn queue_edits_preserve_order_and_rollback_invalid_moves() {
869        let (_dir, config, episodes) = queue_fixture();
870        let store = Store::open(&config.data_dir.join("radio.db")).unwrap();
871        for episode in [&episodes[0], &episodes[1], &episodes[0]] {
872            store
873                .podcast_queue_update(QueueAction::Add {
874                    id: episode.id.clone(),
875                })
876                .unwrap();
877        }
878        let reordered = store
879            .podcast_queue_update(QueueAction::Move {
880                id: episodes[0].id.clone(),
881                position: 2,
882            })
883            .unwrap();
884        assert_eq!(
885            reordered.iter().map(|e| &e.id).collect::<Vec<_>>(),
886            vec![&episodes[1].id, &episodes[0].id]
887        );
888        assert!(
889            store
890                .podcast_queue_update(QueueAction::Move {
891                    id: episodes[0].id.clone(),
892                    position: 3,
893                })
894                .is_err()
895        );
896        assert_eq!(store.podcast_queue().unwrap(), reordered);
897        let remaining = store
898            .podcast_queue_update(QueueAction::Remove {
899                id: episodes[1].id.clone(),
900            })
901            .unwrap();
902        assert_eq!(remaining.len(), 1);
903        assert_eq!(remaining[0].id, episodes[0].id);
904        assert!(
905            store
906                .podcast_queue_update(QueueAction::Clear)
907                .unwrap()
908                .is_empty()
909        );
910    }
911}