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 $use_hest = $Conf{HyperEstraierIndex};
my ($index_path, $index_node_url);
if ($use_hest) {
        use HyperEstraier;
        if ($use_hest =~ m#^http://#) {
                $index_node_url = $use_hest;
        } else {
                $index_path = $TopDir . '/' . $index_path;
                $index_path =~ s#//#/#g;
        }
}
print "-- $use_hest : $index_path OR $index_node_url --\n";


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;
my $hest_node;

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

        unless ($use_hest) {
                print STDERR "HyperEstraier support not enabled in configuration\n";
                return;
        }

        print curr_time," updating HyperEstraier:";

        my $t = time();

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

        print " opening index $use_hest";
        if ($index_path) {
                $hest_db = HyperEstraier::Database->new();
                $hest_db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);
                print " directly";
        } elsif ($index_node_url) {
                $hest_node ||= HyperEstraier::Node->new($index_node_url);
                $hest_node->set_auth('admin', 'admin');
                print " via node URL";
        } else {
                die "don't know how to use HyperEstraier Index $use_hest";
        }
        print " increment is " . EST_CHUNK . " files:";

        my $results = 0;

        do {

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

                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(@data);
                $results = $sth->rows;

                if ($results == 0) {
                        print " - no new 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 || $hest_node)->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
                        if ($hest_db) {
                                $hest_db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
                        } elsif ($hest_node) {
                                $hest_node->put_doc($doc);
                        } else {
                                die "not supported";
                        }
                        $added++;
                }

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

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

        if ($index_path) {
                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) if ($nf + $nd > 0);
                }

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