[mythtv-users] Duplicate detection

[Michael T. Dean]

On 09/20/2016 11:41 AM, Jan Ceuleers wrote:
> On 20/09/16 16:16, Michael T. Dean wrote:
>> So, you could enable dup matching to include the description and it
>> should work--except for the fact that you're explicitly trying to ignore
>> the descriptions since they differ across your listings sources.  That
>> means the only other option to get automated duplicate matching would be
>> to include it in your custom rule.  It wouldn't be too terribly
>> difficult to do for a "title only" matching mechanism (which you could
>> do for a film-only rule)--at least compared to trying to do our full
>> dup-matching approaches in a custom rule.  You'd need to add a clause
>> that checks that oldrecorded.recstatus (which might also require adding
>> oldrecorded as an additional table--but try without, first) is not -3
>> (recorded).  Fashioning the proper join and tuning it will likely take
>> some experimenting (it would take quite a while for me, so I'm leaving
>> it as an exercise for the reader).
> I tried this by adding the following:
>
> AND NOT EXIST (SELECT title from oldrecorded where
> program.title=oldrecorded.title AND oldrecorded.recstatus=-3)
>
> I also needed to add oldrecorded to the list of tables.
>
> Unfortunately this causes resource usage to skyrocket, bringing the
> server to its knees.

Yeah, you'd probably need to do a LEFT OUTER JOIN, rather than an 
embedded SELECT.  With that, you'd get all the rows in program with the 
equivalent values from oldrecorded, where any program rows without 
matching oldrecorded rows would have NULL for values in the oldrecorded 
columns.  So, after the join, you'd select those episodes with 
oldrecorded.recstatus IS NULL to get those program rows without a 
corresponding oldrecorded entry.  The "Re-record SDTV in HDTV (disable 
duplicate matching)" example clause shows a LEFT OUTER JOIN in use, but 
in a very different style/purpose...

Totally not tested, but something along the lines of:

LEFT OUTER JOIN oldrecorded ON (oldrecorded.title = program.title AND 
oldrecorded.subtitle = program.subtitle AND oldrecorded.recstatus IN 
(-3, 11))
...
(your custom query stuff)
AND oldrecorded.recstatus IS NULL

and anything you can do to further limit the join (conditions in the ON 
clause) will improve performance.  Probably needs a lot of changes, but 
it's a start.

BTW, including recstatus 11 for never records.

Mike