Annotation of /trunk/bin/BackupPC_updatedb

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

use strict;
use lib "__INSTALLDIR__/lib";

use DBI;
use BackupPC::Lib;
use BackupPC::View;
use Data::Dumper;
use Getopt::Std;
use Time::HiRes qw/time/;
use File::Pid;
use POSIX qw/strftime/;

use constant BPC_FTYPE_DIR => 5;

my $debug = 0;
$|=1;

my $start_t = time();

my $pidfile = new File::Pid;

if (my $pid = $pidfile->running ) {
        die "$0 already running: $pid\n";
} elsif ($pidfile->pid ne $$) {
        $pidfile->remove;
        $pidfile = new File::Pid;
}
$pidfile->write;
print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";

my $t_fmt = '%Y-%m-%d %H:%M:%S';

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

my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
my $user = $Conf{SearchUser} || '';

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

my %opt;

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

Options:
        -c      create database on first use
        -d      delete database before import
        -m num  import just num increments for one host
        -v num  set verbosity (debug) level (default $debug)
        -i      update HyperEstraier full text index
EOF
        exit 1;
}

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

## update index ##
if ($opt{i}) {

        my $index_dir = '/var/tmp/casket';

        print "updating HyperEstraier index $index_dir...";

        use HyperEstraier;
        my $db = HyperEstraier::Database->new();
        $db->open($index_dir, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);

        my $sth = $dbh->prepare(qq{
                SELECT
                        files.id                        AS fid,
                        hosts.name                      AS hname,
                        shares.name                     AS sname,
                        shares.share                    AS sharename,
                        files.backupNum                 AS backupNum,
                        files.name                      AS filename,
                        files.path                      AS filepath,
                        files.date                      AS date,
                        files.type                      AS filetype,
                        files.size                      AS size,
                        files.shareid                   AS shareid
                FROM files 
                        INNER JOIN shares       ON files.shareID=shares.ID
                        INNER JOIN hosts        ON hosts.ID = shares.hostID
                        INNER JOIN backups      ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
        });

        $sth->execute();

        my $dot = int($sth->rows / 15);

        print $sth->rows, " files ($dot/#) ";

        sub fmt_date {
                my $t = shift || return;
                my $iso = BackupPC::Lib::timeStamp($t);
                $iso =~ s/\s/T/;
                return $iso;
        }

        my $i = 0;
        my $max = int($sth->rows / $dot);

        while (my $row = $sth->fetchrow_hashref()) {

                # create a document object 
                my $doc = HyperEstraier::Document->new;

                # add attributes to the document object 
                $doc->add_attr('@uri', 'file:///' . $row->{'fid'});

                foreach my $c (qw/fid hname sname sharename backupNum filename filepath shareid/) {
                        $doc->add_attr($c, $row->{$c}) if ($row->{$c});
                }

                $doc->add_attr('date', fmt_date($row->{'date'}));

                # add the body text to the document object 
                my $path = $row->{'filepath'};
                $doc->add_text($path);
                $path =~ s/(.)/$1 /g;
                $doc->add_hidden_text($path);

                print STDERR $doc->dump_draft,"\n" if ($debug > 1);

                # register the document object to the database
                $db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);

                $i++;
                if ($i % $dot == 0) {
                        print "$max ";
                        $max--;
                }

        }

        print "sync";
        $db->sync();
        print " close\n";
        $db->close();

        exit;
}

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

