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 BackupPC::SearchLib;

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) = BackupPC::SearchLib::getHyperEstraier_url($use_hest);

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