Annotation of /trunk/bin/BackupPC_updatedb

#!/usr/local/bin/perl -w
$| = 1;

use strict;
use DBI;
use lib "__INSTALLDIR__/lib";
use BackupPC::Lib;
use BackupPC::View;
use Data::Dumper;
use Getopt::Std;
use constant BPC_FTYPE_DIR => 5;


my $hosts;
my $bpc = BackupPC::Lib->new || die;
my %Conf = $bpc->Conf();
my $TopDir = $bpc->TopDir();
my @beenThere = ();

my $dbh = DBI->connect("dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}", "", "", { RaiseError => 1, AutoCommit => 0 });

my %opt;

if ( !getopts("cdu", \%opt ) ) {
        print STDERR <<EOF;
usage: $0 (-c|-df|-u)

Options:
        -c      Create database on first use
        -d      Delete database before import
        -u      Update database (import new revisions)
EOF
        exit 1;
}

###################################create tables############################3

if ($opt{c})
    {
      print "creating database...";
      
      $dbh->do(
          q{
              create table hosts 
                (   ID             INTEGER              PRIMARY KEY,
                    name             VARCHAR(30)        NOT NULL,
                    IP       VARCHAR(20)        NOT NULL
                );            
          }
      );
              
      $dbh->do(
          q{
              create table shares 
                (   ID             INTEGER           PRIMARY KEY,
                    hostID         INTEGER           NOT NULL,
                    name           VARCHAR(30)       NOT NULL,
                    share          VARCHAR(200)      NOT NULL,
                    localpath      VARCHAR(200)      
                );            
          }
      );
        
      $dbh->do(
          q{
              create table backups 
                ( hostID     INTEGER            NOT NULL, 
                    num      INTEGER            NOT NULL,
                    date             DATE, 
                    type             CHAR(1), 
                    PRIMARY KEY(hostID, num) 
                );            
          }
      );

      $dbh->do(
          q{
              create table dvds
                ( ID         INTEGER            PRIMARY KEY, 
                    num      INTEGER            NOT NULL,
                    name             VARCHAR(255)       NOT NULL,
                    mjesto     VARCHAR(255)
                );
          }
      );

      $dbh->do(
          q{     
              create table files
                (   ID         INTEGER          NOT NULL PRIMARY KEY,  
                    shareID    INTEGER          NOT NULL,
                    backupNum  INTEGER          NOT NULL,
                    name       VARCHAR(255)     NOT NULL,
                    path       VARCHAR(255)     NOT NULL,
                    fullpath   VARCHAR(255)     NOT NULL,
                    date       TIMESTAMP        NOT NULL,
                    type       INTEGER          NOT NULL,
                    size       INTEGER          NOT NULL,
                    dvdid      INTEGER          
                );
          }
      );
      print "done\n";
  }

if ($opt{d})
  {
      print("deleting db first...\n");
      
      $dbh->do(
          q{ DELETE FROM hosts; }
          );
      $dbh->do(
          q{ DELETE FROM shares; }
          );
      $dbh->do(
          q{ DELETE FROM files;}
          );
      $dbh->do(
          q{ DELETE FROM dvds;}
          );
      $dbh->do(
          q{ DELETE FROM backups; }
          );
  }

#################################INSERT VALUES#############################