if ($opt{c}) {
        sub do_index {
                my $index = shift || return;
                my ($table,$col,$unique) = split(/_/, $index);
                $unique ||= '';
                $index =~ s/,/_/g;
                $dbh->do(qq{ create $unique index $index on $table($col) });
        }

        print "creating tables...\n";
      
        $dbh->do(qq{
                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,
                        IP      VARCHAR(15)
                );            
        });
              
        $dbh->do(qq{
                create table shares (
                        ID      SERIAL          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    integer         NOT NULL, 
                        type    CHAR(4)         not null,
                        shareID integer         not null references shares(id),
                        size    integer         not null,
                        PRIMARY KEY(hostID, num, shareID) 
                );            
        });

        #do_index('backups_hostid,num_unique');

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

        $dbh->do(qq{     
                create table files (
                        ID      SERIAL          PRIMARY KEY,  
                        shareID INTEGER         NOT NULL references shares(id),
                        backupNum  INTEGER      NOT NULL,
                        name       VARCHAR(255) NOT NULL,
                        path       VARCHAR(255) NOT NULL,
                        date       integer      NOT NULL,
                        type       INTEGER      NOT NULL,
                        size       INTEGER      NOT NULL,
                        dvdid      INTEGER      references dvds(id)     
                );
        });

        print "creating indexes:";

        foreach my $index (qw(
                hosts_name
                backups_hostID
                backups_num
                shares_hostID
                shares_name
                files_shareID
                files_path
                files_name
                files_date
                files_size
        )) {
                print " $index";
                do_index($index);
        }
        print "...\n";

        $dbh->commit;

}

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

        $dbh->commit;
}

#################################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_count} = $dbh->prepare(qq{
SELECT COUNT(*)
FROM backups
WHERE hostID=? AND num=? AND shareid=?
});

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

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

sub fmt_time {
        my $t = shift || return;
        my $out = "";
        my ($ss,$mm,$hh) = gmtime($t);
        $out .= "${hh}h" if ($hh);
        $out .= sprintf("%02d:%02d", $mm,$ss);
        return $out;
}

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->last_insert_id(undef,undef,'hosts',undef);
        }

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

        my $inc_nr = 0;
        $beenThere = {};

        foreach my $backup (@backups) {

                $inc_nr++;
                last if ($opt{m} && $inc_nr > $opt{m});

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

                printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n", 
                        $hosts->{$host_key}->{'host'},
                        $inc_nr, $incs, $backupNum, 
                        $backup->{type} || '?',
                        $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
                        strftime($t_fmt,localtime($backup->{startTime})),
                        fmt_time($backup->{endTime} - $backup->{startTime})
                );

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

                        my $t = time();

                        $shareID = getShareID($share, $hostID, $hostname);
                
                        $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
                        my ($count) = $sth->{backups_count}->fetchrow_array();
                        # skip if allready in database!
                        next if ($count > 0);

                        # dump some log
                        print strftime($t_fmt,localtime())," ", $share;

                        my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);

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

                        print " commit";
                        $dbh->commit();

                        my $dur = (time() - $t) || 1;
                        printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
                                $nf, $f, $nd, $d,
                                ($size / 1024 / 1024),
                                ( ($f+$d) / $dur ),
                                fmt_time($dur)
                        );
                }

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

print "total duration: ",fmt_time(time() - $start_t),"\n";

$pidfile->remove;

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->last_insert_id(undef,undef,'shares',undef);
}

sub found_in_db {

        my @data = @_;
        shift @data;

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

        return $beenThere->{$key} if (defined($beenThere->{$key}));

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

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

        $beenThere->{$key}++;

        $sth->{'insert_files'}->execute(@data) unless ($rows);
        return $rows;
}

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

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

        print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);

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

        { # scope
                my @stack;

                print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
                my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);

                # first, add all the entries in current directory
                foreach my $path_key (keys %{$filesInBackup}) {
                        print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
                        my @data = (
                                $shareID,
                                $backupNum,
                                $path_key,
                                $filesInBackup->{$path_key}->{'relPath'},
                                $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'}
                        ));

                        my $found;
                        if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
                                print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);

                                if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                        $new_dirs++ unless ($found);
                                        print STDERR " dir\n" if ($debug >= 2);
                                } else {
                                        $new_files++ unless ($found);
                                        print STDERR " file\n" if ($debug >= 2);
                                }
                                $size += $filesInBackup->{$path_key}->{'size'} || 0;
                        }

                        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, $s) = recurseDir($bpc, $hostname, $files, $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;
                        $size += $s;
                }
        }

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

