Annotation of /trunk/bin/BackupPC_updatedb

#!/usr/local/bin/perl -w

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;

$|=1;

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

my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";

my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });

my %opt;

if ( !getopts("cdm:", \%opt ) ) {
        print STDERR <<EOF;
usage: $0 [-c|-d] [-m num]

Options:
        -c      create database on first use
        -d      delete database before import
        -m num  import just num increments for one host
EOF
        exit 1;
}

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

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

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

        $dbh->do(qq{     
                create table files (
                        ID      INTEGER         NOT NULL PRIMARY KEY,  
                        shareID INTEGER         NOT NULL references shares(id),
                        backupNum  INTEGER      NOT NULL references backups(num),
                        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      references dvds(id)     
                );
        });

        print "creating indexes...\n";

        foreach my $index (qw(
                hosts_name
                backups_hostID
                backups_num
                shares_hostID
                shares_name
                files_shareID
                files_path
                files_name
                files_date
                files_size
        )) {
                my ($table,$col) = split(/_/, $index);
                $dbh->do(qq{ create index $index on $table($col) });
        }


}

if ($opt{d}) {
        print "deleting ";
        foreach my $table (qw(hosts shares files dvds backups)) {
                print "$table ";
                $dbh->do(qq{ DELETE FROM $table });
        }
        print " done...\n";
}

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

# get hosts
$hosts = $bpc->HostInfoRead();
my $hostID;
my $shareID;

my $sth;

$sth->{insert_hosts} = $dbh->prepare(qq{
INSERT INTO hosts (name, IP) VALUES (?,?)
});

$sth->{hosts_by_name} = $dbh->prepare(qq{
SELECT ID FROM hosts WHERE name=?
});

$sth->{backups_broj} = $dbh->prepare(qq{
SELECT COUNT(*)
FROM backups
WHERE hostID=? AND num=?
});

$sth->{insert_backups} = $dbh->prepare(qq{
INSERT INTO backups (hostID, num, date, type)
VALUES (?,?,?,?)
});

$sth->{insert_files} = $dbh->prepare(qq{
INSERT INTO files
        (shareID, backupNum, name, path, fullpath, date, type, size)
        VALUES (?,?,?,?,?,?,?,?)
});

foreach my $host_key (keys %{$hosts}) {

        my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";

        $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});

        unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
                $sth->{insert_hosts}->execute(
                        $hosts->{$host_key}->{'host'},
                        $hosts->{$host_key}->{'ip'}
                );

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

        print("host ".$hosts->{$host_key}->{'host'}.": ");
 
        # get backups for a host
        my @backups = $bpc->BackupInfoRead($hostname);
        print scalar @backups, " increments\n";

        my $inc_nr = 0;

        foreach my $backup (@backups) {
                $inc_nr++;
                last if ($opt{m} && $inc_nr > $opt{m});

                my $backupNum = $backup->{'num'};
                my @backupShares = ();

                print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";

                $sth->{backups_broj}->execute($hostID, $backupNum);
                my ($broj) = $sth->{backups_broj}->fetchrow_array();
                next if ($broj > 0);

                my $files = BackupPC::View->new($bpc, $hostname, \@backups);
                foreach my $share ($files->shareList($backupNum)) {

                        print "\t$share";
                        $shareID = getShareID($share, $hostID, $hostname);
                
                        my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
                        print " $nf/$f files $nd/$d dirs\n";
                        $dbh->commit();
                }

                $sth->{insert_backups}->execute(
                        $hostID,
                        $backupNum,
                        $backup->{'endTime'},
                        $backup->{'type'}
                );
                $dbh->commit();

        }
}
undef $sth;
$dbh->commit();
$dbh->disconnect();

sub getShareID() {

        my ($share, $hostID, $hostname) = @_;

        $sth->{share_id} ||= $dbh->prepare(qq{
                SELECT ID FROM shares WHERE hostID=? AND name=?
        });

        $sth->{share_id}->execute($hostID,$share);

        my ($id) = $sth->{share_id}->fetchrow_array();

        return $id if (defined($id));

        $sth->{insert_share} ||= $dbh->prepare(qq{
                INSERT INTO shares 
                        (hostID,name,share,localpath) 
                VALUES (?,?,?,?)
        });

        my $drop_down = $hostname . '/' . $share;
        $drop_down =~ s#//+#/#g;

        $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
        return $dbh->func('last_insert_rowid');         
}

sub found_in_db {

        my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;

        $sth->{file_in_db} ||= $dbh->prepare(qq{
                SELECT count(*) FROM files
                WHERE shareID = ? and
                        path = ? and 
                        name = ? and
                        date = ? and
                        size = ?
        });

        my @param = ($shareID,$path,$name,$date,$size);
        $sth->{file_in_db}->execute(@param);
        my ($rows) = $sth->{file_in_db}->fetchrow_array();
#       print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
        return $rows;
}

