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


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: select files";

        my $t = time();

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

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

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

        if ($results == 0) {
                print " - no files, skipping\n";
                return;
        }

        my $dot = int($results / 15) || 1;

        print " $results ($dot/#)";

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

        my $max = int($results / $dot);

        print ", opening index $index_path...";
        use HyperEstraier;
        my $db = HyperEstraier::Database->new();

#       unless ($hest_db) {
#               print " open reader";
#               $hest_db = HyperEstraier::Database->new();
#
#       }


        $db->open($index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);

        my $added = 0;

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

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

                my $id = $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
                $db->put_doc($doc, $HyperEstraier::Database::PDCLEAN);

                $added++;
                if ($added % $dot == 0) {
                        print "$max ";
                        $max--;
                }

                if ($added % EST_SYNC_EVERY == 0) {
                        print "sync ";
                        $db->sync();
                }

        }

        print "sync $added new files";
        $db->sync();
        print ", close";
        $db->close();

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