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 = ();

print Dumper(\%Conf);

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