Ticket #3404: myth.rebuilddatabase.pl

File myth.rebuilddatabase.pl, 14.0 KB (added by anonymous, 18 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: