Rename qam.pl

From MythTV Official Wiki
Jump to: navigation, search

Author Bob
Description Fix channel information to match contents of a CSV file
Supports


rename_qam.pl

Given a CSV file, connect to database and edit channel data to match.

Command line usage

perl rename_qam.pl [ -f ] qam_data.csv

where the f option will force channels that are not present in the database (but present in the CSV file) to be created.

or

perl rename_qam.pl -D > qam_data.csv

to dump your database configuration to a CSV file to share with someone else.

The Code

Script.png fix_scan_chan.pl

#!/usr/bin/perl

# Version 1.0 - Sep 8, 2010
# Version 1.01 - Sep 19, 2010 - add chomp() to match last column name

use MythTV;
use DBI;
use Getopt::Std;
use strict;

my(%opts, $myth, $sql, $dbh, $sth, $row, $fn, @fields, @chans, $source_id,
   $chan, $chanid, $col, $freq, $n, $i, @cols, $r, $opt_force, @columns);

# Default options
$opt_force = 0;

getopts('fD', \%opts);
foreach (keys %opts) {
    $opt_force = 1 if($_ eq 'f');  # force missing channels to be inserted
}

# Connect to backend
$myth = new MythTV();
# Connect to database
$dbh = $myth->{'dbh'};
if(! defined $dbh) {
    die "failed to connect to database: $!";
}

# Find QAM source ID
$sql = "SELECT DISTINCT sourceid FROM ";
$sql .= "dtv_multiplex WHERE modulation LIKE 'qam%'";
$sth = $dbh->prepare($sql);
die "bad sql: $!" if(! $sth->execute());
$row = $sth->fetchrow_hashref;
$source_id = $row->{'sourceid'};
$sth->finish();
print STDERR "source id = $source_id\n";

if(defined $opts{'D'}) {
    @cols = qw(channum callsign name xmltvid visible freqid serviceid);
    # Dump existing set-up to stdout
    $sql = "SELECT channum, callsign, name, xmltvid, visible, ";
    $sql .= "freqid, serviceid FROM channel where sourceid='" . $source_id;
    $sql .= "' order by channum";
    $sth = $dbh->prepare($sql);
    die "bad sql: $!" if(! $sth->execute());
    $n = 0;
    while($row = $sth->fetchrow_hashref) {
        if($n == 0) {
            # print column headers
            print join(',', @cols) . "\n";
        }
        for($i = 0; $i < scalar(@cols); $i++) {
            print $row->{$cols[$i]};
            print "," if($i != scalar(@cols) - 1);
        }
        print "\n";
        $n++;
    }
    $sth->finish();
    exit(0);
}

# Read CSV config file
$fn = shift;
die "need CSV file name" if(! defined $fn);
die "failed to open $fn: $!" if(! open F, "$fn");

# First line is header line
$_ = <F>;
chomp $_;
@columns = split(/\s*,\s*/, $_);
foreach (@columns) {
    next if($_ =~ m/^(freqid|serviceid|channum|callsign|name|xmltvid|visible)$/);
    die "unknown column: $_\n";
}
if(scalar(@columns) != 7) {
    print "missing a column from:\n";
    print "freqid, serviceid, channum, callsign, name, xmltvid, visible\n";
    exit(1);
}

while(<F>) {
    chomp $_;
    @fields = split /\s*,\s*/, $_;
    if(scalar(@columns) != scalar(@fields)) {
        die "line $. does not have the same number of columns as header line\n";
    }
    push @chans, { $columns[0] => $fields[0],
                   $columns[1] => $fields[1],
                   $columns[2] => $fields[2],
                   $columns[3] => $fields[3],
                   $columns[4] => $fields[4],
                   $columns[5] => $fields[5],
                   $columns[6] => $fields[6] };
}
close F;
print STDERR "read " . scalar(@chans) . " channels\n";

