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:v", \%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";
}

if ($opt{v}) {
        print "Debug level at $opt{v}\n";
        $debug = $opt{v};
}

#################################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, 1);
                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, 1);
                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	32	if ( !getopts("cdm:v", \%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	32	if ($opt{v}) {
127			print "Debug level at $opt{v}\n";
128			$debug = $opt{v};
129			}
130
131	dpavlin	6	#################################INSERT VALUES#############################
132
133			# get hosts
134	dpavlin	8	$hosts = $bpc->HostInfoRead();
135	dpavlin	6	my $hostID;
136			my $shareID;
137
138	dpavlin	14	my $sth;
139	dpavlin	6
140	dpavlin	14	$sth->{insert_hosts} = $dbh->prepare(qq{
141			INSERT INTO hosts (name, IP) VALUES (?,?)
142			});
143	dpavlin	6
144	dpavlin	14	$sth->{hosts_by_name} = $dbh->prepare(qq{
145			SELECT ID FROM hosts WHERE name=?
146			});
147
148			$sth->{backups_broj} = $dbh->prepare(qq{
149			SELECT COUNT(*)
150			FROM backups
151			WHERE hostID=? AND num=?
152			});
153
154			$sth->{insert_backups} = $dbh->prepare(qq{
155			INSERT INTO backups (hostID, num, date, type)
156			VALUES (?,?,?,?)
157			});
158
159			$sth->{insert_files} = $dbh->prepare(qq{
160			INSERT INTO files
161			(shareID, backupNum, name, path, fullpath, date, type, size)
162			VALUES (?,?,?,?,?,?,?,?)
163			});
164
165			foreach my $host_key (keys %{$hosts}) {
166
167			my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
168
169			$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
170
171			unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
172			$sth->{insert_hosts}->execute(
173			$hosts->{$host_key}->{'host'},
174			$hosts->{$host_key}->{'ip'}
175			);
176
177			$hostID = $dbh->func('last_insert_rowid');
178			}
179
180			print("host ".$hosts->{$host_key}->{'host'}.": ");
181	dpavlin	6
182	dpavlin	14	# get backups for a host
183			my @backups = $bpc->BackupInfoRead($hostname);
184			print scalar @backups, " increments\n";
185	dpavlin	6
186	dpavlin	14	my $inc_nr = 0;
187
188			foreach my $backup (@backups) {
189			$inc_nr++;
190			last if ($opt{m} && $inc_nr > $opt{m});
191
192			my $backupNum = $backup->{'num'};
193			my @backupShares = ();
194
195	dpavlin	15	print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";
196	dpavlin	14
197			$sth->{backups_broj}->execute($hostID, $backupNum);
198			my ($broj) = $sth->{backups_broj}->fetchrow_array();
199			next if ($broj > 0);
200
201	dpavlin	34	my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
202	dpavlin	14	foreach my $share ($files->shareList($backupNum)) {
203
204			print "\t$share";
205			$shareID = getShareID($share, $hostID, $hostname);
206
207			my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
208			print " $nf/$f files $nd/$d dirs\n";
209			$dbh->commit();
210			}
211	dpavlin	29
212			$sth->{insert_backups}->execute(
213			$hostID,
214			$backupNum,
215			$backup->{'endTime'},
216			$backup->{'type'}
217			);
218			$dbh->commit();
219
220	dpavlin	6	}
221			}
222	dpavlin	14	undef $sth;
223	dpavlin	6	$dbh->commit();
224			$dbh->disconnect();
225
226	dpavlin	14	sub getShareID() {
227	dpavlin	6
228	dpavlin	14	my ($share, $hostID, $hostname) = @_;
229
230			$sth->{share_id} \|\|= $dbh->prepare(qq{
231			SELECT ID FROM shares WHERE hostID=? AND name=?
232			});
233
234			$sth->{share_id}->execute($hostID,$share);
235
236			my ($id) = $sth->{share_id}->fetchrow_array();
237
238			return $id if (defined($id));
239
240			$sth->{insert_share} \|\|= $dbh->prepare(qq{
241			INSERT INTO shares
242			(hostID,name,share,localpath)
243			VALUES (?,?,?,?)
244			});
245
246	dpavlin	25	my $drop_down = $hostname . '/' . $share;
247			$drop_down =~ s#//+#/#g;
248
249			$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
250	dpavlin	14	return $dbh->func('last_insert_rowid');
251	dpavlin	6	}
252
253	dpavlin	14	sub found_in_db {
254
255			my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
256
257			$sth->{file_in_db} \|\|= $dbh->prepare(qq{
258			SELECT count(*) FROM files
259			WHERE shareID = ? and
260			path = ? and
261			name = ? and
262			date = ? and
263			size = ?
264			});
265
266			my @param = ($shareID,$path,$name,$date,$size);
267			$sth->{file_in_db}->execute(@param);
268			my ($rows) = $sth->{file_in_db}->fetchrow_array();
269	dpavlin	15	# print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
270	dpavlin	14	return $rows;
271	dpavlin	6	}
272
273			####################################################
274			# recursing through filesystem structure and #
275			# and returning flattened files list #
276			####################################################
277	dpavlin	14	sub recurseDir($$$$$$$$) {
278	dpavlin	6
279	dpavlin	14	my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
280
281	dpavlin	30	print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
282	dpavlin	29
283	dpavlin	14	my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
284
285	dpavlin	27	{ # scope
286	dpavlin	29	my @stack;
287	dpavlin	14
288	dpavlin	34	my $files = BackupPC::View->new($bpc, $hostname, $backups, 1);
289	dpavlin	27	my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
290	dpavlin	14
291	dpavlin	27	# first, add all the entries in current directory
292			foreach my $path_key (keys %{$filesInBackup}) {
293			my @data = (
294			$shareID,
295			$backupNum,
296			$path_key,
297			$filesInBackup->{$path_key}->{'relPath'},
298			$filesInBackup->{$path_key}->{'fullPath'},
299			# $filesInBackup->{$path_key}->{'sharePathM'},
300			$filesInBackup->{$path_key}->{'mtime'},
301			$filesInBackup->{$path_key}->{'type'},
302			$filesInBackup->{$path_key}->{'size'}
303			);
304
305			my $key = join(" ", (
306			$shareID,
307			$dir,
308			$path_key,
309			$filesInBackup->{$path_key}->{'mtime'},
310			$filesInBackup->{$path_key}->{'size'}
311			));
312
313
314			if (! $beenThere->{$key} && ! found_in_db(@data)) {
315	dpavlin	30	print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
316	dpavlin	27	$sth->{'insert_files'}->execute(@data);
317			if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
318			$new_dirs++;
319	dpavlin	30	print STDERR " dir\n" if ($debug >= 2);
320	dpavlin	27	} else {
321			$new_files++;
322	dpavlin	30	print STDERR " file\n" if ($debug >= 2);
323	dpavlin	27	}
324			}
325			$beenThere->{$key}++;
326
327	dpavlin	14	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
328	dpavlin	27	$nr_dirs++;
329
330	dpavlin	29	my $full_path = $dir . '/' . $path_key;
331			push @stack, $full_path;
332	dpavlin	30	print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
333	dpavlin	29
334	dpavlin	27	# my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
335			#
336			# $nr_files += $f;
337			# $new_files += $nf;
338			# $nr_dirs += $d;
339			# $new_dirs += $nd;
340
341	dpavlin	14	} else {
342	dpavlin	27	$nr_files++;
343	dpavlin	14	}
344			}
345
346	dpavlin	30	print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
347	dpavlin	14
348	dpavlin	29	while ( my $dir = shift @stack ) {
349			my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID);
350	dpavlin	30	print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
351	dpavlin	29	$nr_files += $f;
352			$new_files += $nf;
353			$nr_dirs += $d;
354			$new_dirs += $nd;
355			}
356	dpavlin	14	}
357
358			return ($nr_files, $new_files, $nr_dirs, $new_dirs);
359	dpavlin	6	}
360