--- trunk/bin/BackupPC_updatedb 2005/07/10 22:56:43 14 +++ trunk/bin/BackupPC_updatedb 2005/08/20 15:51:16 50 @@ -1,16 +1,35 @@ #!/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 $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(); @@ -18,19 +37,24 @@ my $beenThere = {}; my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}"; +my $user = ''; + +# DEBUG option! +($dsn,$user) = qw/dbi:Pg:dbname=backuppc dpavlin/; -my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 }); +my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 }); my %opt; -if ( !getopts("cdm:", \%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) ); @@ -50,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, @@ -62,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_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) @@ -79,20 +112,20 @@ $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(id), + backupNum INTEGER NOT NULL references backups(num), 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 @@ -106,20 +139,29 @@ 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"; + + eval { $dbh->commit; }; +} + +if ($opt{v}) { + print "Debug level at $opt{v}\n"; + $debug = $opt{v}; } #################################INSERT VALUES############################# @@ -156,6 +198,15 @@ 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"; @@ -168,7 +219,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'}.": "); @@ -180,13 +231,17 @@ 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\n"; $sth->{backups_broj}->execute($hostID, $backupNum); my ($broj) = $sth->{backups_broj}->fetchrow_array(); @@ -200,22 +255,32 @@ ); $dbh->commit(); - my $files = BackupPC::View->new($bpc, $hostname, \@backups); + my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1); foreach my $share ($files->shareList($backupNum)) { - print "\t$share"; + my $t = time(); + + print strftime($t_fmt,localtime())," ", $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"; + 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(); +$pidfile->remove; + sub getShareID() { my ($share, $hostID, $hostname) = @_; @@ -236,16 +301,24 @@ VALUES (?,?,?,?) }); - $sth->{insert_share}->execute($hostID,$share, $hostname . $share,undef); - return $dbh->func('last_insert_rowid'); + my $drop_down = $hostname . '/' . $share; + $drop_down =~ s#//+#/#g; + + $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef); + 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 @@ -255,8 +328,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; } @@ -266,59 +343,81 @@ #################################################### sub recurseDir($$$$$$$$) { - my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_; + my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_; - my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0); + print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1); - my $files = BackupPC::View->new($bpc, $hostname, $backups); - my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir); + my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0); - # 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'} - ); + { # scope + my @stack; - my $key = join(" ", ( - $shareID, - $dir, - $path_key, - $filesInBackup->{$path_key}->{'mtime'}, - $filesInBackup->{$path_key}->{'size'} - )); + 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 + 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 (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) { + print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2); + + if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) { + $new_dirs++; + print STDERR " dir\n" if ($debug >= 2); + } else { + $new_files++; + print STDERR " file\n" if ($debug >= 2); + } + } - 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++; + $nr_dirs++; + + my $full_path = $dir . '/' . $path_key; + push @stack, $full_path; + print STDERR "### store to stack: $full_path\n" if ($debug >= 3); + +# my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key}); +# +# $nr_files += $f; +# $new_files += $nf; +# $nr_dirs += $d; +# $new_dirs += $nd; + } else { - $new_files++; + $nr_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); + print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2); + while ( my $dir = shift @stack ) { + 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; $nr_dirs += $d; $new_dirs += $nd; - - } else { - $nr_files++; } }