[mythtv] Power Recording Rule excluding genres

Stephen Worthington stephen_agent at jsw.gen.nz
Wed Dec 12 02:32:09 UTC 2018


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


More information about the mythtv-dev mailing list