Generate SQL to pull podcast episode names from pocketcasts sqlite db

00_pull-podcast-metadata-from-PocketCasts-DB.md

PocketCasts makes it hard to migrate

Why are you doing this?

😮‍💨 PocketCasts will save "starred" podcasts in your device storage -- but not let you access them. I want more control over my podcasts than PocketCasts gives me, so I'm probably moving away from using this app.

Before I migrate away, I want to create an archive of saved podcast episodes. Unfortunately, unless you have PocketCasts Plus (which has an API), you can't get easy access to your metadata. Also, podcast producers don't really tag their episodes well, either. 🙄

I didn't see any instructions on how to do this, so I created this gist.

Grab your Starred (and downloaded) files

Use the guide here To get a copy of your downloaded (and starred) files from Pocketcasts.

Grab your pocketcasts sqlite db from Android

There are specific instructions here: Exporting the App Database from the app.

Use the included script to generate SQL

Run this script in the folder with your podcast episode dump.

$ sqlite3-generate-select.sh
select podcast_episodes.uuid, podcast_episodes.title, podcast_episodes.cleanTitle, podcast_episodes.published_date, podcast_episodes.podcast_id, podcasts.title
FROM podcast_episodes JOIN podcasts ON podcasts.uuid=podcast_episodes.podcast_id
WHERE podcast_episodes.uuid IN ( . . . );

Run the generated SQL against the pocketcasts Database dump

This is an sqlite database, so you can use the sqlite3 cli or something like DBeaver.

Don't worry! The DB most likely fits in RAM, so this should be instantaneous.

sqlite3-generate-select.sh

#!/usr/bin/env bash

set -e
set -u

cat << EOF
select podcast_episodes.uuid, podcast_episodes.title, podcast_episodes.cleanTitle, podcast_episodes.published_date, podcast_episodes.podcast_id, podcasts.title
FROM podcast_episodes JOIN podcasts ON podcasts.uuid=podcast_episodes.podcast_id
EOF

/usr/bin/echo -n "WHERE podcast_episodes.uuid IN ("

for file in $(find . -type f ); do
  no_path_file=${file##*/}
  no_extension_file=${no_path_file%.*}
  /usr/bin/echo -n "'${no_extension_file}', "
done| sed 's/\, $//'

/usr/bin/echo -n ");"