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 ($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;

                        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 $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 "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/,/_/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 dvds (
                        ID      SERIAL          PRIMARY KEY, 
                        num     INTEGER         NOT NULL,
                        name    VARCHAR(255)    NOT NULL,
                        mjesto  VARCHAR(255)
                );
        });
        
        $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 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
                );
        });


        $dbh->do( qq{
                create table archive
                (
                        id                      int not null,
                        dvd_nr          int not null,
                        note            text,
                        username        varchar(20) not null,
                        date            timestamp,
                        primary key(id)
                );      
        }
        );

        $dbh->do( qq{
                create table archive_backup
                (
                        archive_id      int not null,
                        backup_id       int not null,
                        status          text,
                        primary key(archive_id, backup_id)
                );
        });

        $dbh->do( qq{
                create table workflows(
                        id                      int not null,
                        step_id         int not null,
                        start           timestamp,
                        stop            timestamp,
                        username        varchar(20),
                        archive_id      int not null,
                        running         boolean default true,
                        primary key(id)
                );
        });

        $dbh->do( qq{
                create table workflow_step
                (
                        step_id         int not null,
                        code            text,
                        next_step       int,
                        stop            boolean default false,
                        primary key(step_id)
                );
        });

        $dbh->do( qq{
                        alter table workflow_step
                                add constraint fk_workflow_next_step
                                foreign key(next_step)
                                references workflow_step(step_id);
        });

        $dbh->do( qq{
                alter table workflows
                        add constraint fk_workflows_step_id
                        foreign key(step_id)
                        references workflow_step(step_id);
        });
        
        $dbh->do( qq{
                alter table workflows
                        add constraint fk_workflows_archive_id
                        foreign key(archive_id)
                        references archive(id); 
        });

        $dbh->do( qq{
                create table workflow_log
                (
                        workflow_id             int not null,
                        step_id                 int not null,
                        date                    timestamp not null,
                        status                  text,
                        primary key(workflow_id, step_id)
                );
        });

        $dbh->do( qq{
                alter table workflow_log
                        add constraint fk_workflow_log_workflow_id
                        foreign key (workflow_id)
                        references workflows(id);
                });
        
        $dbh->do( qq{
                alter table workflow_log
                        add constraint fk_workflow_log_step_id
                        foreign key (step_id)
                        references      workflow_step(step_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'},
                                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,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	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	98	if ($host_id && $share_id && $num) {
147			$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	131	if (($opt{i} \|\| $opt{j} \|\| ($index_path && ! -e $index_path)) && !$opt{c}) {
260	dpavlin	89	# update all
261			print "force update of HyperEstraier index ";
262			print "importing existing data" unless (-e $index_path);
263			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			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	130
299			$dbh->do(qq{
300			create table dvds (
301			ID SERIAL PRIMARY KEY,
302			num INTEGER NOT NULL,
303			name VARCHAR(255) NOT NULL,
304			mjesto VARCHAR(255)
305			);
306			});
307	dpavlin	6
308	dpavlin	14	$dbh->do(qq{
309			create table backups (
310			hostID INTEGER NOT NULL references hosts(id),
311			num INTEGER NOT NULL,
312	dpavlin	49	date integer NOT NULL,
313			type CHAR(4) not null,
314	dpavlin	65	shareID integer not null references shares(id),
315	dpavlin	66	size integer not null,
316	dpavlin	65	PRIMARY KEY(hostID, num, shareID)
317	dpavlin	6	);
318	dpavlin	14	});
319	dpavlin	6
320	dpavlin	65	#do_index('backups_hostid,num_unique');
321	dpavlin	49
322	dpavlin	6
323	dpavlin	14	$dbh->do(qq{
324			create table files (
325	dpavlin	49	ID SERIAL PRIMARY KEY,
326	dpavlin	14	shareID INTEGER NOT NULL references shares(id),
327	dpavlin	52	backupNum INTEGER NOT NULL,
328	dpavlin	14	name VARCHAR(255) NOT NULL,
329			path VARCHAR(255) NOT NULL,
330	dpavlin	49	date integer NOT NULL,
331	dpavlin	14	type INTEGER NOT NULL,
332	dpavlin	130	size INTEGER NOT NULL
333	dpavlin	6	);
334	dpavlin	14	});
335	dpavlin	6
336	dpavlin	130
337			$dbh->do( qq{
338			create table archive
339			(
340			id int not null,
341			dvd_nr int not null,
342			note text,
343			username varchar(20) not null,
344			date timestamp,
345			primary key(id)
346			);
347			}
348			);
349
350			$dbh->do( qq{
351			create table archive_backup
352			(
353			archive_id int not null,
354			backup_id int not null,
355			status text,
356			primary key(archive_id, backup_id)
357			);
358			});
359
360			$dbh->do( qq{
361			create table workflows(
362			id int not null,
363			step_id int not null,
364			start timestamp,
365			stop timestamp,
366			username varchar(20),
367			archive_id int not null,
368			running boolean default true,
369			primary key(id)
370			);
371			});
372
373			$dbh->do( qq{
374			create table workflow_step
375			(
376			step_id int not null,
377			code text,
378			next_step int,
379			stop boolean default false,
380			primary key(step_id)
381			);
382			});
383
384			$dbh->do( qq{
385			alter table workflow_step
386			add constraint fk_workflow_next_step
387			foreign key(next_step)
388			references workflow_step(step_id);
389			});
390
391			$dbh->do( qq{
392			alter table workflows
393			add constraint fk_workflows_step_id
394			foreign key(step_id)
395			references workflow_step(step_id);
396			});
397
398			$dbh->do( qq{
399			alter table workflows
400			add constraint fk_workflows_archive_id
401			foreign key(archive_id)
402			references archive(id);
403			});
404
405			$dbh->do( qq{
406			create table workflow_log
407			(
408			workflow_id int not null,
409			step_id int not null,
410			date timestamp not null,
411			status text,
412			primary key(workflow_id, step_id)
413			);
414			});
415
416			$dbh->do( qq{
417			alter table workflow_log
418			add constraint fk_workflow_log_workflow_id
419			foreign key (workflow_id)
420			references workflows(id);
421			});
422
423			$dbh->do( qq{
424			alter table workflow_log
425			add constraint fk_workflow_log_step_id
426			foreign key (step_id)
427			references workflow_step(step_id);
428			});
429
430	dpavlin	49	print "creating indexes:";
431	dpavlin	6
432	dpavlin	14	foreach my $index (qw(
433			hosts_name
434			backups_hostID
435			backups_num
436			shares_hostID
437			shares_name
438			files_shareID
439			files_path
440			files_name
441			files_date
442			files_size
443			)) {
444	dpavlin	49	print " $index";
445			do_index($index);
446	dpavlin	14	}
447	dpavlin	49	print "...\n";
448	dpavlin	14
449	dpavlin	49	$dbh->commit;
450	dpavlin	14
451			}
452
453	dpavlin	89	## delete data before inseting ##
454	dpavlin	14	if ($opt{d}) {
455			print "deleting ";
456	dpavlin	49	foreach my $table (qw(files dvds backups shares hosts)) {
457	dpavlin	14	print "$table ";
458			$dbh->do(qq{ DELETE FROM $table });
459			}
460			print " done...\n";
461	dpavlin	49
462	dpavlin	51	$dbh->commit;
463	dpavlin	14	}
464
465	dpavlin	89	## insert new values ##
466	dpavlin	6
467			# get hosts
468	dpavlin	8	$hosts = $bpc->HostInfoRead();
469	dpavlin	6	my $hostID;
470			my $shareID;
471
472	dpavlin	14	my $sth;
473	dpavlin	6
474	dpavlin	14	$sth->{insert_hosts} = $dbh->prepare(qq{
475			INSERT INTO hosts (name, IP) VALUES (?,?)
476			});
477	dpavlin	6
478	dpavlin	14	$sth->{hosts_by_name} = $dbh->prepare(qq{
479			SELECT ID FROM hosts WHERE name=?
480			});
481
482	dpavlin	65	$sth->{backups_count} = $dbh->prepare(qq{
483	dpavlin	14	SELECT COUNT(*)
484			FROM backups
485	dpavlin	65	WHERE hostID=? AND num=? AND shareid=?
486	dpavlin	14	});
487
488			$sth->{insert_backups} = $dbh->prepare(qq{
489	dpavlin	66	INSERT INTO backups (hostID, num, date, type, shareid, size)
490			VALUES (?,?,?,?,?,?)
491	dpavlin	14	});
492
493			$sth->{insert_files} = $dbh->prepare(qq{
494			INSERT INTO files
495	dpavlin	62	(shareID, backupNum, name, path, date, type, size)
496			VALUES (?,?,?,?,?,?,?)
497	dpavlin	14	});
498
499			foreach my $host_key (keys %{$hosts}) {
500
501			my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
502
503			$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
504
505			unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
506			$sth->{insert_hosts}->execute(
507			$hosts->{$host_key}->{'host'},
508			$hosts->{$host_key}->{'ip'}
509			);
510
511	dpavlin	49	$hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
512	dpavlin	14	}
513
514	dpavlin	67	print "host ".$hosts->{$host_key}->{'host'}.": ";
515	dpavlin	6
516	dpavlin	14	# get backups for a host
517			my @backups = $bpc->BackupInfoRead($hostname);
518	dpavlin	67	my $incs = scalar @backups;
519			print "$incs increments\n";
520	dpavlin	6
521	dpavlin	14	my $inc_nr = 0;
522	dpavlin	67	$beenThere = {};
523	dpavlin	14
524			foreach my $backup (@backups) {
525	dpavlin	40
526	dpavlin	14	$inc_nr++;
527			last if ($opt{m} && $inc_nr > $opt{m});
528
529			my $backupNum = $backup->{'num'};
530			my @backupShares = ();
531
532	dpavlin	67	printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
533			$hosts->{$host_key}->{'host'},
534			$inc_nr, $incs, $backupNum,
535			$backup->{type} \|\| '?',
536			$backup->{nFilesNew} \|\| '?', $backup->{nFiles} \|\| '?',
537	dpavlin	57	strftime($t_fmt,localtime($backup->{startTime})),
538	dpavlin	67	fmt_time($backup->{endTime} - $backup->{startTime})
539			);
540	dpavlin	14
541	dpavlin	34	my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
542	dpavlin	14	foreach my $share ($files->shareList($backupNum)) {
543
544	dpavlin	37	my $t = time();
545
546	dpavlin	14	$shareID = getShareID($share, $hostID, $hostname);
547
548	dpavlin	65	$sth->{backups_count}->execute($hostID, $backupNum, $shareID);
549			my ($count) = $sth->{backups_count}->fetchrow_array();
550			# skip if allready in database!
551			next if ($count > 0);
552
553			# dump some log
554	dpavlin	86	print curr_time," ", $share;
555	dpavlin	65
556	dpavlin	66	my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
557	dpavlin	65
558			$sth->{insert_backups}->execute(
559			$hostID,
560			$backupNum,
561			$backup->{'endTime'},
562	dpavlin	130	substr($backup->{'type'},0,4),
563	dpavlin	66	$shareID,
564			$size,
565	dpavlin	65	);
566
567			print " commit";
568			$dbh->commit();
569
570	dpavlin	50	my $dur = (time() - $t) \|\| 1;
571	dpavlin	66	printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
572	dpavlin	37	$nf, $f, $nd, $d,
573	dpavlin	66	($size / 1024 / 1024),
574	dpavlin	50	( ($f+$d) / $dur ),
575			fmt_time($dur)
576	dpavlin	37	);
577	dpavlin	89
578	dpavlin	107	hest_update($hostID, $shareID, $backupNum) if ($nf + $nd > 0);
579	dpavlin	14	}
580	dpavlin	29
581	dpavlin	6	}
582			}
583	dpavlin	14	undef $sth;
584	dpavlin	6	$dbh->commit();
585			$dbh->disconnect();
586
587	dpavlin	51	print "total duration: ",fmt_time(time() - $start_t),"\n";
588
589	dpavlin	38	$pidfile->remove;
590
591	dpavlin	14	sub getShareID() {
592	dpavlin	6
593	dpavlin	14	my ($share, $hostID, $hostname) = @_;
594
595			$sth->{share_id} \|\|= $dbh->prepare(qq{
596			SELECT ID FROM shares WHERE hostID=? AND name=?
597			});
598
599			$sth->{share_id}->execute($hostID,$share);
600
601			my ($id) = $sth->{share_id}->fetchrow_array();
602
603			return $id if (defined($id));
604
605			$sth->{insert_share} \|\|= $dbh->prepare(qq{
606			INSERT INTO shares
607			(hostID,name,share,localpath)
608			VALUES (?,?,?,?)
609			});
610
611	dpavlin	25	my $drop_down = $hostname . '/' . $share;
612			$drop_down =~ s#//+#/#g;
613
614			$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
615	dpavlin	49	return $dbh->last_insert_id(undef,undef,'shares',undef);
616	dpavlin	6	}
617
618	dpavlin	14	sub found_in_db {
619
620	dpavlin	48	my @data = @_;
621			shift @data;
622	dpavlin	14
623	dpavlin	74	my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
624	dpavlin	48
625			return $beenThere->{$key} if (defined($beenThere->{$key}));
626
627	dpavlin	14	$sth->{file_in_db} \|\|= $dbh->prepare(qq{
628	dpavlin	48	SELECT 1 FROM files
629	dpavlin	14	WHERE shareID = ? and
630			path = ? and
631			date = ? and
632			size = ?
633	dpavlin	74	LIMIT 1
634	dpavlin	14	});
635
636	dpavlin	67	my @param = ($shareID,$path,$date,$size);
637	dpavlin	14	$sth->{file_in_db}->execute(@param);
638	dpavlin	48	my $rows = $sth->{file_in_db}->rows;
639	dpavlin	74	print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
640	dpavlin	48
641			$beenThere->{$key}++;
642
643			$sth->{'insert_files'}->execute(@data) unless ($rows);
644	dpavlin	14	return $rows;
645	dpavlin	6	}
646
647			####################################################
648			# recursing through filesystem structure and #
649			# and returning flattened files list #
650			####################################################
651	dpavlin	14	sub recurseDir($$$$$$$$) {
652	dpavlin	6
653	dpavlin	35	my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
654	dpavlin	14
655	dpavlin	44	print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
656	dpavlin	29
657	dpavlin	66	my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
658	dpavlin	14
659	dpavlin	27	{ # scope
660	dpavlin	29	my @stack;
661	dpavlin	14
662	dpavlin	45	print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
663	dpavlin	27	my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
664	dpavlin	14
665	dpavlin	27	# first, add all the entries in current directory
666			foreach my $path_key (keys %{$filesInBackup}) {
667	dpavlin	66	print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
668	dpavlin	27	my @data = (
669			$shareID,
670			$backupNum,
671			$path_key,
672			$filesInBackup->{$path_key}->{'relPath'},
673			$filesInBackup->{$path_key}->{'mtime'},
674			$filesInBackup->{$path_key}->{'type'},
675			$filesInBackup->{$path_key}->{'size'}
676			);
677
678			my $key = join(" ", (
679			$shareID,
680			$dir,
681			$path_key,
682			$filesInBackup->{$path_key}->{'mtime'},
683			$filesInBackup->{$path_key}->{'size'}
684			));
685
686	dpavlin	70	my $found;
687			if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
688	dpavlin	30	print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
689	dpavlin	48
690	dpavlin	27	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
691	dpavlin	70	$new_dirs++ unless ($found);
692	dpavlin	30	print STDERR " dir\n" if ($debug >= 2);
693	dpavlin	27	} else {
694	dpavlin	70	$new_files++ unless ($found);
695	dpavlin	30	print STDERR " file\n" if ($debug >= 2);
696	dpavlin	27	}
697	dpavlin	66	$size += $filesInBackup->{$path_key}->{'size'} \|\| 0;
698	dpavlin	27	}
699
700	dpavlin	14	if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
701	dpavlin	27	$nr_dirs++;
702
703	dpavlin	29	my $full_path = $dir . '/' . $path_key;
704			push @stack, $full_path;
705	dpavlin	30	print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
706	dpavlin	29
707	dpavlin	27	# my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
708			#
709			# $nr_files += $f;
710			# $new_files += $nf;
711			# $nr_dirs += $d;
712			# $new_dirs += $nd;
713
714	dpavlin	14	} else {
715	dpavlin	27	$nr_files++;
716	dpavlin	14	}
717			}
718
719	dpavlin	30	print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
720	dpavlin	14
721	dpavlin	29	while ( my $dir = shift @stack ) {
722	dpavlin	66	my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
723	dpavlin	30	print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
724	dpavlin	29	$nr_files += $f;
725			$new_files += $nf;
726			$nr_dirs += $d;
727			$new_dirs += $nd;
728	dpavlin	66	$size += $s;
729	dpavlin	29	}
730	dpavlin	14	}
731
732	dpavlin	66	return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
733	dpavlin	6	}
734