path: root/abs/mv-core/MythVantage-system/imdb-bulk-update.pl

#!/usr/bin/perl
################################################################################
# Wrapper for imdb.pl tool for bulk movie metadata updates for mythdora        #
#                                                                              #
# 08/28/2007                                                                   #
# Ryan Pisani <mythdora@thepisanis.com>                                        #
################################################################################
#Parts of this were adopted from imdbbatchupdater.pl which in turn uses much   #
#of the original imdb.pl. This is a simpler wrapper to maintain and it utilizes#
#the standard imdb.pl shipped with mythvideo.                                  #
################################################################################
#version: 1.0 : Initial roll-out works with imdb.pl 1.3                        #
#version: 1.1 : Added some further error checking                              #
#version: 1.2 : Added -Manual flag for manual entering of IMDBIDs              #
#version: 1.3 : Added -fileup metadata update for newly added/removed movies   #
#version: 1.4 : Duplicate menu will default to first entry in the returned     #
#               list.                                                          #
#               Added -Exclude flag to mark any items with non-imdb match with #
#               a bogus imdb number to be skipped in later searches.           #
#               Added -help for extended examples in usage                     #
#version: 1.5 : Added -Folder flag to overide videopath for new video search   #
#               Fixed hash sort of new files so that entries with more than 1  #
#               entry in the DB will not be "true" in new check.               #
#               Fixed a bug in the newvideo find where multiples were added    #
#               Fixed a bug in the newvideo find where multiples were added    #
#version:1.5.1: Added showlevel fix in -Fileup to fix incorrect default showlvl# 
#version: 1.6 : Found bug in -Title lookups where title was updated blank      # 
################################################################################
use strict;
use DBI;
use Getopt::Long;
use File::Find;
use File::Basename;
use Sys::Hostname;

#Set Global Path For Execution
$ENV{PATH}="/bin:/usr/sbin:/usr/bin:/sbin";

#Defaults & global declarations here
my $dbhost = '127.0.0.1';
my $db = 'mythconverg';
my $dbuser = 'mythtv';
my $dbpass = 'mythtv';
my $imdbnum;  		#imdbnum set from imdb.pl
my $movie;  		#movie title set from query
my $intid;  		#incremental id for videometadata table
my @FAILEDLOOKUP;	#array to store failed lookups
my $imdb= '/usr/local/share/mythtv/mythvideo/scripts/imdb.pl';
my $posters; 		#coverart directory
my $new;		#-N flag
my $all;		#-A flag
my $sqlstat;		#used to store sql statments
my $stitle;		#grabbed from input for title search
my $dup;		#set if dupskip is on
my $manual;		#set if manual is on
my $host = hostname;	#the executing hosts machine name
my $fileup;		#set if fileup is on
my $exclude;		#set if exclude is on
my $currentimdb;	#dbs current imdb number before lookup is set
my $help;  		#pass to usage if help flag i set for extended
my $folder; 		#set if -Folder is set to overide DB for Videopath
my $title;		#Global Title to carry 


GetOptions("Posters=s"=>\$posters,
	   "A"=>\$all,
	   "N"=>\$new,
	   "Fileup"=>\$fileup,
	   "help"=>\$help,
	   "Exclude"=>\$exclude,
	   "Manual"=>\$manual,
	   "Dupskip"=>\$dup,
	   "Title=s"=>\$stitle,
	   "Folder=s"=>\$folder,
           "Host=s"=>\$dbhost,
           "User=s"=>\$dbuser,
           "Imdb=s"=>\$imdb,
           "Password=s"=>\$dbpass);


if ( $help eq '1' ) {

	#Show extended usage
	usage($help);

}

#One quick sanity check here for anyone looking to -A and -Exclude together

if ( $exclude eq '1' && $all eq '1' ) {

print <<STOP;

**********************************Warning***************************************

	-Exclude and -A are not recommended to be used together. If they are 
        used together you may find yourself seriously pissed off. 

	-Exclude and -A will override any you may have previously entered in 
        manually because IMDB lookup may have failed. Furthermore the files 
        will go back to no-data and excluded from future searches.

	It is in your best interest to use -Exclude only with -N

	You have been warned. 

**********************************Warning***************************************

Hit [Enter] to exit, or type 'go' to continue with -Exclude and -A:
STOP

chomp(my $choice=<STDIN>);

	if ( "$choice" ne "go" ) {

		exit;

	}

}


#if the file update flag is set we'll update the db videometadata before continuing
if ( $fileup eq '1' ) {

        findnewvids($folder);

}

#Now parse sql options in an order that we care about
if ( $stitle ne "" ) {

	$sqlstat = "select inetref, filename, intid from videometadata where title like \"%$stitle%\"";

}

