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

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

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

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

        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)) {
                        $sth->{'insert_files'}->execute(@data);
#                       print STDERR "$key\n";
                        if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                $new_dirs++;
                        } else {
                                $new_files++;
                        }
                }
                $beenThere->{$key}++;

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

                        my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID);

                        $nr_files += $f;
                        $new_files += $nf;
                        $nr_dirs += $d;
                        $new_dirs += $nd;

                } else {
                        $nr_files++;
                }
        }

        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			$sth->{insert_backups}->execute(
196			$hostID,
197			$backupNum,
198			$backup->{'endTime'},
199			$backup->{'type'}
200			);
201			$dbh->commit();
202
203			my $files = BackupPC::View->new($bpc, $hostname, \@backups);
204			foreach my $share ($files->shareList($backupNum)) {
205
206			print "\t$share";
207			$shareID = getShareID($share, $hostID, $hostname);
208
209			my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
210			print " $nf/$f files $nd/$d dirs\n";
211			$dbh->commit();
212			}
213	dpavlin	6	}
214			}
215	dpavlin	14	undef $sth;
216	dpavlin	6	$dbh->commit();
217			$dbh->disconnect();
218
219	dpavlin	14	sub getShareID() {
220	dpavlin	6
221	dpavlin	14	my ($share, $hostID, $hostname) = @_;
222
223			$sth->{share_id} \|\|= $dbh->prepare(qq{
224			SELECT ID FROM shares WHERE hostID=? AND name=?
225			});
226
227			$sth->{share_id}->execute($hostID,$share);
228
229			my ($id) = $sth->{share_id}->fetchrow_array();
230
231			return $id if (defined($id));
232
233			$sth->{insert_share} \|\|= $dbh->prepare(qq{
234			INSERT INTO shares
235			(hostID,name,share,localpath)
236			VALUES (?,?,?,?)
237			});
238
239	dpavlin	25	my $drop_down = $hostname . '/' . $share;
240			$drop_down =~ s#//+#/#g;
241
242			$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
243	dpavlin	14	return $dbh->func('last_insert_rowid');
244	dpavlin	6	}
245
246	dpavlin	14	sub found_in_db {
247
248			my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
249
250			$sth->{file_in_db} \|\|= $dbh->prepare(qq{
251			SELECT count(*) FROM files
252			WHERE shareID = ? and
253			path = ? and
254			name = ? and
255			date = ? and
256			size = ?
257			});
258
259			my @param = ($shareID,$path,$name,$date,$size);
260			$sth->{file_in_db}->execute(@param);
261			my ($rows) = $sth->{file_in_db}->fetchrow_array();
262	dpavlin	15	# print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
263	dpavlin	14	return $rows;
264	dpavlin	6	}
265
266			####################################################
267			# recursing through filesystem structure and #
268			# and returning flattened files list #
269			####################################################
270	dpavlin	14	sub recurseDir($$$$$$$$) {
271	dpavlin	6
272	dpavlin	14	my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
273
274			my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
275
276			my $files = BackupPC::View->new($bpc, $hostname, $backups);
277			my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
278
279			# first, add all the entries in current directory
280			foreach my $path_key (keys %{$filesInBackup}) {
281			my @data = (
282			$shareID,
283			$backupNum,
284			$path_key,
285			$filesInBackup->{$path_key}->{'relPath'},
286			$filesInBackup->{$path_key}->{'fullPath'},
287			# $filesInBackup->{$path_key}->{'sharePathM'},
288			$filesInBackup->{$path_key}->{'mtime'},
289			$filesInBackup->{$path_key}->{'type'},
290			$filesInBackup->{$path_key}->{'size'}
291			);
292
293			my $key = join(" ", (
294			$shareID,
295			$dir,
296			$path_key,
297			$filesInBackup->{$path_key}->{'mtime'},
298			$filesInBackup->{$path_key}->{'size'}
299			));
300
301
302			if (! $beenThere->{$key} && ! found_in_db(@data)) {
303			$sth->{'insert_files'}->execute(@data);
304	dpavlin	15	# print STDERR "$key\n";
305	dpavlin	14	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
306			$new_dirs++;
307			} else {
308			$new_files++;
309			}
310			}
311			$beenThere->{$key}++;
312
313			if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
314			$nr_dirs++;
315
316			my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID);
317
318			$nr_files += $f;
319			$new_files += $nf;
320			$nr_dirs += $d;
321			$new_dirs += $nd;
322
323			} else {
324			$nr_files++;
325			}
326			}
327
328			return ($nr_files, $new_files, $nr_dirs, $new_dirs);
329	dpavlin	6	}
330