Annotation of /trunk/bin/BackupPC_updatedb

#!/usr/local/bin/perl
$| = 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();
print Dumper($hosts);
my $hostID;
my $shareID;
foreach my $host_key (keys %{$hosts})
{
  my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
  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();
  if (my $tmp = $st->fetchrow_hashref())
      {
          $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	8	#!/usr/local/bin/perl
2	dpavlin	6	$\| = 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	dpavlin	8	$hosts = $bpc->HostInfoRead();
131			print Dumper($hosts);
132	dpavlin	6	my $hostID;
133			my $shareID;
134	dpavlin	8	foreach my $host_key (keys %{$hosts})
135	dpavlin	6	{
136	dpavlin	8	my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
137	dpavlin	6	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	dpavlin	8	if (my $tmp = $st->fetchrow_hashref())
145	dpavlin	6	{
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