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;

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

        }

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