Custom Recording

From MythTV Official Wiki
Revision as of 23:47, 15 September 2012 by Llib (talk | contribs) (Add time zone information for 0.26)

Jump to: navigation, search

The basics about custom recording, including a tutorial, can be found at:

http://www.mythtv.org/docs/mythtv-HOWTO-12.html#ss12.5


Preferring HD programs to record

Problem

I want to record HD programs rather than the SD broadcasts that I get on other channels. In this instance I want to record anything with 'Soccer' anywhere in the title if it is a sports event.

Solution

I set up a custom recording rule (Power Search if you are doing this using MythWeb). The search phrase is as follows:

program.title LIKE '%Soccer%'
AND program.category = 'Sports event'
AND
(
    program.hdtv > 0
    OR program.programid NOT IN
    (
        SELECT program2.programid
        FROM program program2
        WHERE program2.hdtv > 0
    )
)

Please note that this rule will override any SD broadcasts of the same program when there is an HD broadcast. This means you could lose a recording entirely if it is broadcast in HD but was overridden by a higher priority recording. I have 3 tuners, so I see this as a very low probability, but if you only have one, it is something to keep in mind.

Recording NFL Sunday Morning and Afternoon with Extra Time

Problem

NFL Football double headers gave me a scheduling headache since I wanted to pad the recordings with an extra 45 minutes in case of overtime or just long games until I discovered the power of Custom Recording.

Solution

Set up your usual program schedule to record NFL Football and tell it to end 45 minutes late. This will take care of all morning and evening games. Take note of the recording priority you choose for this program schedule. Then go to Custom Record and add a new rule with the following criteria:

For release 0.26 and above:

program.title = 'NFL Football'
AND HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) = 14

For releases before 0.26:

program.title = 'NFL Football'
AND HOUR(program.starttime) = 14

And select Record at the bottom. Set this custom recording schedule to start recording 45 minutes late, and end 45 minutes late. Set its priority one level lower than your previously defined program schedule. This allows your regular scheduler to take precedence over the custom recording, but when it conflicts with itself for the afternoon game, the custom recording schedule kicks in and starts as soon as the regular scheduler ends (late).

Please note that this solution assumes 'NFL Football' is the program title your scheduler uses and I am in mountain standard time, so my afternoon games start at 2:00 pm (hour 14 in 24 hour time). This style can be used for any program that needs to be extended and tends to have back-to-back, recurring showings.

Recording Comedy Specials: An Advanced Custom Recording Example

Introduction

After reading the above tutorial and becoming familiar with custom recording, take a look at the following example that illustrates how to use additional tables in your custom rules along with how to use the programgenres table to search for very specific program types to record.

Problem

I wanted a custom recording rule that would record all standup comedy specials. Of course, a basic recording rule won't cut it because each standup special has a different title - "Dave Chappelle: For What It's Worth", "Dennis Miller: The Raw Feed", etc. A custom power search rule is definitely required for this job.

First Attempt

The first thing I noticed is that all standup specials had a category of "Special" in the program table, but the category "Special" is used for various types of programs (my listings come from DataDirect) so category alone wasn't going to do it. Since the bulk of programs I was looking for were being shown on the Comedy Network [Canada], I decided to restrict my search to "Special" programs on the Comedy Network. Here's the first custom record rule I created:

Search Phrase:
channel.callsign LIKE "COMED%" AND program.category = "Special"

Basically, this rule says record all programs with a category of "Special" on the Comedy Network. I use a LIKE clause for the callsign since I have two feeds of the Comedy Network (east and west coast). This rule worked and I used it for quite some time, until one day I found a standup comedy routine being shown on another channel that interested me. Obviously, my power search rule needed to be changed.

The programgenres Table

Users of DataDirect, perhaps others, will have a populated programgenres table. This table contains numerous category names for each program in your program table. Each row for a specific program includes a genre column that has a genre description and a relevance column which ranks the given genre. The most relevant genre for a program (the entry with the lowest number in the relevance column) is copied into the category column for the program in the program table while the others are only available in this table. Though the category column in program gives the most relevant genre for that program, the combination of all genres from the programgenres table gives a much more detailed description of the program. It is the combination of all listed genres that will allow me to seek out the exact types of programming I'm looking to record.

Here's an example of data from the programgenres table. This example shows the listed genres for a comedy special airing on 04 Aug 2006 22:00:

