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                 $dbh->do(qq{ create $unique index $index on $table($col) });
  69         }
  70
  71         print "creating tables...\n";
  72
  73         $dbh->do(qq{
  74                 create table hosts (
  75                         ID      SERIAL          PRIMARY KEY,
  76                         name    VARCHAR(30)     NOT NULL,
  77                         IP      VARCHAR(15)
  78                 );
  79         });
  80
  81         $dbh->do(qq{
  82                 create table shares (
  83                         ID      SERIAL          PRIMARY KEY,
  84                         hostID  INTEGER         NOT NULL references hosts(id),
  85                         name    VARCHAR(30)     NOT NULL,
  86                         share   VARCHAR(200)    NOT NULL,
  87                         localpath VARCHAR(200)
  88                 );
  89         });
  90
  91         $dbh->do(qq{
  92                 create table backups (
  93                         hostID  INTEGER         NOT NULL references hosts(id),
  94                         num     INTEGER         NOT NULL,
  95                         date    integer         NOT NULL,
  96                         type    CHAR(4)         not null,
  97                         PRIMARY KEY(hostID, num)
  98                 );
  99         });
 100
 101         do_index('backups_num_unique');
 102
 103         $dbh->do(qq{
 104                 create table dvds (
 105                         ID      SERIAL          PRIMARY KEY,
 106                         num     INTEGER         NOT NULL,
 107                         name    VARCHAR(255)    NOT NULL,
 108                         mjesto  VARCHAR(255)
 109                 );
 110         });
 111
 112         $dbh->do(qq{
 113                 create table files (
 114                         ID      SERIAL          PRIMARY KEY,
 115                         shareID INTEGER         NOT NULL references shares(id),
 116                         backupNum  INTEGER      NOT NULL references backups(num),
 117                         name       VARCHAR(255) NOT NULL,
 118                         path       VARCHAR(255) NOT NULL,
 119                         fullpath   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, fullpath, 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\n";
 244
 245                 $sth->{backups_broj}->execute($hostID, $backupNum);
 246                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 247                 next if ($broj > 0);
 248
 249                 $sth->{insert_backups}->execute(
 250                         $hostID,
 251                         $backupNum,
 252                         $backup->{'endTime'},
 253                         $backup->{'type'}
 254                 );
 255                 $dbh->commit();
 256
 257                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 258                 foreach my $share ($files->shareList($backupNum)) {
 259
 260                         my $t = time();
 261
 262                         print strftime($t_fmt,localtime())," ", $share;
 263                         $shareID = getShareID($share, $hostID, $hostname);
 264
 265                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 266                         my $dur = (time() - $t) || 1;
 267                         printf(" %d/%d files %d/%d dirs [%.2f/s dur: %s]\n",
 268                                 $nf, $f, $nd, $d,
 269                                 ( ($f+$d) / $dur ),
 270                                 fmt_time($dur)
 271                         );
 272                         $dbh->commit();
 273                 }
 274
 275         }
 276 }
 277 undef $sth;
 278 $dbh->commit();
 279 $dbh->disconnect();
 280
 281 print "total duration: ",fmt_time(time() - $start_t),"\n";
 282
 283 $pidfile->remove;
 284
 285 sub getShareID() {
 286
 287         my ($share, $hostID, $hostname) = @_;
 288
 289         $sth->{share_id} ||= $dbh->prepare(qq{
 290                 SELECT ID FROM shares WHERE hostID=? AND name=?
 291         });
 292
 293         $sth->{share_id}->execute($hostID,$share);
 294
 295         my ($id) = $sth->{share_id}->fetchrow_array();
 296
 297         return $id if (defined($id));
 298
 299         $sth->{insert_share} ||= $dbh->prepare(qq{
 300                 INSERT INTO shares
 301                         (hostID,name,share,localpath)
 302                 VALUES (?,?,?,?)
 303         });
 304
 305         my $drop_down = $hostname . '/' . $share;
 306         $drop_down =~ s#//+#/#g;
 307
 308         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 309         return $dbh->last_insert_id(undef,undef,'shares',undef);
 310 }
 311
 312 sub found_in_db {
 313
 314         my @data = @_;
 315         shift @data;
 316
 317         my ($key, $shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 318
 319         return $beenThere->{$key} if (defined($beenThere->{$key}));
 320
 321         $sth->{file_in_db} ||= $dbh->prepare(qq{
 322                 SELECT 1 FROM files
 323                 WHERE shareID = ? and
 324                         path = ? and
 325                         name = ? and
 326                         date = ? and
 327                         size = ?
 328         });
 329
 330         my @param = ($shareID,$path,$name,$date,$size);
 331         $sth->{file_in_db}->execute(@param);
 332         my $rows = $sth->{file_in_db}->rows;
 333         print STDERR "## found_in_db ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
 334
 335         $beenThere->{$key}++;
 336
 337         $sth->{'insert_files'}->execute(@data) unless ($rows);
 338         return $rows;
 339 }
 340
 341 ####################################################
 342 # recursing through filesystem structure and       #
 343 # and returning flattened files list               #
 344 ####################################################
 345 sub recurseDir($$$$$$$$) {
 346
 347         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 348
 349         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 350
 351         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 352
 353         { # scope
 354                 my @stack;
 355
 356                 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
 357                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 358
 359                 # first, add all the entries in current directory
 360                 foreach my $path_key (keys %{$filesInBackup}) {
 361                         my @data = (
 362                                 $shareID,
 363                                 $backupNum,
 364                                 $path_key,
 365                                 $filesInBackup->{$path_key}->{'relPath'},
 366                                 $filesInBackup->{$path_key}->{'fullPath'},
 367         #                       $filesInBackup->{$path_key}->{'sharePathM'},
 368                                 $filesInBackup->{$path_key}->{'mtime'},
 369                                 $filesInBackup->{$path_key}->{'type'},
 370                                 $filesInBackup->{$path_key}->{'size'}
 371                         );
 372
 373                         my $key = join(" ", (
 374                                 $shareID,
 375                                 $dir,
 376                                 $path_key,
 377                                 $filesInBackup->{$path_key}->{'mtime'},
 378                                 $filesInBackup->{$path_key}->{'size'}
 379                         ));
 380
 381
 382                         if (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) {
 383                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 384
 385                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 386                                         $new_dirs++;
 387                                         print STDERR " dir\n" if ($debug >= 2);
 388                                 } else {
 389                                         $new_files++;
 390                                         print STDERR " file\n" if ($debug >= 2);
 391                                 }
 392                         }
 393
 394                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 395                                 $nr_dirs++;
 396
 397                                 my $full_path = $dir . '/' . $path_key;
 398                                 push @stack, $full_path;
 399                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 400
 401 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 402 #
 403 #                               $nr_files += $f;
 404 #                               $new_files += $nf;
 405 #                               $nr_dirs += $d;
 406 #                               $new_dirs += $nd;
 407
 408                         } else {
 409                                 $nr_files++;
 410                         }
 411                 }
 412
 413                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 414
 415                 while ( my $dir = shift @stack ) {
 416                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 417                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 418                         $nr_files += $f;
 419                         $new_files += $nf;
 420                         $nr_dirs += $d;
 421                         $new_dirs += $nd;
 422                 }
 423         }
 424
 425         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 426 }
 427