Rename qam.pl
From MythTV Official Wiki
| 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
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);
}