####################################################
# recursing through filesystem structure and       #
# and returning flattened files list               #
####################################################
sub recurseDir($$$$$$$$) {

        my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;

#print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n";

        my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);

        { # scope
                my @stack;

                my $files = BackupPC::View->new($bpc, $hostname, $backups);             
                my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);

                # first, add all the entries in current directory
                foreach my $path_key (keys %{$filesInBackup}) {
                        my @data = (
                                $shareID,
                                $backupNum,
                                $path_key,
                                $filesInBackup->{$path_key}->{'relPath'},
                                $filesInBackup->{$path_key}->{'fullPath'},
        #                       $filesInBackup->{$path_key}->{'sharePathM'},
                                $filesInBackup->{$path_key}->{'mtime'},
                                $filesInBackup->{$path_key}->{'type'},
                                $filesInBackup->{$path_key}->{'size'}
                        );

                        my $key = join(" ", (
                                $shareID,
                                $dir,
                                $path_key,
                                $filesInBackup->{$path_key}->{'mtime'},
                                $filesInBackup->{$path_key}->{'size'}
                        ));


                        if (! $beenThere->{$key} && ! found_in_db(@data)) {
##print STDERR "# key: $key [", $beenThere->{$key},"]";
                                $sth->{'insert_files'}->execute(@data);
                                if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                        $new_dirs++;
##print STDERR " dir\n";
                                } else {
                                        $new_files++;
##print STDERR " file\n";
                                }
                        }
                        $beenThere->{$key}++;

                        if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                $nr_dirs++;

                                my $full_path = $dir . '/' . $path_key;
                                push @stack, $full_path;
##print STDERR "### store to stack: $full_path\n";

#                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
#
#                               $nr_files += $f;
#                               $new_files += $nf;
#                               $nr_dirs += $d;
#                               $new_dirs += $nd;

                        } else {
                                $nr_files++;
                        }
                }

##print STDERR "# STACK ",join(", ", @stack),"\n";

                while ( my $dir = shift @stack ) {
                        my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID);
#       print STDERR "## $dir f: $f nf: $nf d: $d nd: $nd\n";
                        $nr_files += $f;
                        $new_files += $nf;
                        $nr_dirs += $d;
                        $new_dirs += $nd;
                }
        }

        return ($nr_files, $new_files, $nr_dirs, $new_dirs);
}

