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
  44 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
  45
  46 my %opt;
  47
  48 if ( !getopts("cdm:v:", \%opt ) ) {
  49         print STDERR <<EOF;
  50 usage: $0 [-c|-d] [-m num] [-v|-v level]
  51
  52 Options:
  53         -c      create database on first use
  54         -d      delete database before import
  55         -m num  import just num increments for one host
  56         -v num  set verbosity (debug) level (default $debug)
  57 EOF
  58         exit 1;
  59 }
  60
  61 ###################################create tables############################3
  62
  63 if ($opt{c}) {
  64         sub do_index {
  65                 my $index = shift || return;
  66                 my ($table,$col,$unique) = split(/_/, $index);
  67                 $unique ||= '';
  68                 $index =~ s/,/_/g;
  69                 $dbh->do(qq{ create $unique index $index on $table($col) });
  70         }
  71
  72         print "creating tables...\n";
  73
  74         $dbh->do(qq{
  75                 create table hosts (
  76                         ID      SERIAL          PRIMARY KEY,
  77                         name    VARCHAR(30)     NOT NULL,
  78                         IP      VARCHAR(15)
  79                 );
  80         });
  81
  82         $dbh->do(qq{
  83                 create table shares (
  84                         ID      SERIAL          PRIMARY KEY,
  85                         hostID  INTEGER         NOT NULL references hosts(id),
  86                         name    VARCHAR(30)     NOT NULL,
  87                         share   VARCHAR(200)    NOT NULL,
  88                         localpath VARCHAR(200)
  89                 );
  90         });
  91
  92         $dbh->do(qq{
  93                 create table backups (
  94                         hostID  INTEGER         NOT NULL references hosts(id),
  95                         num     INTEGER         NOT NULL,
  96                         date    integer         NOT NULL,
  97                         type    CHAR(4)         not null,
  98                         PRIMARY KEY(hostID, num)
  99                 );
 100         });
 101
 102         do_index('backups_hostid,num_unique');
 103
 104         $dbh->do(qq{
 105                 create table dvds (
 106                         ID      SERIAL          PRIMARY KEY,
 107                         num     INTEGER         NOT NULL,
 108                         name    VARCHAR(255)    NOT NULL,
 109                         mjesto  VARCHAR(255)
 110                 );
 111         });
 112
 113         $dbh->do(qq{
 114                 create table files (
 115                         ID      SERIAL          PRIMARY KEY,
 116                         shareID INTEGER         NOT NULL references shares(id),
 117                         backupNum  INTEGER      NOT NULL,
 118                         name       VARCHAR(255) NOT NULL,
 119                         path       VARCHAR(255) NOT NULL,
 120                         date       integer      NOT NULL,
 121                         type       INTEGER      NOT NULL,
 122                         size       INTEGER      NOT NULL,
 123                         dvdid      INTEGER      references dvds(id)
 124                 );
 125         });
 126
 127         print "creating indexes:";
 128
 129         foreach my $index (qw(
 130                 hosts_name
 131                 backups_hostID
 132                 backups_num
 133                 shares_hostID
 134                 shares_name
 135                 files_shareID
 136                 files_path
 137                 files_name
 138                 files_date
 139                 files_size
 140         )) {
 141                 print " $index";
 142                 do_index($index);
 143         }
 144         print "...\n";
 145
 146         $dbh->commit;
 147
 148 }
 149
 150 if ($opt{d}) {
 151         print "deleting ";
 152         foreach my $table (qw(files dvds backups shares hosts)) {
 153                 print "$table ";
 154                 $dbh->do(qq{ DELETE FROM $table });
 155         }
 156         print " done...\n";
 157
 158         $dbh->commit;
 159 }
 160
 161 if ($opt{v}) {
 162         print "Debug level at $opt{v}\n";
 163         $debug = $opt{v};
 164 }
 165
 166 #################################INSERT VALUES#############################
 167
 168 # get hosts
 169 $hosts = $bpc->HostInfoRead();
 170 my $hostID;
 171 my $shareID;
 172
 173 my $sth;
 174
 175 $sth->{insert_hosts} = $dbh->prepare(qq{
 176 INSERT INTO hosts (name, IP) VALUES (?,?)
 177 });
 178
 179 $sth->{hosts_by_name} = $dbh->prepare(qq{
 180 SELECT ID FROM hosts WHERE name=?
 181 });
 182
 183 $sth->{backups_broj} = $dbh->prepare(qq{
 184 SELECT COUNT(*)
 185 FROM backups
 186 WHERE hostID=? AND num=?
 187 });
 188
 189 $sth->{insert_backups} = $dbh->prepare(qq{
 190 INSERT INTO backups (hostID, num, date, type)
 191 VALUES (?,?,?,?)
 192 });
 193
 194 $sth->{insert_files} = $dbh->prepare(qq{
 195 INSERT INTO files
 196         (shareID, backupNum, name, path, date, type, size)
 197         VALUES (?,?,?,?,?,?,?)
 198 });
 199
 200 sub fmt_time {
 201         my $t = shift || return;
 202         my $out = "";
 203         my ($ss,$mm,$hh) = gmtime($t);
 204         $out .= "${hh}h" if ($hh);
 205         $out .= sprintf("%02d:%02d", $mm,$ss);
 206         return $out;
 207 }
 208
 209 foreach my $host_key (keys %{$hosts}) {
 210
 211         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 212
 213         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 214
 215         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 216                 $sth->{insert_hosts}->execute(
 217                         $hosts->{$host_key}->{'host'},
 218                         $hosts->{$host_key}->{'ip'}
 219                 );
 220
 221                 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
 222         }
 223
 224         print("host ".$hosts->{$host_key}->{'host'}.": ");
 225
 226         # get backups for a host
 227         my @backups = $bpc->BackupInfoRead($hostname);
 228         print scalar @backups, " increments\n";
 229
 230         my $inc_nr = 0;
 231
 232         foreach my $backup (@backups) {
 233
 234                 $inc_nr++;
 235                 last if ($opt{m} && $inc_nr > $opt{m});
 236
 237                 my $backupNum = $backup->{'num'};
 238                 my @backupShares = ();
 239
 240                 print $hosts->{$host_key}->{'host'},
 241                         "\t#$backupNum\t", $backup->{type} || '?', " ",
 242                         $backup->{nFilesNew} || '?', "/", $backup->{nFiles} || '?',
 243                         " files (date: ",
 244                         strftime($t_fmt,localtime($backup->{startTime})),
 245                         " dur: ",
 246                         fmt_time($backup->{endTime} - $backup->{startTime}),
 247                         ")\n";
 248
 249                 $sth->{backups_broj}->execute($hostID, $backupNum);
 250                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 251                 next if ($broj > 0);
 252
 253                 $sth->{insert_backups}->execute(
 254                         $hostID,
 255                         $backupNum,
 256                         $backup->{'endTime'},
 257                         $backup->{'type'}
 258                 );
 259                 $dbh->commit();
 260
 261                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 262                 foreach my $share ($files->shareList($backupNum)) {
 263
 264                         my $t = time();
 265
 266                         print strftime($t_fmt,localtime())," ", $share;
 267                         $shareID = getShareID($share, $hostID, $hostname);
 268
 269                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 270                         my $dur = (time() - $t) || 1;
 271                         printf(" %d/%d files %d/%d dirs [%.2f/s dur: %s]\n",
 272                                 $nf, $f, $nd, $d,
 273                                 ( ($f+$d) / $dur ),
 274                                 fmt_time($dur)
 275                         );
 276                         $dbh->commit();
 277                 }
 278
 279         }
 280 }
 281 undef $sth;
 282 $dbh->commit();
 283 $dbh->disconnect();
 284
 285 print "total duration: ",fmt_time(time() - $start_t),"\n";
 286
 287 $pidfile->remove;
 288
 289 sub getShareID() {
 290
 291         my ($share, $hostID, $hostname) = @_;
 292
 293         $sth->{share_id} ||= $dbh->prepare(qq{
 294                 SELECT ID FROM shares WHERE hostID=? AND name=?
 295         });
 296
 297         $sth->{share_id}->execute($hostID,$share);
 298
 299         my ($id) = $sth->{share_id}->fetchrow_array();
 300
 301         return $id if (defined($id));
 302
 303         $sth->{insert_share} ||= $dbh->prepare(qq{
 304                 INSERT INTO shares
 305                         (hostID,name,share,localpath)
 306                 VALUES (?,?,?,?)
 307         });
 308
 309         my $drop_down = $hostname . '/' . $share;
 310         $drop_down =~ s#//+#/#g;
 311
 312         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 313         return $dbh->last_insert_id(undef,undef,'shares',undef);
 314 }
 315
 316 sub found_in_db {
 317
 318         my @data = @_;
 319         shift @data;
 320
 321         my ($key, $shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 322
 323         return $beenThere->{$key} if (defined($beenThere->{$key}));
 324
 325         $sth->{file_in_db} ||= $dbh->prepare(qq{
 326                 SELECT 1 FROM files
 327                 WHERE shareID = ? and
 328                         path = ? and
 329                         name = ? and
 330                         date = ? and
 331                         size = ?
 332         });
 333
 334         my @param = ($shareID,$path,$name,$date,$size);
 335         $sth->{file_in_db}->execute(@param);
 336         my $rows = $sth->{file_in_db}->rows;
 337         print STDERR "## found_in_db ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
 338
 339         $beenThere->{$key}++;
 340
 341         $sth->{'insert_files'}->execute(@data) unless ($rows);
 342         return $rows;
 343 }
 344
 345 ####################################################
 346 # recursing through filesystem structure and       #
 347 # and returning flattened files list               #
 348 ####################################################
 349 sub recurseDir($$$$$$$$) {
 350
 351         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 352
 353         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 354
 355         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 356
 357         { # scope
 358                 my @stack;
 359
 360                 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
 361                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 362
 363                 # first, add all the entries in current directory
 364                 foreach my $path_key (keys %{$filesInBackup}) {
 365                         my @data = (
 366                                 $shareID,
 367                                 $backupNum,
 368                                 $path_key,
 369                                 $filesInBackup->{$path_key}->{'relPath'},
 370                                 $filesInBackup->{$path_key}->{'mtime'},
 371                                 $filesInBackup->{$path_key}->{'type'},
 372                                 $filesInBackup->{$path_key}->{'size'}
 373                         );
 374
 375                         my $key = join(" ", (
 376                                 $shareID,
 377                                 $dir,
 378                                 $path_key,
 379                                 $filesInBackup->{$path_key}->{'mtime'},
 380                                 $filesInBackup->{$path_key}->{'size'}
 381                         ));
 382
 383
 384                         if (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) {
 385                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 386
 387                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 388                                         $new_dirs++;
 389                                         print STDERR " dir\n" if ($debug >= 2);
 390                                 } else {
 391                                         $new_files++;
 392                                         print STDERR " file\n" if ($debug >= 2);
 393                                 }
 394                         }
 395
 396                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 397                                 $nr_dirs++;
 398
 399                                 my $full_path = $dir . '/' . $path_key;
 400                                 push @stack, $full_path;
 401                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 402
 403 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 404 #
 405 #                               $nr_files += $f;
 406 #                               $new_files += $nf;
 407 #                               $nr_dirs += $d;
 408 #                               $new_dirs += $nd;
 409
 410                         } else {
 411                                 $nr_files++;
 412                         }
 413                 }
 414
 415                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 416
 417                 while ( my $dir = shift @stack ) {
 418                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 419                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 420                         $nr_files += $f;
 421                         $new_files += $nf;
 422                         $nr_dirs += $d;
 423                         $new_dirs += $nd;
 424                 }
 425         }
 426
 427         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 428 }
 429