elsif ( $all eq '1') {

	$sqlstat = "select inetref, filename, intId from videometadata order by title";

}

elsif ( $new eq '1' ) {

	$sqlstat = "select inetref, filename, intId, title from videometadata where inetref=\"00000000\" order by title";

}

else {

	usage();

}	


#Check to see if posters have been defined if not grab from db
if ( $posters eq "" ) {

	$posters=getposter();

}

#Check for the posters directory & perms
if ( ! -d "$posters" || ! -w "$posters" ) {
	
	die "\nError: $posters doesn't exist or is not writable\n\n";

}


#Check for imdb.pl script or the one defined
if ( ! -x "$imdb" ) {

	die "Error: $imdb.pl is not executable\n";

}


#print "$sqlstat\n";

#Prepare the sql connection 
my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

my $sth = $dbh->prepare($sqlstat);

$sth->execute || die  "SQL statement failed";

while (my @row=$sth->fetchrow_array) {

	#Grab current imdb number in DB
	chomp($currentimdb = "$row[0]");

	#Grab incremental id in DB for rewrite
	chomp($intid = "$row[2]");

        lc($movie = `basename "$row[1]"`);

	#Grab file id in DB for search
	chomp($movie);

	#Grab title data 
	chomp(my $title= "$row[3]");

	sep();

	#print "current:$currentimdb , intid:$intid, movie: $movie, title: $title\n";
	#check to see if -Manual flag is set
	if ( $manual ne '1' ) {

		print "Searching for IMDB movieID for $movie\n";
		chomp(($imdbnum)=(split /:/, `$imdb -M "$movie"`) [0] ); 
		
	}	
	else {

		print "Enter the IMDB movieID for $movie\n";
                print "Enter the corresponding imdb number([Enter] to skip):";
		chomp($imdbnum=<STDIN>);
	}

	#We'll check the imdb ids here. If they're longer than expected we assume
	#it to be more than one return
	if (length($imdbnum) > '8') {

		#Check the multiple entries and skip if flag is set
		if ($dup ne '1') {

			my @TITLES=`$imdb -M "$movie"`; 

			print "More than one match for $movie exists at IMDB\n";
	
			for (my $i=0;$i<=$#TITLES;$i++) {

				print "$i) $TITLES[$i]";

			}

                        print "Select a title ([0] Default, [x] to skip, [m] manual imdb number):";
			chomp(my $choice=<STDIN>);

			#print "my choice $choice\n";
			if ($choice eq "") {

				$choice="0";
				chomp($imdbnum=(split /:/, $TITLES[$choice]) [0]);
			}
				
			elsif ($choice eq "x") {

				$imdbnum="";
	                	push(@FAILEDLOOKUP, "$movie");


			}	

			elsif ($choice =~ /[0-9]/) {

				chomp($imdbnum=(split /:/, $TITLES[$choice]) [0]);
				#print $imdbnum;

			}

			elsif ($choice eq "m") {

				print "Enter IMDB number: ";
				chomp($imdbnum=<STDIN>);
			}

			else { 

				die "Not a valid selection\n";

			}
		}
		else {

			$imdbnum="";
			push(@FAILEDLOOKUP, "$movie");
		}

	}

	#Else check for failed lookups and -Exclude flag
	elsif ( "$imdbnum" eq "" && "$exclude" eq "1" && "$currentimdb" eq "00000000" ) {
		
		#We set a new bogus one to be ignored in later -N searches
		$imdbnum="99999999";

		push(@FAILEDLOOKUP, "$movie");

	}
		
	
	#Log the failed lookups here for later
	elsif ( "$imdbnum" eq "" ) {

		push(@FAILEDLOOKUP, "$movie");

		#print $#FAILEDLOOKUP;

	}
		
		
	
	#now check for imdbnumber from query
	if ( "$imdbnum" ne "" ) {
	
		my $coverfile;
		my $year;
		my $director;
		my $plot;
		my $userrating;
		my $rating;
		my $time;

		#If bogus is set do this first
		if ("$imdbnum" eq "99999999") {

			$coverfile="No Cover";
			$year="1895";
			$director="Unknown";
			$plot="Unknown";
			$userrating="0";
			$rating="NR";
			$time="0";
		}

		else {

			#Now lets fine the cover image
			#print "Going to use $imdb -P $imdbnum\n";
			chomp(my $cover=`$imdb -P "$imdbnum"`);
			#print "$cover";

			if ($cover =~ /jpg/) {

				$coverfile="$posters/$imdbnum.jpg";
				print "Retrieving $cover\n";
				system("wget $cover -O $coverfile >/dev/null 2>&1");
				
			}	
			else {

	        		$coverfile="No Cover";	

			}

	
			#Now get the rest of the imdb data
			undef my @TEMPDATA;
			@TEMPDATA=`$imdb -D $imdbnum`;
			chomp(($title)=(split /Title:/, $TEMPDATA[0])[1]);
			$title=~s/"//g;
			chomp(($year)=(split /Year:/, $TEMPDATA[1])[1]);
			chomp(($director)=(split /Director:/, $TEMPDATA[2])[1]);
			chomp(($plot)=(split /Plot:/, $TEMPDATA[3])[1]);
			$plot=~s/"//g;
			chomp(($userrating)=(split /UserRating:/, $TEMPDATA[4])[1]);
			chomp(($rating)=(split /MovieRating:/, $TEMPDATA[5])[1]);
			chomp(($time)=(split /Runtime:/, $TEMPDATA[6])[1]);

			#print $#TEMPDATA;
			#print "$title\n $year\n $director\n $plot\n $userrating\n $rating\n $time\n";
		}

	

		#Now let's write that data into the db
		print "Updating videometadata with new information\n";
		my $sqlstat="update videometadata set title=\"$title\", year=\"$year\", director=\"$director\", plot=\"$plot\", userrating=\"$userrating\", coverfile=\"$coverfile\", rating=\"$rating\", length=\"$time\", inetref=\"$imdbnum\" where intid=\"$intid\"";	


		my $sth = $dbh->prepare($sqlstat);

		$sth->execute || die  "SQL statement failed\n";

	
	}	
	
}

