bin/BackupPC_updatedb

   1 #!/usr/local/bin/perl
   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();
 131 print Dumper($hosts);
 132 my $hostID;
 133 my $shareID;
 134 foreach my $host_key (keys %{$hosts})
 135 {
 136   my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 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   if (my $tmp = $st->fetchrow_hashref())
 145       {
 146           $hostID = $tmp->{'ID'};
 147       }
 148     else
 149       {
 150           $sql = q{ INSERT INTO hosts ( ID, name, IP) VALUES (NULL,"}.
 151                     $hosts->{$host_key}->{'host'}."\", \"".
 152                    $hosts->{$host_key}->{'ip'}."\");";
 153
 154           $dbh->do($sql);
 155           $hostID = $dbh->func('last_insert_rowid');
 156
 157       }
 158   $st->finish();
 159   print("processing host ".$hosts->{$host_key}->{'host'}.":\n");
 160
 161   # get backups for a host
 162   my @backups = $bpc->BackupInfoRead($hostname);
 163   foreach my $backup (@backups)
 164   {
 165     my $backupNum = $backup->{'num'};
 166     my @backupShares = ();
 167
 168
 169     if ($opt{u})
 170         {
 171             my $sql = q{
 172                   SELECT COUNT(*) AS broj
 173                   FROM backups
 174                   WHERE hostID=? AND
 175                         num=?
 176                 };
 177
 178             my $st  = $dbh->prepare($sql);
 179             $st->bind_param(1,$hostID);
 180             $st->bind_param(2,$backupNum);
 181             $st->execute();
 182             my $tmp = $st->fetchrow_hashref();
 183             $st->finish();
 184             if ($tmp->{'broj'} > 0)
 185               {
 186                   next;
 187               }
 188         }
 189
 190     print "\tprocessing backup no. $backupNum...";
 191     my $sql =
 192         q{
 193             INSERT INTO backups (hostID, num, date, type)
 194               VALUES
 195               (}.$hostID.",". $backupNum.q{, }.$backup->{'endTime'}.",\"". $backup->{'type'}.q{");
 196          };
 197     $dbh->do($sql);
 198
 199     my $files = BackupPC::View->new($bpc, $hostname, \@backups);
 200     @backupShares = $files->shareList($backupNum);
 201     foreach my $share (@backupShares)
 202     {
 203       my @flattenedFiles = ();
 204       clearBeenThereCache();
 205       print "\n\t\tprocessing share ".$share."...";
 206       $shareID = getShareID($share, $hostID, $hostname);
 207
 208       @flattenedFiles = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "");
 209       print "done\n";
 210       print "\t\tinserting data into db...";
 211       foreach my $file (@flattenedFiles)
 212           {
 213               $dbh->do("INSERT INTO files(ID, shareID, backupNum, name, path, fullpath, date, type, size) VALUES "
 214                   ."( NULL, $shareID, "
 215                   .$backupNum.", \""
 216                   .$file->{'fileName'}."\", \""
 217                   .$file->{'relPath'}. "\", \""
 218                   .$file->{'fullPath'}."\", "
 219                   .$file->{'mtime'}.", "
 220                   .$file->{'type'}.", "
 221                   .$file->{'size'}.")"
 222                   );
 223           }
 224       print "done\n";
 225     }
 226   }
 227   print "done.\n";
 228 }
 229 $dbh->commit();
 230 $dbh->disconnect();
 231
 232
 233 sub haveBeenThere
 234 {
 235   my ($where) = @_;
 236
 237   foreach my $st (@beenThere)
 238   {
 239     if ($where eq $st)
 240     {
 241       return 1;
 242     }
 243   }
 244
 245   push(@beenThere, $where);
 246   return 0;
 247 }
 248
 249 sub clearBeenThereCache
 250 {
 251   @beenThere = ();
 252 }
 253
 254 sub getShareID()
 255   {
 256       my ($share, $hostID, $hostname) = @_;
 257       my $tmp;
 258
 259       my $st = $dbh -> prepare(" SELECT shares.ID AS ID FROM shares WHERE hostID=? AND name=?");
 260       $st -> execute($hostID,$share);
 261       my $tmp = $st->fetchrow_hashref();
 262       $st->finish();
 263       if ($tmp)
 264         {
 265             return $tmp->{'ID'};
 266         }
 267       my $sql =
 268           q{
 269               INSERT INTO shares(ID,hostID,name,share,localpath)
 270                 VALUES
 271                (NULL,}.
 272               "$hostID,\"$share\",\"//$hostname$share\",NULL);";
 273       $dbh->do($sql);
 274       return $dbh->func('last_insert_rowid');
 275   }
 276
 277 ####################################################
 278 # recursing through filesystem structure and       #
 279 # and returning flattened files list               #
 280 ####################################################
 281 sub recurseDir
 282 {
 283   my ($bpc, $hostname, $backups, $backupNo, $share, $dir) = @_;
 284   my @ret = ();
 285   my $files = BackupPC::View->new($bpc, $hostname, $backups);
 286   my $filesInBackup = $files->dirAttrib($backupNo, $share, $dir);
 287   my $file_key = "";
 288
 289
 290   # first, add all the entries in current directory
 291   foreach $file_key (keys %{$filesInBackup})
 292   {
 293     push(@ret, {
 294                  'fileName' => $file_key,
 295                  'relPath'  => $filesInBackup->{$file_key}->{'relPath'},
 296                  'fullPath' => $filesInBackup->{$file_key}->{'fullPath'},
 297                  'sharePath'=> $filesInBackup->{$file_key}->{'sharePathM'},
 298                  'size'     => $filesInBackup->{$file_key}->{'size'},
 299                  'mtime'    => $filesInBackup->{$file_key}->{'mtime'},
 300                  'type'     => $filesInBackup->{$file_key}->{'type'}
 301                });
 302   }
 303
 304   # then, recurse thru subfolders
 305   foreach my $fold (@ret)
 306   {
 307    if ($fold->{'type'} == BPC_FTYPE_DIR &&
 308        haveBeenThere($fold->{'relPath'}) != 1
 309       )
 310     {
 311
 312       push(@ret,
 313            recurseDir($bpc, $hostname, $backups, $backupNo, $share, $fold->{'relPath'})
 314           );
 315     }
 316   }
 317   return @ret;
 318 }
 319