bin/BackupPC_updatedb

   1 #!/usr/local/bin/perl -w
   2
   3 use strict;
   4 use DBI;
   5 use lib "__INSTALLDIR__/lib";
   6 use BackupPC::Lib;
   7 use BackupPC::View;
   8 use Data::Dumper;
   9 use Getopt::Std;
  10 use Time::HiRes qw/time/;
  11 use File::Pid;
  12 use POSIX qw/strftime/;
  13 use constant BPC_FTYPE_DIR => 5;
  14
  15 my $debug = 0;
  16 $|=1;
  17
  18 my $pidfile = new File::Pid;
  19
  20 if (my $pid = $pidfile->running ) {
  21         die "$0 already running: $pid\n";
  22 } elsif ($pidfile->pid ne $$) {
  23         $pidfile->remove;
  24         $pidfile = new File::Pid;
  25 }
  26 $pidfile->write;
  27 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
  28
  29 my $t_fmt = '%Y-%m-%d %H:%M:%S';
  30
  31 my $hosts;
  32 my $bpc = BackupPC::Lib->new || die;
  33 my %Conf = $bpc->Conf();
  34 my $TopDir = $bpc->TopDir();
  35 my $beenThere = {};
  36
  37 my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
  38
  39 my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
  40
  41 my %opt;
  42
  43 if ( !getopts("cdm:v:", \%opt ) ) {
  44         print STDERR <<EOF;
  45 usage: $0 [-c|-d] [-m num] [-v|-v level]
  46
  47 Options:
  48         -c      create database on first use
  49         -d      delete database before import
  50         -m num  import just num increments for one host
  51         -v num  set verbosity (debug) level (default $debug)
  52 EOF
  53         exit 1;
  54 }
  55
  56 ###################################create tables############################3
  57
  58 if ($opt{c}) {
  59         print "creating tables...\n";
  60
  61         $dbh->do(qq{
  62                 create table hosts (
  63                         ID      INTEGER         PRIMARY KEY,
  64                         name    VARCHAR(30)     NOT NULL,
  65                         IP      VARCHAR(15)
  66                 );
  67         });
  68
  69         $dbh->do(qq{
  70                 create table shares (
  71                         ID      INTEGER         PRIMARY KEY,
  72                         hostID  INTEGER         NOT NULL references hosts(id),
  73                         name    VARCHAR(30)     NOT NULL,
  74                         share   VARCHAR(200)    NOT NULL,
  75                         localpath VARCHAR(200)
  76                 );
  77         });
  78
  79         $dbh->do(qq{
  80                 create table backups (
  81                         hostID  INTEGER         NOT NULL references hosts(id),
  82                         num     INTEGER         NOT NULL,
  83                         date    DATE,
  84                         type    CHAR(1),
  85                         PRIMARY KEY(hostID, num)
  86                 );
  87         });
  88
  89         $dbh->do(qq{
  90                 create table dvds (
  91                         ID      INTEGER         PRIMARY KEY,
  92                         num     INTEGER         NOT NULL,
  93                         name    VARCHAR(255)    NOT NULL,
  94                         mjesto  VARCHAR(255)
  95                 );
  96         });
  97
  98         $dbh->do(qq{
  99                 create table files (
 100                         ID      INTEGER         NOT NULL PRIMARY KEY,
 101                         shareID INTEGER         NOT NULL references shares(id),
 102                         backupNum  INTEGER      NOT NULL references backups(num),
 103                         name       VARCHAR(255) NOT NULL,
 104                         path       VARCHAR(255) NOT NULL,
 105                         fullpath   VARCHAR(255) NOT NULL,
 106                         date       TIMESTAMP    NOT NULL,
 107                         type       INTEGER      NOT NULL,
 108                         size       INTEGER      NOT NULL,
 109                         dvdid      INTEGER      references dvds(id)
 110                 );
 111         });
 112
 113         print "creating indexes...\n";
 114
 115         foreach my $index (qw(
 116                 hosts_name
 117                 backups_hostID
 118                 backups_num
 119                 shares_hostID
 120                 shares_name
 121                 files_shareID
 122                 files_path
 123                 files_name
 124                 files_date
 125                 files_size
 126         )) {
 127                 my ($table,$col) = split(/_/, $index);
 128                 $dbh->do(qq{ create index $index on $table($col) });
 129         }
 130
 131
 132 }
 133
 134 if ($opt{d}) {
 135         print "deleting ";
 136         foreach my $table (qw(hosts shares files dvds backups)) {
 137                 print "$table ";
 138                 $dbh->do(qq{ DELETE FROM $table });
 139         }
 140         print " done...\n";
 141 }
 142
 143 if ($opt{v}) {
 144         print "Debug level at $opt{v}\n";
 145         $debug = $opt{v};
 146 }
 147
 148 #################################INSERT VALUES#############################
 149
 150 # get hosts
 151 $hosts = $bpc->HostInfoRead();
 152 my $hostID;
 153 my $shareID;
 154
 155 my $sth;
 156
 157 $sth->{insert_hosts} = $dbh->prepare(qq{
 158 INSERT INTO hosts (name, IP) VALUES (?,?)
 159 });
 160
 161 $sth->{hosts_by_name} = $dbh->prepare(qq{
 162 SELECT ID FROM hosts WHERE name=?
 163 });
 164
 165 $sth->{backups_broj} = $dbh->prepare(qq{
 166 SELECT COUNT(*)
 167 FROM backups
 168 WHERE hostID=? AND num=?
 169 });
 170
 171 $sth->{insert_backups} = $dbh->prepare(qq{
 172 INSERT INTO backups (hostID, num, date, type)
 173 VALUES (?,?,?,?)
 174 });
 175
 176 $sth->{insert_files} = $dbh->prepare(qq{
 177 INSERT INTO files
 178         (shareID, backupNum, name, path, fullpath, date, type, size)
 179         VALUES (?,?,?,?,?,?,?,?)
 180 });
 181
 182 foreach my $host_key (keys %{$hosts}) {
 183
 184         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 185
 186         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 187
 188         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 189                 $sth->{insert_hosts}->execute(
 190                         $hosts->{$host_key}->{'host'},
 191                         $hosts->{$host_key}->{'ip'}
 192                 );
 193
 194                 $hostID = $dbh->func('last_insert_rowid');
 195         }
 196
 197         print("host ".$hosts->{$host_key}->{'host'}.": ");
 198
 199         # get backups for a host
 200         my @backups = $bpc->BackupInfoRead($hostname);
 201         print scalar @backups, " increments\n";
 202
 203         my $inc_nr = 0;
 204
 205         foreach my $backup (@backups) {
 206
 207                 $inc_nr++;
 208                 last if ($opt{m} && $inc_nr > $opt{m});
 209
 210                 my $backupNum = $backup->{'num'};
 211                 my @backupShares = ();
 212
 213                 print $hosts->{$host_key}->{'host'},
 214                         "\t#$backupNum\t", $backup->{type} || '?', " ",
 215                         $backup->{nFilesNew} || '?', "/", $backup->{nFiles} || '?',
 216                         " files\n";
 217
 218                 $sth->{backups_broj}->execute($hostID, $backupNum);
 219                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 220                 next if ($broj > 0);
 221
 222                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 223                 foreach my $share ($files->shareList($backupNum)) {
 224
 225                         my $t = time();
 226
 227                         print strftime($t_fmt,localtime())," ", $share;
 228                         $shareID = getShareID($share, $hostID, $hostname);
 229
 230                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 231                         printf(" %d/%d files %d/%d dirs [%.2f/s]\n",
 232                                 $nf, $f, $nd, $d,
 233                                 ( ($f+$d) / ((time() - $t) || 1) )
 234                         );
 235                         $dbh->commit();
 236                 }
 237
 238                 $sth->{insert_backups}->execute(
 239                         $hostID,
 240                         $backupNum,
 241                         $backup->{'endTime'},
 242                         $backup->{'type'}
 243                 );
 244                 $dbh->commit();
 245
 246         }
 247 }
 248 undef $sth;
 249 $dbh->commit();
 250 $dbh->disconnect();
 251
 252 $pidfile->remove;
 253
 254 sub getShareID() {
 255
 256         my ($share, $hostID, $hostname) = @_;
 257
 258         $sth->{share_id} ||= $dbh->prepare(qq{
 259                 SELECT ID FROM shares WHERE hostID=? AND name=?
 260         });
 261
 262         $sth->{share_id}->execute($hostID,$share);
 263
 264         my ($id) = $sth->{share_id}->fetchrow_array();
 265
 266         return $id if (defined($id));
 267
 268         $sth->{insert_share} ||= $dbh->prepare(qq{
 269                 INSERT INTO shares
 270                         (hostID,name,share,localpath)
 271                 VALUES (?,?,?,?)
 272         });
 273
 274         my $drop_down = $hostname . '/' . $share;
 275         $drop_down =~ s#//+#/#g;
 276
 277         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 278         return $dbh->func('last_insert_rowid');
 279 }
 280
 281 sub found_in_db {
 282
 283         my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 284
 285         $sth->{file_in_db} ||= $dbh->prepare(qq{
 286                 SELECT count(*) FROM files
 287                 WHERE shareID = ? and
 288                         path = ? and
 289                         name = ? and
 290                         date = ? and
 291                         size = ?
 292         });
 293
 294         my @param = ($shareID,$path,$name,$date,$size);
 295         $sth->{file_in_db}->execute(@param);
 296         my ($rows) = $sth->{file_in_db}->fetchrow_array();
 297 #       print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
 298         return $rows;
 299 }
 300
 301 ####################################################
 302 # recursing through filesystem structure and       #
 303 # and returning flattened files list               #
 304 ####################################################
 305 sub recurseDir($$$$$$$$) {
 306
 307         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 308
 309         print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 310
 311         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 312
 313         { # scope
 314                 my @stack;
 315
 316                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 317
 318                 # first, add all the entries in current directory
 319                 foreach my $path_key (keys %{$filesInBackup}) {
 320                         my @data = (
 321                                 $shareID,
 322                                 $backupNum,
 323                                 $path_key,
 324                                 $filesInBackup->{$path_key}->{'relPath'},
 325                                 $filesInBackup->{$path_key}->{'fullPath'},
 326         #                       $filesInBackup->{$path_key}->{'sharePathM'},
 327                                 $filesInBackup->{$path_key}->{'mtime'},
 328                                 $filesInBackup->{$path_key}->{'type'},
 329                                 $filesInBackup->{$path_key}->{'size'}
 330                         );
 331
 332                         my $key = join(" ", (
 333                                 $shareID,
 334                                 $dir,
 335                                 $path_key,
 336                                 $filesInBackup->{$path_key}->{'mtime'},
 337                                 $filesInBackup->{$path_key}->{'size'}
 338                         ));
 339
 340
 341                         if (! $beenThere->{$key} && ! found_in_db(@data)) {
 342                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 343                                 $sth->{'insert_files'}->execute(@data);
 344                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 345                                         $new_dirs++;
 346                                         print STDERR " dir\n" if ($debug >= 2);
 347                                 } else {
 348                                         $new_files++;
 349                                         print STDERR " file\n" if ($debug >= 2);
 350                                 }
 351                         }
 352                         $beenThere->{$key}++;
 353
 354                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 355                                 $nr_dirs++;
 356
 357                                 my $full_path = $dir . '/' . $path_key;
 358                                 push @stack, $full_path;
 359                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 360
 361 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 362 #
 363 #                               $nr_files += $f;
 364 #                               $new_files += $nf;
 365 #                               $nr_dirs += $d;
 366 #                               $new_dirs += $nd;
 367
 368                         } else {
 369                                 $nr_files++;
 370                         }
 371                 }
 372
 373                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 374
 375                 while ( my $dir = shift @stack ) {
 376                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 377                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 378                         $nr_files += $f;
 379                         $new_files += $nf;
 380                         $nr_dirs += $d;
 381                         $new_dirs += $nd;
 382                 }
 383         }
 384
 385         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 386 }
 387