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:ij", \%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 Hyper Estraier full text index
        -j      update full text, don't check existing files

Option -j is variation on -i. It will allow faster initial creation
of full-text index from existing database.

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

        my $skip_check = $opt{j} && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";

        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($TopDir . $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 (defined($host_id) && defined($share_id) && defined($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;

                        unless ($skip_check) {
                                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} || $opt{j} || ($index_path && ! -e $TopDir . $index_path)) && !$opt{c}) {
        # update all
        print "force update of HyperEstraier index ";
        print "importing existing data" unless (-e $TopDir . $index_path);
        print "by -i flag" if ($opt{i});
        print "by -j flag" if ($opt{j});
        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/\W+/_/g;
                print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
                $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)
                );            

                create table shares (
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,
                        share   VARCHAR(200)    NOT NULL
                );            

                create table dvds (
                        ID      SERIAL          PRIMARY KEY, 
                        num     INTEGER         NOT NULL,
                        name    VARCHAR(255)    NOT NULL,
                        mjesto  VARCHAR(255)
                );

                create table backups (
                        id      serial,
                        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    bigint          not null,
                        inc_size bigint         not null default -1,
                        inc_deleted boolean     default false,
                        parts   integer         not null default 1,
                        PRIMARY KEY(id)
                );            

                create table files (
                        ID              SERIAL,
                        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            bigint  NOT NULL,
                        primary key(id)
                );

                create table archive (
                        id              serial,
                        dvd_nr          int not null,
                        total_size      bigint default -1,
                        note            text,
                        username        varchar(20) not null,
                        date            timestamp default now(),
                        primary key(id)
                );      

                create table archive_backup (
                        archive_id      int not null references archive(id) on delete cascade,
                        backup_id       int not null references backups(id),
                        primary key(archive_id, backup_id)
                );

                create table archive_burned (
                        archive_id      int references archive(id),
                        date            timestamp default now(),
                        part            int not null default 1,
                        copy            int not null default 1,
                        iso_size bigint default -1
                );

                create table backup_parts (
                        id serial,
                        backup_id int references backups(id),
                        part_nr int not null check (part_nr > 0),
                        tar_size bigint not null check (tar_size > 0),
                        size bigint not null check (size > 0),
                        md5 text not null,
                        items int not null check (items > 0),
                        date timestamp default now(),
                        primary key(id)
                );
        });

        print "creating indexes: ";

        foreach my $index (qw(
                hosts:name
                backups:hostID
                backups:num
                backups:shareID
                shares:hostID
                shares:name
                files:shareID
                files:path
                files:name
                files:date
                files:size
                archive:dvd_nr
                archive_burned:archive_id
                backup_parts:backup_id,part_nr
        )) {
                do_index($index);
        }

        print " creating sequence: ";
        foreach my $seq (qw/dvd_nr/) {
                print "$seq ";
                $dbh->do( qq{ CREATE SEQUENCE $seq } );
        }


        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'},
                                substr($backup->{'type'},0,4),
                                $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) 
                VALUES (?,?,?)
        });

        my $drop_down = $hostname . '/' . $share;
        $drop_down =~ s#//+#/#g;

        $sth->{insert_share}->execute($hostID,$share, $drop_down);
        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'}
                        ));

                        # daylight saving time change offset for 1h
                        my $dst_offset = 60 * 60;

                        my $key_dst1 = join(" ", (
                                $shareID,
                                $dir,
                                $path_key,
                                $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
                                $filesInBackup->{$path_key}->{'size'}
                        ));

                        my $key_dst2 = join(" ", (
                                $shareID,
                                $dir,
                                $path_key,
                                $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
                                $filesInBackup->{$path_key}->{'size'}
                        ));

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