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;

my $debug = 0;
$|=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" if ($debug >= 1);

        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},"]" if ($debug >= 2);
                                $sth->{'insert_files'}->execute(@data);
                                if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                        $new_dirs++;
                                        print STDERR " dir\n" if ($debug >= 2);
                                } else {
                                        $new_files++;
                                        print STDERR " file\n" if ($debug >= 2);
                                }
                        }
                        $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" if ($debug >= 3);

#                               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" if ($debug >= 2);

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