if ( $#FAILEDLOOKUP >= 0 ) {

print "\n\nThe following videos had imdb lookup errors:\n @FAILEDLOOKUP\n";

}


sub usage {

	my $help=$_[0];
	my $comm=basename($0);

print <<STOP;

usage: $comm -[Required] -[Optional] -[Host] parameters

   Required parameters:
     -A Get IMDB data for ALL movies in database
     -N Get IMDB data for movies with no IMDBID in database
     -Title Get IMDB data for movie(s) matching a quoted pattern

   Optional parameters:
     -Exclude Mark entries with no result with imdb placeholder (DEFAULT: OFF)
     -Fileup Search for new/removed videos and update database (DEFAULT: OFF)
     -Manual Query for manual IMDB number for each title (DEFAULT: OFF)
     -Dupskip Ignore the videos that find multiple imdb results (DEFAULT: OFF)

    Host parameters:
     -Host Specify a database host (DEFAULT:$dbhost)
     -User Specify a database user (DEFAULT:$dbuser)
     -Password Specify a database password (DEFAULT:$dbpass)
     -Imdb Specify path to a working imdb.pl (DEFAULT:$imdb)
     -Posters Specify a cover art directory if different from entry in DB
     -Folder Specify a video folder to search if differs from entry in DB
   
   Example:
     $comm -N -Host myhostname -User mythuser -Password mythpass 

   Use $comm -help for extended examples
STOP

if ( $help eq '1' ) {
print <<STOP;

   New file check with IMDB lookup and non-local DB:
     $comm -N -Fileup -Host 192.168.1.50
  
   New file check with IMDB lookup and future exclusion for non-match:
     $comm -N -Fileup -Exclude 

   New file check, IMDB lookup for ALL movies, skipping multiple IMDB returns:
     $comm -A -Fileup -Dupskip
   
   Title search with Manual IMDB entry
     $comm -Title "Austin Powers" -Manual
STOP
}
exit;
}


#Get Posters directory from host if possible out of settings
sub getposter {

	my $sqlstat="select data from settings where hostname=\'$host\' and value=\'VideoArtworkDir\'";

	my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass") || die  usage();

	my $sth = $dbh->prepare($sqlstat); 

	$sth->execute || die  "SQL statement failed";

	chomp(my $posters=$sth->fetchrow_array);

	return($posters);

}

