List the latest played media from AntennaPod exported database.

antennapod-last-played.sh

#!/bin/bash
# ---
# summary: List the latest played media (title and local date) from AntennaPod exported database.
# about:
#   - url: https://antennapod.org/
#     sourceCodeRepository: https://github.com/AntennaPod
#     version: 3.7.0f
# sameAs:
#   - url: https://gist.github.com/monking/259c9194c2d170a1936847d28d64ea34
#   - url: ~/.local/lib/antennapod-last-played--gist--259c9194c2d170a1936847d28d64ea34/antennapod-last-played.sh
# tags: media, activity, database
# version: 0.3.1
# dateModified: 2025-03-26T06:53:39Z
# dateCreated: 2025-03-25T21:37:27Z
# uuid: 979075bc-9a16-48d5-9b37-6f0749cee5ae
# ...
g_verbosity=0;
latestPodcasts()
{
  local db;
  local limit=3;
  local userColumns=();
  local userJoins=();
  local userSort='';
  local showUrl=false;
  local showFeed=false;
  local sqliteArgs=();
  while [[ $# -gt 0 ]]; do
    case $1 in
      --verbose|-v) let g_verbosity++;;
      --limit=*) limit="${1#*=}";;
      --select=*) userColumns+=("${1#*=}");;
      --feed) showFeed=true;;
      --url) showUrl=true;;
      --join=*) userJoins+=("${1#*=}");;
      --sort=*) userSort="${1#*=}";;
      --help|-h) showHelp; exit;;
      -json|-line|-list|-html|-csv|-ascii|-box|-column|-markdown|-quote|-table|-tabs) sqliteArgs+=("$1");;
      -outputs|--outputs|--list-outputs) >&2 echo '  -json, -line, -list, -html, -csv, -ascii, -box, -column, -markdown, -quote, -table, -tabs'; exit;;
      *) if [[ -s "$1" ]]; then db="$1"; else sqliteArgs+=("$1"); fi;;
    esac
    shift;
  done
  if [[ ${#sqliteArgs[@]} -eq 0 ]]; then
    sqliteArgs+=(-line);
  fi
  if [[ $showUrl == true ]]; then
    userColumns+=("$SELECT_COLUMN_ITEM_URL" "$SELECT_COLUMN_ITEM_AUDIO");
  fi
  if [[ $showFeed == true ]]; then
    userColumns+=("$SELECT_COLUMN_FEED_TITLE");
    userJoins+=("$SELECT_JOIN_FEED");
    if [[ $showUrl == true ]]; then
      userColumns+=("$SELECT_COLUMN_FEED_URL");
    fi
  fi
  local query='';
  query+='SELECT';
  if [[ ${#userColumns[@]} -eq 1 && ${#userColumns[0]} = '*' ]]; then
    if [[ $g_verbosity -gt 0 ]]; then
      >&2 cat<<'EOF'

---
status: WISH
summary: List columns available in current tables
uuid: 4574be76-933a-4a1a-aea5-158d9217688a
date: 2025-03-26T05:49:47.936670562Z
subjectOf:
  - date: 2025-03-26T06:01:35Z
    status: WORKAROUND
    text: antennapod-last-played AntennaPodBackup-2025-03-25.db -json -v --select='*' | jq --color-output | less -R
...

EOF
    fi
  else
  query+=' "i"."title",';
  query+=' strftime("%Y-%m-%dT%H:%M:%S';
    query+="$(date '+%z')";
    query+='", "m"."last_played_time"/1000, "unixepoch", "localtime") as "last_played"';
  fi
  if [[ ${#userColumns[@]} -gt 0 ]]; then
    query+=", $(join ', ' "${userColumns[@]}")";
  fi
  query+=" $SELECT_FROM_FEEDMEDIA";
  query+=" $SELECT_JOIN_FEEDITEMS";
  if [[ ${#userJoins[@]} -gt 0 ]]; then
    query+=" ${userJoins[*]}";
  fi
  local orderBy='"m"."last_played_time"';
  local order='DESC';
  if [[ -n $userSort ]]; then
    case $userSort in
      ASC|DESC) order="$userSort";;
      *) orderBy="$userSort";;
    esac
  fi
  query+=' WHERE "m"."last_played_time" > 0';
  query+=" ORDER BY ${orderBy} ${order}";
  query+=" LIMIT 0, ${limit};";
  local dbCommand=(sqlite3 "${sqliteArgs[@]}" "$db" "$query");
  if [[ $g_verbosity -gt 0 ]]; then
    >&2 echo "[antennapod-last-played] \$ ${dbCommand[*]}";
    >&2 echo;
  fi;
  "${dbCommand[@]}";
}

showHelp()
{
  echo "USAGE: antennapod-last-played [--help|--verbose] [--limit=NUMBER] [--feed] [--url] [SQLITE3_OUTPUT_OPTIONS] [--{select,join,sort}=SQL] DATABASE";
  echo "TABLES SQL:";
  echo "  $SELECT_FROM_FEEDMEDIA";
  echo "  $SELECT_JOIN_FEEDITEMS";
  echo;
  echo "EXAMPLE:";
  echo "  antennapod-last-played AntennaPodBackup-2025-03-25.db --limit=1 --select='${SELECT_COLUMN_FEED_TITLE}' --join='${SELECT_JOIN_FEED}' --sort=ASC";
}

join()
{
  local sep="$1";
  shift;
  echo -n "$1";
  shift;
  while [[ $# -gt 0 ]]; do
    echo -n "${sep}${1}";
    shift;
  done
}

SELECT_FROM_FEEDMEDIA='FROM "FeedMedia" AS "m"';
SELECT_JOIN_FEEDITEMS='INNER JOIN "FeedItems" AS "i" ON "m"."feeditem" = "i"."id"';
SELECT_JOIN_FEED='INNER JOIN "Feeds" AS "f" ON "i"."feed" = "f"."id"';
SELECT_COLUMN_FEED_TITLE='"f"."title" as "podcast"';
SELECT_COLUMN_FEED_URL='"f"."download_url" as "feed"';
SELECT_COLUMN_ITEM_AUDIO='"m"."download_url" as "audio"';
SELECT_COLUMN_ITEM_URL='"i"."link" as "url"';

latestPodcasts "$@";