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