mythtv to sickrage/sickbeard

As the better half was recording far too many of her lovely soap operas and filling up space on the provider’s DVR I’ve had to come up with a solution.

Having a digital TV tuner card and a Linux computer that’s always on I configured MythTV to record various things, this dumps them to disk and I then use some magic scripts to process them for sickrage to then manage. Playback is done via Plex on the smart TV.

This is the key user job in MythTV which allows me to identify the episode that’s been recorded. This gives me the program name (%TITLE%) and the start time in ISO format.

ln "%DIR%/%FILE%" "%DIR%/%TITLE% %STARTTIMEISO%.mpg"

I used to have a Perl script which used tvmaze to figure out the season and episode names so they could be moved to a location that plex could then use with suitable naming but it’s become unreliable, the alternative was to integrate to the same API which sickbeard/rage uses which seems redundant.

Time for some investigation of the database, for me this is

/etc/sickrage-docker/sickbeard.sb

# sqlite3 /etc/sickrage-docker/sickbeard.db

First let’s see what tables we have

sqlite> .tables
blacklist        imdb_info        scene_numbering  whitelist      
db_version       indexer_mapping  tv_episodes      xem_refresh    
history          info             tv_shows

From this tv_shows and tv_episodes stand out as potentially useful, let’s see what info they can hold.

sqlite> .schema tv_shows
CREATE TABLE tv_shows(show_id INTEGER PRIMARY KEY, indexer_id NUMERIC, indexer NUMERIC, show_name TEXT, location TEXT, network TEXT, genre TEXT, classification TEXT, runtime NUMERIC, quality NUMERIC, airs TEXT, status TEXT, flatten_folders NUMERIC, paused NUMERIC, startyear NUMERIC, air_by_date NUMERIC, lang TEXT, subtitles NUMERIC, notify_list TEXT, imdb_id TEXT, last_update_indexer NUMERIC, dvdorder NUMERIC, archive_firstmatch NUMERIC, rls_require_words TEXT, rls_ignore_words TEXT, sports NUMERIC, anime NUMERIC, scene NUMERIC, default_ep_status NUMERIC DEFAULT -1, sub_use_sr_metadata NUMERIC);
CREATE UNIQUE INDEX idx_indexer_id ON tv_shows(indexer_id);

We can see from this that show_name is a good candidate to identify our show. Let’s see what info we’ve got for one.

sqlite> select * from tv_shows where show_name = "Emmerdale";
69|77715|1|Emmerdale|/soaps/Emmerdale|ITV1||Soap||Scripted|30|32759|Daily 19:00|Continuing|0|0|1972|1|en|0||tt0068069|736569|0||||0|0|0|3|0

So we’ve identified where the shows are located, time to see what’s in the tv_episodes table;

sqlite> .schema tv_episodes
CREATE TABLE tv_episodes(episode_id INTEGER PRIMARY KEY, showid NUMERIC, indexerid NUMERIC, indexer TEXT, name TEXT, season NUMERIC, episode NUMERIC, description TEXT, airdate NUMERIC, hasnfo NUMERIC, hastbn NUMERIC, status NUMERIC, location TEXT, file_size NUMERIC, release_name TEXT, subtitles TEXT, subtitles_searchcount NUMERIC, subtitles_lastsearch TIMESTAMP, is_proper NUMERIC, scene_season NUMERIC, scene_episode NUMERIC, absolute_number NUMERIC, scene_absolute_number NUMERIC, version NUMERIC DEFAULT -1, release_group TEXT);
CREATE INDEX idx_showid ON tv_episodes(showid);
CREATE INDEX idx_sta_epi_air ON tv_episodes(status, episode, airdate);
CREATE INDEX idx_sta_epi_sta_air ON tv_episodes(season, episode, status, airdate);
CREATE INDEX idx_status ON tv_episodes(status,season,episode,airdate);
CREATE INDEX idx_tv_episodes_showid_airdate ON tv_episodes(showid, airdate);

We can see here that there’s an index created for showid and airdate, this is interesting as mythtv is providing us a link for the show name and the date of the recording, hopefully, showid will match up with something from the tv_shows table. There’s a show_id field which looks good. Unfortunately tv_shows.showid doesn’t match up with tv_episides.show_id. We already have some episodes saved for Emmerdale so let’s have a little search based on location, which I’m hoping is where it’s saved. For this, I want to see what value is in showid for a sample of results.

sqlite> select showid from tv_episodes where location like "%Emmerdale%" limit 5; 
77715
77715
77715
77715
77715

This is interesting, if we look back at the results from tv_shows this number was returned as indexer_id so we now know that we need indexer_id from tv_shows as below for the show we’re interested in.

sqlite> select indexer_id from tv_shows where show_name="Emmerdale";
77715