# get hosts
$hosts = $bpc->HostInfoRead("localhost");
my $host_key;
my $hostID;
my $shareID;
foreach $host_key (keys %{$hosts})
{
  my $hostname = $hosts->{$host_key}->{'host'};
  my $backups;
  my $sql; 

  $sql = q{ SELECT ID FROM hosts WHERE name=? };
  my $st = $dbh->prepare($sql);
  $st->bind_param(1,$hosts->{$host_key}->{'host'});
  $st->execute();
  my $tmp = $st->fetchrow_hashref();
  if ($tmp->{'ID'} ne "")
      {
          $hostID = $tmp->{'ID'};
      }
    else
      {
          $sql = q{ INSERT INTO hosts ( ID, name, IP) VALUES (NULL,"}.
                    $hosts->{$host_key}->{'host'}."\", \"".
                   $hosts->{$host_key}->{'ip'}."\");";

          $dbh->do($sql);
          $hostID = $dbh->func('last_insert_rowid');

      }
  $st->finish();
  print("processing host ".$hosts->{$host_key}->{'host'}.":\n");
 
  # get backups for a host
  my @backups = $bpc->BackupInfoRead($hostname);
  foreach my $backup (@backups)
  {
    my $backupNum = $backup->{'num'};
    my @backupShares = ();

      
    if ($opt{u})
        {
            my $sql = q{
                  SELECT COUNT(*) AS broj
                  FROM backups
                  WHERE hostID=? AND
                        num=?
                };
            
            my $st  = $dbh->prepare($sql);
            $st->bind_param(1,$hostID);
            $st->bind_param(2,$backupNum);
            $st->execute();
            my $tmp = $st->fetchrow_hashref();
            $st->finish();
            if ($tmp->{'broj'} > 0)
              {
                  next;
              }
        }
      
    print "\tprocessing backup no. $backupNum...";
    my $sql =     
        q{ 
            INSERT INTO backups (hostID, num, date, type)
              VALUES
              (}.$hostID.",". $backupNum.q{, }.$backup->{'endTime'}.",\"". $backup->{'type'}.q{");
         }; 
    $dbh->do($sql);

    my $files = BackupPC::View->new($bpc, $hostname, \@backups);
    @backupShares = $files->shareList($backupNum);
    foreach my $share (@backupShares)
    {      
      my @flattenedFiles = ();
      clearBeenThereCache();
      print "\n\t\tprocessing share ".$share."...";
      $shareID = getShareID($share, $hostID, $hostname);
        
      @flattenedFiles = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "");
      print "done\n";
      print "\t\tinserting data into db...";
      foreach my $file (@flattenedFiles)
          {
              $dbh->do("INSERT INTO files(ID, shareID, backupNum, name, path, fullpath, date, type, size) VALUES "
                  ."( NULL, $shareID, "
                  .$backupNum.", \"" 
                  .$file->{'fileName'}."\", \""
                  .$file->{'relPath'}. "\", \""
                  .$file->{'fullPath'}."\", "
                  .$file->{'mtime'}.", "
                  .$file->{'type'}.", "
                  .$file->{'size'}.")"
                  );
          }
      print "done\n";
    }
  }
  print "done.\n";
}
$dbh->commit();
$dbh->disconnect();


sub haveBeenThere
{
  my ($where) = @_;
  
  foreach my $st (@beenThere)
  {
    if ($where eq $st)
    {
      return 1;
    }
  }
  
  push(@beenThere, $where);
  return 0;
}

sub clearBeenThereCache
{
  @beenThere = ();
}

sub getShareID()
  {
      my ($share, $hostID, $hostname) = @_;
      my $tmp;
      
      my $st = $dbh -> prepare(" SELECT shares.ID AS ID FROM shares WHERE hostID=? AND name=?");
      $st -> execute($hostID,$share);
      my $tmp = $st->fetchrow_hashref();
      $st->finish();
      if ($tmp)
        {
            return $tmp->{'ID'};
        }
      my $sql = 
          q{
              INSERT INTO shares(ID,hostID,name,share,localpath) 
                VALUES   
               (NULL,}.                  
              "$hostID,\"$share\",\"//$hostname$share\",NULL);";              
      $dbh->do($sql);
      return $dbh->func('last_insert_rowid');           
  }

####################################################
# recursing through filesystem structure and       #
# and returning flattened files list               #
####################################################
sub recurseDir
{
  my ($bpc, $hostname, $backups, $backupNo, $share, $dir) = @_;
  my @ret = ();
  my $files = BackupPC::View->new($bpc, $hostname, $backups);             
  my $filesInBackup = $files->dirAttrib($backupNo, $share, $dir);
  my $file_key = ""; 

 
  # first, add all the entries in current directory
  foreach $file_key (keys %{$filesInBackup})
  {
    push(@ret, {
                 'fileName' => $file_key,
                 'relPath'  => $filesInBackup->{$file_key}->{'relPath'},
                 'fullPath' => $filesInBackup->{$file_key}->{'fullPath'},
                 'sharePath'=> $filesInBackup->{$file_key}->{'sharePathM'},
                 'size'     => $filesInBackup->{$file_key}->{'size'},
                 'mtime'    => $filesInBackup->{$file_key}->{'mtime'},
                 'type'     => $filesInBackup->{$file_key}->{'type'}
               });  
  }
    
  # then, recurse thru subfolders
  foreach my $fold (@ret)
  {
   if ($fold->{'type'} == BPC_FTYPE_DIR &&
       haveBeenThere($fold->{'relPath'}) != 1
      )
    {
      
      push(@ret, 
           recurseDir($bpc, $hostname, $backups, $backupNo, $share, $fold->{'relPath'})
          );
    }
  }
  return @ret;
}

1	dpavlin	6	#!/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