Archiving Old Recordings as Videos

I had way too many old recordings on my broken system, most of which belonged to the kids, and they did’t want to part with them. I didn’t really want to restore the old database onto the new system, as it’s been going for 8 years and I wanted to start from scratch. So, my plan was to archive all the old recordings as videos.

The only problem with this was that MythTV the file names aren’t descriptive, so I had to somehow access the old database to get them out. The plan was to load the old dump into a different database and use that to extract the file names and rename the files. Here’s what I did:

Create the database “old”

mysql -u root -p
(my user's password)
create database old;

Load the dump into database “old”

mysql -u root -p old < mythconverg-1299-20140629075054.sql

Run the following perl script to rename the files. Note that this moves the files rather then renames them, as for so many gigabytes that would have taken way too long. Moving is instantaneous as long as it's on the same disk. Also, make sure you change the directory names to match your system:


#!/bin/bash

# Directory containing MythTV recordings
export MYTHDIR=/mnt/storeold/mythtv

# Directory to hold renamed files
export TVDIR=/mnt/storeold/oldtv

# Run mysql query and pipe into Perl
mysql -u root -p old --execute "select chanid,starttime,endtime,title,subtitle,basename from recorded" |
perl -w -e '
use File::Copy qw(move);

my $mythpath=$ENV{MYTHDIR};
my $tvpath=$ENV{TVDIR};
my $n=1;

<>;
while (<>) {
chomp;
# Get row from database
my ($chanid,$start,$end,$title,$subtitle,$basename) = split /\t/;

if (!defined $subtitle) {$subtitle = ""}
if (! -e "$mythpath/$basename") {print "Skipping $mythpath/$basename\n"; next}

# Generate new filename
my $dir = "${title}";
my $nfn = "${title}";
if ($subtitle) {$nfn="${nfn}__${subtitle}"}

$dir =~ s/ /_/g;
$dir =~ s/&/+/g;
$dir =~ s/[^+0-9a-zA-Z_-]+/_/g;

$nfn =~ s/ /_/g;
$nfn =~ s/&/+/g;
$nfn =~ s/[^+0-9a-zA-Z_-]+/_/g;

$nfn = sprintf "%s.mpg", $nfn;

if (!-d "$tvpath/$dir") {mkdir "$tvpath/$dir" or die "Failed to make directory: $tvpath/$dir\n";}

print "$mythpath/$basename $tvpath/$dir/$nfn\n";
move "$mythpath/$basename", "$tvpath/$dir/$nfn" or die "Failed to move file $tvpath/$dir/$nfn: $!";
}'