Recordings Purge Script for MythTV

James Young · February 10, 2014

Edit 25th Feb:  Discovered a bug in the logic, apparently UNION order isn’t guaranteed.  Reworked the script so it does the right thing now.

I’ve now converted my prior HTPC setup over to using an HDHomeRun and MythTV on my Microserver.  The former MediaPortal box now runs exclusively OpenElec XBMC.  Anyway, MythTV has a proper database backend and a set of Python/Perl bindings, which is great for customization.

I have a pretty specific want for my recording schedules, which ARGUS (on MediaPortal) wasn’t able to do, and which MythTV is not quite able to do.  Namely, I want to record a certain number of a specific series, and then only record more of them if they get watched.  The oldest watched episode should be discarded to make room, so you always maintain N episodes that are unwatched, but you don’t record new episodes if you already have N episodes unwatched.

MythTV’s native “Expire old and record new” functionality does not do this (it will keep recording episodes to maintain N new episodes, overwriting older episodes even if unwatched with new ones).  The “Auto-expire” functionality won’t do this (it will only delete episodes if running out of disk space).

Enter the following script.  Yes, the SQL query is freakin’ huge.

#!/usr/bin/python -W ignore::DeprecationWarning
#
# Purges watched episodes from schedules so that new episodes can be
# recorded
#
# Only purges the oldest watched recording, and only if the schedule
# wouldn't record because it's reached the maximum episodes
#
# I'm terrible at Python.

from MythTV import MythDB, Recorded
import sys
import MySQLdb

db = MythDB()

cur = db.cursor()

# Fetch all candidate recording ids
cur.execute("""
SELECT
  candidates.recordid
FROM
  (
    SELECT
      record.recordid,
      record.title,
      record.maxepisodes,
      COUNT(recorded.basename) AS count
    FROM
      record,
      recorded
    WHERE
      record.autoexpire=1
      AND record.inactive=0
      AND record.maxnewest=0
      AND record.recordid = recorded.recordid
      AND NOT recorded.recgroup = 'LiveTV'
      AND NOT recorded.recgroup = 'Deleted'
      AND recorded.preserve = 0
      AND recorded.autoexpire = 1
    GROUP BY
      recorded.recordid
  ) AS candidates
WHERE
  candidates.maxepisodes <= candidates.count
""")

for record in cur.fetchall() :
        cur2 = db.cursor();
        cur2.execute("""
                SELECT
                  e.title,
                  e.subtitle,
                  e.basename,
                  e.starttime,
                  e.deletepriority
                FROM
                (
                  (
                  -- Subselect fully watched episodes
                  SELECT
                    b.title,
                    b.subtitle,
                    b.basename,
                    b.starttime,
                    1 AS deletepriority
                  FROM
                    recorded AS b
                  WHERE
                    b.recordid = ?
                    AND b.preserve=0
                    AND b.autoexpire=1
                    AND b.watched=1
                    AND NOT b.recgroup = 'LiveTV'
                    AND NOT b.recgroup = 'Deleted'
                  -- End of watched episodes
                  ) UNION (
                  -- Subselect bookmarked episodes
                  SELECT
                    c.title,
                    c.subtitle,
                    c.basename,
                    c.starttime,
                    2 AS deletepriority
                  FROM
                    recorded as c,
                    recordedmarkup as d
                  WHERE
                    c.recordid = ?
                    AND c.preserve=0
                    AND c.autoexpire=1
                    AND c.watched=0
                    AND NOT c.recgroup = 'LiveTV'
                    AND NOT c.recgroup = 'Deleted'
                    AND d.chanid = c.chanid
                    AND d.starttime = c.starttime
                    AND d.type = 2
                  -- End of bookmarked episodes
                  )
                ) AS e
                ORDER BY
                  e.deletepriority,
                  e.starttime
                LIMIT 1
        """, (record[0], record[0]))
        for row in cur2.fetchall() :
                print row[0] + " : " + row[1] + " (" + row[2] + ") [" + str(row[3]) + "]"
                recs = list(db.searchRecorded(basename=row[2]))
                if len(recs) == 0:
                        print '  error - could not find episode by basename!'
                        sys.exit(0)
                for rec in recs:
                        rec.delete()

I’ll break down what this does.

  • For each active autoexpirable recording schedule, see if the number of autoexpirable, nonpreserved, nondeleted recordings exceeds or matches the maximum number of allowable recordings.
  • For each of those schedules, find the oldest fully watched or bookmarked, nonpreserved, autoexpirable, nondeleted recording and delete it.

If you put this in crontab to run once a day, you’ll find that each of your schedules will do what I outlined above.

The great thing about this is that it allows you to set up recording schedules for anything (where you don’t care about episode order) that looks vaguely interesting, set an appropriate maximum (5 or so), and it’ll only keep recording them if they’re actually getting watched.

This is the functionality I want for children’s television, and now I’ve got it.

Twitter, Facebook