Annotation of /trunk/bin/BackupPC_updatedb

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

use strict;
use lib "__INSTALLDIR__/lib";

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

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

my $debug = 0;
$|=1;

my $start_t = time();

my $pidfile = new File::Pid;

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

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

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

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

my $use_hest = $Conf{HyperEstraierIndex};
my ($index_path, $index_node_url) = 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($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);
                print " directly";
        } elsif ($index_node_url) {
                $hest_node ||= HyperEstraier::Node->new($index_node_url);
                $hest_node->set_auth('admin', 'admin');
                print " via node URL";
        } else {
                die "don't know how to use HyperEstraier Index $use_hest";
        }
        print " increment is " . EST_CHUNK . " files:";

        my $results = 0;

        do {

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

                my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);

                my $sth = $dbh->prepare(qq{
                        SELECT
                                files.id                        AS fid,
                                hosts.name                      AS hname,
                                shares.name                     AS sname,
                                -- shares.share                 AS sharename,
                                files.backupnum                 AS backupnum,
                                -- files.name                   AS filename,
                                files.path                      AS filepath,
                                files.date                      AS date,
                                files.type                      AS type,
                                files.size                      AS size,
                                files.shareid                   AS shareid,
                                backups.date                    AS backup_date
                        FROM files 
                                INNER JOIN shares       ON files.shareID=shares.ID
                                INNER JOIN hosts        ON hosts.ID = shares.hostID
                                INNER JOIN backups      ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
                        $where
                        $limit
                });

                $sth->execute(@data);
                $results = $sth->rows;

                if ($results == 0) {
                        print " - no new files\n";
                        last;
                }

                sub fmt_date {
                        my $t = shift || return;
                        my $iso = BackupPC::Lib::timeStamp($t);
                        $iso =~ s/\s/T/;
                        return $iso;
                }

                while (my $row = $sth->fetchrow_hashref()) {

                        my $fid = $row->{'fid'} || die "no fid?";
                        my $uri = 'file:///' . $fid;

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

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

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

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

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

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

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

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

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

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

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

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

#---- /subs ----


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

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

        print "creating tables...\n";
      
        $dbh->do(qq{
                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,
                        IP      VARCHAR(15)
                );            
        });
              
        $dbh->do(qq{
                create table shares (
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,
                        share   VARCHAR(200)    NOT NULL,
                        localpath VARCHAR(200)      
                );            
        });
        
        $dbh->do(qq{
                create table backups (
                        hostID  INTEGER         NOT NULL references hosts(id),
                        num     INTEGER         NOT NULL,
                        date    integer         NOT NULL, 
                        type    CHAR(4)         not null,
                        shareID integer         not null references shares(id),
                        size    integer         not null,
                        PRIMARY KEY(hostID, num, shareID) 
                );            
        });

        #do_index('backups_hostid,num_unique');

        $dbh->do(qq{
                create table dvds (
                        ID      SERIAL          PRIMARY KEY, 
                        num     INTEGER         NOT NULL,
                        name    VARCHAR(255)    NOT NULL,
                        mjesto  VARCHAR(255)
                );
        });

        $dbh->do(qq{     
                create table files (
                        ID      SERIAL          PRIMARY KEY,  
                        shareID INTEGER         NOT NULL references shares(id),
                        backupNum  INTEGER      NOT NULL,
                        name       VARCHAR(255) NOT NULL,
                        path       VARCHAR(255) NOT NULL,
                        date       integer      NOT NULL,
                        type       INTEGER      NOT NULL,
                        size       INTEGER      NOT NULL,
                        dvdid      INTEGER      references dvds(id)     
                );
        });

        print "creating indexes:";

        foreach my $index (qw(
                hosts_name
                backups_hostID
                backups_num
                shares_hostID
                shares_name
                files_shareID
                files_path
                files_name
                files_date
                files_size
        )) {
                print " $index";
                do_index($index);
        }
        print "...\n";

        $dbh->commit;

}

## delete data before inseting ##
if ($opt{d}) {
        print "deleting ";
        foreach my $table (qw(files dvds backups shares hosts)) {
                print "$table ";
                $dbh->do(qq{ DELETE FROM $table });
        }
        print " done...\n";

        $dbh->commit;
}

## insert new values ##

# get hosts
$hosts = $bpc->HostInfoRead();
my $hostID;
my $shareID;

my $sth;

$sth->{insert_hosts} = $dbh->prepare(qq{
INSERT INTO hosts (name, IP) VALUES (?,?)
});

$sth->{hosts_by_name} = $dbh->prepare(qq{
SELECT ID FROM hosts WHERE name=?
});

$sth->{backups_count} = $dbh->prepare(qq{
SELECT COUNT(*)
FROM backups
WHERE hostID=? AND num=? AND shareid=?
});

$sth->{insert_backups} = $dbh->prepare(qq{
INSERT INTO backups (hostID, num, date, type, shareid, size)
VALUES (?,?,?,?,?,?)
});

$sth->{insert_files} = $dbh->prepare(qq{
INSERT INTO files
        (shareID, backupNum, name, path, date, type, size)
        VALUES (?,?,?,?,?,?,?)
});

