View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 1 post ] 
Print view Previous topic   Next topic  
Author Message
Search for:
PostPosted: Mon Aug 10, 2009 1:14 pm 
Offline
Joined: Fri Sep 15, 2006 12:16 pm
Posts: 292
What I wanted was to be able to add / remove channels at SchedulesDirect.org and have mythfilldatabase automatically update all my backends without additional work. Although mythfilldatabase will automatically add new channels, it will not remove old, unused channels and using mythtv-setup to hand remove multiple channels on multiple backends without a mistake is a real PITA.

I want to allow new channels through from SchedulesDirect.org by default as sometimes old channels are moved around or new channels are added. The problem is that I get unusable channels (high def or premium) too and have to delete these from schedulesdirect.org some time after schedulesdirect.org adds them and the unusable channels remain in the guide even after all their data expires.

I couldn't seem to get what I wanted from SchedulesDirect.org / mythfilldatabase, so I wrote a simple script to get me the last mile.

FYI: You can have multiple channel lines in the guide that all have the same channel number. Usually only one will have programs. These scripts are smart enough to delete all of the copies that have zero programs in the guide.

Programmers: Channel.channum is the sql column that the user sees. There can be multiple "channels" that have the same channum. Program.chanid / channel.chanid is the internal number that mythtv uses and they are unique.

It turns out that all I needed was to remove channels (chanid's) that had no programs in the guide. With a little research on mysql commands and some simple shell script programming I came up with a script that lists the chanid's that should be deleted and another script that lists them and also deletes them.

Just hook them into your startup, or cron, or run them manually to remove those pesky channels that have no programs in the guide.

Edit: I haven't added code to detect when mysqld is offline or goes offline in the middle of the script. Bad things could possibly happen. Although I don't expect there to actually be a problem in these cases, I feel it necessary to add this warning just in case. Unfixable problems can be avoided if you will make a database backup with "Main -> Knoppmyth -> Backup Knoppmyth" before using these scripts. If you use these for instance once a month in cron, it would be good to have it make a backup first as part of the cron job.

Here is 'chanid-zero-ls' which just lists the chanid's (and the human readable channum's) that have zero programs:
Code:
#!/bin/bash
# make 'line' an arithmetic variable
declare -i line
line=1

#---------------------------------------------------------------------------
# get channum (what user sees), chanid (what mythtv uses),
#   and count of programs by chanid
#   and scan each line for chanid's that have 0 programs (nothing in guide)
#---------------------------------------------------------------------------
mysql mythconverg -umythtv -pmythtv -e "select channel.channum, channel.chanid, count(program.chanid) from channel left join program on program.chanid = channel.chanid group by chanid order by CAST(channel.channum as unsigned)" | while read -s channum chanid progcount; do

  # first line is human readable header so always display it
  if [ $line -eq 1 -o $progcount == '0' ]; then
    echo -e $channum\\t$chanid\\t$progcount
  fi
  line=line+1

done

To use this just copy the above Code: and paste it into a file named chanid-zero-ls then run
Code:
chmod a+x chanid-zero-ls
then run it with
Code:
./chanid-zero-ls
from a shell prompt on your MythTV backend.

Here is 'chanid-zero-rm' which deletes the chanid's that have zero programs:
Code:
#!/bin/bash
# make 'line' an arithmetic variable
declare -i line
line=1

#---------------------------------------------------------------------------
# get channum (what user sees), chanid (what mythtv uses),
#   and count of programs by chanid
#   and scan each line for chanid's that have 0 programs (nothing in guide)
#
# be aware that you can indeed have multiple records with the same channum
#---------------------------------------------------------------------------
mysql mythconverg -umythtv -pmythtv -e "select channel.channum, channel.chanid, count(program.chanid) from channel left join program on program.chanid = channel.chanid group by chanid order by CAST(channel.channum as unsigned)" | while read -s channum chanid progcount; do

  # first line is human readable column headers
  # so if it is the first line
  if [ $line -eq 1 ]; then
    # display the column header line
    echo -e $channum\\t$chanid\\t$progcount

  else
  # it is not the first line
  # so if there are zero programs in the guide for that channel
    if [ $progcount == '0' ]; then
      # display the line
      echo -ne $channum\\t$chanid\\t$progcount

      # then delete that channel
      #
      # my channel 1 is a local video camera input with no schedule
      # and I don't want to delete it so I include these 4 commented out lines
      #if [ $channum == '1' ]; then
      #  echo -e "\t(special channel not deleted)"
      #else
        echo -e "\tdeleted"
        mysql mythconverg -umythtv -pmythtv -e "delete from channel where chanid = $chanid"
      #fi
    fi
  fi

  line=line+1

done

To use this just copy the above Code: and paste it into a file named chanid-zero-rm then run
Code:
chmod a+x chanid-zero-rm
then run it with
Code:
./chanid-zero-rm
from a shell prompt on your MythTV backend.

I am using R5.5, but I would hazard a guess that these scripts would work on virtually any version of MythTV, old or new.

Cliff


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 


All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu