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 $pidfile = new File::Pid;
  21
  22 if (my $pid = $pidfile->running ) {
  23         die "$0 already running: $pid\n";
  24 } elsif ($pidfile->pid ne $$) {
  25         $pidfile->remove;
  26         $pidfile = new File::Pid;
  27 }
  28 $pidfile->write;
  29 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
  30
  31 my $t_fmt = '%Y-%m-%d %H:%M:%S';
  32
  33 my $hosts;
  34 my $bpc = BackupPC::Lib->new || die;
  35 my %Conf = $bpc->Conf();
  36 my $TopDir = $bpc->TopDir();
  37 my $beenThere = {};
  38
  39 my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
  40 my $user = '';
  41
  42 # DEBUG option!
  43 ($dsn,$user) = qw/dbi:Pg:dbname=backuppc dpavlin/;
  44
  45 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
  46
  47 my %opt;
  48
  49 if ( !getopts("cdm:v:", \%opt ) ) {
  50         print STDERR <<EOF;
  51 usage: $0 [-c|-d] [-m num] [-v|-v level]
  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 EOF
  59         exit 1;
  60 }
  61
  62 ###################################create tables############################3
  63
  64 if ($opt{c}) {
  65         sub do_index {
  66                 my $index = shift || return;
  67                 my ($table,$col,$unique) = split(/_/, $index);
  68                 $unique ||= '';
  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_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 references backups(num),
 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         eval { $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 foreach my $host_key (keys %{$hosts}) {
 202
 203         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 204
 205         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 206
 207         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 208                 $sth->{insert_hosts}->execute(
 209                         $hosts->{$host_key}->{'host'},
 210                         $hosts->{$host_key}->{'ip'}
 211                 );
 212
 213                 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
 214         }
 215
 216         print("host ".$hosts->{$host_key}->{'host'}.": ");
 217
 218         # get backups for a host
 219         my @backups = $bpc->BackupInfoRead($hostname);
 220         print scalar @backups, " increments\n";
 221
 222         my $inc_nr = 0;
 223
 224         foreach my $backup (@backups) {
 225
 226                 $inc_nr++;
 227                 last if ($opt{m} && $inc_nr > $opt{m});
 228
 229                 my $backupNum = $backup->{'num'};
 230                 my @backupShares = ();
 231
 232                 print $hosts->{$host_key}->{'host'},
 233                         "\t#$backupNum\t", $backup->{type} || '?', " ",
 234                         $backup->{nFilesNew} || '?', "/", $backup->{nFiles} || '?',
 235                         " files\n";
 236
 237                 $sth->{backups_broj}->execute($hostID, $backupNum);
 238                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 239                 next if ($broj > 0);
 240
 241                 $sth->{insert_backups}->execute(
 242                         $hostID,
 243                         $backupNum,
 244                         $backup->{'endTime'},
 245                         $backup->{'type'}
 246                 );
 247                 $dbh->commit();
 248
 249                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 250                 foreach my $share ($files->shareList($backupNum)) {
 251
 252                         my $t = time();
 253
 254                         print strftime($t_fmt,localtime())," ", $share;
 255                         $shareID = getShareID($share, $hostID, $hostname);
 256
 257                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 258                         printf(" %d/%d files %d/%d dirs [%.2f/s]\n",
 259                                 $nf, $f, $nd, $d,
 260                                 ( ($f+$d) / ((time() - $t) || 1) )
 261                         );
 262                         $dbh->commit();
 263                 }
 264
 265         }
 266 }
 267 undef $sth;
 268 $dbh->commit();
 269 $dbh->disconnect();
 270
 271 $pidfile->remove;
 272
 273 sub getShareID() {
 274
 275         my ($share, $hostID, $hostname) = @_;
 276
 277         $sth->{share_id} ||= $dbh->prepare(qq{
 278                 SELECT ID FROM shares WHERE hostID=? AND name=?
 279         });
 280
 281         $sth->{share_id}->execute($hostID,$share);
 282
 283         my ($id) = $sth->{share_id}->fetchrow_array();
 284
 285         return $id if (defined($id));
 286
 287         $sth->{insert_share} ||= $dbh->prepare(qq{
 288                 INSERT INTO shares
 289                         (hostID,name,share,localpath)
 290                 VALUES (?,?,?,?)
 291         });
 292
 293         my $drop_down = $hostname . '/' . $share;
 294         $drop_down =~ s#//+#/#g;
 295
 296         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 297         return $dbh->last_insert_id(undef,undef,'shares',undef);
 298 }
 299
 300 sub found_in_db {
 301
 302         my @data = @_;
 303         shift @data;
 304
 305         my ($key, $shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 306
 307         return $beenThere->{$key} if (defined($beenThere->{$key}));
 308
 309         $sth->{file_in_db} ||= $dbh->prepare(qq{
 310                 SELECT 1 FROM files
 311                 WHERE shareID = ? and
 312                         path = ? and
 313                         name = ? and
 314                         date = ? and
 315                         size = ?
 316         });
 317
 318         my @param = ($shareID,$path,$name,$date,$size);
 319         $sth->{file_in_db}->execute(@param);
 320         my $rows = $sth->{file_in_db}->rows;
 321         print STDERR "## found_in_db ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
 322
 323         $beenThere->{$key}++;
 324
 325         $sth->{'insert_files'}->execute(@data) unless ($rows);
 326         return $rows;
 327 }
 328
 329 ####################################################
 330 # recursing through filesystem structure and       #
 331 # and returning flattened files list               #
 332 ####################################################
 333 sub recurseDir($$$$$$$$) {
 334
 335         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 336
 337         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 338
 339         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 340
 341         { # scope
 342                 my @stack;
 343
 344                 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
 345                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 346
 347                 # first, add all the entries in current directory
 348                 foreach my $path_key (keys %{$filesInBackup}) {
 349                         my @data = (
 350                                 $shareID,
 351                                 $backupNum,
 352                                 $path_key,
 353                                 $filesInBackup->{$path_key}->{'relPath'},
 354                                 $filesInBackup->{$path_key}->{'fullPath'},
 355         #                       $filesInBackup->{$path_key}->{'sharePathM'},
 356                                 $filesInBackup->{$path_key}->{'mtime'},
 357                                 $filesInBackup->{$path_key}->{'type'},
 358                                 $filesInBackup->{$path_key}->{'size'}
 359                         );
 360
 361                         my $key = join(" ", (
 362                                 $shareID,
 363                                 $dir,
 364                                 $path_key,
 365                                 $filesInBackup->{$path_key}->{'mtime'},
 366                                 $filesInBackup->{$path_key}->{'size'}
 367                         ));
 368
 369
 370                         if (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) {
 371                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 372
 373                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 374                                         $new_dirs++;
 375                                         print STDERR " dir\n" if ($debug >= 2);
 376                                 } else {
 377                                         $new_files++;
 378                                         print STDERR " file\n" if ($debug >= 2);
 379                                 }
 380                         }
 381
 382                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 383                                 $nr_dirs++;
 384
 385                                 my $full_path = $dir . '/' . $path_key;
 386                                 push @stack, $full_path;
 387                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 388
 389 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 390 #
 391 #                               $nr_files += $f;
 392 #                               $new_files += $nf;
 393 #                               $nr_dirs += $d;
 394 #                               $new_dirs += $nd;
 395
 396                         } else {
 397                                 $nr_files++;
 398                         }
 399                 }
 400
 401                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 402
 403                 while ( my $dir = shift @stack ) {
 404                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 405                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 406                         $nr_files += $f;
 407                         $new_files += $nf;
 408                         $nr_dirs += $d;
 409                         $new_dirs += $nd;
 410                 }
 411         }
 412
 413         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 414 }
 415