mysql> select * from programgenres where chanid=1285 and starttime='2006-08-04 22:00:00';
+--------+---------------------+-----------+---------+
| chanid | starttime           | relevance | genre   |
+--------+---------------------+-----------+---------+
|   1285 | 2006-08-04 22:00:00 | 0         | Special |
|   1285 | 2006-08-04 22:00:00 | 1         | Comedy  |
|   1285 | 2006-08-04 22:00:00 | 2         | Standup |
+--------+---------------------+-----------+---------+

Notice that the most relevant genre for this program is "Special", therefore MythTV copies this genre into the program category column.

Second Attempt

With this new found knowledge, I no longer need to restrict myself to only looking at the Comedy Network, I can now search for specific genres and expand my search space to all channels. In order to do this, I must include this additional table in my power search rule:

Additional Tables: , programgenres as g1, programgenres as g2
Search Phrase:
(g1.chanid = program.chanid AND g1.starttime = program.starttime)
 AND (g2.chanid = program.chanid AND g2.starttime = program.starttime)
 AND g1.genre = "Comedy" AND g2.genre = "Standup" AND program.category = "Special"

Why do I include the same table twice? Since I want to search for more than one genre, I must include the table once for each genre I'm searching for. If I only wanted to search for one genre then I'd only need to include it once; if I were going to be very picky and search for 6 genres I'd need to include the table six times. When including the same table more than once, you must give each instance a unique alias as I did with g1 and g2.

The last part of the query makes sense where you check for the genres you're interested in, but what is the first part of the query all about? I'm assuming you know a little bit about SQL and so to make a long story short, by tracing through the source code I learned that the power search facilities in MythTV join user specified additional tables using a cartesian product. Without specifying the relationship between the program table and the programgenres table, the resulting query would contain a bunch of rows that aren't really valid. On top of that, you'd also end up recording many shows that you really aren't interested in. Again, I assume you know a little bit about SQL so the meaning of the first part of the query is left as an exercise to the reader.

So what does this rule do? Basically, it finds all programs that have a category of "Special" and genres equal to "Standup" and "Comedy". In other words, find all standup comedy specials. I simply setup this recording rule to "record on any channel at any time" and just like that, I'm now able to record any standup specials from any channel.

A Lack of Data Consistency

So just when I thought I had it all figured out, I missed a standup routine. Why didn't Myth record it? Well, after some investigating I discovered that not every standup special lists "Standup" as a genre for the program. Some programs only list "Special" and "Comedy" while others also include the "Standup" genre. So this isn't really the fault of the rule I created, but rather inconsistency from my listings provider (which, in turn, is probably an issue with the network providing the data to DataDirect and so on...). So this means I need to loosen up my search rule and brings us to my final solution.

Final Answer
Addition Tables: , programgenres as g1
Search Phrase:
(g1.chanid = program.chanid AND g1.starttime = program.starttime)
 AND g1.genre = "Comedy" AND g1.relevance <= 1 AND program.category = "Special"

Basically, I had to settle on this rule, which says to find all comedy specials on any channel. I added the check against the relevance value as a way to tighten up this rule in lieu of being able to search for "Standup" as a genre. By checking the relevance value I was able to filter out shows that didn't fit into what I was looking for. For example, without the relevance check this rule originally matched against a documentary special. The listed genres for that program looked like this:

mysql> select * from programgenres where chanid=2002 and starttime='2006-08-11 20:00:00';
+--------+---------------------+-----------+-------------+
| chanid | starttime           | relevance | genre       |
+--------+---------------------+-----------+-------------+
|   2002 | 2006-08-11 20:00:00 | 0         | Special     |
|   2002 | 2006-08-11 20:00:00 | 1         | Documentary |
|   2002 | 2006-08-11 20:00:00 | 2         | Comedy      |
+--------+---------------------+-----------+-------------+

Judging by the description of the program, it appears to be a documentary about some early 20th century silent film maker. Obviously, not what I'm looking for here. You'll notice that Comedy is listed as a low relevancy for this program. By checking the relevance value, I'm able to filter out programs such as this from being recorded.

I set this rule to record on any channel at any time and now I am able to record all comedy specials from any channel.

Issues

