Ticket #3404: myth.rebuilddatabase.pl

File myth.rebuilddatabase.pl, 14.0 KB (added by anonymous, 14 years ago)
Line 
1#!/usr/bin/perl
2## written by greg froese (g_froese@yahoo.com)
3## install instructions by Robert Kulagowski (rkulagow@rocketmail.com)
4##
5## I had trouble maintaining my catalog of recordings when upgrading to
6## cvs and from cvs to more recent cvs, so I wrote this.
7##
8##
9## Here is what this program is supposed to do.
10##
11## It first scans through your myth database and displays all shows listed
12## in the recorded table.
13##
14## It will then traverse your myth directory (queried from the myth
15## database or set with --dir /YOURMYTHDIR) and find all files with
16## video extensions (set with --ext) and check if they appear in the
17## database. If no entry exists you will be prompted for identifying
18## information and a recording entry will be created.
19##
20## See the help message below for options.
21##
22## Use at your own risk. Standard gnu warranty, or lack therof,
23## applies.
24
25## To run:
26## Ensure that the script is executable
27## chmod a+x myth.rebuilddatabase.pl
28## ./myth.rebuilddatabase.pl
29
30## Change log:
31## 9-19-2003: (awithers@anduin.com)
32##  Anduin fights the urge to make code more readable (aka C like).  Battle
33##  of urges ends in stalemate: code was reindented but not "changed" (much).
34##  To make it a little less useless a contribution also did:
35##    - added ability to grab title/subtitle/description from oldrecorded
36##    - support for multiple backends (via separation of host and dbhost
37##      and bothering to insert the host in the recorded table).
38##    - removed dependency on File::Find::Rule stuff
39##    - attempt to determine good default host name
40##    - provide default for --dir from DB (if not provided)
41##    - added --test_mode (for debugging, does everything except INSERT)
42##    - added --try_default (good for when you must specify a command
43##      line option but don't really need to)
44##    - added --quick_run for those occasions where you just don't have
45##      the sort of time to be sitting around hitting enter
46##    - changed all the DB calls to use parameters (avoids escape issues,
47##      and it looks better)
48
49use strict;
50use DBI;
51use Getopt::Long;
52use Sys::Hostname;
53use File::Basename;
54use Date::Parse;
55use Time::Format qw(time_format);
56
57my ($verbose, $dir);
58
59my $show_existing = 0;
60my $test_mode = 0;
61my $quick_run = 0;
62my $try_default = 0;
63
64my $host = hostname;
65my $dbhost = $host;
66my $database = "mythconverg";
67my $user = "mythtv";
68my $pass = "mythtv";
69my $ext = "{nuv,mpg,mpeg,avi}";
70my $file = "";
71my @answers;
72my $norename = 0;
73
74my $date_regx = qr/(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/;
75my $db_date_regx = qr/(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)/;
76my $channel_regx = qr/(\d\d\d\d)/;
77
78sub GetAnswer {
79    my ($prompt, $default) = @_;
80    print $prompt;
81    if ($default) {
82        print " [", $default, "]";
83    }
84    print ": ";
85
86    my $answer;
87    if ($#answers >= 0) {
88        $answer = shift @answers;
89        print $answer, "\n";
90    } else {
91        chomp($answer = <STDIN>);
92        $answer = $default if !$answer;
93    }
94
95    return $answer;
96}
97
98# there's a version of this in CPAN but I don't want to add another dependancy
99sub EscapeFilename {
100    my $fn = $_[0];
101    # escape everything that's possibly dangerous
102    $fn =~ s{([^[:alnum:]])}{\\\1}g;
103    # it's embarassing to escape / and . so put those back
104    $fn =~ s{\\([/.])}{\1}g;
105    return $fn;
106}
107
108my $script_name = $0;
109
110if ($0 =~ m/([^\/]+)$/) {
111        $script_name = $1;
112}
113
114my $script_version = "0.0.3";
115
116## get command line args
117
118my $argc=@ARGV;
119if ($argc == 0) {
120  print "$script_name Version $script_version
121    usage: $script_name [options]
122
123    Where [options] is:
124      --host          - hostname of this backend (default: \"$host\")
125      --dbhost        - hostname or IP address of the mysql server
126                        (default: \"$dbhost\")
127      --user          - DBUSERNAME (default: \"$user\")
128      --pass          - DBPASSWORD (default: \"$pass\")
129      --database      - DATABASENAME (default: \"$database\")
130      --show_existing - Dumps current recorded table.
131      --dir           - path to recordings (default: queried from db)
132      --try_default   - Try to just run with the defaults.
133      --quick_run     - don't prompt for title/subtitle/description just
134                        use the default
135      --test_mode     - do everything except update the database
136      --ext           - file extensions to scan. csh/File::Glob syntax
137                        is used (ie, --ext {mpg,avi,divx})
138      --file          - specific file to import
139      --answer        - command-line response to prompts (give as many
140                        answers as you like)
141      --norename      - don't rename file to myth convention
142
143    Example 1:
144      Assumption: The script is run on DB/backend machine.
145
146        $script_name --try_default
147
148    Example 2:
149      Assumption: The script is run on a backend other than the DB host.
150               
151        $script_name --dbhost=mydbserver
152
153    Example 3:
154      Import one specific file and supply first few answers.
155
156        $script_name --file MyVideo.avi --answer y \\
157                     --answer 1041 --answer \"My Video\"
158
159    The script chooses reasonable defaults for all values so it's possible
160    to do a quick import of a single video by taking input from null:
161
162        $script_name --file MyVideo.avi < /dev/null
163
164    this also works with multiple videos but because record start time is
165    synthesized from file modification time you have to be careful of
166    possible collisions.
167";
168        exit(0);
169}
170
171GetOptions('verbose+'=>\$verbose,
172                'database=s'=>\$database,
173                'dbhost=s'=>\$dbhost,
174                'host=s'=>\$host,
175                'user=s'=>\$user,
176                'pass=s'=>\$pass,
177                'dir=s'=>\$dir,
178                'show_existing|se'=>\$show_existing,
179                'try_default|td'=>\$try_default,
180                'quick_run|qr'=>\$quick_run,
181                'test_mode|t|tm'=>\$test_mode,
182                'ext=s'=>\$ext,
183                'file=s'=>\$file,
184        'answer=s'=>\@answers,    # =s{,} would be nice but isn't implemented widely
185                'norename'=>\$norename
186                );
187
188my $dbh = DBI->connect("dbi:mysql:database=$database:host=$dbhost",
189                "$user","$pass") or die "Cannot connect to database ($!)\n";
190
191my ($starttime, $endtime, $title, $subtitle, $channel, $description, $recgroup);
192my ($syear, $smonth, $sday, $shour, $sminute, $ssecond, $eyear, $emonth, $eday,
193                $ehour, $eminute, $esecond);
194
195my $q = "";
196my $sth;
197
198if (!$dir) {
199        my $dir_query = "select data from settings where value='RecordFilePrefix' and hostname=(?)";
200        $sth = $dbh->prepare($dir_query);
201        $sth->execute($host) or die "Could not execute ($dir_query)";
202        if (my @row = $sth->fetchrow_array) {
203                $dir = $row[0];
204        }
205}
206
207if (!$dir) {
208        print("Error: no directory found or specified\n");
209        exit 1;
210}
211
212# remove trailing slash
213$dir =~ s/\/$//;
214
215if ($show_existing) {
216        $q = "select title, subtitle, starttime, endtime, chanid, recgroup from recorded order by starttime";
217        $sth = $dbh->prepare($q);
218        $sth->execute or die "Could not execute ($q)\n";
219
220        print "\nYour myth database ($database) is reporting the following programs as being recorded:\n\n";
221
222        while (my @row=$sth->fetchrow_array) {
223                $title = $row[0];
224                $subtitle = $row[1];
225                $starttime = $row[2];
226                $endtime = $row[3];
227                $channel = $row[4];
228                $recgroup = $row[5];
229
230## get the pieces of the time
231                if ($starttime =~ m/$db_date_regx/) {
232                        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
233                                ($1, $2, $3, $4, $5, $6);
234                }
235
236                if ($endtime =~ m/$db_date_regx/) {
237                        ($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
238                                ($1, $2, $3, $4, $5, $6);
239                }
240
241##               print "Channel $channel\t$smonth/$sday/$syear $shour:$sminute:$ssecond - $ehour:$eminute:$esecond - $title ($subtitle)\n";
242                print "Channel:    $channel\n";
243                print "Start time: $smonth/$sday/$syear - $shour:$sminute:$ssecond\n";
244                print "End time:   $emonth/$eday/$eyear - $ehour:$eminute:$esecond\n";
245                print "Title:      $title\n";
246                print "Subtitle:   $subtitle\n\n";
247                print "Group:      $recgroup\n\n";
248        }
249}
250
251print "\nThese are the files stored in ($dir) and will be checked against\n";
252print "your database to see if the exist.  If they do not, you will be prompted\n";
253print "for a title and subtitle of the entry, and a record will be created.\n\n";
254
255my @files = $file ? ($dir . "/" . $file) : glob("$dir/*.$ext");
256print "@files\n";
257
258foreach my $show (@files) {
259    my $showBase = basename($show);
260
261    my $cnt = $dbh->selectrow_array("select count(*) from recorded where basename=(?)",
262                                    undef, $showBase);
263
264    my $found_title;
265
266    if ($cnt gt 0) {
267        $found_title = $dbh->selectrow_array("select title from recorded where basename=(?)",
268                                             undef, $showBase);
269    }
270
271    if ($found_title) {
272        print("Found a match between file and database\n");
273        print("    File: '$show'\n");
274        print("    Title: '$found_title'\n");
275
276        # use this so the stuff below doesn't have to be indented
277        next;
278    }
279
280    print("Unknown file $show found.\n");
281    next unless GetAnswer("Do you want to import?", "y") eq "y";
282
283
284    # normal case: import file into the database
285
286    my ($channel, $syear, $smonth, $sday, $shour, $sminute, $ssecond,
287        $eyear, $emonth, $eday, $ehour, $eminute, $esecond);
288    my ($starttime, $duration, $endtime);
289    my ($mythfile);
290
291    # filename varies depending on when the recording was
292    # created. Gleam as much as possible from the name.
293
294    if ($showBase =~ m/$channel_regx\_/) {
295        $channel = $1;
296    } else {
297        $channel = $dbh->selectrow_array("select min(chanid) from channel");
298    }
299
300    if ($showBase =~ m/$channel_regx\_$date_regx\./) {
301        ($syear, $smonth, $sday, $shour, $sminute, $ssecond) =
302            ($2, $3, $4, $5, $6, $7);
303    }
304
305    if ($showBase =~ m/$channel_regx\_$date_regx\_$date_regx/) {
306        ($eyear, $emonth, $eday, $ehour, $eminute, $esecond) =
307            ($8, $9, $10, $11, $12, $13);
308    }
309
310    my $guess_title = $showBase;
311    $guess_title =~ s/[.][^\.]*$//;
312    $guess_title =~ s/_/ /g;
313
314    my $guess_subtitle = "";
315    my $guess_description = "Recovered file " . $showBase;
316
317    # have enough to look for an past recording?
318    if ($ssecond) {
319        $starttime = "$syear$smonth$sday$shour$sminute$ssecond";
320
321        my $guess = "select title, subtitle, description from oldrecorded where chanid=(?) and starttime=(?)";
322        $sth = $dbh->prepare($guess);
323        $sth->execute($channel, $starttime)
324            or die "Could not execute ($guess)\n";
325
326        if (my @row = $sth->fetchrow_array) {
327            $guess_title = $row[0];
328            $guess_subtitle = $row[1];
329            $guess_description = $row[2];
330        }
331
332        print "Found an orphaned file, initializing database record\n";
333        print "Channel:    $channel\n";
334        print "Start time: $smonth/$sday/$syear - $shour:$sminute:$ssecond\n";
335        print "End time:   $emonth/$eday/$eyear - $ehour:$eminute:$esecond\n";
336    }
337
338    my $newtitle = $guess_title;
339    my $newsubtitle = $guess_subtitle;
340    my $newdescription = $guess_description;
341
342    if (!$starttime) {
343        # use file time if we can't infer time from name
344        $starttime = time_format("yyyy-mm{on}-dd hh:mm{in}:ss",
345                                 (stat($show))[9]);
346    }
347
348    if ($quick_run) {
349
350        print("QuickRun defaults:\n");
351        print("        title: '$newtitle'\n");
352        print("     subtitle: '$newsubtitle'\n");
353        print("  description: '$newdescription'\n");
354
355    } else {
356
357        $channel = GetAnswer("Enter channel", $channel);
358        $newtitle = GetAnswer("... title", $newtitle);
359        $newsubtitle = GetAnswer("... subtitle", $newsubtitle);
360        $newdescription = GetAnswer("Description", $newdescription);
361        $starttime = GetAnswer("... start time (YYYY-MM-DD HH:MM:SS)", $starttime);
362        $recgroup = GetAnswer("... Recording Group", "Default");
363
364        if ($endtime) {
365            $duration = (str2time($endtime) - str2time($starttime)) / 60;
366        } else {
367            $duration = "60";
368        }
369        $duration = GetAnswer("... duration (in minutes)", $duration);
370        $endtime = time_format("yyyy-mm{on}-dd hh:mm{in}:ss", str2time($starttime) + $duration * 60);
371
372    }
373
374    if ($norename) {
375        $mythfile = $showBase;
376    } else {
377        my ($ext) = $showBase =~ /([^\.]*)$/;
378        my $time1 = $starttime;
379        $time1 =~ s/[ \-:]//g;
380        $mythfile = sprintf("%s_%s.%s", $channel, $time1, $ext);
381    }
382
383    my $sql = "insert into recorded (chanid, starttime, endtime, title, subtitle, description, hostname, basename, progstart, progend, recgroup) values ((?), (?), (?), (?), (?), (?), (?), (?), (?), (?), (?))";
384
385    if ($test_mode) {
386
387        $sql =~ s/\(\?\)/"%s"/g;
388        my $statement = sprintf($sql, $channel, $starttime, $endtime, $newtitle,
389                                $newsubtitle, $newdescription, $host, $mythfile,
390                                $starttime, $endtime, $recgroup);
391        print("Test mode: insert would have been been:\n");
392        print($statement, ";\n");
393
394    } else {
395
396        $sth = $dbh->prepare($sql);
397        $sth->execute($channel, $starttime, $endtime, $newtitle,
398                      $newsubtitle, $newdescription, $host, $mythfile,
399                      $starttime, $endtime, $recgroup)
400            or die "Could not execute ($sql)\n";
401
402        if ($mythfile ne $showBase) {
403            rename($show, $dir. "/" . $mythfile);
404        }
405
406    }
407
408   
409    print("Building a seek table should improve FF/RW and JUMP functions when watching this video\n");
410
411    if (GetAnswer("Do you want to build a seek table for this file?", "y") eq "y") {
412        # mythcommflag takes --file for myth-originated files and
413        # --video for everything else. We assume it came from myth
414        # if it's a .nuv or if it's an mpeg where the name has that
415        # chanid_startime format
416        my $commflag = "mythcommflag --rebuild " .
417            ($showBase =~ /[.]nuv$/ || ($showBase =~ /[.]mpg$/ && $ssecond)
418             ? "--file" : "--video") .
419             " " . EscapeFilename($dir . "/" . $mythfile);
420        if (!$test_mode) {
421            system($commflag);
422            print "\n"; # cursor isn't always on a new line after commflagging
423        } else {
424            print("Test mode: exec would have done\n");
425            print("  Exec: '", $commflag, "'\n");
426        }
427    }
428
429} ## foreach loop
430
431# vim:sw=4 ts=4 syn=off: