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;
use constant EST_CHUNK => 100000;

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;
if ($index_path) {
        use HyperEstraier;
}


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 signal {
        my($sig) = @_;
        if ($hest_db) {
                print "\nCaught a SIG$sig--syncing database and shutting down\n";
                $hest_db->sync();
                $hest_db->close();
        }
        exit(0);
}

$SIG{'INT'}  = \&signal;
$SIG{'QUIT'} = \&signal;

sub hest_update {

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

        print curr_time," updating HyperEstraier:";

        my $t = time();

        my $offset = 0;
        my $added = 0;

        print " opening index $index_path";
        $hest_db = HyperEstraier::Database->new();
        $hest_db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);

        print " increment is " . EST_CHUNK . " files";

        my $results = 0;

        do {

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

                my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);

                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 type,
                                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
                        $limit
                });

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

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

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

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

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

                        my $id = $hest_db->uri_to_id($uri);
                        next unless ($id == -1);

                        # 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
                        $hest_db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
                        $added++;
                }

                print " $added";
                $hest_db->sync();

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

        print ", close";
        $hest_db->close();

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

#---- /subs ----


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