bin/BackupPC_updatedb

   1 #!/usr/local/bin/perl -w
   2
   3 use strict;
   4 use lib "__INSTALLDIR__/lib";
   5
   6 use DBI;
   7 use BackupPC::Lib;
   8 use BackupPC::View;
   9 use Data::Dumper;
  10 use Getopt::Std;
  11 use Time::HiRes qw/time/;
  12 use File::Pid;
  13 use POSIX qw/strftime/;
  14
  15 use constant BPC_FTYPE_DIR => 5;
  16
  17 my $debug = 0;
  18 $|=1;
  19
  20 my $start_t = time();
  21
  22 my $pidfile = new File::Pid;
  23
  24 if (my $pid = $pidfile->running ) {
  25         die "$0 already running: $pid\n";
  26 } elsif ($pidfile->pid ne $$) {
  27         $pidfile->remove;
  28         $pidfile = new File::Pid;
  29 }
  30 $pidfile->write;
  31 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
  32
  33 my $t_fmt = '%Y-%m-%d %H:%M:%S';
  34
  35 my $hosts;
  36 my $bpc = BackupPC::Lib->new || die;
  37 my %Conf = $bpc->Conf();
  38 my $TopDir = $bpc->TopDir();
  39 my $beenThere = {};
  40
  41 my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
  42 my $user = $Conf{SearchUser} || '';
  43 my $index_path = $Conf{HyperEstraierIndex};
  44
  45 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
  46
  47 my %opt;
  48
  49 if ( !getopts("cdm:v:i", \%opt ) ) {
  50         print STDERR <<EOF;
  51 usage: $0 [-c|-d] [-m num] [-v|-v level] [-i]
  52
  53 Options:
  54         -c      create database on first use
  55         -d      delete database before import
  56         -m num  import just num increments for one host
  57         -v num  set verbosity (debug) level (default $debug)
  58         -i      update HyperEstraier full text index
  59 EOF
  60         exit 1;
  61 }
  62
  63 if ($opt{v}) {
  64         print "Debug level at $opt{v}\n";
  65         $debug = $opt{v};
  66 }
  67
  68 #---- subs ----
  69
  70 sub fmt_time {
  71         my $t = shift || return;
  72         my $out = "";
  73         my ($ss,$mm,$hh) = gmtime($t);
  74         $out .= "${hh}h" if ($hh);
  75         $out .= sprintf("%02d:%02d", $mm,$ss);
  76         return $out;
  77 }
  78
  79 sub curr_time {
  80         return strftime($t_fmt,localtime());
  81 }
  82
  83 #---- /subs ----
  84
  85 ## update index ##
  86 if ($opt{i}) {
  87
  88         print curr_time," updating HyperEstraier: files";
  89
  90         my $t = time();
  91
  92         my $sth = $dbh->prepare(qq{
  93                 SELECT
  94                         files.id                        AS fid,
  95                         hosts.name                      AS hname,
  96                         shares.name                     AS sname,
  97                         -- shares.share                 AS sharename,
  98                         files.backupnum                 AS backupnum,
  99                         -- files.name                   AS filename,
 100                         files.path                      AS filepath,
 101                         files.date                      AS date,
 102                         files.type                      AS filetype,
 103                         files.size                      AS size,
 104                         files.shareid                   AS shareid,
 105                         backups.date                    AS backup_date
 106                 FROM files
 107                         INNER JOIN shares       ON files.shareID=shares.ID
 108                         INNER JOIN hosts        ON hosts.ID = shares.hostID
 109                         INNER JOIN backups      ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
 110         });
 111
 112         $sth->execute();
 113         my $results = $sth->rows;
 114
 115         my $dot = int($results / 15);
 116
 117         print " $results ($dot/#)";
 118
 119         sub fmt_date {
 120                 my $t = shift || return;
 121                 my $iso = BackupPC::Lib::timeStamp($t);
 122                 $iso =~ s/\s/T/;
 123                 return $iso;
 124         }
 125
 126         my $i = 0;
 127         my $max = int($results / $dot);
 128
 129         $index_path = $TopDir . '/' . $index_path;
 130         $index_path =~ s#//#/#g;
 131
 132         print " index $index_path...";
 133         use HyperEstraier;
 134         my $db = HyperEstraier::Database->new();
 135         $db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);
 136
 137
 138         while (my $row = $sth->fetchrow_hashref()) {
 139
 140                 # create a document object
 141                 my $doc = HyperEstraier::Document->new;
 142
 143                 # add attributes to the document object
 144                 $doc->add_attr('@uri', 'file:///' . $row->{'fid'});
 145
 146                 foreach my $c (@{ $sth->{NAME} }) {
 147                         $doc->add_attr($c, $row->{$c}) if ($row->{$c});
 148                 }
 149
 150                 #$doc->add_attr('@cdate', fmt_date($row->{'date'}));
 151
 152                 # add the body text to the document object
 153                 my $path = $row->{'filepath'};
 154                 $doc->add_text($path);
 155                 $path =~ s/(.)/$1 /g;
 156                 $doc->add_hidden_text($path);
 157
 158                 print STDERR $doc->dump_draft,"\n" if ($debug > 1);
 159
 160                 # register the document object to the database
 161                 $db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
 162
 163                 $i++;
 164                 if ($i % $dot == 0) {
 165                         print "$max ";
 166                         $max--;
 167                 }
 168
 169         }
 170
 171         print "sync";
 172         $db->sync();
 173         print " close";
 174         $db->close();
 175
 176         my $dur = (time() - $t) || 1;
 177         printf(" [%.2f/s dur: %s]\n",
 178                 ( $results / $dur ),
 179                 fmt_time($dur)
 180         );
 181
 182         exit;
 183 }
 184
 185 ###################################create tables############################3
 186
 187 if ($opt{c}) {
 188         sub do_index {
 189                 my $index = shift || return;
 190                 my ($table,$col,$unique) = split(/_/, $index);
 191                 $unique ||= '';
 192                 $index =~ s/,/_/g;
 193                 $dbh->do(qq{ create $unique index $index on $table($col) });
 194         }
 195
 196         print "creating tables...\n";
 197
 198         $dbh->do(qq{
 199                 create table hosts (
 200                         ID      SERIAL          PRIMARY KEY,
 201                         name    VARCHAR(30)     NOT NULL,
 202                         IP      VARCHAR(15)
 203                 );
 204         });
 205
 206         $dbh->do(qq{
 207                 create table shares (
 208                         ID      SERIAL          PRIMARY KEY,
 209                         hostID  INTEGER         NOT NULL references hosts(id),
 210                         name    VARCHAR(30)     NOT NULL,
 211                         share   VARCHAR(200)    NOT NULL,
 212                         localpath VARCHAR(200)
 213                 );
 214         });
 215
 216         $dbh->do(qq{
 217                 create table backups (
 218                         hostID  INTEGER         NOT NULL references hosts(id),
 219                         num     INTEGER         NOT NULL,
 220                         date    integer         NOT NULL,
 221                         type    CHAR(4)         not null,
 222                         shareID integer         not null references shares(id),
 223                         size    integer         not null,
 224                         PRIMARY KEY(hostID, num, shareID)
 225                 );
 226         });
 227
 228         #do_index('backups_hostid,num_unique');
 229
 230         $dbh->do(qq{
 231                 create table dvds (
 232                         ID      SERIAL          PRIMARY KEY,
 233                         num     INTEGER         NOT NULL,
 234                         name    VARCHAR(255)    NOT NULL,
 235                         mjesto  VARCHAR(255)
 236                 );
 237         });
 238
 239         $dbh->do(qq{
 240                 create table files (
 241                         ID      SERIAL          PRIMARY KEY,
 242                         shareID INTEGER         NOT NULL references shares(id),
 243                         backupNum  INTEGER      NOT NULL,
 244                         name       VARCHAR(255) NOT NULL,
 245                         path       VARCHAR(255) NOT NULL,
 246                         date       integer      NOT NULL,
 247                         type       INTEGER      NOT NULL,
 248                         size       INTEGER      NOT NULL,
 249                         dvdid      INTEGER      references dvds(id)
 250                 );
 251         });
 252
 253         print "creating indexes:";
 254
 255         foreach my $index (qw(
 256                 hosts_name
 257                 backups_hostID
 258                 backups_num
 259                 shares_hostID
 260                 shares_name
 261                 files_shareID
 262                 files_path
 263                 files_name
 264                 files_date
 265                 files_size
 266         )) {
 267                 print " $index";
 268                 do_index($index);
 269         }
 270         print "...\n";
 271
 272         $dbh->commit;
 273
 274 }
 275
 276 if ($opt{d}) {
 277         print "deleting ";
 278         foreach my $table (qw(files dvds backups shares hosts)) {
 279                 print "$table ";
 280                 $dbh->do(qq{ DELETE FROM $table });
 281         }
 282         print " done...\n";
 283
 284         $dbh->commit;
 285 }
 286
 287 #################################INSERT VALUES#############################
 288
 289 # get hosts
 290 $hosts = $bpc->HostInfoRead();
 291 my $hostID;
 292 my $shareID;
 293
 294 my $sth;
 295
 296 $sth->{insert_hosts} = $dbh->prepare(qq{
 297 INSERT INTO hosts (name, IP) VALUES (?,?)
 298 });
 299
 300 $sth->{hosts_by_name} = $dbh->prepare(qq{
 301 SELECT ID FROM hosts WHERE name=?
 302 });
 303
 304 $sth->{backups_count} = $dbh->prepare(qq{
 305 SELECT COUNT(*)
 306 FROM backups
 307 WHERE hostID=? AND num=? AND shareid=?
 308 });
 309
 310 $sth->{insert_backups} = $dbh->prepare(qq{
 311 INSERT INTO backups (hostID, num, date, type, shareid, size)
 312 VALUES (?,?,?,?,?,?)
 313 });
 314
 315 $sth->{insert_files} = $dbh->prepare(qq{
 316 INSERT INTO files
 317         (shareID, backupNum, name, path, date, type, size)
 318         VALUES (?,?,?,?,?,?,?)
 319 });
 320
 321 foreach my $host_key (keys %{$hosts}) {
 322
 323         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 324
 325         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 326
 327         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 328                 $sth->{insert_hosts}->execute(
 329                         $hosts->{$host_key}->{'host'},
 330                         $hosts->{$host_key}->{'ip'}
 331                 );
 332
 333                 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
 334         }
 335
 336         print "host ".$hosts->{$host_key}->{'host'}.": ";
 337
 338         # get backups for a host
 339         my @backups = $bpc->BackupInfoRead($hostname);
 340         my $incs = scalar @backups;
 341         print  "$incs increments\n";
 342
 343         my $inc_nr = 0;
 344         $beenThere = {};
 345
 346         foreach my $backup (@backups) {
 347
 348                 $inc_nr++;
 349                 last if ($opt{m} && $inc_nr > $opt{m});
 350
 351                 my $backupNum = $backup->{'num'};
 352                 my @backupShares = ();
 353
 354                 printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
 355                         $hosts->{$host_key}->{'host'},
 356                         $inc_nr, $incs, $backupNum,
 357                         $backup->{type} || '?',
 358                         $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
 359                         strftime($t_fmt,localtime($backup->{startTime})),
 360                         fmt_time($backup->{endTime} - $backup->{startTime})
 361                 );
 362
 363                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 364                 foreach my $share ($files->shareList($backupNum)) {
 365
 366                         my $t = time();
 367
 368                         $shareID = getShareID($share, $hostID, $hostname);
 369
 370                         $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
 371                         my ($count) = $sth->{backups_count}->fetchrow_array();
 372                         # skip if allready in database!
 373                         next if ($count > 0);
 374
 375                         # dump some log
 376                         print curr_time," ", $share;
 377
 378                         my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 379
 380                         $sth->{insert_backups}->execute(
 381                                 $hostID,
 382                                 $backupNum,
 383                                 $backup->{'endTime'},
 384                                 $backup->{'type'},
 385                                 $shareID,
 386                                 $size,
 387                         );
 388
 389                         print " commit";
 390                         $dbh->commit();
 391
 392                         my $dur = (time() - $t) || 1;
 393                         printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
 394                                 $nf, $f, $nd, $d,
 395                                 ($size / 1024 / 1024),
 396                                 ( ($f+$d) / $dur ),
 397                                 fmt_time($dur)
 398                         );
 399                 }
 400
 401         }
 402 }
 403 undef $sth;
 404 $dbh->commit();
 405 $dbh->disconnect();
 406
 407 print "total duration: ",fmt_time(time() - $start_t),"\n";
 408
 409 $pidfile->remove;
 410
 411 sub getShareID() {
 412
 413         my ($share, $hostID, $hostname) = @_;
 414
 415         $sth->{share_id} ||= $dbh->prepare(qq{
 416                 SELECT ID FROM shares WHERE hostID=? AND name=?
 417         });
 418
 419         $sth->{share_id}->execute($hostID,$share);
 420
 421         my ($id) = $sth->{share_id}->fetchrow_array();
 422
 423         return $id if (defined($id));
 424
 425         $sth->{insert_share} ||= $dbh->prepare(qq{
 426                 INSERT INTO shares
 427                         (hostID,name,share,localpath)
 428                 VALUES (?,?,?,?)
 429         });
 430
 431         my $drop_down = $hostname . '/' . $share;
 432         $drop_down =~ s#//+#/#g;
 433
 434         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 435         return $dbh->last_insert_id(undef,undef,'shares',undef);
 436 }
 437
 438 sub found_in_db {
 439
 440         my @data = @_;
 441         shift @data;
 442
 443         my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
 444
 445         return $beenThere->{$key} if (defined($beenThere->{$key}));
 446
 447         $sth->{file_in_db} ||= $dbh->prepare(qq{
 448                 SELECT 1 FROM files
 449                 WHERE shareID = ? and
 450                         path = ? and
 451                         date = ? and
 452                         size = ?
 453                 LIMIT 1
 454         });
 455
 456         my @param = ($shareID,$path,$date,$size);
 457         $sth->{file_in_db}->execute(@param);
 458         my $rows = $sth->{file_in_db}->rows;
 459         print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
 460
 461         $beenThere->{$key}++;
 462
 463         $sth->{'insert_files'}->execute(@data) unless ($rows);
 464         return $rows;
 465 }
 466
 467 ####################################################
 468 # recursing through filesystem structure and       #
 469 # and returning flattened files list               #
 470 ####################################################
 471 sub recurseDir($$$$$$$$) {
 472
 473         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 474
 475         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 476
 477         my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
 478
 479         { # scope
 480                 my @stack;
 481
 482                 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
 483                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 484
 485                 # first, add all the entries in current directory
 486                 foreach my $path_key (keys %{$filesInBackup}) {
 487                         print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
 488                         my @data = (
 489                                 $shareID,
 490                                 $backupNum,
 491                                 $path_key,
 492                                 $filesInBackup->{$path_key}->{'relPath'},
 493                                 $filesInBackup->{$path_key}->{'mtime'},
 494                                 $filesInBackup->{$path_key}->{'type'},
 495                                 $filesInBackup->{$path_key}->{'size'}
 496                         );
 497
 498                         my $key = join(" ", (
 499                                 $shareID,
 500                                 $dir,
 501                                 $path_key,
 502                                 $filesInBackup->{$path_key}->{'mtime'},
 503                                 $filesInBackup->{$path_key}->{'size'}
 504                         ));
 505
 506                         my $found;
 507                         if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
 508                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 509
 510                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 511                                         $new_dirs++ unless ($found);
 512                                         print STDERR " dir\n" if ($debug >= 2);
 513                                 } else {
 514                                         $new_files++ unless ($found);
 515                                         print STDERR " file\n" if ($debug >= 2);
 516                                 }
 517                                 $size += $filesInBackup->{$path_key}->{'size'} || 0;
 518                         }
 519
 520                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 521                                 $nr_dirs++;
 522
 523                                 my $full_path = $dir . '/' . $path_key;
 524                                 push @stack, $full_path;
 525                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 526
 527 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 528 #
 529 #                               $nr_files += $f;
 530 #                               $new_files += $nf;
 531 #                               $nr_dirs += $d;
 532 #                               $new_dirs += $nd;
 533
 534                         } else {
 535                                 $nr_files++;
 536                         }
 537                 }
 538
 539                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 540
 541                 while ( my $dir = shift @stack ) {
 542                         my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 543                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 544                         $nr_files += $f;
 545                         $new_files += $nf;
 546                         $nr_dirs += $d;
 547                         $new_dirs += $nd;
 548                         $size += $s;
 549                 }
 550         }
 551
 552         return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
 553 }
 554