foreach $chan (@chans) {
    # See if channel exists
    $sql = "SELECT chanid FROM channel WHERE sourceid='$source_id' AND ";
    $sql .= "freqid='" . $chan->{'freqid'} . "' AND ";
    $sql .= "serviceid='" . $chan->{'serviceid'} . "'";
    $sth = $dbh->prepare($sql);
    die "bad sql: $!" if(! $sth->execute());
    $row = $sth->fetchrow_hashref;
    $sth->finish();
    if(defined $row->{'chanid'}) {
        $chanid = $row->{'chanid'};
        $sql = 'UPDATE channel SET';
        foreach $col (keys %{$chan}) {
            next if($col =~ m/^\s*$/);
            next if($col =~ m/freqid|serviceid/);
            $sql .= " " . $col . "='" . $chan->{$col} . "',";
        }
        chop $sql;
        $sql .= " WHERE freqid='" . $chan->{'freqid'} . "' AND";
        $sql .= " serviceid='" . $chan->{'serviceid'} . "'";
        #print $sql . "\n";
    } elsif($opt_force) {
        # Insert a channel that is not already present
        # First determine mplex it from dtv_multiplex using freqid
        $freq = freqid2freq($chan->{'freqid'});
        $sql = "SELECT mplexid FROM dtv_multiplex WHERE ";
        $sql .= "frequency='" . $freq . "'";
        $sth = $dbh->prepare($sql);
        die "bad sql: $!" if(! $sth->execute());
        $row = $sth->fetchrow_hashref;
        if(! defined $row->{'mplexid'}) {
            # mplex ID not found in dtv table, need to bail or create
            print STDERR "no mplexid for channel " . $chan->{'freqid'};
            print STDERR "create? ";
            $r = <STDIN>;
            chomp $r;
            next if($r !~ m/^y/i);
            # Get a row as an example
            $sql = "SELECT * FROM dtv_multiplex WHERE sourceid='";
            $sql .= $source_id . "' LIMIT 1";
            $sth = $dbh->prepare($sql);
            die "bad sql: $!" if(! $sth->execute());
            $row = $sth->fetchrow_hashref;
            $sth->finish();
            $sql = "INSERT INTO dtv_multiplex (";
            $r = '(';
            foreach (keys %{$row}) {
                next if($_ =~ m/mplexid|updatetimestamp/);
                $sql .= $_ . ","; 
                if($_ ne 'frequency') {
                    $r .= "'" . $row->{$_} . "',";
                } else {
                    $r .= "'" . $freq . "',";
                }
            }
            # Get rid of trailing commas
            chop $sql;
            chop $r;
            $sql = $sql . ') VALUES ' . $r . ')';
            $sth = $dbh->prepare($sql);
            die "bad sql: $!" if(! $sth->execute());
            $sth->finish();
            # Get newly inserted mplexid
            $sql = "SELECT mplexid FROM dtv_multiplex WHERE ";
            $sql .= "frequency='" . $freq . "'";
            $sth = $dbh->prepare($sql);
            die "bad sql: $!" if(! $sth->execute());
            $row = $sth->fetchrow_hashref;
            $sth->finish();
            if(! defined $row->{'mplexid'}) {
                print STDERR "failed to create mplexid for " .
                  $chan->{'freqid'} . ", skipping\n";
                next;
            }
        }
        $sth->finish();
        $sql = "INSERT INTO channel (chanid,channum,freqid,sourceid,";
        $sql .= "callsign,name,xmltvid,visible,serviceid,mplexid) ";
        $sql .= "VALUES ";
        $chan->{'chanid'} = $source_id . (sprintf "%03d", $chan->{'channum'});
        $sql .= "('" . join("','", ($chan->{'chanid'}, $chan->{'channum'},
                                    $chan->{'freqid'}, $source_id,
                                    $chan->{'callsign'}, $chan->{'name'},
                                    $chan->{'xmltvid'},
                                    $chan->{'visible'}, $chan->{'serviceid'},
                                    $row->{'mplexid'})) . "')";
        #print $sql ."\n";
    }
    $sth = $dbh->prepare($sql);
    die "bad sql: $!" if(! $sth->execute());
    $sth->finish();

}
$dbh->disconnect();
exit(0);

sub freqid2freq($) {
  my $id = shift;
  my @freqs = ( undef,
                undef,
                57000000,
                63000000,
                69000000,
                79000000,
                85000000,
                177000000,
                183000000,
                189000000,
                195000000,
                201000000,
                207000000,
                213000000,
                123012500,
                129012500,
                135012500,
                141000000,
                147000000,
                153000000,
                159000000,
                165000000,
                171000000,
                219000000,
                225000000,
                231012500,
                237012500,
                243012500,
                249012500,
                255012500,
                261012500,
                267012500,
                273012500,
                279012500,
                285012500,
                291012500,
                297012500,
                303012500,
                309012500,
                315012500,
                321012500,
                327012500,
                333025000,
                339012500,
                345012500,
                351012500,
                357012500,
                363012500,
                369012500,
                375012500,
                381012500,
                387012500,
                393012500,
                399012500,
                405000000,
                411000000,
                417000000,
                423000000,
                429000000,
                435000000,
                441000000,
                447000000,
                453000000,
                459000000,
                465000000,
                471000000,
                477000000,
                483000000,
                489000000,
                495000000,
                501000000,
                507000000,
                513000000,
                519000000,
                525000000,
                531000000,
                537000000,
                543000000,
                549000000,
                555000000,
                561000000,
                567000000,
                573000000,
                579000000,
                585000000,
                591000000,
                597000000,
                603000000,
                609000000,
                615000000,
                621000000,
                627000000,
                633000000,
                639000000,
                645000000,
                93000000,
                99000000,
                105000000,
                111025000,
                117025000,
                651000000,
                657000000,
                663000000,
                669000000,
                675000000,
                681000000,
                687000000,
                693000000,
                699000000,
                705000000,
                711000000,
                717000000,
                723000000,
                729000000,
                735000000,
                741000000,
                747000000,
                753000000,
                759000000,
                765000000,
                771000000,
                777000000,
                783000000,
                789000000,
                795000000,
                801000000 );
  return(undef) if($id > scalar(@freqs) - 1);
  return($freqs[$id]);
}

sub freq2freqid($) {
  my($freq, $i, $h, $f);
  $freq = shift;
  for($f = 0, $i = 2; $i < 127; $i++) {
    $h = freqid2freq($i);
    if($h == $freq) {
      $f = 1;
      last;
    }
  }
  if(!$f) {
    return(undef);
  }
  return($i);
}