#Routine to locate new & removed videos and update videometadata accordingly
sub findnewvids {

	my $folder=$_[0]; 
	my $videopath;

        sep();


	if ( $folder eq "" ) {

        print "Retrieving Video Path for this $host from $db.settings\n";

        #First get video directory path from settings database for exectuting host
        my $sqlstat="select data from settings where hostname=\'$host\' and value=\'VideoStartupDir\'";

        my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

        my $sth = $dbh->prepare($sqlstat);

        $sth->execute || die  "SQL statement failed";

        chomp($videopath=$sth->fetchrow_array);

	}
	else {
		print "Setting Video Path with manual override to $folder\n";
		chomp($videopath="$folder");

	}
        sep(1);

        #Now let's get the ignored file types so that we can truly ignore any overrides
        print "Retrieving ignored files extensions from $db.videometadata\n";

        $sqlstat = "select extension from videotypes where f_ignore=\"1\" order by extension";

        my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

        my $sth = $dbh->prepare($sqlstat);

        $sth->execute || die  "SQL statement failed";

        my @ITYPES;
        #put the ignored extensions found in the DB into an array
        while (my @row=$sth->fetchrow_array) {

                        push(@ITYPES,"@row");

        }

        sep(1);
        #Now look at allowed extensions and create an array to use when finding files
        #We'll append these to a list of pre-defined ones below

        print "Retrieving allowable file extensions from $db.videometadata\n";

        $sqlstat = "select extension from videotypes where f_ignore=\"0\" order by extension";

        my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

        my $sth = $dbh->prepare($sqlstat);

        $sth->execute || die  "SQL statement failed";


        #Array of commonly allowed video extensions
        my @TYPES=(".wmv",".avi",".vob",".iso",".bin",".mpeg",".mpg",".mp4",".mov");

        #put the extensions found in the DB into an array
        while (my @row=$sth->fetchrow_array) {

                if ( "@TYPES" !~ /@row/ ) {

                        push(@TYPES,".@row");

                }


        }

        my @FTYPES;

        #Pull any ignored extensions out of final file type search string
        foreach (@TYPES) {

                if ( "@ITYPES" !~ /$_/ ) {

                        push(@FTYPES,"($_)\\Z|");

                }


        }

        my $types=join"",@FTYPES;

        #Take the last | off of the search string
        chop($types);

        sep(1);

        #Search for all files that match our allow file extension patterns as defined in $types
        my @FILES;
        find (sub{

                my $file=$File::Find::name ;
                if ( $file =~ /$types/i) {
                        push(@FILES,"$file");
                        #print "$file\n";
                }

        }, "$videopath");

        @FILES=sort(@FILES);
	
	my $totalfiles="$#FILES" + 1;
	
	if ($totalfiles gt '1') {

        	print "Found $totalfiles total videos in $videopath\n";
        	sep(1);

	}
	else {

		die "No video files were found in $videopath\n";
	

	}

        #Now lets see what's in videometadata
        print "Retrieving a list of known videos from $db.videometadata\n";

        sep(1);

        $sqlstat = "select filename from videometadata where filename like \"%$videopath%\" order by filename";

        my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

        my $sth = $dbh->prepare($sqlstat);

        $sth->execute || die  "SQL statement failed";

        #put those extensions found in the DB into an array

        my @LISTINDB;
        while (my @row=$sth->fetchrow_array) {

                push(@LISTINDB,"@row");

        }

        print "Scrubbing the database for any videos that no longer exist in $videopath\n";

        my @FILESINDB;  #Database entries with an actual file associated
        #Now scrub database entries for any files that are no longer present
        foreach (@LISTINDB) {

                if (! -e "$_") {

                      print "\nRemoving entry for $_ from database\n";

                      $sqlstat = "delete from videometadata where filename=\"$_\"";

                      my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

                      my $sth = $dbh->prepare($sqlstat);

                      $sth->execute || die  "SQL statement failed";

                }
                else {

                        #Add to new scrubbed list
                        push(@FILESINDB, "$_");

                }



        }

        sep();


        my %count =();

        my @FILESDIFF;
        #Now well check to see what files differ between DB entries the Data
        foreach my $element (@FILESINDB, @FILES) {

                $count{$element}++;

        }
        foreach my $element (keys %count) {

                if ( $count{$element} < 2 ) {

                        push(@FILESDIFF, $element);
			print "$element\n";

                }
        }

        #Now we tell the user how many updates to be made
	my $totaldiff="$#FILESDIFF" + 1;

	if ($totaldiff gt '0') {

        	print "Found a total of $totaldiff to be added to database\n";
        	sep (1);
	
	}
	else {

		print "Found no new videos to be added to database\n";

	}

        #Now we need to grab just a title and populate the db with
        foreach (@FILESDIFF) {

                my $title=basename($_,@TYPES);

                print "\nAdding entry for $_ to $db.videometadata\n";

                $sqlstat = "insert into videometadata (title,director,rating,inetref,coverfile,filename,showlevel) values(\"$title\",\"Unknown\",\"NR\",\"00000000\",\"No Cover\",\"$_\",\"1\");";

                my $dbh = DBI->connect("dbi:mysql:$db:$dbhost","$dbuser","$dbpass");

                my $sth = $dbh->prepare($sqlstat);

                $sth->execute || die  "SQL statement failed";

        }

        #print "@FILESDIFF\n";



}




#Prints a easy on the eye separator
sub sep {

	if (defined $_[0]) {	

		print "=============================================================================\n";
		sleep $_[0];
	}
	else {

		print "=============================================================================\n";

	}

}