[mythtv-users] Auto-delete duplicate recordings

[Tony Lill]

On 12/17/2013 10:07 AM, George Nassas wrote:
> On Dec 17, 2013, at 12:56 AM, Jacob Strandlien <kepesk at gmail.com>
> wrote:
> 
>> Hello!  I have been trying to find a way to do this for a couple
>> of days now with no luck.
>> 
>> I have a large archive of recordings on a well-used mythbox
>> (0.27) and I know I have accumulated a lot of duplicates.  I need
>> to free some space, but going through and manually getting rid of
>> the duplicates would be a monumental task.
>> 
>> Is there any mechanism or script available to track down these
>> duplicates, and preferably delete all but the newest recording?
>> I've searched the web and poked at my interface with no luck.
> 
> I think your only recourse is a direct update to the database but
> the main question is what counts as a duplicate? A good identifier
> is the column programid and using that I whipped up this little bit
> of sql:
> 
> update recorded r1 set recgroup = 'Duplicates' where r1.programid
> != '' and substr(r1.programid, 11) != '0000' and exists (select 1 
> from recorded r2 where r1.starttime < r2.starttime and r1.programid
> = r2.programid);
> 

I tried that, but it didn't work. Can't update a table used in a from
statement. The following modification does, though.

create temporary table duplicate_recorded
   select programid, chanid, starttime from recorded r1
      where r1.programid != ''
      and substr(r1.programid, 11) != '0000'
      and exists (select 1
                 from recorded r2
        where r1.starttime < r2.starttime
          and r1.programid = r2.programid);
update recorded
   inner join duplicate_recorded on
          recorded.chanid = duplicate_recorded.chanid
      and recorded.starttime = duplicate_recorded.starttime
    set recorded.recgroup = "Duplicates";
drop table duplicate_recorded;


-- 
Tony Lill, OCT,                    Tony.Lill at AJLC.Waterloo.ON.CA
President, A. J. Lill Consultants                 (519) 650 0660
539 Grand Valley Dr., Cambridge, Ont. N3H 2S2     (519) 241 2461
--------------- http://www.ajlc.waterloo.on.ca/ ----------------