Myth.rebuilddatabase.pl

From MythTV Official Wiki
Jump to: navigation, search

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: