--- trunk/bin/BackupPC_updatedb	2005/06/23 12:22:21	8
+++ trunk/bin/BackupPC_updatedb	2005/07/10 22:56:43	14
@@ -1,5 +1,4 @@
-#!/usr/local/bin/perl
-$| = 1;
+#!/usr/local/bin/perl -w
 
 use strict;
 use DBI;
@@ -10,310 +9,319 @@
 use Getopt::Std;
 use constant BPC_FTYPE_DIR => 5;
 
+$|=1;
 
 my $hosts;
 my $bpc = BackupPC::Lib->new || die;
 my %Conf = $bpc->Conf();
 my $TopDir = $bpc->TopDir();
-my @beenThere = ();
+my $beenThere = {};
 
-my $dbh = DBI->connect("dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}", "", "", { RaiseError => 1, AutoCommit => 0 });
+my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
+
+my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
 
 my %opt;
 
-if ( !getopts("cdu", \%opt ) ) {
+if ( !getopts("cdm:", \%opt ) ) {
 	print STDERR <<EOF;
-usage: $0 (-c|-df|-u)
+usage: $0 [-c|-d] [-m num]
 
 Options:
-	-c	Create database on first use
-	-d	Delete database before import
-	-u	Update database (import new revisions)
+	-c	create database on first use
+	-d	delete database before import
+	-m num	import just num	increments for one host
 EOF
 	exit 1;
 }
 
 ###################################create tables############################3
 
-if ($opt{c})
-    {
-      print "creating database...";
+if ($opt{c}) {
+	print "creating tables...\n";
       
-      $dbh->do(
-	  q{
-	      create table hosts 
-		(   ID             INTEGER 		PRIMARY KEY,
-		    name 	     VARCHAR(30)      	NOT NULL,
-		    IP	     VARCHAR(20)	NOT NULL
+	$dbh->do(qq{
+		create table hosts (
+			ID	INTEGER 	PRIMARY KEY,
+			name	VARCHAR(30)	NOT NULL,
+			IP	VARCHAR(15)
 		);	      
-	  }
-      );
+	});
 	      
-      $dbh->do(
-	  q{
-	      create table shares 
-		(   ID             INTEGER           PRIMARY KEY,
-		    hostID         INTEGER           NOT NULL,
-		    name           VARCHAR(30)       NOT NULL,
-		    share          VARCHAR(200)      NOT NULL,
-		    localpath      VARCHAR(200)      
+	$dbh->do(qq{
+		create table shares (
+			ID	INTEGER		PRIMARY KEY,
+			hostID	INTEGER		NOT NULL references hosts(id),
+			name	VARCHAR(30)	NOT NULL,
+			share	VARCHAR(200)	NOT NULL,
+			localpath VARCHAR(200)      
 		);	      
-	  }
-      );
+	});
 	
-      $dbh->do(
-	  q{
-	      create table backups 
-		( hostID     INTEGER 		NOT NULL, 
-		    num	     INTEGER 		NOT NULL,
-		    date	     DATE, 
-		    type	     CHAR(1), 
-		    PRIMARY KEY(hostID, num) 
+	$dbh->do(qq{
+		create table backups (
+			hostID	INTEGER		NOT NULL references hosts(id),
+			num     INTEGER		NOT NULL,
+			date	DATE, 
+			type	CHAR(1), 
+			PRIMARY KEY(hostID, num) 
 		); 	      
-	  }
-      );
+	});
 
-      $dbh->do(
-	  q{
-	      create table dvds
-		( ID   	     INTEGER		PRIMARY KEY, 
-		    num	     INTEGER		NOT NULL,
-		    name	     VARCHAR(255)	NOT NULL,
-		    mjesto     VARCHAR(255)
+	$dbh->do(qq{
+		create table dvds (
+			ID	INTEGER		PRIMARY KEY, 
+			num     INTEGER		NOT NULL,
+			name	VARCHAR(255)	NOT NULL,
+			mjesto	VARCHAR(255)
 		);
-	  }
-      );
+	});
 
-      $dbh->do(
-	  q{     
-	      create table files
-		(   ID         INTEGER          NOT NULL PRIMARY KEY,  
-		    shareID    INTEGER		NOT NULL,
-		    backupNum  INTEGER		NOT NULL,
-		    name       VARCHAR(255)	NOT NULL,
-		    path       VARCHAR(255)	NOT NULL,
-		    fullpath   VARCHAR(255)	NOT NULL,
-		    date       TIMESTAMP        NOT NULL,
-		    type       INTEGER          NOT NULL,
-		    size       INTEGER          NOT NULL,
-		    dvdid      INTEGER		
+	$dbh->do(qq{     
+		create table files (
+			ID	INTEGER		NOT NULL PRIMARY KEY,  
+			shareID	INTEGER		NOT NULL references shares(id),
+			backupNum  INTEGER	NOT NULL references backups(id),
+			name       VARCHAR(255)	NOT NULL,
+			path       VARCHAR(255)	NOT NULL,
+			fullpath   VARCHAR(255)	NOT NULL,
+			date       TIMESTAMP	NOT NULL,
+			type       INTEGER	NOT NULL,
+			size       INTEGER	NOT NULL,
+			dvdid      INTEGER	references dvds(id)	
 		);
-	  }
-      );
-      print "done\n";
-  }
-
-if ($opt{d})
-  {
-      print("deleting db first...\n");
-      
-      $dbh->do(
-	  q{ DELETE FROM hosts; }
-	  );
-      $dbh->do(
-	  q{ DELETE FROM shares; }
-	  );
-      $dbh->do(
-	  q{ DELETE FROM files;}
-	  );
-      $dbh->do(
-	  q{ DELETE FROM dvds;}
-	  );
-      $dbh->do(
-	  q{ DELETE FROM backups; }
-	  );
-  }
+	});
+
+	print "creating indexes...\n";
+
+	foreach my $index (qw(
+		hosts_name
+		backups_hostID
+		backups_num
+		shares_hostID
+		shares_name
+		files_shareID
+		files_path
+		files_name
+		files_date
+		files_size
+	)) {
+		my ($table,$col) = split(/_/, $index);
+		$dbh->do(qq{ create index $index on $table($col) });
+	}
+
+
+}
+
+if ($opt{d}) {
+	print "deleting ";
+	foreach my $table (qw(hosts shares files dvds backups)) {
+		print "$table ";
+		$dbh->do(qq{ DELETE FROM $table });
+	}
+	print " done...\n";
+}
 
 #################################INSERT VALUES#############################
 
 # get hosts
 $hosts = $bpc->HostInfoRead();
-print Dumper($hosts);
 my $hostID;
 my $shareID;
-foreach my $host_key (keys %{$hosts})
-{
-  my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
-  my $backups;
-  my $sql; 
-
-  $sql = q{ SELECT ID FROM hosts WHERE name=? };
-  my $st = $dbh->prepare($sql);
-  $st->bind_param(1,$hosts->{$host_key}->{'host'});
-  $st->execute();
-  if (my $tmp = $st->fetchrow_hashref())
-      {
-	  $hostID = $tmp->{'ID'};
-      }
-    else
-      {
-	  $sql = q{ INSERT INTO hosts ( ID, name, IP) VALUES (NULL,"}.
-                    $hosts->{$host_key}->{'host'}."\", \"".
-                   $hosts->{$host_key}->{'ip'}."\");";
-
-          $dbh->do($sql);
-          $hostID = $dbh->func('last_insert_rowid');
-
-      }
-  $st->finish();
-  print("processing host ".$hosts->{$host_key}->{'host'}.":\n");
+
+my $sth;
+
+$sth->{insert_hosts} = $dbh->prepare(qq{
+INSERT INTO hosts (name, IP) VALUES (?,?)
+});
+
+$sth->{hosts_by_name} = $dbh->prepare(qq{
+SELECT ID FROM hosts WHERE name=?
+});
+
+$sth->{backups_broj} = $dbh->prepare(qq{
+SELECT COUNT(*)
+FROM backups
+WHERE hostID=? AND num=?
+});
+
+$sth->{insert_backups} = $dbh->prepare(qq{
+INSERT INTO backups (hostID, num, date, type)
+VALUES (?,?,?,?)
+});
+
+$sth->{insert_files} = $dbh->prepare(qq{
+INSERT INTO files
+	(shareID, backupNum, name, path, fullpath, date, type, size)
+	VALUES (?,?,?,?,?,?,?,?)
+});
+
+foreach my $host_key (keys %{$hosts}) {
+
+	my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
+
+	$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
+
+	unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
+		$sth->{insert_hosts}->execute(
+			$hosts->{$host_key}->{'host'},
+			$hosts->{$host_key}->{'ip'}
+		);
+
+		$hostID = $dbh->func('last_insert_rowid');
+	}
+
+	print("host ".$hosts->{$host_key}->{'host'}.": ");
  
-  # get backups for a host
-  my @backups = $bpc->BackupInfoRead($hostname);
-  foreach my $backup (@backups)
-  {
-    my $backupNum = $backup->{'num'};
-    my @backupShares = ();
+	# get backups for a host
+	my @backups = $bpc->BackupInfoRead($hostname);
+	print scalar @backups, " increments\n";
+
+	my $inc_nr = 0;
+
+	foreach my $backup (@backups) {
+		$inc_nr++;
+		last if ($opt{m} && $inc_nr > $opt{m});
+
+		my $backupNum = $backup->{'num'};
+		my @backupShares = ();
+
+		print $hosts->{$host_key}->{'host'},"\t$backupNum\n";
+
+		$sth->{backups_broj}->execute($hostID, $backupNum);
+		my ($broj) = $sth->{backups_broj}->fetchrow_array();
+		next if ($broj > 0);
+
+		$sth->{insert_backups}->execute(
+			$hostID,
+			$backupNum,
+			$backup->{'endTime'},
+			$backup->{'type'}
+		);
+		$dbh->commit();
 
-      
-    if ($opt{u})
-	{
-	    my $sql = q{
-		  SELECT COUNT(*) AS broj
-		  FROM backups
-		  WHERE hostID=? AND
-		        num=?
-		};
-	    
-	    my $st  = $dbh->prepare($sql);
-	    $st->bind_param(1,$hostID);
-	    $st->bind_param(2,$backupNum);
-	    $st->execute();
-	    my $tmp = $st->fetchrow_hashref();
-	    $st->finish();
-	    if ($tmp->{'broj'} > 0)
-	      {
-		  next;
-	      }
+		my $files = BackupPC::View->new($bpc, $hostname, \@backups);
+		foreach my $share ($files->shareList($backupNum)) {
+
+			print "\t$share";
+			$shareID = getShareID($share, $hostID, $hostname);
+		
+			my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
+			print " $nf/$f files $nd/$d dirs\n";
+			$dbh->commit();
+		}
 	}
-      
-    print "\tprocessing backup no. $backupNum...";
-    my $sql =     
-	q{ 
-	    INSERT INTO backups (hostID, num, date, type)
-	      VALUES
-	      (}.$hostID.",". $backupNum.q{, }.$backup->{'endTime'}.",\"". $backup->{'type'}.q{");
-	 }; 
-    $dbh->do($sql);
-
-    my $files = BackupPC::View->new($bpc, $hostname, \@backups);
-    @backupShares = $files->shareList($backupNum);
-    foreach my $share (@backupShares)
-    {      
-      my @flattenedFiles = ();
-      clearBeenThereCache();
-      print "\n\t\tprocessing share ".$share."...";
-      $shareID = getShareID($share, $hostID, $hostname);
-	
-      @flattenedFiles = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "");
-      print "done\n";
-      print "\t\tinserting data into db...";
-      foreach my $file (@flattenedFiles)
-	  {
-	      $dbh->do("INSERT INTO files(ID, shareID, backupNum, name, path, fullpath, date, type, size) VALUES "
-		  ."( NULL, $shareID, "
-                  .$backupNum.", \"" 
-		  .$file->{'fileName'}."\", \""
-		  .$file->{'relPath'}. "\", \""
-		  .$file->{'fullPath'}."\", "
-	          .$file->{'mtime'}.", "
-		  .$file->{'type'}.", "
-		  .$file->{'size'}.")"
-	          );
-	  }
-      print "done\n";
-    }
-  }
-  print "done.\n";
 }
+undef $sth;
 $dbh->commit();
 $dbh->disconnect();
 
+sub getShareID() {
 
-sub haveBeenThere
-{
-  my ($where) = @_;
-  
-  foreach my $st (@beenThere)
-  {
-    if ($where eq $st)
-    {
-      return 1;
-    }
-  }
-  
-  push(@beenThere, $where);
-  return 0;
-}
+	my ($share, $hostID, $hostname) = @_;
+
+	$sth->{share_id} ||= $dbh->prepare(qq{
+		SELECT ID FROM shares WHERE hostID=? AND name=?
+	});
+
+	$sth->{share_id}->execute($hostID,$share);
+
+	my ($id) = $sth->{share_id}->fetchrow_array();
+
+	return $id if (defined($id));
 
-sub clearBeenThereCache
-{
-  @beenThere = ();
+	$sth->{insert_share} ||= $dbh->prepare(qq{
+		INSERT INTO shares 
+			(hostID,name,share,localpath) 
+		VALUES (?,?,?,?)
+	});
+
+	$sth->{insert_share}->execute($hostID,$share, $hostname . $share,undef);
+	return $dbh->func('last_insert_rowid');      	
 }
 
-sub getShareID()
-  {
-      my ($share, $hostID, $hostname) = @_;
-      my $tmp;
-      
-      my $st = $dbh -> prepare(" SELECT shares.ID AS ID FROM shares WHERE hostID=? AND name=?");
-      $st -> execute($hostID,$share);
-      my $tmp = $st->fetchrow_hashref();
-      $st->finish();
-      if ($tmp)
-	{
-	    return $tmp->{'ID'};
-	}
-      my $sql = 
-	  q{
-	      INSERT INTO shares(ID,hostID,name,share,localpath) 
-		VALUES	 
-	       (NULL,}.	 		 
-	      "$hostID,\"$share\",\"//$hostname$share\",NULL);";	      
-      $dbh->do($sql);
-      return $dbh->func('last_insert_rowid');      	
-  }
+sub found_in_db {
+
+	my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
+
+	$sth->{file_in_db} ||= $dbh->prepare(qq{
+		SELECT count(*) FROM files
+		WHERE shareID = ? and
+			path = ? and 
+			name = ? and
+			date = ? and
+			size = ?
+	});
+
+	my @param = ($shareID,$path,$name,$date,$size);
+	$sth->{file_in_db}->execute(@param);
+	my ($rows) = $sth->{file_in_db}->fetchrow_array();
+	print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
+	return $rows;
+}
 
 ####################################################
 # recursing through filesystem structure and       #
 # and returning flattened files list               #
 ####################################################
-sub recurseDir
-{
-  my ($bpc, $hostname, $backups, $backupNo, $share, $dir) = @_;
-  my @ret = ();
-  my $files = BackupPC::View->new($bpc, $hostname, $backups);             
-  my $filesInBackup = $files->dirAttrib($backupNo, $share, $dir);
-  my $file_key = ""; 
+sub recurseDir($$$$$$$$) {
 
- 
-  # first, add all the entries in current directory
-  foreach $file_key (keys %{$filesInBackup})
-  {
-    push(@ret, {
-                 'fileName' => $file_key,
-                 'relPath'  => $filesInBackup->{$file_key}->{'relPath'},
-                 'fullPath' => $filesInBackup->{$file_key}->{'fullPath'},
-                 'sharePath'=> $filesInBackup->{$file_key}->{'sharePathM'},
-                 'size'	    => $filesInBackup->{$file_key}->{'size'},
-                 'mtime'    => $filesInBackup->{$file_key}->{'mtime'},
-                 'type'	    => $filesInBackup->{$file_key}->{'type'}
-               });  
-  }
-    
-  # then, recurse thru subfolders
-  foreach my $fold (@ret)
-  {
-   if ($fold->{'type'} == BPC_FTYPE_DIR &&
-       haveBeenThere($fold->{'relPath'}) != 1
-      )
-    {
-      
-      push(@ret, 
-           recurseDir($bpc, $hostname, $backups, $backupNo, $share, $fold->{'relPath'})
-          );
-    }
-  }
-  return @ret;
+	my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
+
+	my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
+
+	my $files = BackupPC::View->new($bpc, $hostname, $backups);             
+	my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
+
+	# first, add all the entries in current directory
+	foreach my $path_key (keys %{$filesInBackup}) {
+		my @data = (
+			$shareID,
+			$backupNum,
+			$path_key,
+			$filesInBackup->{$path_key}->{'relPath'},
+			$filesInBackup->{$path_key}->{'fullPath'},
+#			$filesInBackup->{$path_key}->{'sharePathM'},
+			$filesInBackup->{$path_key}->{'mtime'},
+			$filesInBackup->{$path_key}->{'type'},
+			$filesInBackup->{$path_key}->{'size'}
+		);
+
+		my $key = join(" ", (
+			$shareID,
+			$dir,
+			$path_key,
+			$filesInBackup->{$path_key}->{'mtime'},
+			$filesInBackup->{$path_key}->{'size'}
+		));
+
+
+		if (! $beenThere->{$key} && ! found_in_db(@data)) {
+			$sth->{'insert_files'}->execute(@data);
+			print STDERR "$key\n";
+			if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
+				$new_dirs++;
+			} else {
+				$new_files++;
+			}
+		}
+		$beenThere->{$key}++;
+
+		if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
+			$nr_dirs++;
+
+			my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID);
+
+			$nr_files += $f;
+			$new_files += $nf;
+			$nr_dirs += $d;
+			$new_dirs += $nd;
+
+		} else {
+			$nr_files++;
+		}
+	}
+
+	return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 }