1	dpavlin	14	#!/usr/local/bin/perl -w
2	dpavlin	6
3			use strict;
4			use DBI;
5			use lib "__INSTALLDIR__/lib";
6			use BackupPC::Lib;
7			use BackupPC::View;
8			use Data::Dumper;
9			use Getopt::Std;
10			use constant BPC_FTYPE_DIR => 5;
11
12	dpavlin	14	$\|=1;
13	dpavlin	6
14			my $hosts;
15			my $bpc = BackupPC::Lib->new \|\| die;
16			my %Conf = $bpc->Conf();
17			my $TopDir = $bpc->TopDir();
18	dpavlin	14	my $beenThere = {};
19	dpavlin	6
20	dpavlin	14	my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
21	dpavlin	6
22	dpavlin	14	my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
23
24	dpavlin	6	my %opt;
25
26	dpavlin	14	if ( !getopts("cdm:", \%opt ) ) {
27	dpavlin	6	print STDERR <<EOF;
28	dpavlin	14	usage: $0 [-c\|-d] [-m num]
29	dpavlin	6
30			Options:
31	dpavlin	14	-c create database on first use
32			-d delete database before import
33			-m num import just num increments for one host
34	dpavlin	6	EOF
35			exit 1;
36			}
37
38			###################################create tables############################3
39
40	dpavlin	14	if ($opt{c}) {
41			print "creating tables...\n";
42	dpavlin	6
43	dpavlin	14	$dbh->do(qq{
44			create table hosts (
45			ID INTEGER PRIMARY KEY,
46			name VARCHAR(30) NOT NULL,
47			IP VARCHAR(15)
48	dpavlin	6	);
49	dpavlin	14	});
50	dpavlin	6
51	dpavlin	14	$dbh->do(qq{
52			create table shares (
53			ID INTEGER PRIMARY KEY,
54			hostID INTEGER NOT NULL references hosts(id),
55			name VARCHAR(30) NOT NULL,
56			share VARCHAR(200) NOT NULL,
57			localpath VARCHAR(200)
58	dpavlin	6	);
59	dpavlin	14	});
60	dpavlin	6
61	dpavlin	14	$dbh->do(qq{
62			create table backups (
63			hostID INTEGER NOT NULL references hosts(id),
64			num INTEGER NOT NULL,
65			date DATE,
66			type CHAR(1),
67			PRIMARY KEY(hostID, num)
68	dpavlin	6	);
69	dpavlin	14	});
70	dpavlin	6
71	dpavlin	14	$dbh->do(qq{
72			create table dvds (
73			ID INTEGER PRIMARY KEY,
74			num INTEGER NOT NULL,
75			name VARCHAR(255) NOT NULL,
76			mjesto VARCHAR(255)
77	dpavlin	6	);
78	dpavlin	14	});
79	dpavlin	6
80	dpavlin	14	$dbh->do(qq{
81			create table files (
82			ID INTEGER NOT NULL PRIMARY KEY,
83			shareID INTEGER NOT NULL references shares(id),
84	dpavlin	18	backupNum INTEGER NOT NULL references backups(num),
85	dpavlin	14	name VARCHAR(255) NOT NULL,
86			path VARCHAR(255) NOT NULL,
87			fullpath VARCHAR(255) NOT NULL,
88			date TIMESTAMP NOT NULL,
89			type INTEGER NOT NULL,
90			size INTEGER NOT NULL,
91			dvdid INTEGER references dvds(id)
92	dpavlin	6	);
93	dpavlin	14	});
94	dpavlin	6
95	dpavlin	14	print "creating indexes...\n";
96	dpavlin	6
97	dpavlin	14	foreach my $index (qw(
98			hosts_name
99			backups_hostID
100			backups_num
101			shares_hostID
102			shares_name
103			files_shareID
104			files_path
105			files_name
106			files_date
107			files_size
108			)) {
109			my ($table,$col) = split(/_/, $index);
110			$dbh->do(qq{ create index $index on $table($col) });
111			}
112
113
114			}
115
116			if ($opt{d}) {
117			print "deleting ";
118			foreach my $table (qw(hosts shares files dvds backups)) {
119			print "$table ";
120			$dbh->do(qq{ DELETE FROM $table });
121			}
122			print " done...\n";
123			}
124
125	dpavlin	6	#################################INSERT VALUES#############################
126
127			# get hosts
128	dpavlin	8	$hosts = $bpc->HostInfoRead();
129	dpavlin	6	my $hostID;
130			my $shareID;
131
132	dpavlin	14	my $sth;
133	dpavlin	6
134	dpavlin	14	$sth->{insert_hosts} = $dbh->prepare(qq{
135			INSERT INTO hosts (name, IP) VALUES (?,?)
136			});
137	dpavlin	6
138	dpavlin	14	$sth->{hosts_by_name} = $dbh->prepare(qq{
139			SELECT ID FROM hosts WHERE name=?
140			});
141
142			$sth->{backups_broj} = $dbh->prepare(qq{
143			SELECT COUNT(*)
144			FROM backups
145			WHERE hostID=? AND num=?
146			});
147
148			$sth->{insert_backups} = $dbh->prepare(qq{
149			INSERT INTO backups (hostID, num, date, type)
150			VALUES (?,?,?,?)
151			});
152
153			$sth->{insert_files} = $dbh->prepare(qq{
154			INSERT INTO files
155			(shareID, backupNum, name, path, fullpath, date, type, size)
156			VALUES (?,?,?,?,?,?,?,?)
157			});
158
159			foreach my $host_key (keys %{$hosts}) {
160
161			my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
162
163			$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
164
165			unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
166			$sth->{insert_hosts}->execute(
167			$hosts->{$host_key}->{'host'},
168			$hosts->{$host_key}->{'ip'}
169			);
170
171			$hostID = $dbh->func('last_insert_rowid');
172			}
173
174			print("host ".$hosts->{$host_key}->{'host'}.": ");
175	dpavlin	6
176	dpavlin	14	# get backups for a host
177			my @backups = $bpc->BackupInfoRead($hostname);
178			print scalar @backups, " increments\n";
179	dpavlin	6
180	dpavlin	14	my $inc_nr = 0;
181
182			foreach my $backup (@backups) {
183			$inc_nr++;
184			last if ($opt{m} && $inc_nr > $opt{m});
185
186			my $backupNum = $backup->{'num'};
187			my @backupShares = ();
188
189	dpavlin	15	print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";
190	dpavlin	14
191			$sth->{backups_broj}->execute($hostID, $backupNum);
192			my ($broj) = $sth->{backups_broj}->fetchrow_array();
193			next if ($broj > 0);
194
195			my $files = BackupPC::View->new($bpc, $hostname, \@backups);
196			foreach my $share ($files->shareList($backupNum)) {
197
198			print "\t$share";
199			$shareID = getShareID($share, $hostID, $hostname);
200
201			my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
202			print " $nf/$f files $nd/$d dirs\n";
203			$dbh->commit();
204			}
205	dpavlin	29
206			$sth->{insert_backups}->execute(
207			$hostID,
208			$backupNum,
209			$backup->{'endTime'},
210			$backup->{'type'}
211			);
212			$dbh->commit();
213
214	dpavlin	6	}
215			}
216	dpavlin	14	undef $sth;
217	dpavlin	6	$dbh->commit();
218			$dbh->disconnect();
219
220	dpavlin	14	sub getShareID() {
221	dpavlin	6
222	dpavlin	14	my ($share, $hostID, $hostname) = @_;
223
224			$sth->{share_id} \|\|= $dbh->prepare(qq{
225			SELECT ID FROM shares WHERE hostID=? AND name=?
226			});
227
228			$sth->{share_id}->execute($hostID,$share);
229
230			my ($id) = $sth->{share_id}->fetchrow_array();
231
232			return $id if (defined($id));
233
234			$sth->{insert_share} \|\|= $dbh->prepare(qq{
235			INSERT INTO shares
236			(hostID,name,share,localpath)
237			VALUES (?,?,?,?)
238			});
239
240	dpavlin	25	my $drop_down = $hostname . '/' . $share;
241			$drop_down =~ s#//+#/#g;
242
243			$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
244	dpavlin	14	return $dbh->func('last_insert_rowid');
245	dpavlin	6	}
246
247	dpavlin	14	sub found_in_db {
248
249			my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
250
251			$sth->{file_in_db} \|\|= $dbh->prepare(qq{
252			SELECT count(*) FROM files
253			WHERE shareID = ? and
254			path = ? and
255			name = ? and
256			date = ? and
257			size = ?
258			});
259
260			my @param = ($shareID,$path,$name,$date,$size);
261			$sth->{file_in_db}->execute(@param);
262			my ($rows) = $sth->{file_in_db}->fetchrow_array();
263	dpavlin	15	# print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
264	dpavlin	14	return $rows;
265	dpavlin	6	}
266
267			####################################################
268			# recursing through filesystem structure and #
269			# and returning flattened files list #
270			####################################################
271	dpavlin	14	sub recurseDir($$$$$$$$) {
272	dpavlin	6
273	dpavlin	14	my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
274
275	dpavlin	29	#print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n";
276
277	dpavlin	14	my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
278
279	dpavlin	27	{ # scope
280	dpavlin	29	my @stack;
281	dpavlin	14
282	dpavlin	27	my $files = BackupPC::View->new($bpc, $hostname, $backups);
283			my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
284	dpavlin	14
285	dpavlin	27	# first, add all the entries in current directory
286			foreach my $path_key (keys %{$filesInBackup}) {
287			my @data = (
288			$shareID,
289			$backupNum,
290			$path_key,
291			$filesInBackup->{$path_key}->{'relPath'},
292			$filesInBackup->{$path_key}->{'fullPath'},
293			# $filesInBackup->{$path_key}->{'sharePathM'},
294			$filesInBackup->{$path_key}->{'mtime'},
295			$filesInBackup->{$path_key}->{'type'},
296			$filesInBackup->{$path_key}->{'size'}
297			);
298
299			my $key = join(" ", (
300			$shareID,
301			$dir,
302			$path_key,
303			$filesInBackup->{$path_key}->{'mtime'},
304			$filesInBackup->{$path_key}->{'size'}
305			));
306
307
308			if (! $beenThere->{$key} && ! found_in_db(@data)) {
309	dpavlin	29	##print STDERR "# key: $key [", $beenThere->{$key},"]";
310	dpavlin	27	$sth->{'insert_files'}->execute(@data);
311			if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
312			$new_dirs++;
313	dpavlin	29	##print STDERR " dir\n";
314	dpavlin	27	} else {
315			$new_files++;
316	dpavlin	29	##print STDERR " file\n";
317	dpavlin	27	}
318			}
319			$beenThere->{$key}++;
320
321	dpavlin	14	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
322	dpavlin	27	$nr_dirs++;
323
324	dpavlin	29	my $full_path = $dir . '/' . $path_key;
325			push @stack, $full_path;
326			##print STDERR "### store to stack: $full_path\n";
327
328	dpavlin	27	# my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
329			#
330			# $nr_files += $f;
331			# $new_files += $nf;
332			# $nr_dirs += $d;
333			# $new_dirs += $nd;
334
335	dpavlin	14	} else {
336	dpavlin	27	$nr_files++;
337	dpavlin	14	}
338			}
339
340	dpavlin	29	##print STDERR "# STACK ",join(", ", @stack),"\n";
341	dpavlin	14
342	dpavlin	29	while ( my $dir = shift @stack ) {
343			my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID);
344			# print STDERR "## $dir f: $f nf: $nf d: $d nd: $nd\n";
345			$nr_files += $f;
346			$new_files += $nf;
347			$nr_dirs += $d;
348			$new_dirs += $nd;
349			}
350	dpavlin	14	}
351
352			return ($nr_files, $new_files, $nr_dirs, $new_dirs);
353	dpavlin	6	}
354