Difference between revisions of "Myth.rebuilddatabase.pl"

From MythTV Official Wiki
Jump to: navigation, search
m (just added an Ubuntu version which needs an extra package to run this script.)
m (Typo)
 
(7 intermediate revisions by 5 users not shown)
Line 1: Line 1:
 
{{Wrongtitle|myth.rebuilddatabase.pl}}
 
{{Wrongtitle|myth.rebuilddatabase.pl}}
'''myth.rebuilddatabase.pl''' is a script in the contrib directory that traverses the specified MythTV recordings directory and finds all files with video extensions and checks if they appear in the database. If no entry exists it prompts for identifying information and a recording entry is created.
+
{{Warning box|This script is deprecated. Please put any videos that are not recorded by your MythTV system into [[MythVideo]].}}
  
Let's say you have a recorded program, a *.nuv file for example. And MythTV doesn't know about it
+
Once upon a time, Greg Froese wrote a script to re-insert recordings that might have gone missing due to database corruption, ''etc.''
(e.g., you copied it from your video directory to a backup location and then deleted it
 
from the MythTV list of recordings).
 
And then you want to re-introduce the recording to MythTV (so the recording
 
shows up again in the list of recordings).
 
How do you do that re-introduction?
 
The myth.rebuilddatabase.pl script can do that for you.
 
  
<PRE>
+
For a while, it even lived in the MythTV source tree, under contrib/recovery, then contrib/maintenance. It was removed before 0.24.
    usage: myth.rebuilddatabase.pl [options]
+
 
 +
There is an old copy in [[Myth.rebuilddb.pl]], but after MythTV 0.25, it does something like:
 +
 
 +
  DBD::mysql::st execute failed: Field 'season' doesn't have a default value at ./myth.rebuilddb.pl line 424, <STDIN> line 8.
 +
  Could not execute (insert into recorded (chanid, starttime, endtime, title, subtitle, description, hostname, basename, progstart, progend, storagegroup, recgroup) values ((?), (?), (?), (?), (?), (?), (?), (?), (?), (?), (?), (?)))
 +
 
 +