1	dpavlin	14	#!/usr/local/bin/perl -w
2	dpavlin	6
3			use strict;
4	dpavlin	48	use lib "__INSTALLDIR__/lib";
5
6	dpavlin	6	use DBI;
7			use BackupPC::Lib;
8			use BackupPC::View;
9			use Data::Dumper;
10			use Getopt::Std;
11	dpavlin	37	use Time::HiRes qw/time/;
12	dpavlin	38	use File::Pid;
13	dpavlin	37	use POSIX qw/strftime/;
14	dpavlin	48
15	dpavlin	6	use constant BPC_FTYPE_DIR => 5;
16
17	dpavlin	30	my $debug = 0;
18	dpavlin	14	$\|=1;
19	dpavlin	6
20	dpavlin	51	my $start_t = time();
21
22	dpavlin	38	my $pidfile = new File::Pid;
23
24			if (my $pid = $pidfile->running ) {
25			die "$0 already running: $pid\n";
26			} elsif ($pidfile->pid ne $$) {
27			$pidfile->remove;
28			$pidfile = new File::Pid;
29			}
30	dpavlin	39	$pidfile->write;
31			print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
32	dpavlin	38
33	dpavlin	37	my $t_fmt = '%Y-%m-%d %H:%M:%S';
34
35	dpavlin	6	my $hosts;
36			my $bpc = BackupPC::Lib->new \|\| die;
37			my %Conf = $bpc->Conf();
38			my $TopDir = $bpc->TopDir();
39	dpavlin	14	my $beenThere = {};
40	dpavlin	6
41	dpavlin	51	my $dsn = $Conf{SearchDSN} \|\| die "Need SearchDSN in config.pl\n";
42			my $user = $Conf{SearchUser} \|\| '';
43	dpavlin	6
44	dpavlin	49	my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
45
46	dpavlin	6	my %opt;
47
48	dpavlin	81	if ( !getopts("cdm:v:i", \%opt ) ) {
49	dpavlin	6	print STDERR <<EOF;
50	dpavlin	81	usage: $0 [-c\|-d] [-m num] [-v\|-v level] [-i]
51	dpavlin	6
52			Options:
53	dpavlin	14	-c create database on first use
54			-d delete database before import
55			-m num import just num increments for one host
56	dpavlin	44	-v num set verbosity (debug) level (default $debug)
57	dpavlin	81	-i update HyperEstraier full text index
58	dpavlin	6	EOF
59			exit 1;
60			}
61
62	dpavlin	81	if ($opt{v}) {
63			print "Debug level at $opt{v}\n";
64			$debug = $opt{v};
65			}
66
67			## update index ##
68			if ($opt{i}) {
69
70			my $index_dir = '/var/tmp/casket';
71
72			print "updating HyperEstraier index $index_dir...";
73
74			use HyperEstraier;
75			my $db = HyperEstraier::Database->new();
76			$db->open($index_dir, $HyperEstraier::Database::DBWRITER \| $HyperEstraier::Database::DBCREAT);
77
78			my $sth = $dbh->prepare(qq{
79			SELECT
80			files.id AS fid,
81			hosts.name AS hname,
82			shares.name AS sname,
83			shares.share AS sharename,
84			files.backupNum AS backupNum,
85			files.name AS filename,
86			files.path AS filepath,
87			files.date AS date,
88			files.type AS filetype,
89			files.size AS size,
90			files.shareid AS shareid
91			FROM files
92			INNER JOIN shares ON files.shareID=shares.ID
93			INNER JOIN hosts ON hosts.ID = shares.hostID
94			INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
95			});
96
97			$sth->execute();
98
99			my $dot = int($sth->rows / 15);
100
101			print $sth->rows, " files ($dot/#) ";
102
103			sub fmt_date {
104			my $t = shift \|\| return;
105			my $iso = BackupPC::Lib::timeStamp($t);
106			$iso =~ s/\s/T/;
107			return $iso;
108			}
109
110			my $i = 0;
111			my $max = int($sth->rows / $dot);
112
113			while (my $row = $sth->fetchrow_hashref()) {
114
115			# create a document object
116			my $doc = HyperEstraier::Document->new;
117
118			# add attributes to the document object
119			$doc->add_attr('@uri', 'file:///' . $row->{'fid'});
120
121			foreach my $c (qw/fid hname sname sharename backupNum filename filepath shareid/) {
122			$doc->add_attr($c, $row->{$c}) if ($row->{$c});
123			}
124
125			$doc->add_attr('date', fmt_date($row->{'date'}));
126
127			# add the body text to the document object
128			my $path = $row->{'filepath'};
129			$doc->add_text($path);
130			$path =~ s/(.)/$1 /g;
131			$doc->add_hidden_text($path);
132
133			print STDERR $doc->dump_draft,"\n" if ($debug > 1);
134
135			# register the document object to the database
136			$db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
137
138			$i++;
139			if ($i % $dot == 0) {
140			print "$max ";
141			$max--;
142			}
143
144			}
145
146			print "sync";
147			$db->sync();
148			print " close\n";
149			$db->close();
150
151			exit;
152			}
153
154	dpavlin	6	###################################create tables############################3
155
156	dpavlin	14	if ($opt{c}) {
157	dpavlin	49	sub do_index {
158			my $index = shift \|\| return;
159			my ($table,$col,$unique) = split(/_/, $index);
160			$unique \|\|= '';
161	dpavlin	52	$index =~ s/,/_/g;
162	dpavlin	49	$dbh->do(qq{ create $unique index $index on $table($col) });
163			}
164
165	dpavlin	14	print "creating tables...\n";
166	dpavlin	6
167	dpavlin	14	$dbh->do(qq{
168			create table hosts (
169	dpavlin	49	ID SERIAL PRIMARY KEY,
170	dpavlin	14	name VARCHAR(30) NOT NULL,
171			IP VARCHAR(15)
172	dpavlin	6	);
173	dpavlin	14	});
174	dpavlin	6
175	dpavlin	14	$dbh->do(qq{
176			create table shares (
177	dpavlin	49	ID SERIAL PRIMARY KEY,
178	dpavlin	14	hostID INTEGER NOT NULL references hosts(id),
179			name VARCHAR(30) NOT NULL,
180			share VARCHAR(200) NOT NULL,
181			localpath VARCHAR(200)
182	dpavlin	6	);
183	dpavlin	14	});
184	dpavlin	6
185	dpavlin	14	$dbh->do(qq{
186			create table backups (
187			hostID INTEGER NOT NULL references hosts(id),
188			num INTEGER NOT NULL,
189	dpavlin	49	date integer NOT NULL,
190			type CHAR(4) not null,
191	dpavlin	65	shareID integer not null references shares(id),
192	dpavlin	66	size integer not null,
193	dpavlin	65	PRIMARY KEY(hostID, num, shareID)
194	dpavlin	6	);
195	dpavlin	14	});
196	dpavlin	6
197	dpavlin	65	#do_index('backups_hostid,num_unique');
198	dpavlin	49
199	dpavlin	14	$dbh->do(qq{
200			create table dvds (
201	dpavlin	49	ID SERIAL PRIMARY KEY,
202	dpavlin	14	num INTEGER NOT NULL,
203			name VARCHAR(255) NOT NULL,
204			mjesto VARCHAR(255)
205	dpavlin	6	);
206	dpavlin	14	});
207	dpavlin	6
208	dpavlin	14	$dbh->do(qq{
209			create table files (
210	dpavlin	49	ID SERIAL PRIMARY KEY,
211	dpavlin	14	shareID INTEGER NOT NULL references shares(id),
212	dpavlin	52	backupNum INTEGER NOT NULL,
213	dpavlin	14	name VARCHAR(255) NOT NULL,
214			path VARCHAR(255) NOT NULL,
215	dpavlin	49	date integer NOT NULL,
216	dpavlin	14	type INTEGER NOT NULL,
217			size INTEGER NOT NULL,
218			dvdid INTEGER references dvds(id)
219	dpavlin	6	);
220	dpavlin	14	});
221	dpavlin	6
222	dpavlin	49	print "creating indexes:";
223	dpavlin	6
224	dpavlin	14	foreach my $index (qw(
225			hosts_name
226			backups_hostID
227			backups_num
228			shares_hostID
229			shares_name
230			files_shareID
231			files_path
232			files_name
233			files_date
234			files_size
235			)) {
236	dpavlin	49	print " $index";
237			do_index($index);
238	dpavlin	14	}
239	dpavlin	49	print "...\n";
240	dpavlin	14
241	dpavlin	49	$dbh->commit;
242	dpavlin	14
243			}
244
245			if ($opt{d}) {
246			print "deleting ";
247	dpavlin	49	foreach my $table (qw(files dvds backups shares hosts)) {
248	dpavlin	14	print "$table ";
249			$dbh->do(qq{ DELETE FROM $table });
250			}
251			print " done...\n";
252	dpavlin	49
253	dpavlin	51	$dbh->commit;
254	dpavlin	14	}
255
256	dpavlin	6	#################################INSERT VALUES#############################
257
258			# get hosts
259	dpavlin	8	$hosts = $bpc->HostInfoRead();
260	dpavlin	6	my $hostID;
261			my $shareID;
262
263	dpavlin	14	my $sth;
264	dpavlin	6
265	dpavlin	14	$sth->{insert_hosts} = $dbh->prepare(qq{
266			INSERT INTO hosts (name, IP) VALUES (?,?)
267			});
268	dpavlin	6
269	dpavlin	14	$sth->{hosts_by_name} = $dbh->prepare(qq{
270			SELECT ID FROM hosts WHERE name=?
271			});
272
273	dpavlin	65	$sth->{backups_count} = $dbh->prepare(qq{
274	dpavlin	14	SELECT COUNT(*)
275			FROM backups
276	dpavlin	65	WHERE hostID=? AND num=? AND shareid=?
277	dpavlin	14	});
278
279			$sth->{insert_backups} = $dbh->prepare(qq{
280	dpavlin	66	INSERT INTO backups (hostID, num, date, type, shareid, size)
281			VALUES (?,?,?,?,?,?)
282	dpavlin	14	});
283
284			$sth->{insert_files} = $dbh->prepare(qq{
285			INSERT INTO files
286	dpavlin	62	(shareID, backupNum, name, path, date, type, size)
287			VALUES (?,?,?,?,?,?,?)
288	dpavlin	14	});
289
290	dpavlin	50	sub fmt_time {
291			my $t = shift \|\| return;
292			my $out = "";
293			my ($ss,$mm,$hh) = gmtime($t);
294			$out .= "${hh}h" if ($hh);
295			$out .= sprintf("%02d:%02d", $mm,$ss);
296			return $out;
297			}
298
299	dpavlin	14	foreach my $host_key (keys %{$hosts}) {
300
301			my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
302
303			$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
304
305			unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
306			$sth->{insert_hosts}->execute(
307			$hosts->{$host_key}->{'host'},
308			$hosts->{$host_key}->{'ip'}
309			);
310
311	dpavlin	49	$hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
312	dpavlin	14	}
313
314	dpavlin	67	print "host ".$hosts->{$host_key}->{'host'}.": ";
315	dpavlin	6
316	dpavlin	14	# get backups for a host
317			my @backups = $bpc->BackupInfoRead($hostname);
318	dpavlin	67	my $incs = scalar @backups;
319			print "$incs increments\n";
320	dpavlin	6
321	dpavlin	14	my $inc_nr = 0;
322	dpavlin	67	$beenThere = {};
323	dpavlin	14
324			foreach my $backup (@backups) {
325	dpavlin	40
326	dpavlin	14	$inc_nr++;
327			last if ($opt{m} && $inc_nr > $opt{m});
328
329			my $backupNum = $backup->{'num'};
330			my @backupShares = ();
331
332	dpavlin	67	printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
333			$hosts->{$host_key}->{'host'},
334			$inc_nr, $incs, $backupNum,
335			$backup->{type} \|\| '?',
336			$backup->{nFilesNew} \|\| '?', $backup->{nFiles} \|\| '?',
337	dpavlin	57	strftime($t_fmt,localtime($backup->{startTime})),
338	dpavlin	67	fmt_time($backup->{endTime} - $backup->{startTime})
339			);
340	dpavlin	14
341	dpavlin	34	my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
342	dpavlin	14	foreach my $share ($files->shareList($backupNum)) {
343
344	dpavlin	37	my $t = time();
345
346	dpavlin	14	$shareID = getShareID($share, $hostID, $hostname);
347
348	dpavlin	65	$sth->{backups_count}->execute($hostID, $backupNum, $shareID);
349			my ($count) = $sth->{backups_count}->fetchrow_array();
350			# skip if allready in database!
351			next if ($count > 0);
352
353			# dump some log
354			print strftime($t_fmt,localtime())," ", $share;
355
356	dpavlin	66	my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
357	dpavlin	65
358			$sth->{insert_backups}->execute(
359			$hostID,
360			$backupNum,
361			$backup->{'endTime'},
362			$backup->{'type'},
363	dpavlin	66	$shareID,
364			$size,
365	dpavlin	65	);
366
367			print " commit";
368			$dbh->commit();
369
370	dpavlin	50	my $dur = (time() - $t) \|\| 1;
371	dpavlin	66	printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
372	dpavlin	37	$nf, $f, $nd, $d,
373	dpavlin	66	($size / 1024 / 1024),
374	dpavlin	50	( ($f+$d) / $dur ),
375			fmt_time($dur)
376	dpavlin	37	);
377	dpavlin	14	}
378	dpavlin	29
379	dpavlin	6	}
380			}
381	dpavlin	14	undef $sth;
382	dpavlin	6	$dbh->commit();
383			$dbh->disconnect();
384
385	dpavlin	51	print "total duration: ",fmt_time(time() - $start_t),"\n";
386
387	dpavlin	38	$pidfile->remove;
388
389	dpavlin	14	sub getShareID() {
390	dpavlin	6
391	dpavlin	14	my ($share, $hostID, $hostname) = @_;
392
393			$sth->{share_id} \|\|= $dbh->prepare(qq{
394			SELECT ID FROM shares WHERE hostID=? AND name=?
395			});
396
397			$sth->{share_id}->execute($hostID,$share);
398
399			my ($id) = $sth->{share_id}->fetchrow_array();
400
401			return $id if (defined($id));
402
403			$sth->{insert_share} \|\|= $dbh->prepare(qq{
404			INSERT INTO shares
405			(hostID,name,share,localpath)
406			VALUES (?,?,?,?)
407			});
408
409	dpavlin	25	my $drop_down = $hostname . '/' . $share;
410			$drop_down =~ s#//+#/#g;
411
412			$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
413	dpavlin	49	return $dbh->last_insert_id(undef,undef,'shares',undef);
414	dpavlin	6	}
415
416	dpavlin	14	sub found_in_db {
417
418	dpavlin	48	my @data = @_;
419			shift @data;
420	dpavlin	14
421	dpavlin	74	my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
422	dpavlin	48
423			return $beenThere->{$key} if (defined($beenThere->{$key}));
424
425	dpavlin	14	$sth->{file_in_db} \|\|= $dbh->prepare(qq{
426	dpavlin	48	SELECT 1 FROM files
427	dpavlin	14	WHERE shareID = ? and
428			path = ? and
429			date = ? and
430			size = ?
431	dpavlin	74	LIMIT 1
432	dpavlin	14	});
433
434	dpavlin	67	my @param = ($shareID,$path,$date,$size);
435	dpavlin	14	$sth->{file_in_db}->execute(@param);
436	dpavlin	48	my $rows = $sth->{file_in_db}->rows;
437	dpavlin	74	print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
438	dpavlin	48
439			$beenThere->{$key}++;
440
441			$sth->{'insert_files'}->execute(@data) unless ($rows);
442	dpavlin	14	return $rows;
443	dpavlin	6	}
444
445			####################################################
446			# recursing through filesystem structure and #
447			# and returning flattened files list #
448			####################################################
449	dpavlin	14	sub recurseDir($$$$$$$$) {
450	dpavlin	6
451	dpavlin	35	my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
452	dpavlin	14
453	dpavlin	44	print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
454	dpavlin	29
455	dpavlin	66	my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
456	dpavlin	14
457	dpavlin	27	{ # scope
458	dpavlin	29	my @stack;
459	dpavlin	14
460	dpavlin	45	print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
461	dpavlin	27	my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
462	dpavlin	14
463	dpavlin	27	# first, add all the entries in current directory
464			foreach my $path_key (keys %{$filesInBackup}) {
465	dpavlin	66	print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
466	dpavlin	27	my @data = (
467			$shareID,
468			$backupNum,
469			$path_key,
470			$filesInBackup->{$path_key}->{'relPath'},
471			$filesInBackup->{$path_key}->{'mtime'},
472			$filesInBackup->{$path_key}->{'type'},
473			$filesInBackup->{$path_key}->{'size'}
474			);
475
476			my $key = join(" ", (
477			$shareID,
478			$dir,
479			$path_key,
480			$filesInBackup->{$path_key}->{'mtime'},
481			$filesInBackup->{$path_key}->{'size'}
482			));
483
484	dpavlin	70	my $found;
485			if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
486	dpavlin	30	print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
487	dpavlin	48
488	dpavlin	27	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
489	dpavlin	70	$new_dirs++ unless ($found);
490	dpavlin	30	print STDERR " dir\n" if ($debug >= 2);
491	dpavlin	27	} else {
492	dpavlin	70	$new_files++ unless ($found);
493	dpavlin	30	print STDERR " file\n" if ($debug >= 2);
494	dpavlin	27	}
495	dpavlin	66	$size += $filesInBackup->{$path_key}->{'size'} \|\| 0;
496	dpavlin	27	}
497
498	dpavlin	14	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
499	dpavlin	27	$nr_dirs++;
500
501	dpavlin	29	my $full_path = $dir . '/' . $path_key;
502			push @stack, $full_path;
503	dpavlin	30	print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
504	dpavlin	29
505	dpavlin	27	# my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
506			#
507			# $nr_files += $f;
508			# $new_files += $nf;
509			# $nr_dirs += $d;
510			# $new_dirs += $nd;
511
512	dpavlin	14	} else {
513	dpavlin	27	$nr_files++;
514	dpavlin	14	}
515			}
516
517	dpavlin	30	print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
518	dpavlin	14
519	dpavlin	29	while ( my $dir = shift @stack ) {
520	dpavlin	66	my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
521	dpavlin	30	print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
522	dpavlin	29	$nr_files += $f;
523			$new_files += $nf;
524			$nr_dirs += $d;
525			$new_dirs += $nd;
526	dpavlin	66	$size += $s;
527	dpavlin	29	}
528	dpavlin	14	}
529
530	dpavlin	66	return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
531	dpavlin	6	}
532