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