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 use BackupPC::SearchLib;
  15
  16 use constant BPC_FTYPE_DIR => 5;
  17 use constant EST_CHUNK => 100000;
  18
  19 my $debug = 0;
  20 $|=1;
  21
  22 my $start_t = time();
  23
  24 my $pidfile = new File::Pid;
  25
  26 if (my $pid = $pidfile->running ) {
  27         die "$0 already running: $pid\n";
  28 } elsif ($pidfile->pid ne $$) {
  29         $pidfile->remove;
  30         $pidfile = new File::Pid;
  31 }
  32 $pidfile->write;
  33 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
  34
  35 my $t_fmt = '%Y-%m-%d %H:%M:%S';
  36
  37 my $hosts;
  38 my $bpc = BackupPC::Lib->new || die;
  39 my %Conf = $bpc->Conf();
  40 my $TopDir = $bpc->TopDir();
  41 my $beenThere = {};
  42
  43 my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
  44 my $user = $Conf{SearchUser} || '';
  45
  46 my $use_hest = $Conf{HyperEstraierIndex};
  47 my ($index_path, $index_node_url) = BackupPC::SearchLib::getHyperEstraier_url($use_hest);
  48
  49 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
  50
  51 my %opt;
  52
  53 if ( !getopts("cdm:v:i", \%opt ) ) {
  54         print STDERR <<EOF;
  55 usage: $0 [-c|-d] [-m num] [-v|-v level] [-i]
  56
  57 Options:
  58         -c      create database on first use
  59         -d      delete database before import
  60         -m num  import just num increments for one host
  61         -v num  set verbosity (debug) level (default $debug)
  62         -i      update HyperEstraier full text index
  63 EOF
  64         exit 1;
  65 }
  66
  67 if ($opt{v}) {
  68         print "Debug level at $opt{v}\n";
  69         $debug = $opt{v};
  70 }
  71
  72 #---- subs ----
  73
  74 sub fmt_time {
  75         my $t = shift || return;
  76         my $out = "";
  77         my ($ss,$mm,$hh) = gmtime($t);
  78         $out .= "${hh}h" if ($hh);
  79         $out .= sprintf("%02d:%02d", $mm,$ss);
  80         return $out;
  81 }
  82
  83 sub curr_time {
  84         return strftime($t_fmt,localtime());
  85 }
  86
  87 my $hest_db;
  88 my $hest_node;
  89
  90 sub signal {
  91         my($sig) = @_;
  92         if ($hest_db) {
  93                 print "\nCaught a SIG$sig--syncing database and shutting down\n";
  94                 $hest_db->sync();
  95                 $hest_db->close();
  96         }
  97         exit(0);
  98 }
  99
 100 $SIG{'INT'}  = \&signal;
 101 $SIG{'QUIT'} = \&signal;
 102
 103 sub hest_update {
 104
 105         my ($host_id, $share_id, $num) = @_;
 106
 107         unless ($use_hest) {
 108                 print STDERR "HyperEstraier support not enabled in configuration\n";
 109                 return;
 110         }
 111
 112         print curr_time," updating HyperEstraier:";
 113
 114         my $t = time();
 115
 116         my $offset = 0;
 117         my $added = 0;
 118
 119         print " opening index $use_hest";
 120         if ($index_path) {
 121                 $hest_db = HyperEstraier::Database->new();
 122                 $hest_db->open($TopDir . $index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);
 123                 print " directly";
 124         } elsif ($index_node_url) {
 125                 $hest_node ||= HyperEstraier::Node->new($index_node_url);
 126                 $hest_node->set_auth('admin', 'admin');
 127                 print " via node URL";
 128         } else {
 129                 die "don't know how to use HyperEstraier Index $use_hest";
 130         }
 131         print " increment is " . EST_CHUNK . " files:";
 132
 133         my $results = 0;
 134
 135         do {
 136
 137                 my $where = '';
 138                 my @data;
 139                 if ($host_id && $share_id && $num) {
 140                         $where = qq{
 141                         WHERE
 142                                 hosts.id = ? AND
 143                                 shares.id = ? AND
 144                                 files.backupnum = ?
 145                         };
 146                         @data = ( $host_id, $share_id, $num );
 147                 }
 148
 149                 my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);
 150
 151                 my $sth = $dbh->prepare(qq{
 152                         SELECT
 153                                 files.id                        AS fid,
 154                                 hosts.name                      AS hname,
 155                                 shares.name                     AS sname,
 156                                 -- shares.share                 AS sharename,
 157                                 files.backupnum                 AS backupnum,
 158                                 -- files.name                   AS filename,
 159                                 files.path                      AS filepath,
 160                                 files.date                      AS date,
 161                                 files.type                      AS type,
 162                                 files.size                      AS size,
 163                                 files.shareid                   AS shareid,
 164                                 backups.date                    AS backup_date
 165                         FROM files
 166                                 INNER JOIN shares       ON files.shareID=shares.ID
 167                                 INNER JOIN hosts        ON hosts.ID = shares.hostID
 168                                 INNER JOIN backups      ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
 169                         $where
 170                         $limit
 171                 });
 172
 173                 $sth->execute(@data);
 174                 $results = $sth->rows;
 175
 176                 if ($results == 0) {
 177                         print " - no new files\n";
 178                         last;
 179                 }
 180
 181                 sub fmt_date {
 182                         my $t = shift || return;
 183                         my $iso = BackupPC::Lib::timeStamp($t);
 184                         $iso =~ s/\s/T/;
 185                         return $iso;
 186                 }
 187
 188                 while (my $row = $sth->fetchrow_hashref()) {
 189
 190                         my $fid = $row->{'fid'} || die "no fid?";
 191                         my $uri = 'file:///' . $fid;
 192
 193                         my $id = ($hest_db || $hest_node)->uri_to_id($uri);
 194                         next unless ($id == -1);
 195
 196                         # create a document object
 197                         my $doc = HyperEstraier::Document->new;
 198
 199                         # add attributes to the document object
 200                         $doc->add_attr('@uri', $uri);
 201
 202                         foreach my $c (@{ $sth->{NAME} }) {
 203                                 $doc->add_attr($c, $row->{$c}) if ($row->{$c});
 204                         }
 205
 206                         #$doc->add_attr('@cdate', fmt_date($row->{'date'}));
 207
 208                         # add the body text to the document object
 209                         my $path = $row->{'filepath'};
 210                         $doc->add_text($path);
 211                         $path =~ s/(.)/$1 /g;
 212                         $doc->add_hidden_text($path);
 213
 214                         print STDERR $doc->dump_draft,"\n" if ($debug > 1);
 215
 216                         # register the document object to the database
 217                         if ($hest_db) {
 218                                 $hest_db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
 219                         } elsif ($hest_node) {
 220                                 $hest_node->put_doc($doc);
 221                         } else {
 222                                 die "not supported";
 223                         }
 224                         $added++;
 225                 }
 226
 227                 print " $added";
 228                 $hest_db->sync() if ($index_path);
 229
 230                 $offset += EST_CHUNK;
 231
 232         } while ($results == EST_CHUNK);
 233
 234         if ($index_path) {
 235                 print ", close";
 236                 $hest_db->close();
 237         }
 238
 239         my $dur = (time() - $t) || 1;
 240         printf(" [%.2f/s dur: %s]\n",
 241                 ( $added / $dur ),
 242                 fmt_time($dur)
 243         );
 244 }
 245
 246 #---- /subs ----
 247
 248
 249 ## update index ##
 250 if (($opt{i} || ($index_path && ! -e $index_path)) && !$opt{c}) {
 251         # update all
 252         print "force update of HyperEstraier index ";
 253         print "importing existing data" unless (-e $index_path);
 254         print "by -i flag" if ($opt{i});
 255         print "\n";
 256         hest_update();
 257 }
 258
 259 ## create tables ##
 260 if ($opt{c}) {
 261         sub do_index {
 262                 my $index = shift || return;
 263                 my ($table,$col,$unique) = split(/_/, $index);
 264                 $unique ||= '';
 265                 $index =~ s/,/_/g;
 266                 $dbh->do(qq{ create $unique index $index on $table($col) });
 267         }
 268
 269         print "creating tables...\n";
 270
 271         $dbh->do(qq{
 272                 create table hosts (
 273                         ID      SERIAL          PRIMARY KEY,
 274                         name    VARCHAR(30)     NOT NULL,
 275                         IP      VARCHAR(15)
 276                 );
 277         });
 278
 279         $dbh->do(qq{
 280                 create table shares (
 281                         ID      SERIAL          PRIMARY KEY,
 282                         hostID  INTEGER         NOT NULL references hosts(id),
 283                         name    VARCHAR(30)     NOT NULL,
 284                         share   VARCHAR(200)    NOT NULL,
 285                         localpath VARCHAR(200)
 286                 );
 287         });
 288
 289         $dbh->do(qq{
 290                 create table dvds (
 291                         ID      SERIAL          PRIMARY KEY,
 292                         num     INTEGER         NOT NULL,
 293                         name    VARCHAR(255)    NOT NULL,
 294                         mjesto  VARCHAR(255)
 295                 );
 296         });
 297
 298         $dbh->do(qq{
 299                 create table backups (
 300                         hostID  INTEGER         NOT NULL references hosts(id),
 301                         num     INTEGER         NOT NULL,
 302                         date    integer         NOT NULL,
 303                         type    CHAR(4)         not null,
 304                         shareID integer         not null references shares(id),
 305                         size    integer         not null,
 306                         PRIMARY KEY(hostID, num, shareID)
 307                 );
 308         });
 309
 310         #do_index('backups_hostid,num_unique');
 311
 312
 313         $dbh->do(qq{
 314                 create table files (
 315                         ID      SERIAL          PRIMARY KEY,
 316                         shareID INTEGER         NOT NULL references shares(id),
 317                         backupNum  INTEGER      NOT NULL,
 318                         name       VARCHAR(255) NOT NULL,
 319                         path       VARCHAR(255) NOT NULL,
 320                         date       integer      NOT NULL,
 321                         type       INTEGER      NOT NULL,
 322                         size       INTEGER      NOT NULL
 323                 );
 324         });
 325
 326
 327         $dbh->do( qq{
 328                 create table archive
 329                 (
 330                         id                      int not null,
 331                         dvd_nr          int not null,
 332                         note            text,
 333                         username        varchar(20) not null,
 334                         date            timestamp,
 335                         primary key(id)
 336                 );
 337         }
 338         );
 339
 340         $dbh->do( qq{
 341                 create table archive_backup
 342                 (
 343                         archive_id      int not null,
 344                         backup_id       int not null,
 345                         status          text,
 346                         primary key(archive_id, backup_id)
 347                 );
 348         });
 349
 350         $dbh->do( qq{
 351                 create table workflows(
 352                         id                      int not null,
 353                         step_id         int not null,
 354                         start           timestamp,
 355                         stop            timestamp,
 356                         username        varchar(20),
 357                         archive_id      int not null,
 358                         running         boolean default true,
 359                         primary key(id)
 360                 );
 361         });
 362
 363         $dbh->do( qq{
 364                 create table workflow_step
 365                 (
 366                         step_id         int not null,
 367                         code            text,
 368                         next_step       int,
 369                         stop            boolean default false,
 370                         primary key(step_id)
 371                 );
 372         });
 373
 374         $dbh->do( qq{
 375                         alter table workflow_step
 376                                 add constraint fk_workflow_next_step
 377                                 foreign key(next_step)
 378                                 references workflow_step(step_id);
 379         });
 380
 381         $dbh->do( qq{
 382                 alter table workflows
 383                         add constraint fk_workflows_step_id
 384                         foreign key(step_id)
 385                         references workflow_step(step_id);
 386         });
 387
 388         $dbh->do( qq{
 389                 alter table workflows
 390                         add constraint fk_workflows_archive_id
 391                         foreign key(archive_id)
 392                         references archive(id);
 393         });
 394
 395         $dbh->do( qq{
 396                 create table workflow_log
 397                 (
 398                         workflow_id             int not null,
 399                         step_id                 int not null,
 400                         date                    timestamp not null,
 401                         status                  text,
 402                         primary key(workflow_id, step_id)
 403                 );
 404         });
 405
 406         $dbh->do( qq{
 407                 alter table workflow_log
 408                         add constraint fk_workflow_log_workflow_id
 409                         foreign key (workflow_id)
 410                         references workflows(id);
 411                 });
 412
 413         $dbh->do( qq{
 414                 alter table workflow_log
 415                         add constraint fk_workflow_log_step_id
 416                         foreign key (step_id)
 417                         references      workflow_step(step_id);
 418                 });
 419
 420         print "creating indexes:";
 421
 422         foreach my $index (qw(
 423                 hosts_name
 424                 backups_hostID
 425                 backups_num
 426                 shares_hostID
 427                 shares_name
 428                 files_shareID
 429                 files_path
 430                 files_name
 431                 files_date
 432                 files_size
 433         )) {
 434                 print " $index";
 435                 do_index($index);
 436         }
 437         print "...\n";
 438
 439         $dbh->commit;
 440
 441 }
 442
 443 ## delete data before inseting ##
 444 if ($opt{d}) {
 445         print "deleting ";
 446         foreach my $table (qw(files dvds backups shares hosts)) {
 447                 print "$table ";
 448                 $dbh->do(qq{ DELETE FROM $table });
 449         }
 450         print " done...\n";
 451
 452         $dbh->commit;
 453 }
 454
 455 ## insert new values ##
 456
 457 # get hosts
 458 $hosts = $bpc->HostInfoRead();
 459 my $hostID;
 460 my $shareID;
 461
 462 my $sth;
 463
 464 $sth->{insert_hosts} = $dbh->prepare(qq{
 465 INSERT INTO hosts (name, IP) VALUES (?,?)
 466 });
 467
 468 $sth->{hosts_by_name} = $dbh->prepare(qq{
 469 SELECT ID FROM hosts WHERE name=?
 470 });
 471
 472 $sth->{backups_count} = $dbh->prepare(qq{
 473 SELECT COUNT(*)
 474 FROM backups
 475 WHERE hostID=? AND num=? AND shareid=?
 476 });
 477
 478 $sth->{insert_backups} = $dbh->prepare(qq{
 479 INSERT INTO backups (hostID, num, date, type, shareid, size)
 480 VALUES (?,?,?,?,?,?)
 481 });
 482
 483 $sth->{insert_files} = $dbh->prepare(qq{
 484 INSERT INTO files
 485         (shareID, backupNum, name, path, date, type, size)
 486         VALUES (?,?,?,?,?,?,?)
 487 });
 488
 489 foreach my $host_key (keys %{$hosts}) {
 490
 491         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 492
 493         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 494
 495         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 496                 $sth->{insert_hosts}->execute(
 497                         $hosts->{$host_key}->{'host'},
 498                         $hosts->{$host_key}->{'ip'}
 499                 );
 500
 501                 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
 502         }
 503
 504         print "host ".$hosts->{$host_key}->{'host'}.": ";
 505
 506         # get backups for a host
 507         my @backups = $bpc->BackupInfoRead($hostname);
 508         my $incs = scalar @backups;
 509         print  "$incs increments\n";
 510
 511         my $inc_nr = 0;
 512         $beenThere = {};
 513
 514         foreach my $backup (@backups) {
 515
 516                 $inc_nr++;
 517                 last if ($opt{m} && $inc_nr > $opt{m});
 518
 519                 my $backupNum = $backup->{'num'};
 520                 my @backupShares = ();
 521
 522                 printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
 523                         $hosts->{$host_key}->{'host'},
 524                         $inc_nr, $incs, $backupNum,
 525                         $backup->{type} || '?',
 526                         $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
 527                         strftime($t_fmt,localtime($backup->{startTime})),
 528                         fmt_time($backup->{endTime} - $backup->{startTime})
 529                 );
 530
 531                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 532                 foreach my $share ($files->shareList($backupNum)) {
 533
 534                         my $t = time();
 535
 536                         $shareID = getShareID($share, $hostID, $hostname);
 537
 538                         $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
 539                         my ($count) = $sth->{backups_count}->fetchrow_array();
 540                         # skip if allready in database!
 541                         next if ($count > 0);
 542
 543                         # dump some log
 544                         print curr_time," ", $share;
 545
 546                         my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 547
 548                         $sth->{insert_backups}->execute(
 549                                 $hostID,
 550                                 $backupNum,
 551                                 $backup->{'endTime'},
 552                                 substr($backup->{'type'},0,4),
 553                                 $shareID,
 554                                 $size,
 555                         );
 556
 557                         print " commit";
 558                         $dbh->commit();
 559
 560                         my $dur = (time() - $t) || 1;
 561                         printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
 562                                 $nf, $f, $nd, $d,
 563                                 ($size / 1024 / 1024),
 564                                 ( ($f+$d) / $dur ),
 565                                 fmt_time($dur)
 566                         );
 567
 568                         hest_update($hostID, $shareID, $backupNum) if ($nf + $nd > 0);
 569                 }
 570
 571         }
 572 }
 573 undef $sth;
 574 $dbh->commit();
 575 $dbh->disconnect();
 576
 577 print "total duration: ",fmt_time(time() - $start_t),"\n";
 578
 579 $pidfile->remove;
 580
 581 sub getShareID() {
 582
 583         my ($share, $hostID, $hostname) = @_;
 584
 585         $sth->{share_id} ||= $dbh->prepare(qq{
 586                 SELECT ID FROM shares WHERE hostID=? AND name=?
 587         });
 588
 589         $sth->{share_id}->execute($hostID,$share);
 590
 591         my ($id) = $sth->{share_id}->fetchrow_array();
 592
 593         return $id if (defined($id));
 594
 595         $sth->{insert_share} ||= $dbh->prepare(qq{
 596                 INSERT INTO shares
 597                         (hostID,name,share,localpath)
 598                 VALUES (?,?,?,?)
 599         });
 600
 601         my $drop_down = $hostname . '/' . $share;
 602         $drop_down =~ s#//+#/#g;
 603
 604         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 605         return $dbh->last_insert_id(undef,undef,'shares',undef);
 606 }
 607
 608 sub found_in_db {
 609
 610         my @data = @_;
 611         shift @data;
 612
 613         my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
 614
 615         return $beenThere->{$key} if (defined($beenThere->{$key}));
 616
 617         $sth->{file_in_db} ||= $dbh->prepare(qq{
 618                 SELECT 1 FROM files
 619                 WHERE shareID = ? and
 620                         path = ? and
 621                         date = ? and
 622                         size = ?
 623                 LIMIT 1
 624         });
 625
 626         my @param = ($shareID,$path,$date,$size);
 627         $sth->{file_in_db}->execute(@param);
 628         my $rows = $sth->{file_in_db}->rows;
 629         print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
 630
 631         $beenThere->{$key}++;
 632
 633         $sth->{'insert_files'}->execute(@data) unless ($rows);
 634         return $rows;
 635 }
 636
 637 ####################################################
 638 # recursing through filesystem structure and       #
 639 # and returning flattened files list               #
 640 ####################################################
 641 sub recurseDir($$$$$$$$) {
 642
 643         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 644
 645         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 646
 647         my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
 648
 649         { # scope
 650                 my @stack;
 651
 652                 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
 653                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 654
 655                 # first, add all the entries in current directory
 656                 foreach my $path_key (keys %{$filesInBackup}) {
 657                         print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
 658                         my @data = (
 659                                 $shareID,
 660                                 $backupNum,
 661                                 $path_key,
 662                                 $filesInBackup->{$path_key}->{'relPath'},
 663                                 $filesInBackup->{$path_key}->{'mtime'},
 664                                 $filesInBackup->{$path_key}->{'type'},
 665                                 $filesInBackup->{$path_key}->{'size'}
 666                         );
 667
 668                         my $key = join(" ", (
 669                                 $shareID,
 670                                 $dir,
 671                                 $path_key,
 672                                 $filesInBackup->{$path_key}->{'mtime'},
 673                                 $filesInBackup->{$path_key}->{'size'}
 674                         ));
 675
 676                         my $found;
 677                         if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
 678                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 679
 680                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 681                                         $new_dirs++ unless ($found);
 682                                         print STDERR " dir\n" if ($debug >= 2);
 683                                 } else {
 684                                         $new_files++ unless ($found);
 685                                         print STDERR " file\n" if ($debug >= 2);
 686                                 }
 687                                 $size += $filesInBackup->{$path_key}->{'size'} || 0;
 688                         }
 689
 690                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 691                                 $nr_dirs++;
 692
 693                                 my $full_path = $dir . '/' . $path_key;
 694                                 push @stack, $full_path;
 695                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 696
 697 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 698 #
 699 #                               $nr_files += $f;
 700 #                               $new_files += $nf;
 701 #                               $nr_dirs += $d;
 702 #                               $new_dirs += $nd;
 703
 704                         } else {
 705                                 $nr_files++;
 706                         }
 707                 }
 708
 709                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 710
 711                 while ( my $dir = shift @stack ) {
 712                         my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 713                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 714                         $nr_files += $f;
 715                         $new_files += $nf;
 716                         $nr_dirs += $d;
 717                         $new_dirs += $nd;
 718                         $size += $s;
 719                 }
 720         }
 721
 722         return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
 723 }
 724