Here is a version that works with 0.28:  {{Code box|myth.rebuilddb.pl|
 +
<pre>
 +
#!/usr/bin/perl
 +
## written by greg froese (g_froese@yahoo.com)
 +
## install instructions by Robert Kulagowski (rkulagow@rocketmail.com)
 +
##
 +
## I had trouble maintaining my catalog of recordings when upgrading to
 +
## cvs and from cvs to more recent cvs, so I wrote this.
 +
##
 +
##
 +
## Here is what this program is supposed to do.
 +
##
 +
## It first scans through your myth database and displays all shows listed
 +
## in the recorded table.
 +
##
 +
## It will then traverse the specified MythTV recordings directory
 +
## set with --dir /YOURMYTHDIR) and find all files with
 +
## video extensions (set with --ext) and check if they appear in the
 +
## database. If no entry exists you will be prompted for identifying
 +
## information and a recording entry will be created.
 +
##
 +
## See the help message below for options.
 +
##
 +
## Use at your own risk. Standard gnu warranty, or lack therof,
 +
## applies.
 +
 
 +
## To run:
 +
## Ensure that the script is executable
 +
## chmod a+x myth.rebuilddatabase.pl
 +
## ./myth.rebuilddatabase.pl
 +
 
 +
## Change log:
 +
## 9-19-2003: (awithers@anduin.com)
 +
##  Anduin fights the urge to make code more readable (aka C like).  Battle
 +
##  of urges ends in stalemate: code was reindented but not "changed" (much).
 +
##  To make it a little less useless a contribution also did:
 +
##    - added ability to grab title/subtitle/description from oldrecorded
 +
##    - support for multiple backends (via separation of host and dbhost
 +
##      and bothering to insert the host in the recorded table).
 +
##    - removed dependency on File::Find::Rule stuff
 +
##    - attempt to determine good default host name
 +
##    - provide default for --dir from DB (if not provided)
 +
##    - added --test_mode (for debugging, does everything except INSERT)
 +
##    - added --try_default (good for when you must specify a command
 +
##      line option but don't really need to)
 +
##    - added --quick_run for those occasions where you just don't have
 +
##      the sort of time to be sitting around hitting enter
 +
##    - changed all the DB calls to use parameters (avoids escape issues,
 +
##      and it looks better)
 +
## 2017-01-22: Nigel
 +
##    - Add default values for recorded.season, episode & inetref
 +
##    - Remove dependence on Time::Format.pm
 +
##    - Shorten all lines to 80char max
 +
##    - Add a few more file extensions, esp: .ts
 +
 
 +
use strict;
 +
use DBI;
 +
use Getopt::Long;
 +
use Sys::Hostname;
 +
use File::Basename;
 +
use Date::Parse;
 +
 
 +
my ($verbose, $dir);
 +
 
 +
my $show_existing = 0;
 +
my $test_mode = 0;
 +
my $quick_run = 0;
 +
my $try_default = 0;
 +
 
 +
my $host = hostname;
 +
my $dbhost = $host;
 +
my $database = "mythconverg";
 +
my $user = "mythtv";
 +
my $pass = "mythtv";
 +
my $ext = "{nuv,mpg,mpeg,mpeg2,ts,avi}";
 +
my $file = "";
 +
my @answers;
 +
my $norename = 0;
 +
my $storagegroup = "Default";
 +
 
 +
my $date_regx = qr/(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/;
 +
my $db_date_regx = qr/(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)/;
 +
my $channel_regx = qr/(\d\d\d\d)/;
 +
 
 +
sub GetAnswer {
 +
    my ($prompt, $default) = @_;
 +
    print $prompt;
 +
    if ($default) {
 +
        print " [", $default, "]";
 +
    }
 +
    print ": ";
 +
 
 +
    my $answer;
 +
    if ($#answers >= 0) {
 +
        $answer = shift @answers;
 +
        print $answer, "\n";
 +
    } else {
 +
        chomp($answer = <STDIN>);
 +
        $answer = $default if !$answer;
 +
    }
 +
 
 +
    return $answer;
 +
}
 +
 
 +
# there's a version of this in CPAN but I don't want to add another dependancy
 +
sub EscapeFilename {
 +
    my $fn = $_[0];
 +
    # escape everything that's possibly dangerous
 +
    $fn =~ s{([^[:alnum:]])}{\\\1}g;
 +
    # it's embarrassing to escape / and . so put those back
 +
    $fn =~ s{\\([/.])}{\1}g;
 +
    return $fn;
 +
}
 +
 
 +
# Remove need for Time::Format
 +
sub UnixTimeToDBtime($)
 +
{
 +
    my ($unixTime) = @_;
 +
 
 +
    my ($sec,$min,$hour,$mday,$mon,$year,undef) = localtime($unixTime);
 +
 
 +
    return sprintf("%4d-%02d-%02d %02d:%02d:%02d",
 +
$year+1900, $mon+1, $mday, $hour, $min, $sec);
 +
}
 +
 
 +
my $script_name = $0;
 +
 
 +
if ($0 =~ m/([^\/]+)$/) {
 +
$script_name = $1;
 +
}
 +
 
 +
my $script_version = "0.0.3";
 +
 
 +
## get command line args
 +
 
 +
my $argc=@ARGV;
 +
if ($argc == 0) {
 +
  print "$script_name Version $script_version
 +
    usage: $script_name [options]
  
 
     Where [options] is:
 
     Where [options] is:
       --host          - hostname of this backend (default: "ubuntu")
+
       --host          - hostname of this backend (default: \"$host\")
 
       --dbhost        - hostname or IP address of the mysql server
 
       --dbhost        - hostname or IP address of the mysql server
                         (default: "ubuntu")
+
                         (default: \"$dbhost\")
       --user          - DBUSERNAME (default: "mythtv")
+
       --user          - DBUSERNAME (default: \"$user\")
       --pass          - DBPASSWORD (default: "mythtv")
+
       --pass          - DBPASSWORD (default: \"$pass\")
       --database      - DATABASENAME (default: "mythconverg")
+
       --database      - DATABASENAME (default: \"$database\")
 
       --show_existing - Dumps current recorded table.
 
       --show_existing - Dumps current recorded table.
 
       --dir          - path to recordings
 
       --dir          - path to recordings
       --group        - Storage Group to import as (default: "Default")
+
       --group        - Storage Group to import as (default: \"Default\")
 
       --try_default  - Try to just run with the defaults.
 
       --try_default  - Try to just run with the defaults.
 
       --quick_run    - don't prompt for title/subtitle/description just
 
       --quick_run    - don't prompt for title/subtitle/description just
Line 37: Line 175:
 
       Assumption: The script is run on DB/backend machine.
 
       Assumption: The script is run on DB/backend machine.
  
         myth.rebuilddatabase.pl --try_default
+
         $script_name --try_default
  
 
     Example 2:
 
     Example 2:
 
       Assumption: The script is run on a backend other than the DB host.
 
       Assumption: The script is run on a backend other than the DB host.
 
+
         myth.rebuilddatabase.pl --dbhost=mydbserver
+
         $script_name --dbhost=mydbserver
  
 
     Example 3:
 
     Example 3:
 
       Import one specific file and supply first few answers.
 
       Import one specific file and supply first few answers.
  
         myth.rebuilddatabase.pl --file MyVideo.avi --answer y \
+
         $script_name --file MyVideo.avi --answer y \\
                     --answer 1041 --answer "My Video"
+
                     --answer 1041 --answer \"My Video\"
  
 
     The script chooses reasonable defaults for all values so it's possible
 
     The script chooses reasonable defaults for all values so it's possible
 
     to do a quick import of a single video by taking input from null:
 
     to do a quick import of a single video by taking input from null:
  
         myth.rebuilddatabase.pl --file MyVideo.avi < /dev/null
+
         $script_name --file MyVideo.avi < /dev/null
  
 
     this also works with multiple videos but because record start time is
 
     this also works with multiple videos but because record start time is
 
     synthesized from file modification time you have to be careful of
 
     synthesized from file modification time you have to be careful of
 
     possible collisions.
 
     possible collisions.
</PRE>
+
";
Here's an example session to re-introduce a recording.
+
exit(0);
 +
}
 +
 
 +
GetOptions('verbose+'=>\$verbose,
 +
'database=s'=>\$database,
 +
'dbhost=s'=>\$dbhost,
 +
'host=s'=>\$host,
 +
'user=s'=>\$user,
 +
'pass=s'=>\$pass,
 +
'dir=s'=>\$dir,
 +
'group=s'=>\$storagegroup,
 +
'show_existing|se'=>\$show_existing,
 +
'try_default|td'=>\$try_default,
 +
'quick_run|qr'=>\$quick_run,
 +
'test_mode|t|tm'=>\$test_mode,
 +
'ext=s'=>\$ext,
 +
'file=s'=>\$file,
 +
'answer=s'=>\@answers, # =s{,} would be nice,
 +
# but isn't implemented widely
 +
'norename'=>\$norename
 +
);
 +
 
 +
my $dbh = DBI->connect("dbi:mysql:database=$database:host=$dbhost",
 +
"$user","$pass")
 +
or die "Cannot connect to database ($!)\n";
 +
 
 +
my ($starttime, $endtime, $title, $subtitle, $channel,
 +
    $description, $season, $episode, $recgroup);
 +
my ($syear, $smonth, $sday, $shour, $sminute, $ssecond,
 +
    $eyear, $emonth, $eday, $ehour, $eminute, $esecond);
 +
 
 +
my $q = "";
 +
my $sth;
 +
 
 +
if (!$dir) {
 +
print("Error: No recordings directory specified.\n");
 +
print("      You must use the --dir option",
 +
" to specify a recording directory to use.\n");
 +
exit 1;
 +
}
 +
 
 +
# remove trailing slash
 +
$dir =~ s/\/$//;
 +
 
 +
if ($show_existing) {
 +
$q = "select title, subtitle, starttime, endtime, chanid, recgroup"
 +
. " from recorded order by starttime";
 +
$sth = $dbh->prepare($q);
 +
$sth->execute or die "Could not execute ($q)\n";
 +
 
 +
print "\nYour myth database ($database) is reporting"
 +
. " the following programs as being recorded:\n\n";
 +
 
 +
while (my @row=$sth->fetchrow_array) {
 +
$title = $row[0];
 +
$subtitle = $row[1];
 +
$starttime = $row[2];
 +
$endtime = $row[3];
 +
$channel = $row[4];
 +
$recgroup = $row[5];
 +
 
 +
## get the pieces of the time
 +
if ($starttime =~ m/$db_date_regx/) {
 +
($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
 +
($1, $2, $3, $4, $5, $6);
 +
}
 +
 
 +
if ($endtime =~ m/$db_date_regx/) {
 +
($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
 +
($1, $2, $3, $4, $5, $6);
 +
}
 +
 
 +
print "Channel:    $channel\n";
 +
print "Start time: $smonth/$sday/$syear"
 +
. " - $shour:$sminute:$ssecond\n";
 +
print "End time:  $emonth/$eday/$eyear"
 +
. " - $ehour:$eminute:$esecond\n";
 +
print "Title:      $title\n";
 +
print "Subtitle:  $subtitle\n";
 +
print "RecGroup:  $recgroup\n\n";
 +
}
 +
}
 +
 
 +
print "\nThese are the files stored in ($dir) and will be checked against\n";
 +
print "your database to see if the exist."
 +
. "  If they do not, you will be prompted\n";
 +
print "for a title and subtitle of the entry,"
 +
. " and a record will be created.\n\n";
 +
 
 +
my @files = $file ? ($dir . "/" . $file) : glob("$dir/*.$ext");
 +
print "@files\n";
 +
 
 +
foreach my $show (@files) {
 +
    my $showBase = basename($show);
 +
 
 +
    my $cnt = $dbh->selectrow_array("select count(*) from recorded where"
 +
. " basename=(?)", undef, $showBase);
 +
 
 +
    my $found_title;
 +
 
 +
    if ($cnt gt 0) {
 +
        $found_title = $dbh->selectrow_array("select title from recorded where"
 +
. " basename=(?)",
 +
undef, $showBase);
 +
    }
 +
 
 +
    if ($found_title) {
 +
        print("Found a match between file and database\n");
 +
        print("    File: '$show'\n");
 +
        print("    Title: '$found_title'\n");
 +
 
 +
        # use this so the stuff below doesn't have to be indented
 +
        next;
 +
    }
 +
 
 +
    print("Unknown file $show found.\n");
 +
    next unless GetAnswer("Do you want to import?", "y") eq "y";
 +
 
 +
 
 +
    # normal case: import file into the database
 +
 
 +
    my ($channel, $syear, $smonth, $sday, $shour, $sminute, $ssecond,
 +
        $eyear, $emonth, $eday, $ehour, $eminute, $esecond);
 +
    my ($starttime, $duration, $endtime);
 +
    my ($mythfile);
 +
 
 +
    # filename varies depending on when the recording was
 +
    # created. Gleam as much as possible from the name.
 +
 
 +
    if ($showBase =~ m/$channel_regx\_/) {
 +
        $channel = $1;
 +
    } else {
 +
        $channel = $dbh->selectrow_array("select min(chanid) from channel");
 +
    }
 +
 
 +
    if ($showBase =~ m/$channel_regx\_$date_regx\./) {
 +
        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
 +
            ($2, $3, $4, $5, $6, $7);
 +
    }
 +
 
 +
    if ($showBase =~ m/$channel_regx\_$date_regx\_$date_regx/) {
 +
        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
 +
            ($2, $3, $4, $5, $6, $7);
 +
        ($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
 +
            ($8, $9, $10, $11, $12, $13);
 +
    }
 +
 
 +
    my $guess_title = $showBase;
 +
    $guess_title =~ s/[.][^\.]*$//;
 +
    $guess_title =~ s/_/ /g;
 +
 
 +
    my $guess_subtitle = "";
 +
    my $guess_description = "Recovered file " . $showBase;
 +
 
 +
    # have enough to look for an past recording?
 +
    if ($ssecond) {
 +
        print "Checking for a recording...\n";
 +
        $starttime = "$syear$smonth$sday$shour$sminute$ssecond";
 +
 
 +
        my $guess = "select title, subtitle, description from oldrecorded"
 +
. " where chanid=(?) and starttime=(?)";
 +
        $sth = $dbh->prepare($guess);
 +
        $sth->execute($channel, $starttime)
 +
            or die "Could not execute ($guess)\n";
 +
 
 +
        if (my @row = $sth->fetchrow_array) {
 +
            $guess_title = $row[0];
 +
            $guess_subtitle = $row[1];
 +
            $guess_description = $row[2];
 +
        }
 +
 
 +
        print "Found an orphaned file, initializing database record\n";
 +
        print "Channel:    $channel\n";
 +
        print "Start time: $smonth/$sday/$syear - $shour:$sminute:$ssecond\n";
 +
        print "End time:  $emonth/$eday/$eyear - $ehour:$eminute:$esecond\n";
 +
    }
 +
 
 +
    # what about checking for guide data?
 +
    if($guess_description =~ /^Recovered file/) {
 +
        print "Checking for guide data...\n";
 +
        my $guess = "select title, subtitle, description from program where " .
 +
                    "chanid='$channel' and " .
 +
                    "starttime='$syear-$smonth-$sday $shour:$sminute:$ssecond'";
 +
        $sth = $dbh->prepare($guess);
 +
        $sth->execute()
 +
            or die "Could not execute ($guess)\n";
 +
 
 +
        if (my @row = $sth->fetchrow_array) {
 +
            $guess_title = $row[0];
 +
            $guess_subtitle = $row[1];
 +
            $guess_description = $row[2];
 +
            print "Using guide data information for defaults\n";
 +
        }
 +
    }
 +
 
 +
    my $newtitle = $guess_title;
 +
    my $newsubtitle = $guess_subtitle;
 +
    my $newdescription = $guess_description;
 +
 
 +
    if (!$starttime) {
 +
        # use file time if we can't infer time from name
 +
$starttime = &UnixTimeToDBtime((stat $show)[9]);
 +
    }
 +
 
 +
    if ($quick_run) {
 +
 
 +
        print("QuickRun defaults:\n");
 +
        print("        title: '$newtitle'\n");
 +
        print("    subtitle: '$newsubtitle'\n");
 +
        print("  description: '$newdescription'\n");
 +
 
 +
$recgroup = "Default";
 +
$season  = $episode = 0;
 +
 
 +
    } else {
 +
 
 +
        $channel  = GetAnswer("Enter channel", $channel);
 +
        $newtitle = GetAnswer("... title", $newtitle);
 +
        $season  = GetAnswer("... Season",  "0");
 +
        $episode  = GetAnswer("... Episode", "0");
 +
        $newsubtitle    = GetAnswer("... subtitle", $newsubtitle);
 +
        $newdescription = GetAnswer("Description", $newdescription);
 +
        $starttime      = GetAnswer("... start time (YYYY-MM-DD HH:MM:SS)",
 +
$starttime);
 +
        $recgroup      = GetAnswer("... Recording Group", "Default");
 +
    }
 +
 
 +
    if ($endtime) {
 +
        $duration = (str2time($endtime) - str2time($starttime)) / 60;
 +
    } else {
 +
        $duration = "60";
 +
    }
 +
    $duration = GetAnswer("... duration (in minutes)", $duration);
 +
    $endtime = &UnixTimeToDBtime(str2time($starttime) + $duration * 60);
 +
 
 +
    if ($norename) {
 +
        $mythfile = $showBase;
 +
    } else {
 +
        my ($ext) = $showBase =~ /([^\.]*)$/;
 +
        my $time1 = $starttime;
 +
        $time1 =~ s/[ \-:]//g;
 +
        $mythfile = sprintf("%s_%s.%s", $channel, $time1, $ext);
 +
    }
 +
 
 +
    my $sql = "insert into recorded (chanid, starttime, endtime, title,"
 +
. " subtitle, description, hostname, basename, progstart,"
 +
. " progend, storagegroup, recgroup, season, episode, inetref)"
 +
. " values ((?), (?), (?), (?), (?), (?), (?),"
 +
. " (?), (?), (?), (?), (?), (?), (?), '')";
 +
 
 +
    if ($test_mode) {
 +
 
 +
        $sql =~ s/\(\?\)/"%s"/g;
 +
        my $statement = sprintf($sql, $channel, $starttime, $endtime, $newtitle,
 +
                                $newsubtitle, $newdescription, $host, $mythfile,
 +
                                $starttime, $endtime, $storagegroup, $recgroup,
 +
$season, $episode);
 +
        print("Test mode: insert would have been been:\n");
 +
        print($statement, ";\n");
 +
 
 +
    } else {
  
<PRE>
+
        $sth = $dbh->prepare($sql);
$ cd /video
+
        $sth->execute($channel, $starttime, $endtime, $newtitle,
$ ls *Peach*
+
                      $newsubtitle, $newdescription, $host, $mythfile,
JamesAndTheGiantPeach.nuv
+
                      $starttime, $endtime, $storagegroup, $recgroup,
$ alias myrbdb=/local/mythtv-src/mythtv-0.20/contrib/myth.rebuilddatabase.pl
+
      $season, $episode)
$ mysql -u mythtv -pmythtv mythconverg -e 'SELECT chanid,starttime,endtime,title,subtitle,description FROM recordedprogram WHERE title LIKE "%Peach%"'
+
            or die "Could not execute ($sql)\n";
chanid starttime endtime title subtitle description
 
1059 2007-08-08 11:00:00 2007-08-08 12:30:00 James and the Giant Peach A kindly British orphan (Paul Terry) escapes his cruel aunts (Joanna Lumley, Miriam Margolyes) to sail the sea on a giant peach with six friendly bugs. Animated.
 
$
 
$ myrbdb --dbhost localhost --file JamesAndTheGiantPeach.nuv
 
  
These are the files stored in (/video) and will be checked against
+
        if ($mythfile ne $showBase) {
your database to see if the exist. If they do not, you will be prompted
+
            rename($show, $dir. "/" . $mythfile);
for a title and subtitle of the entry, and a record will be created.
+
        }
  
/video/JamesAndTheGiantPeach.nuv
+
    }
Unknown file /video/JamesAndTheGiantPeach.nuv found.
 
Do you want to import? [y]:
 
Enter channel [1002]: 1059
 
... title [JamesAndTheGiantPeach]: James and the Giant Peach
 
... subtitle:
 
Description [Recovered file JamesAndTheGiantPeach.nuv]: A kindly British orphan (Paul Terry) escapes his cruel aunts (Joanna Lumley, Miriam Margolyes) to sail the sea on a giant peach with six friendly bugs. Animated.
 
... start time (YYYY-MM-DD HH:MM:SS) [2007-08-18 20:33:23]: 2007-08-08 11:00:00
 
... duration (in minutes) [60]: 90
 
Building a seek table should improve FF/RW and JUMP functions when watching this video
 
Do you want to build a seek table for this file? [y]:
 
2007-08-20 13:12:19.260 Using runtime prefix = /usr/local
 
2007-08-20 13:12:19.348 New DB connection, total: 1
 
2007-08-20 13:12:19.366 mythcommflag version: 0.20.20060828-3 www.mythtv.org
 
2007-08-20 13:12:19.374 Enabled verbose msgs: important
 
  
MythTV Commercial Flagger, started at Mon Aug 20 13:12:19 2007
+
   
Rebuilding SeekTable(s) for:
+
    print("Building a seek table should improve FF/RW and"
ChanID  Start Time      Title                                      Status
+
. " JUMP functions when watching this video\n");
------  --------------  -----------------------------------------  ------
 
2007-08-20 13:12:21.400 New DB connection, total: 2
 
1059    20070808110000  James and the Giant Peach                  2007-08-20 13:12:21.554 Using protocol version 30
 
99%/ 1935fpsRebuilt
 
  
Finished commercial break flagging at Mon Aug 20 13
+
    if (GetAnswer("Do you want to build a seek table for this file?", "y")
$
+
eq "y") {
</PRE>
+
        # mythcommflag takes --file for myth-originated files and
 +
        # --video for everything else. We assume it came from myth
 +
        # if it's a .nuv or if it's an mpeg where the name has that
 +
        # chanid_startime format
 +
        my $commflag = "mythcommflag --rebuild " .
 +
            ($showBase =~ /[.]nuv$/ || ($showBase =~ /[.]mpg$/ && $ssecond)
 +
            ? "--file" : "--video") .
 +
            " " . EscapeFilename($dir . "/" . $mythfile);
 +
        if (!$test_mode) {
 +
            system($commflag);
 +
            print "\n"; # cursor isn't always on a new line after commflagging
 +
        } else {
 +
            print("Test mode: exec would have done\n");
 +
            print("  Exec: '", $commflag, "'\n");
 +
        }
 +
    }
  
==Version Notes==
+
} ## foreach loop
Version 0.0.3 (installed with Ubuntu's Gutsy Gibbon) does not seem to work with the --file command. As a workaround use the --ext command.
 
  
==Prerequisites==
+
# vim:sw=4 ts=4 syn=off:
On Ubuntu 6.06, 7.04, and 9.04 at least, you must install the libtime-format-perl package.
+
</pre>
 +
}}
  
 
[[Category:Scripts]]
 
[[Category:Scripts]]

Latest revision as of 15:24, 22 January 2017

Important.png Note: The correct title of this article is myth.rebuilddatabase.pl. It appears incorrectly here due to technical restrictions.

Warning.png Warning: This script is deprecated. Please put any videos that are not recorded by your MythTV system into MythVideo.

Once upon a time, Greg Froese wrote a script to re-insert recordings that might have gone missing due to database corruption, etc.

For a while, it even lived in the MythTV source tree, under contrib/recovery, then contrib/maintenance. It was removed before 0.24.

There is an old copy in Myth.rebuilddb.pl, but after MythTV 0.25, it does something like:

 DBD::mysql::st execute failed: Field 'season' doesn't have a default value at ./myth.rebuilddb.pl line 424, <STDIN> line 8.
 Could not execute (insert into recorded (chanid, starttime, endtime, title, subtitle, description, hostname, basename, progstart, progend, storagegroup, recgroup) values ((?), (?), (?), (?), (?), (?), (?), (?), (?), (?), (?), (?)))

Here is a version that works with 0.28:

Script.png myth.rebuilddb.pl

#!/usr/bin/perl
## written by greg froese (g_froese@yahoo.com)
## install instructions by Robert Kulagowski (rkulagow@rocketmail.com)
##
## I had trouble maintaining my catalog of recordings when upgrading to
## cvs and from cvs to more recent cvs, so I wrote this.
##
##
## Here is what this program is supposed to do.
##
## It first scans through your myth database and displays all shows listed
## in the recorded table.
##
## It will then traverse the specified MythTV recordings directory 
## set with --dir /YOURMYTHDIR) and find all files with
## video extensions (set with --ext) and check if they appear in the
## database. If no entry exists you will be prompted for identifying
## information and a recording entry will be created.
##
## See the help message below for options.
##
## Use at your own risk. Standard gnu warranty, or lack therof,
## applies.

## To run:
## Ensure that the script is executable
## chmod a+x myth.rebuilddatabase.pl
## ./myth.rebuilddatabase.pl

## Change log:
## 9-19-2003: (awithers@anduin.com)
##  Anduin fights the urge to make code more readable (aka C like).  Battle
##  of urges ends in stalemate: code was reindented but not "changed" (much).
##  To make it a little less useless a contribution also did:
##    - added ability to grab title/subtitle/description from oldrecorded
##    - support for multiple backends (via separation of host and dbhost
##      and bothering to insert the host in the recorded table).
##    - removed dependency on File::Find::Rule stuff
##    - attempt to determine good default host name
##    - provide default for --dir from DB (if not provided)
##    - added --test_mode (for debugging, does everything except INSERT)
##    - added --try_default (good for when you must specify a command
##      line option but don't really need to)
##    - added --quick_run for those occasions where you just don't have
##      the sort of time to be sitting around hitting enter
##    - changed all the DB calls to use parameters (avoids escape issues,
##      and it looks better)
## 2017-01-22: Nigel
##    - Add default values for recorded.season, episode & inetref
##    - Remove dependence on Time::Format.pm
##    - Shorten all lines to 80char max
##    - Add a few more file extensions, esp: .ts

use strict;
use DBI;
use Getopt::Long;
use Sys::Hostname;
use File::Basename;
use Date::Parse;

my ($verbose, $dir);

my $show_existing = 0;
my $test_mode = 0;
my $quick_run = 0;
my $try_default = 0;

my $host = hostname;
my $dbhost = $host;
my $database = "mythconverg";
my $user = "mythtv";
my $pass = "mythtv";
my $ext = "{nuv,mpg,mpeg,mpeg2,ts,avi}";
my $file = "";
my @answers;
my $norename = 0;
my $storagegroup = "Default";

my $date_regx = qr/(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/;
my $db_date_regx = qr/(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)/;
my $channel_regx = qr/(\d\d\d\d)/;

sub GetAnswer {
    my ($prompt, $default) = @_;
    print $prompt;
    if ($default) {
        print " [", $default, "]";
    }
    print ": ";

    my $answer;
    if ($#answers >= 0) {
        $answer = shift @answers;
        print $answer, "\n";
    } else {
        chomp($answer = <STDIN>);
        $answer = $default if !$answer;
    }

    return $answer;
}

# there's a version of this in CPAN but I don't want to add another dependancy
sub EscapeFilename {
    my $fn = $_[0];
    # escape everything that's possibly dangerous
    $fn =~ s{([^[:alnum:]])}{\\\1}g;
    # it's embarrassing to escape / and . so put those back
    $fn =~ s{\\([/.])}{\1}g;
    return $fn;
}

# Remove need for Time::Format
sub UnixTimeToDBtime($)
{
    my ($unixTime) = @_;

    my ($sec,$min,$hour,$mday,$mon,$year,undef) = localtime($unixTime);

    return sprintf("%4d-%02d-%02d %02d:%02d:%02d",
			$year+1900, $mon+1, $mday, $hour, $min, $sec);
}

my $script_name = $0;

if ($0 =~ m/([^\/]+)$/) {
	$script_name = $1;
}

my $script_version = "0.0.3";

## get command line args

my $argc=@ARGV;
if ($argc == 0) {
  print "$script_name Version $script_version
    usage: $script_name [options]

    Where [options] is:
      --host          - hostname of this backend (default: \"$host\")
      --dbhost        - hostname or IP address of the mysql server
                        (default: \"$dbhost\")
      --user          - DBUSERNAME (default: \"$user\")
      --pass          - DBPASSWORD (default: \"$pass\")
      --database      - DATABASENAME (default: \"$database\")
      --show_existing - Dumps current recorded table.
      --dir           - path to recordings
      --group         - Storage Group to import as (default: \"Default\")
      --try_default   - Try to just run with the defaults.
      --quick_run     - don't prompt for title/subtitle/description just
                        use the default
      --test_mode     - do everything except update the database
      --ext           - file extensions to scan. csh/File::Glob syntax
                        is used (ie, --ext {mpg,avi,divx})
      --file          - specific file to import
      --answer        - command-line response to prompts (give as many
                        answers as you like)
      --norename      - don't rename file to myth convention

    Example 1:
      Assumption: The script is run on DB/backend machine.

        $script_name --try_default

    Example 2:
      Assumption: The script is run on a backend other than the DB host.
		
        $script_name --dbhost=mydbserver

    Example 3:
      Import one specific file and supply first few answers.

        $script_name --file MyVideo.avi --answer y \\
                     --answer 1041 --answer \"My Video\"

    The script chooses reasonable defaults for all values so it's possible
    to do a quick import of a single video by taking input from null:

        $script_name --file MyVideo.avi < /dev/null

    this also works with multiple videos but because record start time is
    synthesized from file modification time you have to be careful of
    possible collisions.
";
	exit(0);
}

GetOptions('verbose+'=>\$verbose,
		'database=s'=>\$database,
		'dbhost=s'=>\$dbhost,
		'host=s'=>\$host,
		'user=s'=>\$user,
		'pass=s'=>\$pass,
		'dir=s'=>\$dir,
		'group=s'=>\$storagegroup,
		'show_existing|se'=>\$show_existing,
		'try_default|td'=>\$try_default,
		'quick_run|qr'=>\$quick_run,
		'test_mode|t|tm'=>\$test_mode,
		'ext=s'=>\$ext,
		'file=s'=>\$file,
		'answer=s'=>\@answers,	# =s{,} would be nice,
					# but isn't implemented widely
		'norename'=>\$norename
		);

my $dbh = DBI->connect("dbi:mysql:database=$database:host=$dbhost",
			"$user","$pass")
		or die "Cannot connect to database ($!)\n";

my ($starttime, $endtime, $title, $subtitle, $channel,
    $description, $season, $episode, $recgroup);
my ($syear, $smonth, $sday, $shour, $sminute, $ssecond,
    $eyear, $emonth, $eday, $ehour, $eminute, $esecond);

my $q = "";
my $sth;

if (!$dir) {
	print("Error: No recordings directory specified.\n");
	print("       You must use the --dir option",
		" to specify a recording directory to use.\n");
	exit 1;
}

# remove trailing slash
$dir =~ s/\/$//;

if ($show_existing) {
	$q = "select title, subtitle, starttime, endtime, chanid, recgroup"
		. " from recorded order by starttime";
	$sth = $dbh->prepare($q);
	$sth->execute or die "Could not execute ($q)\n";

	print "\nYour myth database ($database) is reporting"
		. " the following programs as being recorded:\n\n";

	while (my @row=$sth->fetchrow_array) {
		$title = $row[0];
		$subtitle = $row[1];
		$starttime = $row[2];
		$endtime = $row[3];
		$channel = $row[4];
		$recgroup = $row[5];

## get the pieces of the time
		if ($starttime =~ m/$db_date_regx/) {
			($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
				($1, $2, $3, $4, $5, $6);
		}

		if ($endtime =~ m/$db_date_regx/) {
			($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
				($1, $2, $3, $4, $5, $6);
		}

		print "Channel:    $channel\n";
		print "Start time: $smonth/$sday/$syear"
			. " - $shour:$sminute:$ssecond\n";
		print "End time:   $emonth/$eday/$eyear"
			. " - $ehour:$eminute:$esecond\n";
		print "Title:      $title\n";
		print "Subtitle:   $subtitle\n";
		print "RecGroup:   $recgroup\n\n";
	}
}

print "\nThese are the files stored in ($dir) and will be checked against\n";
print "your database to see if the exist."
	. "  If they do not, you will be prompted\n";
print "for a title and subtitle of the entry,"
	. " and a record will be created.\n\n";

my @files = $file ? ($dir . "/" . $file) : glob("$dir/*.$ext");
print "@files\n";

foreach my $show (@files) {
    my $showBase = basename($show);

    my $cnt = $dbh->selectrow_array("select count(*) from recorded where"
					. " basename=(?)", undef, $showBase);

    my $found_title;

    if ($cnt gt 0) {
        $found_title = $dbh->selectrow_array("select title from recorded where"
						. " basename=(?)",
						undef, $showBase);
    }

    if ($found_title) {
        print("Found a match between file and database\n");
        print("    File: '$show'\n");
        print("    Title: '$found_title'\n");

        # use this so the stuff below doesn't have to be indented
        next;
    }

    print("Unknown file $show found.\n");
    next unless GetAnswer("Do you want to import?", "y") eq "y";


    # normal case: import file into the database

    my ($channel, $syear, $smonth, $sday, $shour, $sminute, $ssecond,
        $eyear, $emonth, $eday, $ehour, $eminute, $esecond);
    my ($starttime, $duration, $endtime);
    my ($mythfile);

    # filename varies depending on when the recording was
    # created. Gleam as much as possible from the name.

    if ($showBase =~ m/$channel_regx\_/) {
        $channel = $1;
    } else {
        $channel = $dbh->selectrow_array("select min(chanid) from channel");
    }

    if ($showBase =~ m/$channel_regx\_$date_regx\./) {
        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
            ($2, $3, $4, $5, $6, $7);
    }

    if ($showBase =~ m/$channel_regx\_$date_regx\_$date_regx/) {
        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
            ($2, $3, $4, $5, $6, $7);
        ($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
            ($8, $9, $10, $11, $12, $13);
    }

    my $guess_title = $showBase;
    $guess_title =~ s/[.][^\.]*$//;
    $guess_title =~ s/_/ /g;

    my $guess_subtitle = "";
    my $guess_description = "Recovered file " . $showBase;

    # have enough to look for an past recording?
    if ($ssecond) {
        print "Checking for a recording...\n";
        $starttime = "$syear$smonth$sday$shour$sminute$ssecond";

        my $guess = "select title, subtitle, description from oldrecorded"
			. " where chanid=(?) and starttime=(?)";
        $sth = $dbh->prepare($guess);
        $sth->execute($channel, $starttime)
            or die "Could not execute ($guess)\n";

        if (my @row = $sth->fetchrow_array) {
            $guess_title = $row[0];
            $guess_subtitle = $row[1];
            $guess_description = $row[2];
        }

        print "Found an orphaned file, initializing database record\n";
        print "Channel:    $channel\n";
        print "Start time: $smonth/$sday/$syear - $shour:$sminute:$ssecond\n";
        print "End time:   $emonth/$eday/$eyear - $ehour:$eminute:$esecond\n";
    }

    # what about checking for guide data?
    if($guess_description =~ /^Recovered file/) {
        print "Checking for guide data...\n";
        my $guess = "select title, subtitle, description from program where " .
                    "chanid='$channel' and " .
                    "starttime='$syear-$smonth-$sday $shour:$sminute:$ssecond'";
        $sth = $dbh->prepare($guess);
        $sth->execute()
            or die "Could not execute ($guess)\n";

        if (my @row = $sth->fetchrow_array) {
            $guess_title = $row[0];
            $guess_subtitle = $row[1];
            $guess_description = $row[2];
            print "Using guide data information for defaults\n";
        }
    }

    my $newtitle = $guess_title;
    my $newsubtitle = $guess_subtitle;
    my $newdescription = $guess_description;

    if (!$starttime) {
        # use file time if we can't infer time from name
	$starttime = &UnixTimeToDBtime((stat $show)[9]);
    }

    if ($quick_run) {

        print("QuickRun defaults:\n");
        print("        title: '$newtitle'\n");
        print("     subtitle: '$newsubtitle'\n");
        print("  description: '$newdescription'\n");

	$recgroup = "Default";
	$season   = $episode = 0;

    } else {

        $channel  = GetAnswer("Enter channel", $channel);
        $newtitle = GetAnswer("... title", $newtitle);
        $season   = GetAnswer("... Season",  "0");
        $episode  = GetAnswer("... Episode", "0");
        $newsubtitle    = GetAnswer("... subtitle", $newsubtitle);
        $newdescription = GetAnswer("Description", $newdescription);
        $starttime      = GetAnswer("... start time (YYYY-MM-DD HH:MM:SS)",
					$starttime);
        $recgroup       = GetAnswer("... Recording Group", "Default");
    }

    if ($endtime) {
        $duration = (str2time($endtime) - str2time($starttime)) / 60;
    } else {
        $duration = "60";
    }
    $duration = GetAnswer("... duration (in minutes)", $duration);
    $endtime = &UnixTimeToDBtime(str2time($starttime) + $duration * 60);

    if ($norename) {
        $mythfile = $showBase;
    } else {
        my ($ext) = $showBase =~ /([^\.]*)$/;
        my $time1 = $starttime;
        $time1 =~ s/[ \-:]//g;
        $mythfile = sprintf("%s_%s.%s", $channel, $time1, $ext);
    }

    my $sql = "insert into recorded (chanid, starttime, endtime, title,"
		. " subtitle, description, hostname, basename, progstart,"
		. " progend, storagegroup, recgroup, season, episode, inetref)"
		. " values ((?), (?), (?), (?), (?), (?), (?),"
		. " (?), (?), (?), (?), (?), (?), (?), '')";

    if ($test_mode) {

        $sql =~ s/\(\?\)/"%s"/g;
        my $statement = sprintf($sql, $channel, $starttime, $endtime, $newtitle,
                                $newsubtitle, $newdescription, $host, $mythfile,
                                $starttime, $endtime, $storagegroup, $recgroup,
				$season, $episode);
        print("Test mode: insert would have been been:\n");
        print($statement, ";\n");

    } else {

        $sth = $dbh->prepare($sql);
        $sth->execute($channel, $starttime, $endtime, $newtitle,
                      $newsubtitle, $newdescription, $host, $mythfile,
                      $starttime, $endtime, $storagegroup, $recgroup,
		      $season, $episode)
            or die "Could not execute ($sql)\n";

        if ($mythfile ne $showBase) {
            rename($show, $dir. "/" . $mythfile);
        }

    }

    
    print("Building a seek table should improve FF/RW and"
		. " JUMP functions when watching this video\n");

    if (GetAnswer("Do you want to build a seek table for this file?", "y")
		eq "y") {
        # mythcommflag takes --file for myth-originated files and
        # --video for everything else. We assume it came from myth
        # if it's a .nuv or if it's an mpeg where the name has that
        # chanid_startime format
        my $commflag = "mythcommflag --rebuild " .
            ($showBase =~ /[.]nuv$/ || ($showBase =~ /[.]mpg$/ && $ssecond)
             ? "--file" : "--video") .
             " " . EscapeFilename($dir . "/" . $mythfile);
        if (!$test_mode) {
            system($commflag);
            print "\n"; # cursor isn't always on a new line after commflagging
        } else { 
            print("Test mode: exec would have done\n"); 
            print("  Exec: '", $commflag, "'\n");
        }
    }

} ## foreach loop

# vim:sw=4 ts=4 syn=off: