Difference between revisions of "Myth.rebuilddatabase.pl"
From MythTV Official Wiki
Pestilence (talk | contribs) m (just added an Ubuntu version which needs an extra package to run this script.) |
Stevegoodey (talk | contribs) m (Typo) |
||
(7 intermediate revisions by 5 users not shown) | |||
Line 1: | Line 1: | ||
{{Wrongtitle|myth.rebuilddatabase.pl}} | {{Wrongtitle|myth.rebuilddatabase.pl}} | ||
− | + | {{Warning box|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: {{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: " | + | --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: " | + | (default: \"$dbhost\") |
− | --user - DBUSERNAME (default: " | + | --user - DBUSERNAME (default: \"$user\") |
− | --pass - DBPASSWORD (default: " | + | --pass - DBPASSWORD (default: \"$pass\") |
− | --database - DATABASENAME (default: " | + | --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. | ||
− | + | $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. | ||
− | + | ||
− | + | $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. | ||
− | + | $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: | ||
− | + | $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. | ||
− | + | "; | |
− | + | 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: |
− | + | </pre> | |
+ | }} | ||
[[Category:Scripts]] | [[Category:Scripts]] |
Latest revision as of 15:24, 22 January 2017
Note: The correct title of this article is myth.rebuilddatabase.pl. It appears incorrectly here due to technical restrictions.
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:
#!/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: