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 => 10000;

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 $index_path = $Conf{HyperEstraierIndex};
$index_path = $TopDir . '/' . $index_path;
$index_path =~ s#//#/#g;
if ($index_path) {
        use HyperEstraier;
}


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;

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

        print curr_time," updating HyperEstraier:";

        my $t = time();

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

        print " opening index $index_path";
        $hest_db = HyperEstraier::Database->new();
        $hest_db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);

        my $results = 0;

        do {

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

                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(@_);
                $results = $sth->rows;

                if ($results == 0) {
                        print " - no more 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->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
                        $hest_db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);
                        $added++;
                }

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

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

        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);
                }

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