My final solution picks up more than standup specials, but I've decided that's ok. If you wanted to be picky, you could stick with the rule created in attempt two. Using that rule would bring up a different issue. Instead, of picking up more than standup routines, you'd actually miss some and not record everything you'd want. Basically, it's a choice between do I want to ensure I get every standup routine recorded at the cost of recording some other programs that I may or may not like or do I use the stricter rule and miss some programs, but know that everytime this rule records something it is definitely recording a standup routine. Issues such as disk space, etc. will dictate which way to go and in my case I decided to go with the final rule instead of the second.

Recording a Rebroadcast in HD: An Advanced Custom Recording Example

Problem

I have both SD and HD tuners in my myth box. Occasionally, I will record something in SD (e.g. from DirecTV) and later that will be broadcast on an over the air network in HD. MythTV (correctly) identifies the HD rebroadcast as a duplicate and does not record it. However, in this specific case, I want to record the HD rebroadcast.

First Attempt

After studying the structure of the "recordedprogram" (existing recordings) and "program" (listings) tables, I devised the following SQL query which will return the title, subtitle, channel, and start time of any HD broadcasts of existing SD programs:

       select  recordedprogram.title, recordedprogram.subtitle,
                channel.callsign, channel.name,
                program.starttime
        from    recordedprogram, channel, program
        where   program.chanid = channel.chanid and
                program.programid = recordedprogram.programid and
                recordedprogram.hdtv = 0 and program.hdtv = 1 and
                program.starttime > now()

Running that script nightly and e-mailing myself the results, and then going in to schedule the recording manually, was one way to do it. But thanks to the MythTV power search, this can be automated. I set up my rule through MythWeb and therein chose the following settings:

  • Record at any time on any channel.
  • Power Search.
  • Title = "HDTV Re-broadcast" (title can be whatever you want)
  • Additional tables = ", recordedprogram"
  • Search phrase = "program.programid = recordedprogram.programid and recordedprogram.hdtv = 0 and program.hdtv = 1"
  • Duplicate check method = None (very important to disable the built-in dupe checker)

This query does exactly what I want - find a program in the listings with the same program ID as something I have recorded, where the item I have already recorded is not in HD (recordedprogram.hdtv = 0) and the upcoming program is in HD (program.hdtv = 1). Note that I did not mess with the channel stuff as in the above example, because that was for informational purposes only.

This rule has one bug - it will record the show in HD multiple times, since the duplicate checker is off. That is undesirable which leads to...

Second Attempt

Since the MythTV duplicate checker is disabled, we need to build some duplicate checking into the rule. Here is the updated rule:

  • Record at any time on any channel.
  • Power Search.
  • Title = "HDTV Re-broadcast 2" (title can be whatever you want)
  • Additional tables = ", recordedprogram A left outer join recordedprogram B on A.programid = B.programid and B.hdtv = 1"
  • Search phrase = "program.programid = A.programid and A.hdtv = 0 and program.hdtv = 1 and B.starttime is null"
  • Duplicate check method = None (very important to disable the built-in dupe checker)

