list-youtube

   1 #!/usr/bin/env bash
   2
   3 #=======================================================================
   4 # list-youtube
   5 # File ID: c5f8c456-f24a-11e2-a3f1-001f3b596ec9
   6 #
   7 # Sort videos downloaded by youtube-dl / yt-dlp in chronological order
   8 #
   9 # Author: Øyvind A. Holm <sunny@sunbase.org>
  10 # License: GNU General Public License version 2 or later.
  11 #=======================================================================
  12
  13 progname=list-youtube
  14 VERSION=0.6.5
  15
  16 ARGS="$(getopt -o "\
  17 d:\
  18 f\
  19 F:\
  20 h\
  21 o:\
  22 q\
  23 r\
  24 S:\
  25 v\
  26 w:\
  27 " -l "\
  28 dbname:,\
  29 filenames,\
  30 from-file:,\
  31 help,\
  32 order-by:,\
  33 quiet,\
  34 reuse,\
  35 sql:,\
  36 verbose,\
  37 version,\
  38 where:,
  39 " -n "$progname" -- "$@")"
  40 test "$?" = "0" || exit 1
  41 eval set -- "$ARGS"
  42
  43 opt_dbname=''
  44 opt_filenames=0
  45 opt_from_file=''
  46 opt_help=0
  47 opt_orderby=upload_date,file
  48 opt_quiet=0
  49 opt_reuse=0
  50 opt_sql=''
  51 opt_verbose=0
  52 opt_where=''
  53 while :; do
  54     case "$1" in
  55         -d|--dbname) opt_dbname="$2"; shift 2 ;;
  56         -f|--filenames) opt_filenames=1; shift ;;
  57         -F|--from-file) opt_from_file="$2"; shift 2 ;;
  58         -h|--help) opt_help=1; shift ;;
  59         -o|--order-by) opt_orderby="$2"; shift 2 ;;
  60         -q|--quiet) opt_quiet=$(($opt_quiet + 1)); shift ;;
  61         -r|--reuse) opt_reuse=1; shift ;;
  62         -S|--sql) opt_sql="$2"; shift 2 ;;
  63         -v|--verbose) opt_verbose=$(($opt_verbose + 1)); shift ;;
  64         -w|--where) opt_where="$2"; shift 2 ;;
  65         --version) echo $progname $VERSION; exit 0 ;;
  66         --) shift; break ;;
  67         *) echo $progname: Internal error >&2; exit 1 ;;
  68     esac
  69 done
  70 opt_verbose=$(($opt_verbose - $opt_quiet))
  71
  72 if test "$opt_help" = "1"; then
  73     test $opt_verbose -gt 0 && { echo; echo $progname $VERSION; }
  74     cat <<END
  75
  76 Sort videos downloaded by youtube-dl / yt-dlp in chronological order
  77
  78 Usage: $progname [options]
  79
  80 Options:
  81
  82   -d FILE, --dbname FILE
  83     Use FILE as database instead of storing it under
  84     ~/.cache/$progname/[DIR].
  85   -f, --filenames
  86     List filenames instead of the default format with date and title.
  87     Useful for creating playlists.
  88   -F FILE, --from-file FILE
  89     Read filenames from FILE instead of using find(1).
  90   -h, --help
  91     Show this help.
  92   -o COLUMN, --order-by COLUMN
  93     Sort output by COLUMN.
  94   -q, --quiet
  95     Be more quiet. Can be repeated to increase silence.
  96   -r, --reuse
  97     Reuse the database for the current directory, don't scan files and
  98     create a new one.
  99   -S STATEMENT, --sql STATEMENT
 100     Execute SQL statement directly against the sqlite3 database. If
 101     STATEMENT is "-", read commands from stdin or if stdin is empty, use
 102     the sqlite3 shell. Examples:
 103       $progname -S .schema
 104       $progname -S "SELECT view_count AS v, file FROM yt ORDER BY v;"
 105       $progname -S -
 106       (echo .dbinfo; echo "SELECT file FROM yt;") | $progname -r -S -
 107   -v, --verbose
 108     Increase level of verbosity. Can be repeated.
 109   --version
 110     Print version information.
 111   -w CLAUSE, --where CLAUSE
 112     List only entries specified by CLAUSE.
 113
 114 END
 115     exit 0
 116 fi
 117
 118 mkinsert() {
 119     echo "BEGIN;"
 120     sed "s/'/''/g; s/.*/INSERT INTO $1 VALUES('&');/;"
 121     echo "COMMIT;"
 122 }
 123
 124 if test -n "$opt_dbname"; then
 125     db=$opt_dbname
 126 else
 127     odir="$HOME/.cache/$progname$(pwd -P)"
 128     mkdir -p "$odir" || exit 1
 129     db="$odir/$progname.sqlite"
 130 fi
 131 if test "$opt_reuse" != "1" -o ! -e "$db"; then
 132     rm -f "$db" "$db-journal"
 133     cat <<SQL_END | sqlite3 "$db" || exit 1
 134 CREATE TABLE t (file TEXT);
 135 CREATE TABLE yt (
 136   average_rating REAL,
 137   description TEXT,
 138   dir TEXT,
 139   dislike_count INTEGER,
 140   display_id TEXT,
 141   duration INTEGER,
 142   extractor TEXT,
 143   file TEXT,
 144   filesize INTEGER,
 145   format TEXT,
 146   format_id TEXT,
 147   fulltitle TEXT,
 148   height INTEGER,
 149   length TEXT,
 150   like_count INTEGER,
 151   resolution TEXT,
 152   title TEXT,
 153   upload_date TEXT,
 154   uploader TEXT,
 155   uploader_id TEXT,
 156   view_count INTEGER,
 157   webpage_url TEXT,
 158   width INTEGER,
 159   ytid TEXT,
 160   j TEXT
 161 );
 162 SQL_END
 163     if test -n "$opt_from_file"; then
 164         grep '\.info\.json$' "$opt_from_file"
 165     else
 166         find -name '*.info.json'
 167     fi | mkinsert t | sqlite3 "$db"
 168     cat <<SQL_END | sqlite3 "$db"
 169 BEGIN;
 170 ALTER TABLE t ADD j TEXT;
 171 UPDATE t SET j = json(readfile(file));
 172 INSERT INTO yt
 173   SELECT
 174       json_extract(j, '$.average_rating') AS average_rating,
 175       json_extract(j, '$.description') AS description,
 176       NULL AS dir,
 177       json_extract(j, '$.dislike_count') AS dislike_count,
 178       json_extract(j, '$.display_id') AS display_id,
 179       json_extract(j, '$.duration') AS duration,
 180       json_extract(j, '$.extractor') AS extractor,
 181       file,
 182       json_extract(j, '$.filesize') AS filesize,
 183       json_extract(j, '$.format') AS format,
 184       json_extract(j, '$.format_id') AS format_id,
 185       json_extract(j, '$.fulltitle') AS fulltitle,
 186       json_extract(j, '$.height') AS height,
 187       NULL AS length,
 188       json_extract(j, '$.like_count') AS like_count,
 189       json_extract(j, '$.width') || 'x' || json_extract(j, '$.height')
 190         AS resolution,
 191       json_extract(j, '$.title') AS title,
 192       json_extract(j, '$.upload_date') AS upload_date,
 193       json_extract(j, '$.uploader') AS uploader,
 194       json_extract(j, '$.uploader_id') AS uploader_id,
 195       json_extract(j, '$.view_count') AS view_count,
 196       json_extract(j, '$.webpage_url') AS webpage_url,
 197       json_extract(j, '$.width') AS width,
 198       json_extract(j, '$.id') AS ytid,
 199       j
 200     FROM t;
 201 UPDATE yt SET upload_date = substr(upload_date, 1, 4) || '-'
 202   || substr(upload_date, 5, 2) || '-' || substr(upload_date, 7, 2);
 203 UPDATE yt SET length = printf('%02u:%02u:%02u',
 204                               duration / 3600,
 205                               duration / 60 % 60,
 206                               duration % 60);
 207 COMMIT;
 208 SQL_END
 209 fi
 210
 211 if test -n "$opt_sql"; then
 212     if test "$opt_sql" = "-"; then
 213         sqlite3 "$db"
 214     else
 215         sqlite3 "$db" "$opt_sql" | $PAGER
 216     fi
 217     exit
 218 fi
 219
 220 where_str='WHERE ytid IS NOT NULL'
 221 if test -n "$opt_where"; then
 222     where_str="WHERE ($opt_where) AND ytid IS NOT NULL"
 223 fi
 224
 225 if test "$opt_filenames" = "1"; then
 226     sqlite3 "$db" "DROP TABLE IF EXISTS films;"
 227     sqlite3 "$db" "CREATE TABLE films (file TEXT);"
 228     find | filmer | mkinsert films | sqlite3 "$db"
 229     cat <<SQL_END | sqlite3 "$db" | sqlite3 "$db"
 230 SELECT 'BEGIN;';
 231 SELECT printf('SELECT file FROM films WHERE file LIKE ''%%-%s%%'';', ytid)
 232   FROM yt
 233   $where_str
 234   ORDER BY $opt_orderby;
 235 SELECT 'COMMIT;';
 236 SQL_END
 237 else
 238     cat <<SQL_END | sqlite3 -header -column "$db" | sed 's/ *$//g;' | $PAGER
 239 SELECT
 240     upload_date AS ud,
 241     resolution,
 242     length,
 243     uploader,
 244     file
 245   FROM yt
 246   $where_str
 247   ORDER BY $opt_orderby;
 248 SQL_END
 249 fi