[mythtv-users] Power search help

[Karl Newman]

On Tue, Feb 18, 2014 at 8:41 PM, Karl Newman <newmank1 at asme.org> wrote:

> On Tue, Feb 18, 2014 at 8:07 PM, David Caruso <dcaruso at cox.net> wrote:
>
>>
>> On 2/18/2014 9:54 PM, Karl Newman wrote:
>>
>>  On Tue, Feb 18, 2014 at 6:42 PM, Karl Newman <newmank1 at asme.org> wrote:
>>
>>>   On Tue, Feb 18, 2014 at 6:25 PM, Hika van den Hoven <hikavdh at gmail.com
>>> > wrote:
>>>
>>>>  Hoi Hika,
>>>>
>>>>
>>>> Wednesday, February 19, 2014, 3:15:37 AM, you wrote:
>>>>
>>>>
>>>>  Hoi Karl,
>>>>
>>>> Wednesday, February 19, 2014, 2:54:57 AM, you wrote:
>>>>
>>>>
>>>>  On Tue, Feb 18, 2014 at 5:46 PM, Hika van den Hoven <hikavdh at gmail.com>
>>>> wrote:
>>>> Hoi Karl,
>>>>
>>>>
>>>> Wednesday, February 19, 2014, 2:30:18 AM, you wrote:
>>>>
>>>>
>>>>  I need some help from the SQL experts which I know lurk on this list.
>>>> I'm trying to create a power rule to re-record movies (which are currently
>>>> recorded) when they air on channels which I've marked as commercial free. I
>>>> started with the re-record in HD rule as per
>>>> http://www.mythtv.org/wiki/Custom_Recording#Recording_a_Rebroadcast_in_HD:_An_Advanced_Custom_Recording_Example and
>>>> have the basic search working like this (formatted as a SQL query for
>>>> testing, not as a power rule--ignore the title, starttime, channel.name
>>>>  columns):
>>>>
>>>> SELECT program.title, program.starttime, channel.name FROM program
>>>> JOIN channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
>>>> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
>>>> C.commmethod != -2) WHERE program.category_type = "movie";
>>>>
>>>> It's missing the "AND channel.commmethod = -2" clause to only pick up
>>>> programs airing on comm free channels because I'm planning to just use the
>>>> "Commercial Free" filter which I presume will be appended to the WHERE
>>>> clause. I probably also need to add filters for Live TV and Deleted groups,
>>>> but that's pretty trivial to add. This seems to work great, but the problem
>>>> is that I have to disable duplicate matching for this to work, obviously,
>>>> so I need to implement that in the SELECT statement otherwise it will keep
>>>> recording the movie as long as the non-commercial free recording exists.
>>>> Following the pattern in the HD re-record I tried to do this:
>>>>
>>>> SELECT program.title, program.starttime, channel.name FROM program
>>>> JOIN channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
>>>> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
>>>> C.commmethod != -2) LEFT OUTER JOIN recordedprogram R2 USING (programid)
>>>> JOIN channel AS C2 ON (R2.chanid = C2.chanid AND C2.commmethod = -2) WHERE
>>>> program.category_type = "movie" AND R2.starttime = NULL ORDER BY title;
>>>> The idea there is to exclude items where there is an entry for that
>>>> programid in recordedprogram which was recorded on a commercial free
>>>> channel (C2.commmethod = -2). The problem is to accomplish that I think I
>>>> need to do an inner join of the channel table ONLY on/with the chanid from
>>>> the outer joined R2 and I don't know how to structure that. The above
>>>> almost works but I think it's joining C2 on the entire previous selected
>>>> row which causes it to return an empty set (because then there are clauses
>>>> for commmethod = -2 AND commmethod != -2).
>>>>
>>>> So, any suggestions?
>>>>
>>>> Thanks,
>>>>
>>>> Karl
>>>>
>>>>
>>>>
>>>>
>>>> You can try nested queries, which means doing a guery on a query
>>>> instead of on a table.
>>>> I'm not exactly clear on the syntax and whether mysql supports this.
>>>> This was long ago
>>>> on an Oracle database.
>>>> Thanks. I played with nested queries a little bit while following an
>>>> alternate path to duplicate matching, but my impression is that mysql
>>>> performance can be variable with nested queries, and also I think it's more
>>>> than I need. This query is very fast (0.00 sec--probably just hitting cache
>>>> though) and I think I'm very close to a solution, I just need to know how
>>>> to structure that one join.
>>>>
>>>> Karl
>>>>
>>>>
>>>>
>>>> I think you're encountering a problem I have encountered with joins. If
>>>> any side of it gives no record you get empty.
>>>> Even though with your own logic you would expect result.
>>>> I probably am phrasing it badly. But the solution for me was to work
>>>> with two independent queries or filter further
>>>> in programming code. I think, but I'm only a self-learned in sql, the
>>>> nested solution is your best bet.
>>>>
>>>> Tot mails,
>>>>   Hika                            mailto:hikavdh at gmail.com<hikavdh at gmail.com>
>>>>
>>>> "Zonder hoop kun je niet leven
>>>> Zonder leven is er geen hoop
>>>> Het eeuwige dilemma
>>>> Zeker als je hoop moet vernietigen om te kunnen overleven!"
>>>>
>>>> De lerende Mens
>>>> --
>>>>
>>>>
>>>>  I'm thinking about my phrasing; I mean if either part has no record
>>>> that satisfies the join,
>>>> it doesn't look any further but throws it out. So any possible
>>>> satisfying record that doesn't
>>>> exist in any member of the join...
>>>>
>>>
>>>   I think I found the answer. My earlier experiments with parentheses
>>> resulted in syntax errors but now that I look closer at it I think I
>>> understand why. Here's what I have now:
>>> SELECT program.title, program.starttime, channel.name FROM program JOIN
>>> channel ON (program.chanid = channel.chanid) JOIN recordedprogram AS R
>>> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid AND
>>> C.commmethod != -2) LEFT OUTER JOIN (recordedprogram R2 JOIN channel AS C2
>>> ON (R2.chanid = C2.chanid AND C2.commmethod = -2)) USING (programid) WHERE
>>> program.category_type = "movie" AND R2.starttime = NULL;
>>>
>>>  I'm still getting an empty set, though, even when I temporarily mark a
>>> channel with upcoming re-airings as commercial free. I need to experiment
>>> some more. I think maybe I need to give my outer joined table a name and
>>> then check that starttime = NULL on that.
>>>
>>>  Karl
>>>
>>
>>  Okay, this is weird. If I remove the AND R2.starttime = NULL clause,
>> then I get the results that I was getting before I added the duplicate
>> matching statements. However, if I add R2.starttime to the SELECTed
>> columns, they all show up as NULL. So why isn't that matching when it's in
>> the WHERE clause???
>>
>>  Karl
>>
>>
>> _______________________________________________
>> mythtv-users mailing listmythtv-users at mythtv.orghttp://www.mythtv.org/mailman/listinfo/mythtv-usershttp://wiki.mythtv.org/Mailing_List_etiquette
>> MythTV Forums: https://forum.mythtv.org
>>
>>
>> I'm not exactly a MySQL expert but in Oracle/MSSQL/Teradata NULL never
>> equals NULL so your join condition of R2.starttime = NULL will always
>> evaluate to false.  Try something like R2.starttime is NULL instead.
>>
>
> That was the trick, thanks. I should have looked more closely at the
> re-record HDTV example.
>
> Thanks,
>
> Karl
>

