--- trunk/bin/BackupPC_updatedb 2005/08/20 11:24:55 44 +++ trunk/bin/BackupPC_updatedb 2005/08/28 17:04:12 89 @@ -1,8 +1,9 @@ #!/usr/local/bin/perl -w use strict; -use DBI; use lib "__INSTALLDIR__/lib"; + +use DBI; use BackupPC::Lib; use BackupPC::View; use Data::Dumper; @@ -10,11 +11,14 @@ 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 ) { @@ -34,33 +38,207 @@ 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 $index_path = $Conf{HyperEstraierIndex}; +$index_path = $TopDir . '/' . $index_path; +$index_path =~ s#//#/#g; + -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:i", \%opt ) ) { print STDERR <prepare(qq{ + SELECT + files.id AS fid, + hosts.name AS hname, + shares.name AS sname, + -- shares.share AS sharename, + files.backupnum AS backupnum, + -- files.name AS filename, + files.path AS filepath, + files.date AS date, + files.type AS filetype, + files.size AS size, + files.shareid AS shareid, + backups.date AS backup_date + FROM files + INNER JOIN shares ON files.shareID=shares.ID + INNER JOIN hosts ON hosts.ID = shares.hostID + INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID + $where + }); + + $sth->execute(@_); + my $results = $sth->rows; + + if ($results == 0) { + print " no files\n"; + return; + } + + my $dot = int($results / 15) || 1; + + print " $results ($dot/#)"; + + sub fmt_date { + my $t = shift || return; + my $iso = BackupPC::Lib::timeStamp($t); + $iso =~ s/\s/T/; + return $iso; + } + + my $max = int($results / $dot); + + print " index $index_path..."; + use HyperEstraier; + my $db = HyperEstraier::Database->new(); + +# unless ($hest_db) { +# print " open reader"; +# $hest_db = HyperEstraier::Database->new(); +# +# } + + + $db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT); + + my $added = 0; + + while (my $row = $sth->fetchrow_hashref()) { + + my $fid = $row->{'fid'} || die "no fid?"; + my $uri = 'file:///' . $fid; + + next if ($db->uri_to_id($uri)); + + # create a document object + my $doc = HyperEstraier::Document->new; + + # add attributes to the document object + $doc->add_attr('@uri', $uri); + + foreach my $c (@{ $sth->{NAME} }) { + $doc->add_attr($c, $row->{$c}) if ($row->{$c}); + } + + #$doc->add_attr('@cdate', fmt_date($row->{'date'})); + + # add the body text to the document object + my $path = $row->{'filepath'}; + $doc->add_text($path); + $path =~ s/(.)/$1 /g; + $doc->add_hidden_text($path); + + print STDERR $doc->dump_draft,"\n" if ($debug > 1); + + # register the document object to the database + $db->put_doc($doc, $HyperEstraier::Database::PDCLEAN); + + $added++; + if ($added % $dot == 0) { + print "$max "; + $max--; + } + + } + + print "sync $added new files"; + $db->sync(); + print " close"; + $db->close(); + + my $dur = (time() - $t) || 1; + printf(" [%.2f/s new %.2f/s dur: %s]\n", + ( $results / $dur ), + ( $added / $dur ), + fmt_time($dur) + ); +} + +#---- /subs ---- + +## update index ## +if ($opt{i} || ($index_path && ! -e $index_path)) { + # update all + print "force update of HyperEstraier index "; + print "importing existing data" unless (-e $index_path); + print "by -i flag" if ($opt{i}); + print "\n"; + hest_update(); +} + +## create tables ## if ($opt{c}) { + sub do_index { + my $index = shift || return; + my ($table,$col,$unique) = split(/_/, $index); + $unique ||= ''; + $index =~ s/,/_/g; + $dbh->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) ); @@ -68,7 +246,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, @@ -80,15 +258,19 @@ create table backups ( hostID INTEGER NOT NULL references hosts(id), num INTEGER NOT NULL, - date DATE, - type CHAR(1), - PRIMARY KEY(hostID, num) + date integer NOT NULL, + type CHAR(4) not null, + shareID integer not null references shares(id), + size integer not null, + PRIMARY KEY(hostID, num, shareID) ); }); + #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) @@ -97,20 +279,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 @@ -124,28 +305,28 @@ 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; } +## delete data before inseting ## 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"; -} -if ($opt{v}) { - print "Debug level at $opt{v}\n"; - $debug = $opt{v}; + $dbh->commit; } -#################################INSERT VALUES############################# +## insert new values ## # get hosts $hosts = $bpc->HostInfoRead(); @@ -162,21 +343,21 @@ SELECT ID FROM hosts WHERE name=? }); -$sth->{backups_broj} = $dbh->prepare(qq{ +$sth->{backups_count} = $dbh->prepare(qq{ SELECT COUNT(*) FROM backups -WHERE hostID=? AND num=? +WHERE hostID=? AND num=? AND shareid=? }); $sth->{insert_backups} = $dbh->prepare(qq{ -INSERT INTO backups (hostID, num, date, type) -VALUES (?,?,?,?) +INSERT INTO backups (hostID, num, date, type, shareid, size) +VALUES (?,?,?,?,?,?) }); $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 (?,?,?,?,?,?,?) }); foreach my $host_key (keys %{$hosts}) { @@ -191,16 +372,18 @@ $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'}.": "); + print "host ".$hosts->{$host_key}->{'host'}.": "; # get backups for a host my @backups = $bpc->BackupInfoRead($hostname); - print scalar @backups, " increments\n"; + my $incs = scalar @backups; + print "$incs increments\n"; my $inc_nr = 0; + $beenThere = {}; foreach my $backup (@backups) { @@ -210,38 +393,54 @@ my $backupNum = $backup->{'num'}; my @backupShares = (); - 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(); - next if ($broj > 0); + printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n", + $hosts->{$host_key}->{'host'}, + $inc_nr, $incs, $backupNum, + $backup->{type} || '?', + $backup->{nFilesNew} || '?', $backup->{nFiles} || '?', + strftime($t_fmt,localtime($backup->{startTime})), + fmt_time($backup->{endTime} - $backup->{startTime}) + ); 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); - printf(" %d/%d files %d/%d dirs [%.2f/s]\n", - $nf, $f, $nd, $d, - ( ($f+$d) / ((time() - $t) || 1) ) + $sth->{backups_count}->execute($hostID, $backupNum, $shareID); + my ($count) = $sth->{backups_count}->fetchrow_array(); + # skip if allready in database! + next if ($count > 0); + + # dump some log + print curr_time," ", $share; + + my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID); + + $sth->{insert_backups}->execute( + $hostID, + $backupNum, + $backup->{'endTime'}, + $backup->{'type'}, + $shareID, + $size, ); + + print " commit"; $dbh->commit(); - } - $sth->{insert_backups}->execute( - $hostID, - $backupNum, - $backup->{'endTime'}, - $backup->{'type'} - ); - $dbh->commit(); + my $dur = (time() - $t) || 1; + printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n", + $nf, $f, $nd, $d, + ($size / 1024 / 1024), + ( ($f+$d) / $dur ), + fmt_time($dur) + ); + + hest_update($hostID, $shareID, $backupNum); + } } } @@ -249,6 +448,8 @@ $dbh->commit(); $dbh->disconnect(); +print "total duration: ",fmt_time(time() - $start_t),"\n"; + $pidfile->remove; sub getShareID() { @@ -275,26 +476,35 @@ $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,$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 date = ? and size = ? + LIMIT 1 }); - my @param = ($shareID,$path,$name,$date,$size); + my @param = ($shareID,$path,$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($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3); + + $beenThere->{$key}++; + + $sth->{'insert_files'}->execute(@data) unless ($rows); return $rows; } @@ -308,22 +518,22 @@ 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); + my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0); { # scope my @stack; + 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}) { + print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3); 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'} @@ -337,19 +547,19 @@ $filesInBackup->{$path_key}->{'size'} )); - - if (! $beenThere->{$key} && ! found_in_db(@data)) { + my $found; + if (! defined($beenThere->{$key}) && ! ($found = 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++; + $new_dirs++ unless ($found); print STDERR " dir\n" if ($debug >= 2); } else { - $new_files++; + $new_files++ unless ($found); print STDERR " file\n" if ($debug >= 2); } + $size += $filesInBackup->{$path_key}->{'size'} || 0; } - $beenThere->{$key}++; if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) { $nr_dirs++; @@ -373,15 +583,16 @@ 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); + my ($f,$nf,$d,$nd, $s) = 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; + $size += $s; } } - return ($nr_files, $new_files, $nr_dirs, $new_dirs); + return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size); }