--- trunk/bin/BackupPC_updatedb 2005/07/31 18:10:45 32 +++ trunk/bin/BackupPC_updatedb 2005/08/21 15:59:55 62 @@ -1,37 +1,59 @@ #!/usr/local/bin/perl -w use strict; -use DBI; use lib "__INSTALLDIR__/lib"; + +use DBI; use BackupPC::Lib; use BackupPC::View; use Data::Dumper; use Getopt::Std; +use Time::HiRes qw/time/; +use File::Pid; +use POSIX qw/strftime/; + use constant BPC_FTYPE_DIR => 5; my $debug = 0; $|=1; +my $start_t = time(); + +my $pidfile = new File::Pid; + +if (my $pid = $pidfile->running ) { + die "$0 already running: $pid\n"; +} elsif ($pidfile->pid ne $$) { + $pidfile->remove; + $pidfile = new File::Pid; +} +$pidfile->write; +print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n"; + +my $t_fmt = '%Y-%m-%d %H:%M:%S'; + my $hosts; my $bpc = BackupPC::Lib->new || die; my %Conf = $bpc->Conf(); my $TopDir = $bpc->TopDir(); my $beenThere = {}; -my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}"; +my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n"; +my $user = $Conf{SearchUser} || ''; -my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 }); +my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 }); my %opt; -if ( !getopts("cdm:v", \%opt ) ) { +if ( !getopts("cdm:v:", \%opt ) ) { print STDERR <do(qq{ create $unique index $index on $table($col) }); + } + print "creating tables...\n"; $dbh->do(qq{ create table hosts ( - ID INTEGER PRIMARY KEY, + ID SERIAL PRIMARY KEY, name VARCHAR(30) NOT NULL, IP VARCHAR(15) ); @@ -51,7 +81,7 @@ $dbh->do(qq{ create table shares ( - ID INTEGER PRIMARY KEY, + ID SERIAL PRIMARY KEY, hostID INTEGER NOT NULL references hosts(id), name VARCHAR(30) NOT NULL, share VARCHAR(200) NOT NULL, @@ -63,15 +93,17 @@ create table backups ( hostID INTEGER NOT NULL references hosts(id), num INTEGER NOT NULL, - date DATE, - type CHAR(1), + date integer NOT NULL, + type CHAR(4) not null, PRIMARY KEY(hostID, num) ); }); + do_index('backups_hostid,num_unique'); + $dbh->do(qq{ create table dvds ( - ID INTEGER PRIMARY KEY, + ID SERIAL PRIMARY KEY, num INTEGER NOT NULL, name VARCHAR(255) NOT NULL, mjesto VARCHAR(255) @@ -80,20 +112,19 @@ $dbh->do(qq{ create table files ( - ID INTEGER NOT NULL PRIMARY KEY, + ID SERIAL PRIMARY KEY, shareID INTEGER NOT NULL references shares(id), - backupNum INTEGER NOT NULL references backups(num), + backupNum INTEGER NOT NULL, name VARCHAR(255) NOT NULL, path VARCHAR(255) NOT NULL, - fullpath VARCHAR(255) NOT NULL, - date TIMESTAMP NOT NULL, + date integer NOT NULL, type INTEGER NOT NULL, size INTEGER NOT NULL, dvdid INTEGER references dvds(id) ); }); - print "creating indexes...\n"; + print "creating indexes:"; foreach my $index (qw( hosts_name @@ -107,20 +138,24 @@ files_date files_size )) { - my ($table,$col) = split(/_/, $index); - $dbh->do(qq{ create index $index on $table($col) }); + print " $index"; + do_index($index); } + print "...\n"; + $dbh->commit; } if ($opt{d}) { print "deleting "; - foreach my $table (qw(hosts shares files dvds backups)) { + foreach my $table (qw(files dvds backups shares hosts)) { print "$table "; $dbh->do(qq{ DELETE FROM $table }); } print " done...\n"; + + $dbh->commit; } if ($opt{v}) { @@ -158,10 +193,19 @@ $sth->{insert_files} = $dbh->prepare(qq{ INSERT INTO files - (shareID, backupNum, name, path, fullpath, date, type, size) - VALUES (?,?,?,?,?,?,?,?) + (shareID, backupNum, name, path, date, type, size) + VALUES (?,?,?,?,?,?,?) }); +sub fmt_time { + my $t = shift || return; + my $out = ""; + my ($ss,$mm,$hh) = gmtime($t); + $out .= "${hh}h" if ($hh); + $out .= sprintf("%02d:%02d", $mm,$ss); + return $out; +} + foreach my $host_key (keys %{$hosts}) { my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key"; @@ -174,7 +218,7 @@ $hosts->{$host_key}->{'ip'} ); - $hostID = $dbh->func('last_insert_rowid'); + $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef); } print("host ".$hosts->{$host_key}->{'host'}.": "); @@ -186,29 +230,26 @@ 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"; + print $hosts->{$host_key}->{'host'}, + "\t#$backupNum\t", $backup->{type} || '?', " ", + $backup->{nFilesNew} || '?', "/", $backup->{nFiles} || '?', + " files (date: ", + strftime($t_fmt,localtime($backup->{startTime})), + " dur: ", + fmt_time($backup->{endTime} - $backup->{startTime}), + ")\n"; $sth->{backups_broj}->execute($hostID, $backupNum); my ($broj) = $sth->{backups_broj}->fetchrow_array(); next if ($broj > 0); - 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(); - } - $sth->{insert_backups}->execute( $hostID, $backupNum, @@ -217,12 +258,34 @@ ); $dbh->commit(); + my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1); + foreach my $share ($files->shareList($backupNum)) { + + my $t = time(); + + print strftime($t_fmt,localtime())," ", $share; + $shareID = getShareID($share, $hostID, $hostname); + + my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID); + my $dur = (time() - $t) || 1; + printf(" %d/%d files %d/%d dirs [%.2f/s dur: %s]\n", + $nf, $f, $nd, $d, + ( ($f+$d) / $dur ), + fmt_time($dur) + ); + $dbh->commit(); + } + } } undef $sth; $dbh->commit(); $dbh->disconnect(); +print "total duration: ",fmt_time(time() - $start_t),"\n"; + +$pidfile->remove; + sub getShareID() { my ($share, $hostID, $hostname) = @_; @@ -247,15 +310,20 @@ $drop_down =~ s#//+#/#g; $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef); - return $dbh->func('last_insert_rowid'); + return $dbh->last_insert_id(undef,undef,'shares',undef); } sub found_in_db { - my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_; + my @data = @_; + shift @data; + + my ($key, $shareID,undef,$name,$path,undef,$date,undef,$size) = @_; + + return $beenThere->{$key} if (defined($beenThere->{$key})); $sth->{file_in_db} ||= $dbh->prepare(qq{ - SELECT count(*) FROM files + SELECT 1 FROM files WHERE shareID = ? and path = ? and name = ? and @@ -265,8 +333,12 @@ 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"; + my $rows = $sth->{file_in_db}->rows; + print STDERR "## found_in_db ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3); + + $beenThere->{$key}++; + + $sth->{'insert_files'}->execute(@data) unless ($rows); return $rows; } @@ -276,16 +348,16 @@ #################################################### sub recurseDir($$$$$$$$) { - my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_; + my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_; - print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1); + print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1); my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0); { # scope my @stack; - my $files = BackupPC::View->new($bpc, $hostname, $backups); + print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2); my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir); # first, add all the entries in current directory @@ -295,8 +367,6 @@ $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'} @@ -311,9 +381,9 @@ )); - if (! $beenThere->{$key} && ! found_in_db(@data)) { + if (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) { print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2); - $sth->{'insert_files'}->execute(@data); + if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) { $new_dirs++; print STDERR " dir\n" if ($debug >= 2); @@ -322,7 +392,6 @@ print STDERR " file\n" if ($debug >= 2); } } - $beenThere->{$key}++; if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) { $nr_dirs++; @@ -346,7 +415,7 @@ print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2); while ( my $dir = shift @stack ) { - my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID); + my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID); print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1); $nr_files += $f; $new_files += $nf;