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