[mythtv-users] Duplicate detection

[Jan Ceuleers]

On 20/09/16 18:20, Michael T. Dean wrote:
> 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.

Mike,

I'd like to test the query manually before I insert it as a recording
rule (given the fairly exploderous effect I noticed while playing around
with this power rule earlier, in terms of resource usage).

Do you know precisely what query mythtv will execute for a power rule if
record.description = XXX and
record.whatevercolumnisusedforadditionaltables = YYY?

Note that in my tests I was unable to get the LEFT OUTER JOIN to work if
the FROM clause contains more than one table (it said that program.title
was unknown in the ON clause shown above). So in my mysql cli tests I
had to drop the restriction on channel.callsign by taking channel out of
the FROM clause and of course dropping the corresponding conditions from
the WHERE clause. The following query works:

SELECT program.title FROM program
  LEFT OUTER JOIN oldrecorded
  ON (program.title = oldrecorded.title AND oldrecorded.subtitle =
program.subtitle AND oldrecorded.recstatus IN (-3, 11))
  WHERE program.category in
('film','movie','action','romance','crime','comedy','western','drama','tv
movie','mystery','historical/period drama','literary adaptation', 'war',
'musical', 'crime drama') AND TIMESTAMPDIFF(MINUTE, program.starttime,
program.endtime) >= 70 AND program.airdate >= 1970 AND
oldrecorded.recstatus IS NULL;

BTW: a nice trick I spotted on the wiki is to include the LEFT OUTER
JOIN ... ON clauses in the additional tables field in order to ensure
that it shows up before the WHERE keyword which must be part of the
hard-coded query mythtv constructs.

Many thanks, Jan