Difference between revisions of "Custom Recording"

Revision as of 03:10, 31 January 2008

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

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

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...

Final Answer

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. 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). This returns true and Myth records. (This 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 query does not return the result 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, and Myth does not record. (For what it's worth, the B.starttime is null would also be false.)

In conclusion, this does exactly what I want - record the re-broadcast in HD exactly one time.

Issues

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