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         $pidfile->write;
  26         print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
  27 }
  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                 $inc_nr++;
 206                 last if ($opt{m} && $inc_nr > $opt{m});
 207
 208                 my $backupNum = $backup->{'num'};
 209                 my @backupShares = ();
 210
 211                 print $hosts->{$host_key}->{'host'}, "\t#$backupNum\n";
 212
 213                 $sth->{backups_broj}->execute($hostID, $backupNum);
 214                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 215                 next if ($broj > 0);
 216
 217                 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
 218                 foreach my $share ($files->shareList($backupNum)) {
 219
 220                         my $t = time();
 221
 222                         print strftime($t_fmt,localtime())," ", $share;
 223                         $shareID = getShareID($share, $hostID, $hostname);
 224
 225                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
 226                         printf(" %d/%d files %d/%d dirs [%.2f/s]\n",
 227                                 $nf, $f, $nd, $d,
 228                                 ( ($f+$d) / ((time() - $t) || 1) )
 229                         );
 230                         $dbh->commit();
 231                 }
 232
 233                 $sth->{insert_backups}->execute(
 234                         $hostID,
 235                         $backupNum,
 236                         $backup->{'endTime'},
 237                         $backup->{'type'}
 238                 );
 239                 $dbh->commit();
 240
 241         }
 242 }
 243 undef $sth;
 244 $dbh->commit();
 245 $dbh->disconnect();
 246
 247 $pidfile->remove;
 248
 249 sub getShareID() {
 250
 251         my ($share, $hostID, $hostname) = @_;
 252
 253         $sth->{share_id} ||= $dbh->prepare(qq{
 254                 SELECT ID FROM shares WHERE hostID=? AND name=?
 255         });
 256
 257         $sth->{share_id}->execute($hostID,$share);
 258
 259         my ($id) = $sth->{share_id}->fetchrow_array();
 260
 261         return $id if (defined($id));
 262
 263         $sth->{insert_share} ||= $dbh->prepare(qq{
 264                 INSERT INTO shares
 265                         (hostID,name,share,localpath)
 266                 VALUES (?,?,?,?)
 267         });
 268
 269         my $drop_down = $hostname . '/' . $share;
 270         $drop_down =~ s#//+#/#g;
 271
 272         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 273         return $dbh->func('last_insert_rowid');
 274 }
 275
 276 sub found_in_db {
 277
 278         my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 279
 280         $sth->{file_in_db} ||= $dbh->prepare(qq{
 281                 SELECT count(*) FROM files
 282                 WHERE shareID = ? and
 283                         path = ? and
 284                         name = ? and
 285                         date = ? and
 286                         size = ?
 287         });
 288
 289         my @param = ($shareID,$path,$name,$date,$size);
 290         $sth->{file_in_db}->execute(@param);
 291         my ($rows) = $sth->{file_in_db}->fetchrow_array();
 292 #       print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
 293         return $rows;
 294 }
 295
 296 ####################################################
 297 # recursing through filesystem structure and       #
 298 # and returning flattened files list               #
 299 ####################################################
 300 sub recurseDir($$$$$$$$) {
 301
 302         my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
 303
 304         print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 305
 306         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 307
 308         { # scope
 309                 my @stack;
 310
 311                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 312
 313                 # first, add all the entries in current directory
 314                 foreach my $path_key (keys %{$filesInBackup}) {
 315                         my @data = (
 316                                 $shareID,
 317                                 $backupNum,
 318                                 $path_key,
 319                                 $filesInBackup->{$path_key}->{'relPath'},
 320                                 $filesInBackup->{$path_key}->{'fullPath'},
 321         #                       $filesInBackup->{$path_key}->{'sharePathM'},
 322                                 $filesInBackup->{$path_key}->{'mtime'},
 323                                 $filesInBackup->{$path_key}->{'type'},
 324                                 $filesInBackup->{$path_key}->{'size'}
 325                         );
 326
 327                         my $key = join(" ", (
 328                                 $shareID,
 329                                 $dir,
 330                                 $path_key,
 331                                 $filesInBackup->{$path_key}->{'mtime'},
 332                                 $filesInBackup->{$path_key}->{'size'}
 333                         ));
 334
 335
 336                         if (! $beenThere->{$key} && ! found_in_db(@data)) {
 337                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 338                                 $sth->{'insert_files'}->execute(@data);
 339                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 340                                         $new_dirs++;
 341                                         print STDERR " dir\n" if ($debug >= 2);
 342                                 } else {
 343                                         $new_files++;
 344                                         print STDERR " file\n" if ($debug >= 2);
 345                                 }
 346                         }
 347                         $beenThere->{$key}++;
 348
 349                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 350                                 $nr_dirs++;
 351
 352                                 my $full_path = $dir . '/' . $path_key;
 353                                 push @stack, $full_path;
 354                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 355
 356 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 357 #
 358 #                               $nr_files += $f;
 359 #                               $new_files += $nf;
 360 #                               $nr_dirs += $d;
 361 #                               $new_dirs += $nd;
 362
 363                         } else {
 364                                 $nr_files++;
 365                         }
 366                 }
 367
 368                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 369
 370                 while ( my $dir = shift @stack ) {
 371                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
 372                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 373                         $nr_files += $f;
 374                         $new_files += $nf;
 375                         $nr_dirs += $d;
 376                         $new_dirs += $nd;
 377                 }
 378         }
 379
 380         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 381 }
 382