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