[mythtvnz] Channel id to name tables: Where are they?

[Stephen Worthington]

On Tue, 23 Oct 2012 10:16:29 +1300, you wrote:

>On 23/10/12 02:56, Steve Hodge wrote:
>> Before you do any of the stuff that Stephen has suggested you should post a
>> dump of your channel table that includes the sourceid and useronairguide
>> columns:
>> select chanid, channum, freqid, sourceid, callsign, name, xmltvid, visible,
>> useonairguide, mplexid, serviceid from channel;
>
>Okidoki.
>
>Here it is.
>
>Worik

Good call, Steve.  There are two sources.  So now we need to see how
the two sources are set up.  The sourceid field is used in only three
tables: channel, videosource and dtv_multiplex.  So please post the
results of these:

select * from videosource;
select * from dtv_multiplex;

I think this is all still the result of two scans, and that one source
can be deleted, along with all the related channel records.

To safely delete one duplicate set of channel records, we also need to
know where the chanid values in those records are being used, and
whether they will then need to be changed from 1xxx to 3xxx and vice
versus.  I did a dump of the database structure:

  mysqldump -d -u $DBUserName -p$DBPassword -h $DBHostName $DBName
>mythconverg_schema.sql

and went searching with my editor to find all references to chanid. As
it is the field used to tie together channels and everything else, I
was not surprised to find lots of references to it.  Here is the list
of tables it is used in:

  channel
  channelgroup
  credits
  eit_cache
  inuseprograms
  jobqueue
  oldrecorded
  pidcache
  program
  programgenres
  programrating
  record
  record_tmp
  recorded
  recordedcredits
  recordedfile
  recordedmarkup
  recordedprogram
  recordedrating
  recordedseek
  recordmatch
  tvchain

So we need to be sure of whether either or both the 1xxx and 3xxx
channels have been used in all those tables.  Some, like tvchain and
pidcache, I would expect to be empty tables, but there will be lots of
rows in most of them.

The results of this script (count_chanids.sh) should tell us where the
1xxx and 3xxx chanid values have been used:

#!/bin/bash

source /etc/mythtv/mysql.txt

for table in \
    channel channelgroup credits eit_cache inuseprograms jobqueue \
    oldrecorded pidcache program programgenres programrating record \
    record_tmp recorded recordedcredits recordedfile recordedmarkup \
    recordedprogram recordedrating recordedseek recordmatch tvchain \
    ; do

    echo
    echo $table
    mysql -B -N -u $DBUserName -p$DBPassword -h $DBHostName $DBName <<
EOF
	select '<1000    ',count(*) from $table where chanid < 1000;
	select '1000-1999',count(*) from $table where chanid >= 1000
and chanid < 2000;
	select '2000-2999',count(*) from $table where chanid >= 2000
and chanid < 3000;
	select '3000-3999',count(*) from $table where chanid >= 3000
and chanid < 4000;
	select '>4000    ',count(*) from $table where chanid >= 4000;
EOF
done