Well that was unpleasant. I ran into some system-stalling problem while
trying to implement this as a power search. First, I wasn't able to enter
the power search using mythweb, because mythweb aggressively strips
non-word characters (character class \W) from the end of the "Additional
Tables" field, including the closing parens which I needed for my JOIN
conditions. Instead of trying to remove that scrubbing from mythweb with
unknown effects, I thought I'd just use the frontend to edit the schedule.
Well, when I did that, I ran into the 128 character limitation for the
Additional Tables (because it uses the subtitle field I believe) which was
truncating it just short of what I needed. So, I reworked the FROM section
to replace a join with a comma and move the join conditions to the WHERE
section and eliminated some optional keywords and was able to get it down
to (I think) exactly 128 characters. However, when I pressed the Test
button, it locked up the frontend AND backend. The rest of the system was
responsive (to SSH, etc.) but I could see mysqld consuming 100% of one CPU.
I tried restarting mythbackend and mythwelcome, but when mythwelcome
restarted it just stalled at the blue background and wouldn't proceed. So I
ended up rebooting the system (For whatever reason, I didn't think to
restart mysqld, which is probably where the problem was). After the reboot,
I thought I'd try again by taking out a couple characters of whitespace in
case I was running into some sort of string overflow bug where I didn't
leave enough room for the null at the end. But unfortunately the same thing
happened (I selected "Record" this time which I guess was the right
option...?).

I'm not sure why it did this because I was testing with essentially the
same query (or at least all the tricky parts) using the mysql command line.
In case anyone wants to try this and troubleshoot it, here's my search
rules:
Additional Tables: ,channel C,recorded R LEFT JOIN (recorded R2 JOIN
channel C2 ON (R2.chanid=C2.chanid AND C2.commmethod=-2)) USING (programid)
Search Phrase: program.programid = R.programid AND C.commmethod != -2 AND
program.category_type = "movie" AND R2.starttime IS NULL AND R.programid !=
'' AND R.recgroup !='LiveTV' AND R.recgroup !='Deleted' AND R.endtime <
CONVERT_TZ(NOW(),'SYSTEM','UTC')

As an aside, it was not easy to find out how to edit the power search. You
can't just go to the schedule detail and edit it like the normal schedules;
you have to press the Menu button and select Edit Search Rule or something
like that.

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140219/ac86187a/attachment.html>