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 $index_path = $Conf{HyperEstraierIndex};
$index_path = $TopDir . '/' . $index_path;
$index_path =~ s#//#/#g;


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

#---- subs ----

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

sub curr_time {
        return strftime($t_fmt,localtime());
}

my $hest_db;

sub hest_update {

        my ($host_id, $share_id, $num) = @_;

        print curr_time," updating HyperEstraier: files";

        my $t = time();

        my $where = '';
        if ($host_id && $share_id && $num) {
                $where = qq{
                WHERE
                        hosts.id = ? AND
                        shares.id = ? AND
                        files.backupnum = ?
                };
        }

        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,
                        backups.date                    AS backup_date
                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
                $where
        });

        $sth->execute(@_);
        my $results = $sth->rows;

        if ($results == 0) {
                print " no files\n";
                return;
        }

        my $dot = int($results / 15) || 1;

        print " $results ($dot/#)";

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

        my $max = int($results / $dot);

        print " index $index_path...";
        use HyperEstraier;
        my $db = HyperEstraier::Database->new();

#       unless ($hest_db) {
#               print " open reader";
#               $hest_db = HyperEstraier::Database->new();
#
#       }


        $db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);

        my $added = 0;

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

                my $fid = $row->{'fid'} || die "no fid?";
                my $uri = 'file:///' . $fid;

                next if ($db->uri_to_id($uri));

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

                # add attributes to the document object 
                $doc->add_attr('@uri', $uri);

                foreach my $c (@{ $sth->{NAME} }) {
                        $doc->add_attr($c, $row->{$c}) if ($row->{$c});
                }

                #$doc->add_attr('@cdate', 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);

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

        }

        print "sync $added new files";
        $db->sync();
        print " close";
        $db->close();

        my $dur = (time() - $t) || 1;
        printf(" [%.2f/s new %.2f/s dur: %s]\n",
                ( $results / $dur ),
                ( $added / $dur ),
                fmt_time($dur)
        );
}

#---- /subs ----


## update index ##
if ($opt{i} || ($index_path && ! -e $index_path)) {
        # update all
        print "force update of HyperEstraier index ";
        print "importing existing data" unless (-e $index_path);
        print "by -i flag" if ($opt{i});
        print "\n";
        hest_update();
}

## create tables ##
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;

}

## delete data before inseting ##
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 new 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 (?,?,?,?,?,?,?)
});

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 curr_time," ", $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)
                        );

                        hest_update($hostID, $shareID, $backupNum);
                }

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