So now we need to identify which episode we have. Fortunately the episode names for Emmerdale in sickrage are the date the episode airs and there seems to be a name field in tv_episodes.

sqlite> select airdate,name from tv_episodes where showid=77715 order by airdate desc limit 5;
736573|Fri 1 Sept 2017
736572|Thurs 31 Aug 2017 Pt2
736572|Thurs 31 Aug 2017 Pt1
736571|Wed 30 Aug 2017
736570|Tue 29 Aug 2017

We have the episode names and they do match the date it airs. However, airdate looks odd and not a format I recognize. It seems to be based on the date and not affected by time as both episodes on the 31st have the same airdate.

A quick search in the sickrage code came up with this in

sickbeard/tv.py

airdate_ordinal = self.airdate.toordinal()

Is this the magic which is needed?

# python
Python 2.7.12 (default, Nov 23 2016, 13:47:51) 
[GCC 4.9.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import datetime,sys;
>>> 
>>> print str(datetime.datetime.strptime("2017-08-31", "%Y-%m-%d").toordinal());
736572
>>> 

So what I need is YYYY-MM-DD from the iso timestamp on the files. I’m gonna do this the dirty way as linux’s date command doesn’t seem to have a way to format the time I need.

# fdate="2017-08-31"
# python -c 'import datetime,sys; print str(datetime.datetime.strptime("'$fdate'","%Y-%m-%d").toordinal())'
736572

Putting it all together I now have this shell script which runs 5 minutes past every half hour.

#!/bin/bash

SickrageDB=/etc/sickrage-docker/sickbeard.db

cd /Storage/MythTV

# the grep gets us the show name and date of recording, without time or file extension as these will be identified later
ls -rt1 *\ *.mpg | grep -v Omnibus | grep -Po ^"[[:alnum:] ]+ [0-9]{4}-[0-9]{2}-[0-9]{2}(?=T[0-9]{2}:[0-9]{2}:[0-9]{2}\.mpg)" | sort | uniq | while read captured; do
        date
        echo "Input: ${captured}"

        # we want the show name
        show_name=$( echo ${captured} | grep -oP ^"[[:alnum:] ]+(?= [0-9]{4}-[0-9]{2}-[0-9]{2})")

        # Fix up some names so they match with sickrage
        case $show_name in
                "The Crystal Maze")
                        show_name="The Crystal Maze (2017)"
                        ;;
        esac

        echo "Series: $show_name"

        show_id=$(echo 'select indexer_id from tv_shows where show_name like "'$show_name'";' | sqlite3 $SickrageDB )
        echo "Series ID: $show_id"

        fdate=$(echo ${captured} | grep -Eo "[0-9]{4}-[0-9]{2}-[0-9]{2}"$)
        echo "Date: $fdate"
        orddate=$(python -c 'import datetime,sys; print str(datetime.datetime.strptime("'$fdate'","%Y-%m-%d").toordinal())')
        echo "Ordinal Date: $orddate"

        count=$(echo 'select count(*) from tv_episodes where airdate='$orddate' and showid='$show_id';' | sqlite3 $SickrageDB)
        filecount=$(ls "${captured}"* | wc -l)
        echo "Count: ${count:-1}, Files: $filecount"
        if [ "$filecount" -eq "${count:-0}" ]; then
                echo "$count Files and Episodes found for $show_name on $fdate"
                floop=1
                while [ "$count" -ge "$floop" ]; do
                        echo "Processing file/episode $floop"
                        sqlrow=$(echo ${floop}-1|bc)
                        filename=$(ls -rt "${captured}"* | head -n1)
                        echo "Filename: $filename"
                        echo 'select quote(season),quote(episode),quote(name) from tv_episodes where airdate='$orddate' and showid='$show_id' order by season,episode asc limit '$sqlrow',1;' | sqlite3 -separator " " $SickrageDB | while read ep_season ep_episode ep_name; do
                                echo "Season: $ep_season"
                                echo "Season: $ep_episode"
                                echo "Season: $ep_name"
                                target_filename="${show_name}.S${ep_season}E${ep_episode}.${ep_name//\'/}.PDTV.x264-ATuin.mp4" 
                                echo "Target Filename: $target_filename"
                                echo "Converting \"$filename\" to \"$target_filename\""
                                # ffmpeg loglevel taken from https://superuser.com/questions/326629/how-can-i-make-ffmpeg-be-quieter-less-verbose
                                ffmpeg -loglevel fatal -i "$filename" -threads 2 "$target_filename" \
                                        && mv -v "$target_filename" /Storage/sickrage/to_process/ \
                                        && rm -fv "$filename"
                        done

                        (( floop++ ))
                done

        else
                echo "$count episodes of $show_name found airing on $fdate, skipping"
        fi

        echo
        sleep 1
done