[mythtv] Power Recording Rule excluding genres

[David Engel]

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.

> >John
> 
> It may be possible to use a stored procedure and just call it from
> your power recording rule:
> 
> https://mariadb.com/kb/en/library/stored-procedures
> 
> The syntax needed to create a stored procedure is a pain - for an
> example, have a look at this on my web server:
> 
> http://www.jsw.gen.nz/mythtv/epg_fixes.sql
> 
> In that code, I have a DROP PROCEDURE command at the end after it is
> called, as I do want to re-create it each time.  In your case, you
> would want your code to create the procedure to start with DROP
> PROCEDURE IF EXISTS before the CREATE PROCEDURE, but not have the DROP
> PROCEDURE at the end.  You would need to run the SQL code each time
> you change the PROCEDURE.  Then in the Power Recording Rule you would
> put a CALL command that runs the PROCEDURE.  Having never done a CALL
> from a Power Recording Rule I am not sure if it will work, but I can
> not see any reason for it not to.

Stored procedures can be very helpful, especially at hiding the
mundane details in comlex queries.  We should probably use them some.
I know the BUQSs could benefit from them.  However, I don't see them
being able to help much in John's case due to the way power rules were
misdesigned.

David
-- 
David Engel
david at istwok.net