[mythtv-users] Conditional custom recording rule

Tue Oct 7 07:59:08 UTC 2014

On Mon Oct 06, 2014 at 06:15:07PM -0600, John P Poet wrote:

> On Mon, Oct 6, 2014 at 5:28 PM, Nicolas Krzywinski <myth at site7even.de>
> wrote:
> 
> >
> >
> > Am 2014-10-06 23:37, schrieb mythtv-users-request at mythtv.org:
> >
> >> Message: 18
> >> Date: Mon, 6 Oct 2014 15:36:44 -0600
> >> From: John P Poet <jppoet at gmail.com>
> >> To: Discussion about MythTV <mythtv-users at mythtv.org>
> >> Subject: Re: Conditional custom recording rule
> >> Message-ID:
> >>         <CACSqvJkhM_fJsMXQ4HeZS+Fthd3vwohQhXdP3EAO6GDBc3aoUQ@
> >> mail.gmail.com>
> >> Content-Type: text/plain; charset="utf-8"
> >>
> >> On Mon, Oct 6, 2014 at 2:25 PM, Ian Evans <dheianevans at gmail.com> wrote:
> >>
> >>  First off, I'd like to thank Hika, Stephen and Nicolas (and anyone else
> >>> who'll be jumping in) for looking at this. The fact that we can even toss
> >>> this around shows how great MythTV is as compared to the STBs offered by
> >>> the cable and sat companies.
> >>>
> >>> I guess it's really an exercise in laziness. :-) I could just have a rule
> >>> for the backup newscast set at a lower priority, but I like to keep my
> >>> conflict list free of priority conflicts (I only occasionally get them)
> >>> so
> >>> that when I look I see the real conflict choices between two classic
> >>> movies
> >>> or docs.
> >>>
> >>> I see here (http://www.mythtv.org/wiki/Custom_Recording) that nested
> >>> selects work. But do if/then/else phrases?
> >>>
> >>> This is not valid sql or mythtv obviously but the logic might be:
> >>>
> >>> IF (select title='CTV News' and time='6pm') = 0 THEN schedule Global News
> >>> at 6pm
> >>>
> >>>
> >> You can create a Custom Record Rule which uses multiple references to
> >> programs.  The 'Additional Tables' just needs filled in to let it know
> >> that
> >> you want to base your rule off of another "program".  See the attached
> >> screenshot for an example (hopefully you can read it, since I had to save
> >> it at an extremely low quality).
> >>
> >> John
> >> -------------- next part --------------
> >> An HTML attachment was scrubbed...
> >> URL:
> >>
> >> <http://www.mythtv.org/pipermail/mythtv-users/
> >> attachments/20141006/2b948262/attachment.html>
> >> -------------- next part --------------
> >> A non-text attachment was scrubbed...
> >> Name: Fallback-recording.jpg
> >> Type: image/jpeg
> >> Size: 25698 bytes
> >> Desc: not available
> >> URL:
> >>
> >> <http://www.mythtv.org/pipermail/mythtv-users/
> >> attachments/20141006/2b948262/attachment.jpg>
> >>
> > Thanks for this post John, as I learned a lot from it.
> >
> > First, now I understand the additional tables box within the recording
> > rule mask. SQLly spoken, the "additional tables" box content is appended to
> > the tables expression of the sql query and because of this, there is a
> > comma in front, right?
> 
> 
> Yes
> 
> 
> > Second, I have to assume - due to my lack of deeper knowledge at this
> > point - that multiple sql table aliases to the same table can be used like
> > different tables. At least this is my understanding of why we can use the
> > program table doubled - one as original program table and the same one
> > aliased as pother.
> >
> 
> Yes.
> 
> Third, after thinking a quite amount of times about your example, I guess
> > now I finally understand it - which leads me to a final question:
> > pother.starttime has to exactly match program.starttime, so if the
> > alternative news would not exactly start at the same time, this has to be
> > extended, correct?
> >
> 
> pother.starttime does not have to equal program.starttime, but it does need
> to be "reigned in" to allow mysql to know what program it is checking.
> 
> You could think of it as two separate queries.   mysql is going to pull all
> the matches for any program(s) that match the given criteria for 'program':
> 
> program.title = "News" AND program.chanid = 1071
> 
> It is also going to grab any program(s) which match 'pother':
> 
> pother.title <> "Other News" AND DAYNAME(pother.starttime) = 'Saturday'
> 
> As long as the results for both of those are not empty sets, you
> effectively have a match and it will record 'program'.  The "loser" your
> pother query is though, the harder mysql has to work.  Without a starttime
> you are making it check every program on that day.  If you also leave off
> that chanid, it has to check every show on every channel.  If you are not
> careful, the mysql query will become extremely intensive, and you will kill
> the performance of the scheduler.
> 
> You could probably get away with limiting the start time to a range:
> 
> (HOUR(program.starttime,...) BETWEEN 12 AND 14) AND
> (HOUR(pother.starttime,...) BETWEEN 16 AND 22)
> 
> 
> As mentioned earlier, this really is an awesome example of what MythTV is
> > able to do!
> >
> 
> Yup, I make heavy use of power rules.  I generally don't push them this
> far, though ;-)
> 
> 
I don't think this will do what you think it will. You're asking it to
record where there's a program on in that time range (or at that time)
which isn't called 'CTV News', rather than if there's not a program on
in that time range called 'CTV News' (i.e. if there's any other show on
that channel in the specified time range then it will record, regardless
of whether CTV News is also on).

I'd use a not exists rule instead. Lose the additional table detail and
specify it all in the query - e.g.:

    program.title = 'Evening News' AND
    HOUR(CONVERT_TZ(program.starttime,'Etc/UTC', 'SYSTEM')) = 18 AND
    NOT EXISTS (
        select 1 from program pother
        where pother.chanid=1662
        AND pother.starttime = program.starttime
        AND pother.title LIKE 'CTV NEWS%'
    )

You may need to tweak the start times if they're not definitely 6PM (or
not necessarily the same).

I've not tested this, but if MythTV is just throwing this into a
standard SQL where clause then I don't see why it shouldn't work.

HTH,
    Robin
-- 
     ___        
    ( ' }     |       Robin Hill        <myth at robinhill.me.uk>  |
   / / )      | Little Jim says ....                            |
  // !!       |      "He fallen in de water !!"                 |
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 181 bytes
Desc: Digital signature
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20141007/fbb130fe/attachment.sig>