Annotation of /trunk/bin/BackupPC_updatedb

#!/usr/local/bin/perl -w

use strict;
use lib "__INSTALLDIR__/lib";

use DBI;
use BackupPC::Lib;
use BackupPC::View;
use Data::Dumper;
use Getopt::Std;
use Time::HiRes qw/time/;
use File::Pid;
use POSIX qw/strftime/;
use BackupPC::SearchLib;

use constant BPC_FTYPE_DIR => 5;
use constant EST_CHUNK => 100000;

my $debug = 0;
$|=1;

my $start_t = time();

my $pidfile = new File::Pid;

if (my $pid = $pidfile->running ) {
        die "$0 already running: $pid\n";
} elsif ($pidfile->pid ne $$) {
        $pidfile->remove;
        $pidfile = new File::Pid;
}
$pidfile->write;
print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";

my $t_fmt = '%Y-%m-%d %H:%M:%S';

my $hosts;
my $bpc = BackupPC::Lib->new || die;
my %Conf = $bpc->Conf();
my $TopDir = $bpc->TopDir();
my $beenThere = {};

my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
my $user = $Conf{SearchUser} || '';

my $use_hest = $Conf{HyperEstraierIndex};
my ($index_path, $index_node_url) = BackupPC::SearchLib::getHyperEstraier_url($use_hest);

my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });

my %opt;

if ( !getopts("cdm:v:i", \%opt ) ) {
        print STDERR <<EOF;
usage: $0 [-c|-d] [-m num] [-v|-v level] [-i]

Options:
        -c      create database on first use
        -d      delete database before import
        -m num  import just num increments for one host
        -v num  set verbosity (debug) level (default $debug)
        -i      update HyperEstraier full text index
EOF
        exit 1;
}

if ($opt{v}) {
        print "Debug level at $opt{v}\n";
        $debug = $opt{v};
}

#---- subs ----

sub fmt_time {
        my $t = shift || return;
        my $out = "";
        my ($ss,$mm,$hh) = gmtime($t);
        $out .= "${hh}h" if ($hh);
        $out .= sprintf("%02d:%02d", $mm,$ss);
        return $out;
}

sub curr_time {
        return strftime($t_fmt,localtime());
}

my $hest_db;
my $hest_node;

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

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

sub hest_update {

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

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

        print curr_time," updating HyperEstraier:";

        my $t = time();

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

        print " opening index $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;

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

                        # create a document object 
                        my $doc = HyperEstraier::Document->new;

                        # add attributes to the document object 
                        $doc->add_attr('@uri', $uri);

                        foreach my $c (@{ $sth->{NAME} }) {
                                $doc->add_attr($c, $row->{$c}) if ($row->{$c});
                        }

                        #$doc->add_attr('@cdate', fmt_date($row->{'date'}));

                        # add the body text to the document object 
                        my $path = $row->{'filepath'};
                        $doc->add_text($path);
                        $path =~ s/(.)/$1 /g;
                        $doc->add_hidden_text($path);

                        print STDERR $doc->dump_draft,"\n" if ($debug > 1);

                        # register the document object to the database
                        if ($hest_db) {
                                $hest_db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
                        } elsif ($hest_node) {
                                $hest_node->put_doc($doc);
                        } else {
                                die "not supported";
                        }
                        $added++;
                }

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

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

        if ($index_path) {
                print ", close";
                $hest_db->close();
        }

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

#---- /subs ----


## update index ##
if (($opt{i} || ($index_path && ! -e $index_path)) && !$opt{c}) {
        # update all
        print "force update of HyperEstraier index ";
        print "importing existing data" unless (-e $index_path);
        print "by -i flag" if ($opt{i});
        print "\n";
        hest_update();
}

## create tables ##
if ($opt{c}) {
        sub do_index {
                my $index = shift || return;
                my ($table,$col,$unique) = split(/_/, $index);
                $unique ||= '';
                $index =~ s/,/_/g;
                $dbh->do(qq{ create $unique index $index on $table($col) });
        }

        print "creating tables...\n";
      
        $dbh->do(qq{
                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,
                        IP      VARCHAR(15)
                );            
        });
              
        $dbh->do(qq{
                create table shares (
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,
                        share   VARCHAR(200)    NOT NULL,
                        localpath VARCHAR(200)      
                );            
        });

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