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:", \%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 #################################INSERT VALUES#############################
 127
 128 # get hosts
 129 $hosts = $bpc->HostInfoRead();
 130 my $hostID;
 131 my $shareID;
 132
 133 my $sth;
 134
 135 $sth->{insert_hosts} = $dbh->prepare(qq{
 136 INSERT INTO hosts (name, IP) VALUES (?,?)
 137 });
 138
 139 $sth->{hosts_by_name} = $dbh->prepare(qq{
 140 SELECT ID FROM hosts WHERE name=?
 141 });
 142
 143 $sth->{backups_broj} = $dbh->prepare(qq{
 144 SELECT COUNT(*)
 145 FROM backups
 146 WHERE hostID=? AND num=?
 147 });
 148
 149 $sth->{insert_backups} = $dbh->prepare(qq{
 150 INSERT INTO backups (hostID, num, date, type)
 151 VALUES (?,?,?,?)
 152 });
 153
 154 $sth->{insert_files} = $dbh->prepare(qq{
 155 INSERT INTO files
 156         (shareID, backupNum, name, path, fullpath, date, type, size)
 157         VALUES (?,?,?,?,?,?,?,?)
 158 });
 159
 160 foreach my $host_key (keys %{$hosts}) {
 161
 162         my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 163
 164         $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
 165
 166         unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 167                 $sth->{insert_hosts}->execute(
 168                         $hosts->{$host_key}->{'host'},
 169                         $hosts->{$host_key}->{'ip'}
 170                 );
 171
 172                 $hostID = $dbh->func('last_insert_rowid');
 173         }
 174
 175         print("host ".$hosts->{$host_key}->{'host'}.": ");
 176
 177         # get backups for a host
 178         my @backups = $bpc->BackupInfoRead($hostname);
 179         print scalar @backups, " increments\n";
 180
 181         my $inc_nr = 0;
 182
 183         foreach my $backup (@backups) {
 184                 $inc_nr++;
 185                 last if ($opt{m} && $inc_nr > $opt{m});
 186
 187                 my $backupNum = $backup->{'num'};
 188                 my @backupShares = ();
 189
 190                 print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";
 191
 192                 $sth->{backups_broj}->execute($hostID, $backupNum);
 193                 my ($broj) = $sth->{backups_broj}->fetchrow_array();
 194                 next if ($broj > 0);
 195
 196                 my $files = BackupPC::View->new($bpc, $hostname, \@backups);
 197                 foreach my $share ($files->shareList($backupNum)) {
 198
 199                         print "\t$share";
 200                         $shareID = getShareID($share, $hostID, $hostname);
 201
 202                         my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
 203                         print " $nf/$f files $nd/$d dirs\n";
 204                         $dbh->commit();
 205                 }
 206
 207                 $sth->{insert_backups}->execute(
 208                         $hostID,
 209                         $backupNum,
 210                         $backup->{'endTime'},
 211                         $backup->{'type'}
 212                 );
 213                 $dbh->commit();
 214
 215         }
 216 }
 217 undef $sth;
 218 $dbh->commit();
 219 $dbh->disconnect();
 220
 221 sub getShareID() {
 222
 223         my ($share, $hostID, $hostname) = @_;
 224
 225         $sth->{share_id} ||= $dbh->prepare(qq{
 226                 SELECT ID FROM shares WHERE hostID=? AND name=?
 227         });
 228
 229         $sth->{share_id}->execute($hostID,$share);
 230
 231         my ($id) = $sth->{share_id}->fetchrow_array();
 232
 233         return $id if (defined($id));
 234
 235         $sth->{insert_share} ||= $dbh->prepare(qq{
 236                 INSERT INTO shares
 237                         (hostID,name,share,localpath)
 238                 VALUES (?,?,?,?)
 239         });
 240
 241         my $drop_down = $hostname . '/' . $share;
 242         $drop_down =~ s#//+#/#g;
 243
 244         $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
 245         return $dbh->func('last_insert_rowid');
 246 }
 247
 248 sub found_in_db {
 249
 250         my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
 251
 252         $sth->{file_in_db} ||= $dbh->prepare(qq{
 253                 SELECT count(*) FROM files
 254                 WHERE shareID = ? and
 255                         path = ? and
 256                         name = ? and
 257                         date = ? and
 258                         size = ?
 259         });
 260
 261         my @param = ($shareID,$path,$name,$date,$size);
 262         $sth->{file_in_db}->execute(@param);
 263         my ($rows) = $sth->{file_in_db}->fetchrow_array();
 264 #       print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
 265         return $rows;
 266 }
 267
 268 ####################################################
 269 # recursing through filesystem structure and       #
 270 # and returning flattened files list               #
 271 ####################################################
 272 sub recurseDir($$$$$$$$) {
 273
 274         my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
 275
 276         print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
 277
 278         my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
 279
 280         { # scope
 281                 my @stack;
 282
 283                 my $files = BackupPC::View->new($bpc, $hostname, $backups);
 284                 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
 285
 286                 # first, add all the entries in current directory
 287                 foreach my $path_key (keys %{$filesInBackup}) {
 288                         my @data = (
 289                                 $shareID,
 290                                 $backupNum,
 291                                 $path_key,
 292                                 $filesInBackup->{$path_key}->{'relPath'},
 293                                 $filesInBackup->{$path_key}->{'fullPath'},
 294         #                       $filesInBackup->{$path_key}->{'sharePathM'},
 295                                 $filesInBackup->{$path_key}->{'mtime'},
 296                                 $filesInBackup->{$path_key}->{'type'},
 297                                 $filesInBackup->{$path_key}->{'size'}
 298                         );
 299
 300                         my $key = join(" ", (
 301                                 $shareID,
 302                                 $dir,
 303                                 $path_key,
 304                                 $filesInBackup->{$path_key}->{'mtime'},
 305                                 $filesInBackup->{$path_key}->{'size'}
 306                         ));
 307
 308
 309                         if (! $beenThere->{$key} && ! found_in_db(@data)) {
 310                                 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
 311                                 $sth->{'insert_files'}->execute(@data);
 312                                 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 313                                         $new_dirs++;
 314                                         print STDERR " dir\n" if ($debug >= 2);
 315                                 } else {
 316                                         $new_files++;
 317                                         print STDERR " file\n" if ($debug >= 2);
 318                                 }
 319                         }
 320                         $beenThere->{$key}++;
 321
 322                         if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
 323                                 $nr_dirs++;
 324
 325                                 my $full_path = $dir . '/' . $path_key;
 326                                 push @stack, $full_path;
 327                                 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
 328
 329 #                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
 330 #
 331 #                               $nr_files += $f;
 332 #                               $new_files += $nf;
 333 #                               $nr_dirs += $d;
 334 #                               $new_dirs += $nd;
 335
 336                         } else {
 337                                 $nr_files++;
 338                         }
 339                 }
 340
 341                 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
 342
 343                 while ( my $dir = shift @stack ) {
 344                         my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID);
 345                         print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
 346                         $nr_files += $f;
 347                         $new_files += $nf;
 348                         $nr_dirs += $d;
 349                         $new_dirs += $nd;
 350                 }
 351         }
 352
 353         return ($nr_files, $new_files, $nr_dirs, $new_dirs);
 354 }
 355