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