[mythtv-users] reconciling database and recorded files

[Jerome Yuzyk]

On Wednesday 03 September 2008 20:50, Jerome Yuzyk wrote:
>
> Then this got me wondering about the subject of this message. Is there any
> tool that would reconcile database entries vs recorded files (probably
> including the PNG thumbnails)? Maybe someone has a handy SQL query snippet
> or pointer to such to get me started?

Before anyone takes the time to reply, I have answered my own question, and 
wrote a Perl script to give me a little report on all the 'recorded' records 
in the db, files with no record, records with no file, and thumbnails with no 
record. It's not fancy, but it answered part of my question.

--------------------------------------------------------------------------
#!/usr/bin/perl

# Reconcile database records with filesystem files (MPG and PNG).

my $recordings = "/home/mythtv/video/recordings";       # <-- CONFIGURE
my $user   = '-umythtv -pmythtv';                       # <-- CONFIGURE

my $select = 'chanid,starttime,title,subtitle';

my @R = `mysql $user mythconverg -B --exec "select $select from recorded;"`;
shift @R;       # first row is column titles

my %R;

foreach my $rec ( @R ) {

    chomp $rec;

    my ( $chanid, $start, $title, $subtitle ) = split /\t/, $rec;

    $start =~ s/[^0-9]//g;

    $subtitle = ( defined $subtitle ) ? " - $subtitle" : "" ;

    my $file = "${chanid}_${start}.mpg";

    $R{$file} = "$title$subtitle";
    }

my @F = `ls $recordings/*.mpg`; my @P = `ls $recordings/*.png`;

print "\n==== Database Records\n";

foreach ( sort keys %R ) { print "$_    $R{$_}\n"; }


print "\n==== File(s) with No Record\n";

foreach my $file ( sort @F ) {

    chomp $file; $file =~ s#$recordings/##;

    ( defined $R{$file} ) or print "$file\n";
    }


print "\n==== Record(s) with No File\n";

my %F = map{ chomp $_ => $_ } @F;

foreach my $file ( sort keys %F ) {

    $file =~ s#$recordings/##;

    ( defined $F{$file} ) or print "$file\n";
    }


print "\n==== Thumbnail(s) with No Record\n";

foreach my $file ( sort @P ) {

    chomp $file;

    $file =~ s/.png//; $file =~ s#$recordings/##;

    ( defined $R{$file} ) or print "$file.png\n";
    }
--------------------------------------------------------------------------