The "left outer join" performs a join between two tables on the condition mentioned. In a normal join if there are no matches to the "on" query, nothing is returned. But the "left outer join" in that case will still return the results from the table on the "left" joined with NULL for the results from the right. So in this case, we do a join between the existing recordings (recordedprogram A) and itself (recordedprogram B) on the condition that the program ID from each side matches, AND the program from the right side of the join was in HD. The search phrase is nearly similar to the above except for the final condition. Considering the four possible situations:

  • The show has never been recorded. Thus there are no results in recordedprogram at all and the query returns an empty set, and doesn't even have to evaluate the where clause. Myth does not record.
  • The show has been recorded (at least) once in SD but never in HD. There is a result from "A" but no result from "B" (since B has to be in HD). All results from "A" are joined to null. The search phrase contains A.hdtv = 0 (yes because A is SD), and B.starttime is null (yes, because B is null). The "where" clause returns true and so the query returns a result, and Myth records. (This use of a left outer join to weed out duplicates is a common SQL trick.)
  • The show has been recorded (at least) once in SD and (at least) once in HD. There is a result from "A" and a result from "B". Thus the join returns the data from "A" joined to "B". In this case B.starttime is not null, because we found results from B. Thus the "where" clause is false, so the query returns no results, and Myth does not record. (This is my built-in duplicate checker.)
  • The show has been recorded (at least) once in HD but never in SD. In this case the "A.hdtv = 0" condition in the "where" clause is not satisfied because there is no SD recording. No results from the query are returned and Myth does not record. (For what it's worth, B.starttime would not be null, either.)

However, the SQL query is really slow, and I have observed some undesired re-recordings, mainly for shows that have been deleted.

Almost Final Answer

To optimize this query, I turned on logging on the MySQL side (MySQL Manual) to see exactly what was going on. It turns out that the actual query being executed had a "select" on the "channel" table which was otherwise not used elsewhere in the query, so my result was pointlessly being joined without condition to each channel I had defined. Adding a condition that program.chanid = channel.chanid improved the speed from 58 seconds down to 0.5 seconds. I also added an index on the 'programid' column of the relevant tables.

For the problem of the undesired shows, I discovered that 'recordedprogram' might still contain details about a show that was recorded and deleted but has not yet been purged out of MythTV. So I need to join this to the 'recorded' table to eliminate that possibility. So I wrote my query like so:

Additional Tables = ", recorded C, recordedprogram A left outer join recordedprogram B on A.programid = B.programid and B.hdtv >= 1"

Search Phrase = "C.programid = A.programid and program.programid = A.programid and A.hdtv = 0 and program.hdtv >= 1 and B.starttime is null and channel.chanid = program.chanid"

This led to my rule not working, and when I checked the produced SQL I found it to be invalid. I found and fixed the problem (#4563). If you are not into patching the code, until that gets fixed you could use the "Second Attempt" and add the "channel.chanid = program.chanid" to get the speed improvement.

Issues

This version causes problems when watching LiveTV, and it tries to duplicate recordings (i.e., you may end up with your system recording both an HD and a SD version of a programme simultaneously.) These issues also exist with the example rule built in to Myth as it seems to be based on the second version shown above not the final version despite the fact that (#4563) has now been fixed.

Final Version

What was required was a filter on the recording group to stop programmes that you are watching on LiveTV from causing the rule to kick in and start an HD recording going. This was done by adding "C.recgroup !='LiveTV'". I also added the same for the "Deleted" group as there were a couple of instances where deleted items seemed to reappear.

The second issue was with simultaneous recordings. In the instance where a SD recording is scheduled to take place due to the tuners being unable to record from the HD version of the channel, after the programme started the original rule would cause Myth to record at the same time from the HD channel, if the other recordings that were scheduled did not have a higher priority. I.e, this could cause other recordings to come into conflict with the HD re-record rule and be cancelled. Obviously this was only happening because the Myth scheduler had made the correct choice to record the SD broadcast to allow other programs to also record at the same time.

This is resolved by stating the the endtime of the recording must be in the past.

The third problem was identified April 2012 (this final version was first posted here in November 2011, so if you implemented this rule between those dates you Really want to update). The issue was that all versions of this rule use the programid to link the recording to the the upcoming programme in the EPG data. If your recording has a blank programid it will match all programmes that have a blank programid. For me this caused this rule to find some 150,000+ matches, which caused Myth to believe it did not have enough disk space and so deleted some 50+ recordings, and caused mysql to use 100% CPU which caused GUI response issues, recordings to fail, or to start very late (15-20 minutes late).

I had not seen this in the 5 months since I implemented this rule as the broadcasters I record from normally include a programid, however it would seem that Channel 5 (in the UK) have recently slipped up and several of their programmes do not contain a programid in the EIT EPG data. This then started to match other entire channels.

This is resolved by adding "C.programid!=''", unfortunately with this change, Myth will not be able to re-record in HD any programme missing the programid, however as the duplicate checking probably won't work correctly without the programid, there's a good chance Myth will record it anyway!

So now the Additional Tables is as follows:

, recorded C, recordedprogram A left outer join recordedprogram B on A.programid = B.programid and B.hdtv >= 1

With the Search Phrase like this:

C.programid!='' and C.programid = A.programid and program.programid = A.programid 
and A.hdtv = 0 and program.hdtv >= 1 and B.starttime is null 
and channel.chanid = program.chanid 
and C.recgroup !='LiveTV' and C.recgroup !='Deleted' and C.endTime <now()
Issues

The only known issue with this version is that any SD recording that is cancelled (if you stop them before the scheduled endtime) can cause an HD recording to be setup. I'm sure the SQL could be more efficient, however I will leave this to someone better able to speed test.

I have only been running this modification since 1st November 2011(See above issue regarding blank programid) 15th April 2012, so I will update this should I find any other issues.

Enhancments

The only additional enhancement I might want is to delete the SD recording once the HD recording has been made. If I were to do this, it would be a user job. But that is beyond the scope of custom recording.

Reference

I posted my question and then ultimately my solutions on the MythTV users list:

http://www.gossamer-threads.com/lists/mythtv/users/311831

People Search replacement: An Advanced Custom Recording Example

Introduction

Using the People Search is a quick and easy method of constructing a simple search for your favorite actors, directors etc. Unfortunately, because of its simplicity, you can't gain the flexibility of a Custom Search. A couple of examples to illustrate this - you want to record anything with Patrick Stewart in it but you already have the DVDs of Star Trek: The Next Generation and don't want to record them, or you like a person as a director but not as an actor and only want to record them directing. The People Search doesn't have this flexibility so you end up going through Upcoming Recordings and marking program after program to Don't Record or Never Record.

Problem

To create a Custom Record rule that would successfully search for People, but also allowed the ability to fine tune which programs for that person were picked up.

The Solution

After a bit of trial and error, I managed to find the magic invocation for this. Simply fill in the boxes as follows:

  • Additional tables = "JOIN credits AS c ON c.chanid=program.chanid AND c.starttime=program.starttime JOIN people AS p ON p.person=c.person"
  • Search phrase = "p.name = 'Patrick Stewart' AND program.title != 'Star Trek: The Next Generation' AND c.role='director'"

The Additional tables must be filled in as specified above. This brings in the people and credits tables in the correct manner, linking the credits table to the program table on chanid and starttime, and linking the people table to the credits table on person.

The Search phrase though is where you get to build up your customization. The Search phrase I gave incorporates both the problems I gave as examples above:

  • The first phrase, "p.name = 'Patrick Stewart'", is where you name the person you are interested in.
  • The second phrase, "program.title != 'Star Trek: The Next Generation'", excludes programs with a specific name. You could change this in any manner you wanted, such as including only specific channels, or excluding specific program types.
  • The third phrase, "c.role = 'director'", includes only programs where the named person is a director. There are several types of role available, but what you have personally available depends on the quality of the listings you receive - my data for example, using the Radio Times grabber in the UK, plus some EIT data from DVB-T only gives me actor and director roles, but the table definition allows other roles, such as writer, guest star, executive producer etc.
MythTV Version information

This solution was written and tested using version 0.21, specifically 19631M in branches/release-0-21-fixes, using Fedora 9.

AniMonday, an example of a time block whose content changes often

SyFy channel's AniMonday block stretches from Monday 11pm to Tuesday 1am. Its programming mutates every few weeks as one series completes and they start a new series, or air a movie. Fortunately this is one of the easiest examples on the page, even if it does look a little complicated.

  • search phrase = "channel.callsign='SYFY' AND ((DAYNAME(program.starttime)='Monday' AND HOUR(program.starttime)>=23) OR (DAYNAME(program.starttime)='Tuesday' AND HOUR(program.starttime)<1)) "

This example also shows you how to deal with a time block that begins in one day and ends in another. The "(DAYNAME(program.starttime)='Monday' AND HOUR(program.starttime)>=23)" catches programs that start between Monday 11pm and midnight. The "(DAYNAME(program.starttime)='Tuesday' AND HOUR(program.starttime)<1))" catches the programs that start between Tuesday midnight and 1am.

If the channel changes its call sign (like from SCIFI to SYFY) you will have to edit your rule.

Recording your favorite college football team, and no other teams

Problem

I wanted to record all Oregon Ducks college football games, but not record any Oregon State games. The recordings must not be repeats (I don't need to see classic games), and I want the first showing, not a reshowing at 1:30 AM

The Solution

After testing over a number of weeks everything was working fine with one exception. It didn't want to record the Oregon at Oregon State game. This was an easy fix with some addition checking and a few OR clauses. My final rule is below

program.title = 'College Football' AND program.subtitle LIKE '%Oregon%' AND (program.subtitle NOT LIKE '%Oregon State%' OR program.subtitle = 'Oregon at Oregon State' OR program.subtitle = 'Oregon State at Oregon') AND program.previouslyshown = 0 AND program.first > 0

Notes

This rule is in production on my 0.24 environment. With minor changes, it should be usable for other sports and basketball. At the root, this is a rule that will record <Program Title> where the <Program subtitle> contains some things AND not others (or in this case, it could also include a specific thing). The recording will always be the first showing it is available.

This will also likely not record the bowl game your team is in (or playoffs, superbowl, march madness) as the <Program Title> will likely change.