foreach my $host_key (keys %{$hosts}) {

        my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";

        $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});

        unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
                $sth->{insert_hosts}->execute(
                        $hosts->{$host_key}->{'host'},
                        $hosts->{$host_key}->{'ip'}
                );

                $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
        }

        print "host ".$hosts->{$host_key}->{'host'}.": ";
 
        # get backups for a host
        my @backups = $bpc->BackupInfoRead($hostname);
        my $incs = scalar @backups;
        print  "$incs increments\n";

        my $inc_nr = 0;
        $beenThere = {};

        foreach my $backup (@backups) {

                $inc_nr++;
                last if ($opt{m} && $inc_nr > $opt{m});

                my $backupNum = $backup->{'num'};
                my @backupShares = ();

                printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n", 
                        $hosts->{$host_key}->{'host'},
                        $inc_nr, $incs, $backupNum, 
                        $backup->{type} || '?',
                        $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
                        strftime($t_fmt,localtime($backup->{startTime})),
                        fmt_time($backup->{endTime} - $backup->{startTime})
                );

                my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
                foreach my $share ($files->shareList($backupNum)) {

                        my $t = time();

                        $shareID = getShareID($share, $hostID, $hostname);
                
                        $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
                        my ($count) = $sth->{backups_count}->fetchrow_array();
                        # skip if allready in database!
                        next if ($count > 0);

                        # dump some log
                        print curr_time," ", $share;

                        my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);

                        $sth->{insert_backups}->execute(
                                $hostID,
                                $backupNum,
                                $backup->{'endTime'},
                                $backup->{'type'},
                                $shareID,
                                $size,
                        );

                        print " commit";
                        $dbh->commit();

                        my $dur = (time() - $t) || 1;
                        printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
                                $nf, $f, $nd, $d,
                                ($size / 1024 / 1024),
                                ( ($f+$d) / $dur ),
                                fmt_time($dur)
                        );

                        hest_update($hostID, $shareID, $backupNum) if ($nf + $nd > 0);
                }

        }
}
undef $sth;
$dbh->commit();
$dbh->disconnect();

print "total duration: ",fmt_time(time() - $start_t),"\n";

$pidfile->remove;

sub getShareID() {

        my ($share, $hostID, $hostname) = @_;

        $sth->{share_id} ||= $dbh->prepare(qq{
                SELECT ID FROM shares WHERE hostID=? AND name=?
        });

        $sth->{share_id}->execute($hostID,$share);

        my ($id) = $sth->{share_id}->fetchrow_array();

        return $id if (defined($id));

        $sth->{insert_share} ||= $dbh->prepare(qq{
                INSERT INTO shares 
                        (hostID,name,share,localpath) 
                VALUES (?,?,?,?)
        });

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

        $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
        return $dbh->last_insert_id(undef,undef,'shares',undef);
}

sub found_in_db {

        my @data = @_;
        shift @data;

        my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;

        return $beenThere->{$key} if (defined($beenThere->{$key}));

        $sth->{file_in_db} ||= $dbh->prepare(qq{
                SELECT 1 FROM files
                WHERE shareID = ? and
                        path = ? and 
                        date = ? and
                        size = ?
                LIMIT 1
        });

        my @param = ($shareID,$path,$date,$size);
        $sth->{file_in_db}->execute(@param);
        my $rows = $sth->{file_in_db}->rows;
        print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);

        $beenThere->{$key}++;

        $sth->{'insert_files'}->execute(@data) unless ($rows);
        return $rows;
}

####################################################
# recursing through filesystem structure and       #
# and returning flattened files list               #
####################################################
sub recurseDir($$$$$$$$) {

        my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;

        print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);

        my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);

        { # scope
                my @stack;

                print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
                my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);

                # first, add all the entries in current directory
                foreach my $path_key (keys %{$filesInBackup}) {
                        print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
                        my @data = (
                                $shareID,
                                $backupNum,
                                $path_key,
                                $filesInBackup->{$path_key}->{'relPath'},
                                $filesInBackup->{$path_key}->{'mtime'},
                                $filesInBackup->{$path_key}->{'type'},
                                $filesInBackup->{$path_key}->{'size'}
                        );

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

                        my $found;
                        if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) {
                                print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);

                                if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                        $new_dirs++ unless ($found);
                                        print STDERR " dir\n" if ($debug >= 2);
                                } else {
                                        $new_files++ unless ($found);
                                        print STDERR " file\n" if ($debug >= 2);
                                }
                                $size += $filesInBackup->{$path_key}->{'size'} || 0;
                        }

                        if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
                                $nr_dirs++;

                                my $full_path = $dir . '/' . $path_key;
                                push @stack, $full_path;
                                print STDERR "### store to stack: $full_path\n" if ($debug >= 3);

#                               my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
#
#                               $nr_files += $f;
#                               $new_files += $nf;
#                               $nr_dirs += $d;
#                               $new_dirs += $nd;

                        } else {
                                $nr_files++;
                        }
                }

                print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);

                while ( my $dir = shift @stack ) {
                        my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
                        print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
                        $nr_files += $f;
                        $new_files += $nf;
                        $nr_dirs += $d;
                        $new_dirs += $nd;
                        $size += $s;
                }
        }

        return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
}

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