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