[mythtv] Power Recording Rule excluding genres

Stephen Worthington stephen_agent at jsw.gen.nz
Wed Dec 12 08:40:24 UTC 2018


On Wed, 12 Dec 2018 00:18:34 -0600, you 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.
>
>> >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

Yes, stored procedures will not work from Power Recording Rules - you
can not do a CALL.  But stored functions should work.


More information about the mythtv-dev mailing list