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