bin/BackupPC_updatedb

   1 #!/usr/local/bin/perl -w
   2 $| = 1;
   3
   4 use strict;
   5 use DBI;
   6 use lib "__INSTALLDIR__/lib";
   7 use BackupPC::Lib;
   8 use BackupPC::View;
   9 use Data::Dumper;
  10 use Getopt::Std;
  11 use constant BPC_FTYPE_DIR => 5;
  12
  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 $dbh = DBI->connect("dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}", "", "", { RaiseError => 1, AutoCommit => 0 });
  21
  22 my %opt;
  23
  24 if ( !getopts("cdu", \%opt ) ) {
  25         print STDERR <<EOF;
  26 usage: $0 (-c|-df|-u)
  27
  28 Options:
  29         -c      Create database on first use
  30         -d      Delete database before import
  31         -u      Update database (import new revisions)
  32 EOF
  33         exit 1;
  34 }
  35
  36 ###################################create tables############################3
  37
  38 if ($opt{c})
  39     {
  40       print "creating database...";
  41
  42       $dbh->do(
  43           q{
  44               create table hosts
  45                 (   ID             INTEGER              PRIMARY KEY,
  46                     name             VARCHAR(30)        NOT NULL,
  47                     IP       VARCHAR(20)        NOT NULL
  48                 );
  49           }
  50       );
  51
  52       $dbh->do(
  53           q{
  54               create table shares
  55                 (   ID             INTEGER           PRIMARY KEY,
  56                     hostID         INTEGER           NOT NULL,
  57                     name           VARCHAR(30)       NOT NULL,
  58                     share          VARCHAR(200)      NOT NULL,
  59                     localpath      VARCHAR(200)
  60                 );
  61           }
  62       );
  63
  64       $dbh->do(
  65           q{
  66               create table backups
  67                 ( hostID     INTEGER            NOT NULL,
  68                     num      INTEGER            NOT NULL,
  69                     date             DATE,
  70                     type             CHAR(1),
  71                     PRIMARY KEY(hostID, num)
  72                 );
  73           }
  74       );
  75
  76       $dbh->do(
  77           q{
  78               create table dvds
  79                 ( ID         INTEGER            PRIMARY KEY,
  80                     num      INTEGER            NOT NULL,
  81                     name             VARCHAR(255)       NOT NULL,
  82                     mjesto     VARCHAR(255)
  83                 );
  84           }
  85       );
  86
  87       $dbh->do(
  88           q{
  89               create table files
  90                 (   ID         INTEGER          NOT NULL PRIMARY KEY,
  91                     shareID    INTEGER          NOT NULL,
  92                     backupNum  INTEGER          NOT NULL,
  93                     name       VARCHAR(255)     NOT NULL,
  94                     path       VARCHAR(255)     NOT NULL,
  95                     fullpath   VARCHAR(255)     NOT NULL,
  96                     date       TIMESTAMP        NOT NULL,
  97                     type       INTEGER          NOT NULL,
  98                     size       INTEGER          NOT NULL,
  99                     dvdid      INTEGER
 100                 );
 101           }
 102       );
 103       print "done\n";
 104   }
 105
 106 if ($opt{d})
 107   {
 108       print("deleting db first...\n");
 109
 110       $dbh->do(
 111           q{ DELETE FROM hosts; }
 112           );
 113       $dbh->do(
 114           q{ DELETE FROM shares; }
 115           );
 116       $dbh->do(
 117           q{ DELETE FROM files;}
 118           );
 119       $dbh->do(
 120           q{ DELETE FROM dvds;}
 121           );
 122       $dbh->do(
 123           q{ DELETE FROM backups; }
 124           );
 125   }
 126
 127 #################################INSERT VALUES#############################
 128
 129 # get hosts
 130 $hosts = $bpc->HostInfoRead("localhost");
 131 my $host_key;
 132 my $hostID;
 133 my $shareID;
 134 foreach $host_key (keys %{$hosts})
 135 {
 136   my $hostname = $hosts->{$host_key}->{'host'};
 137   my $backups;
 138   my $sql;
 139
 140   $sql = q{ SELECT ID FROM hosts WHERE name=? };
 141   my $st = $dbh->prepare($sql);
 142   $st->bind_param(1,$hosts->{$host_key}->{'host'});
 143   $st->execute();
 144   my $tmp = $st->fetchrow_hashref();
 145   if ($tmp->{'ID'} ne "")
 146       {
 147           $hostID = $tmp->{'ID'};
 148       }
 149     else
 150       {
 151           $sql = q{ INSERT INTO hosts ( ID, name, IP) VALUES (NULL,"}.
 152                     $hosts->{$host_key}->{'host'}."\", \"".
 153                    $hosts->{$host_key}->{'ip'}."\");";
 154
 155           $dbh->do($sql);
 156           $hostID = $dbh->func('last_insert_rowid');
 157
 158       }
 159   $st->finish();
 160   print("processing host ".$hosts->{$host_key}->{'host'}.":\n");
 161
 162   # get backups for a host
 163   my @backups = $bpc->BackupInfoRead($hostname);
 164   foreach my $backup (@backups)
 165   {
 166     my $backupNum = $backup->{'num'};
 167     my @backupShares = ();
 168
 169
 170     if ($opt{u})
 171         {
 172             my $sql = q{
 173                   SELECT COUNT(*) AS broj
 174                   FROM backups
 175                   WHERE hostID=? AND
 176                         num=?
 177                 };
 178
 179             my $st  = $dbh->prepare($sql);
 180             $st->bind_param(1,$hostID);
 181             $st->bind_param(2,$backupNum);
 182             $st->execute();
 183             my $tmp = $st->fetchrow_hashref();
 184             $st->finish();
 185             if ($tmp->{'broj'} > 0)
 186               {
 187                   next;
 188               }
 189         }
 190
 191     print "\tprocessing backup no. $backupNum...";
 192     my $sql =
 193         q{
 194             INSERT INTO backups (hostID, num, date, type)
 195               VALUES
 196               (}.$hostID.",". $backupNum.q{, }.$backup->{'endTime'}.",\"". $backup->{'type'}.q{");
 197          };
 198     $dbh->do($sql);
 199
 200     my $files = BackupPC::View->new($bpc, $hostname, \@backups);
 201     @backupShares = $files->shareList($backupNum);
 202     foreach my $share (@backupShares)
 203     {
 204       my @flattenedFiles = ();
 205       clearBeenThereCache();
 206       print "\n\t\tprocessing share ".$share."...";
 207       $shareID = getShareID($share, $hostID, $hostname);
 208
 209       @flattenedFiles = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "");
 210       print "done\n";
 211       print "\t\tinserting data into db...";
 212       foreach my $file (@flattenedFiles)
 213           {
 214               $dbh->do("INSERT INTO files(ID, shareID, backupNum, name, path, fullpath, date, type, size) VALUES "
 215                   ."( NULL, $shareID, "
 216                   .$backupNum.", \""
 217                   .$file->{'fileName'}."\", \""
 218                   .$file->{'relPath'}. "\", \""
 219                   .$file->{'fullPath'}."\", "
 220                   .$file->{'mtime'}.", "
 221                   .$file->{'type'}.", "
 222                   .$file->{'size'}.")"
 223                   );
 224           }
 225       print "done\n";
 226     }
 227   }
 228   print "done.\n";
 229 }
 230 $dbh->commit();
 231 $dbh->disconnect();
 232
 233
 234 sub haveBeenThere
 235 {
 236   my ($where) = @_;
 237
 238   foreach my $st (@beenThere)
 239   {
 240     if ($where eq $st)
 241     {
 242       return 1;
 243     }
 244   }
 245
 246   push(@beenThere, $where);
 247   return 0;
 248 }
 249
 250 sub clearBeenThereCache
 251 {
 252   @beenThere = ();
 253 }
 254
 255 sub getShareID()
 256   {
 257       my ($share, $hostID, $hostname) = @_;
 258       my $tmp;
 259
 260       my $st = $dbh -> prepare(" SELECT shares.ID AS ID FROM shares WHERE hostID=? AND name=?");
 261       $st -> execute($hostID,$share);
 262       my $tmp = $st->fetchrow_hashref();
 263       $st->finish();
 264       if ($tmp)
 265         {
 266             return $tmp->{'ID'};
 267         }
 268       my $sql =
 269           q{
 270               INSERT INTO shares(ID,hostID,name,share,localpath)
 271                 VALUES
 272                (NULL,}.
 273               "$hostID,\"$share\",\"//$hostname$share\",NULL);";
 274       $dbh->do($sql);
 275       return $dbh->func('last_insert_rowid');
 276   }
 277
 278 ####################################################
 279 # recursing through filesystem structure and       #
 280 # and returning flattened files list               #
 281 ####################################################
 282 sub recurseDir
 283 {
 284   my ($bpc, $hostname, $backups, $backupNo, $share, $dir) = @_;
 285   my @ret = ();
 286   my $files = BackupPC::View->new($bpc, $hostname, $backups);
 287   my $filesInBackup = $files->dirAttrib($backupNo, $share, $dir);
 288   my $file_key = "";
 289
 290
 291   # first, add all the entries in current directory
 292   foreach $file_key (keys %{$filesInBackup})
 293   {
 294     push(@ret, {
 295                  'fileName' => $file_key,
 296                  'relPath'  => $filesInBackup->{$file_key}->{'relPath'},
 297                  'fullPath' => $filesInBackup->{$file_key}->{'fullPath'},
 298                  'sharePath'=> $filesInBackup->{$file_key}->{'sharePathM'},
 299                  'size'     => $filesInBackup->{$file_key}->{'size'},
 300                  'mtime'    => $filesInBackup->{$file_key}->{'mtime'},
 301                  'type'     => $filesInBackup->{$file_key}->{'type'}
 302                });
 303   }
 304
 305   # then, recurse thru subfolders
 306   foreach my $fold (@ret)
 307   {
 308    if ($fold->{'type'} == BPC_FTYPE_DIR &&
 309        haveBeenThere($fold->{'relPath'}) != 1
 310       )
 311     {
 312
 313       push(@ret,
 314            recurseDir($bpc, $hostname, $backups, $backupNo, $share, $fold->{'relPath'})
 315           );
 316     }
 317   }
 318   return @ret;
 319 }
 320