[mythtv-users] Duplicate Channels Cleanup

[Martin Compton]

On 09/15/2014 01:38 PM, Eric Sharkey wrote:
> On Mon, Sep 15, 2014 at 11:37 AM, Michael T. Dean
> <mtdean at thirdcontact.com>  wrote:
>> On 09/15/2014 10:25 AM, Martin Compton wrote:
>>> I was cleaning up my DISH-TV Schedules Direct listings and ran
>>> mythfilldatabase to update the channels.
>>>
>>> When I was checking the channels table I noticed that some have 2-3
>>> duplicate entries for the same sourceid+channel+callsign+xmltvid (etc).
>>>
>>> Can I safely reassign the chanid in the associated tables:
>>> channelgroup
>>> program (these duplicates can be deleted)
>>> programgenres
>>> programrating
>>> record
>>> recorded
>>> oldrecorded
>>>
>>> and remove the duplicate channels?
>>>
>>> Am I missing any tables with a "chanid" column?
>> The proper way to handle "ghost" channels (as you get when you don't
>> properly set the XMLTV ID on channels before running mythfilldatabase and
>> your broadcasters provide in-stream information about the channel that
>> mythfilldatabase can't match to one of your scanned channels) is to "Delete
>> all video sources" (to clear out everything--both the good and the bad
>> channel info--from all the tables that are affected) and then rescan, set
>> the XMLTV ID's, then run mythfilldatabase.
> You've posted this response before.  I can't say I recall anyone
> agreeing that this is the best approach.  There's too much collateral
> damage.
>
> Martin, I think your originally posted plan is best.  There is no good
> automated way to remove a channel that doesn't leave dangling foreign
> key references from other tables.
That's what I ended up doing, "bashing" out the script included below.

Bear in mind, I was simply trying to cleanup what happened after I ran 
"mythfilldatabase" with the "--do-channel-updates" option (more than 
once) in an attempt to clean up DISH channel changes/additions (via 
SchedulesDirect).  The duplicates contain exactly the same record data, 
only the "chanid" was different.

After cleaning my Schedules Direct lineup and channels table, I have 
about %40 fewer channels and program data.  In my view proper 
de-duplication was worth the effort versus dumping everything and 
starting over.

Here's the bash script (best to stop myth-backend and /backup database/ 
first!):

#!/bin/bash
# MythTV v0.27 remove duplicate channel: duplicate_chanid valid_chanid
# deletes duplicate channel/guide entries but preserves recording history

if [ $# -lt 2 ]; then
     echo -e "Usage:\n\tremove_dup_channel.sh duplicate_chanid valid_chanid"
     exit 1
fi

DUPID=$1
CHANID=$2

cat << EOF | mysql -umythtv -pdIIOS40x mythconverg
delete from channelgroup where chanid=${DUPID};
update jobqueue set chanid=${CHANID} where chanid=${DUPID};
delete from program where chanid=${DUPID};
delete from programgenres where chanid=${DUPID};
delete from programrating where chanid=${DUPID};
update oldrecorded set chanid=${CHANID} where chanid=${DUPID};
update recorded set chanid=${CHANID} where chanid=${DUPID};
update recordedcredits set chanid=${CHANID} where chanid=${DUPID};
update recordedfile set chanid=${CHANID} where chanid=${DUPID};
update recordedmarkup set chanid=${CHANID} where chanid=${DUPID};
update recordedprogram set chanid=${CHANID} where chanid=${DUPID};
update recordedrating set chanid=${CHANID} where chanid=${DUPID};
update recordedseek set chanid=${CHANID} where chanid=${DUPID};
delete from recordmatch where chanid=${DUPID};
update record_tmp set chanid=${CHANID} where chanid=${DUPID};
update record set chanid=${CHANID} where chanid=${DUPID};
update tvchain set chanid=${CHANID} where chanid=${DUPID};
delete from channel where chanid=${DUPID}
EOF

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140916/78a6a15a/attachment.html>