[mythtv] Power Recording Rule excluding genres

David Hampton mythtv at love2code.net
Wed Dec 12 13:38:01 UTC 2018


On Wed, 2018-12-12 at 00:18 -0600, David Engel wrote:
> On Wed, Dec 12, 2018 at 03:32:09PM +1300, Stephen Worthington wrote:
> > On Tue, 11 Dec 2018 15:39:07 -0700, you wrote:
> > 
> > > On Mon, Dec 10, 2018 at 7:51 PM David Engel <david at istwok.net>
> > > wrote:
> > > 
> > > > On Mon, Dec 10, 2018 at 05:59:56PM -0700, John P Poet wrote:
> > > > > I would like to set up a "Power Recording Rule" which records
> > > > > all PBS
> > > > > movies as long as the genre is NOT Comedy, Historical,
> > > > > Romance or
> > > > History.
> > > > > I am able to achieve rejecting one genre with a syntax like
> > > > > this:
> > > > > 
> > > > > channel.callsign LIKE 'PBS%HD' AND program.category_type =
> > > > > 'movie' AND
> > > > > 'Comedy' <> ALL (select genre from programgenres where
> > > > > program.chanid=programgenres.chanid AND
> > > > > program.starttime=programgenres.starttime)
> > > > > 
> > > > > However, that gets ugly real quick, if I want to negate more
> > > > > than one
> > > > genre:
> > > > > channel.callsign LIKE 'PBS%HD' AND program.category_type =
> > > > > 'movie' AND
> > > > > 'Comedy' <> ALL (select genre from programgenres where
> > > > > program.chanid=programgenres.chanid AND
> > > > > program.starttime=programgenres.starttime) AND 'Historical'
> > > > > <> ALL
> > > > (select
> > > > > genre from programgenres where
> > > > > program.chanid=programgenres.chanid AND
> > > > > program.starttime=programgenres.starttime) AND 'Romance' <>
> > > > > ALL (select
> > > > > genre from programgenres where
> > > > > program.chanid=programgenres.chanid AND
> > > > > program.starttime=programgenres.starttime) AND 'History' <>
> > > > > ALL (select
> > > > > genre from programgenres where
> > > > > program.chanid=programgenres.chanid AND
> > > > > program.starttime=programgenres.starttime)
> > > > > 
> > > > > I figured I could put the sub query into a derived table and
> > > > > then just
> > > > use
> > > > > that when doing the "genre <> ALL (results)" but either that
> > > > > does not or
> > > > I
> > > > > have not figured out the syntax.
> > > > > 
> > > > > Anyone have any suggestion?
> > > > 
> > > > I don't have time to work out and test a complete solution but
> > > > I think
> > > > something like this will work.
> > > > 
> > > > For joinging, use
> > > > 
> > > >     join ( select chanid, starttime,
> > > >                   group_concat(genre order by genre) allgenres
> > > >            from programgenres group by chanid, starttime ) g
> > > >          on program.chanid = g.chanid
> > > >             and program.starttime = g.starttime
> > > > 
> > > > The subquery will give you results similar to
> > > > 
> > > > 
> > > > +--------+---------------------+-------------------------------
> > > > ------------------------------------------------------------+
> > > > > chanid | starttime           | allgenres
> > > >                                                  |
> > > > 
> > > > +--------+---------------------+-------------------------------
> > > > ------------------------------------------------------------+
> > > > >   6913 | 2018-11-26 00:03:00 |
> > > > Documentary,Entertainment,Show,Special,tvshow
> > > >                |
> > > > >   6260 | 2018-11-26 00:05:00 |
> > > > Adventure,Children,Episode,series,Series,Show
> > > >                |
> > > > >   6760 | 2018-11-26 00:05:00 |
> > > > Adventure,Children,Episode,series,Series,Show
> > > >                |
> > > > >   6901 | 2018-11-26 00:10:00 |
> > > > Documentary,Episode,Newsmagazine,series,Series,Show
> > > >                |
> > > > 
> > > > For your where clause, use something like
> > > > 
> > > >     NOT g.allgenres REGEXP
> > > > '(^|,)(Comedy|Historical|Romance|History)(,|$)'
> > > > 
> > > > You'll probably have to fiddle with the regex.  Basically you
> > > > want the
> > > > regex to match on any of the undesired genres preceeded by the
> > > > beginning of the string or a comma and succeeded by a comma or
> > > > the end
> > > > of the string.
> > > > 
> > > > David
> > > > 
> > > 
> > > Thank you, David.   Unfortunately, that JOIN clause does not fit
> > > in
> > > mythconverg.record.subtitle.  It gets chopped off before the "on
> > > program.chanid ...".
> > > 
> > > When I try it directly in mysql, it complains "Unknown column
> > > 'program.chanid' in 'on clause'".
> > > 
> > > It looks like if I want to do this using a "Power Recording Rule"
> > > some
> > > special support may need to be added to mythbackend to deal with
> > > the
> > > situation.
> 
> I initially thought of suggesting that we consider adding the
> combined
> genres to the program table.  That would probably work for your case
> but would set a bad precedent.  What do we do if someone wants a
> similar query but involving multiple actors instead of genres?
> 
> Short of a major overhaul of the power rules, I only see a couple of
> options.  Either increase the size of the subtitle column or add new,
> extratables and whereclause columns for power rules to use instead of
> overloading the subtitle and description columns.  Of the two,
> increasing the size of the subtitle column is by far the easier
> solution.  You might try it on your own database unless and until
> someone else need the same change.

I've spent some time recently banging my head against the overloading
of the subtitle column by the power search rule.  I'd much prefer that
this hack go away and that new columns be added to the database.

David




More information about the mythtv-dev mailing list