anh.do

I build apps

Copyright © 2006—2021 Anh Do 🇻🇳

Tracking My Podcast Listening Habit

August 5th, 2019 · 1 min read

My effort to back up my social media activities involves Momento, an iOS private journal app that I wrote about in my last post. As the number of my listened podcast episodes over the years crossed 500, I wished to import my listening history into Momento as well. It was a cumbersome and convoluted process involving a lot of tools since there is no official way to do that in Castro, my podcast player of choice.

By emailing Castro’s creator, I learned that the app includes a hidden feature to give access to the underlying SQLite database for technical support:

  1. Open the in-app Settings screen
  2. Navigate to Support and long tap on Email Support
  3. Choose “Email with Database And Logs” and send the email to my address
Export Castro's SQLite database

I then ran the SQL query below to retrieve all played/starred episodes and exported the results as JSON:

SELECT title, name, starred, lastPlayed, 'https://castro.fm/episode/' || e.shortId url
FROM supepisode e, suppodcast p
WHERE e.podcastId = p.id AND (lastplayed > 0 OR starred = 1) AND e.shortId != ''
ORDER BY lastPlayed DESC

The output looked like this:

[
{
"title" : "Cortex Airport",
"starred" : 1,
"name" : "Cortex",
"lastPlayed" : 1564865481.72277,
"url" : "https:\/\/castro.fm\/episode\/MdiHr4"
},
{
"title" : "for 2019\/07\/31 at 10:00 EDT",
"starred" : 0,
"name" : "CBC News: The World This Hour",
"lastPlayed" : 1564585231.21373,
"url" : "https:\/\/castro.fm\/episode\/HJOqlw"
}
]

Notice that I linked each episode to its page on Castro’s server (see example). This came in handy as I later used Ruby to convert the JSON file to… Pocket’s exported bookmark format for use with Pinboard’s Import Bookmarks feature (crazy, I know):

require 'json'
puts <<HERE
<!DOCTYPE html>
<html>
<!--So long and thanks for all the fish-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pocket Export</title>
</head>
<body>
<h1>History</h1>
<ul>
HERE
episodes = JSON.parse(File.read('castro-history.json'))
episodes.each do |episode|
puts <<HERE
<li><a href="#{episode['url']}" time_added="#{episode['lastPlayed'].to_s.split('.')[0]}" tags="castro#{episode['starred'] == 1 ? ',castro-starred' : ''}">#{episode['name']} · #{episode['title']}</a></li>
HERE
end
puts <<HERE
</ul>
</body>
</html>
HERE

I named the resulting file ril_export.html and fetched it to Pinboard:

<!DOCTYPE html>
<html>
<!--So long and thanks for all the fish-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pocket Export</title>
</head>
<body>
<h1>History</h1>
<ul>
<li><a href="https://castro.fm/episode/MdiHr4" time_added="1564865481" tags="castro,castro-starred">Cortex · Cortex Airport</a></li>
<li><a href="https://castro.fm/episode/HJOqlw" time_added="1564585231" tags="castro">CBC News: The World This Hour · for 2019/07/31 at 10:00 EDT</a></li>
</ul>
</body>
</html>
view raw ril_export.html hosted with ❤ by GitHub

Thanks to my archival account, Pinboard quickly imported the list and saved a permanent copy for each page.

Podcast listening history in Pinboard

Finally, I set up a RSS feed to the tag in Momento to show off my listening habit:

Podcast listening history in Momento

It was worth it.