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

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