Contents 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 BackupPC::SearchLib;

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

my $debug = 0;
$|=1;

my $start_t = time();

my $pidfile = new File::Pid;

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

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

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

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

my $use_hest = $Conf{HyperEstraierIndex};
my ($index_path, $index_node_url) = BackupPC::SearchLib::getHyperEstraier_url($use_hest);

my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });

my %opt;

if ( !getopts("cdm:v:ij", \%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 Hyper Estraier full text index
        -j      update full text, don't check existing files

Option -j is variation on -i. It will allow faster initial creation
of full-text index from existing database.

EOF
        exit 1;
}

if ($opt{v}) {
        print "Debug level at $opt{v}\n";
        $debug = $opt{v};
}

#---- subs ----

sub fmt_time {
        my $t = shift || return;
        my $out = "";
        my ($ss,$mm,$hh) = gmtime($t);
        $out .= "${hh}h" if ($hh);
        $out .= sprintf("%02d:%02d", $mm,$ss);
        return $out;
}

sub curr_time {
        return strftime($t_fmt,localtime());
}

my $hest_db;
my $hest_node;

sub signal {
        my($sig) = @_;
        if ($hest_db) {
                print "\nCaught a SIG$sig--syncing database and shutting down\n";
                $hest_db->sync();
                $hest_db->close();
        }
        exit(0);
}

$SIG{'INT'}  = \&signal;
$SIG{'QUIT'} = \&signal;

sub hest_update {

        my ($host_id, $share_id, $num) = @_;

        my $skip_check = $opt{j} && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";

        unless ($use_hest) {
                print STDERR "HyperEstraier support not enabled in configuration\n";
                return;
        }

        print curr_time," updating HyperEstraier:";

        my $t = time();

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

        print " opening index $use_hest";
        if ($index_path) {
                $hest_db = HyperEstraier::Database->new();
                $hest_db->open($TopDir . $index_path, $HyperEstraier::Database::DBWRITER | $HyperEstraier::Database::DBCREAT);
                print " directly";
        } elsif ($index_node_url) {
                $hest_node ||= HyperEstraier::Node->new($index_node_url);
                $hest_node->set_auth('admin', 'admin');
                print " via node URL";
        } else {
                die "don't know how to use HyperEstraier Index $use_hest";
        }
        print " increment is " . EST_CHUNK . " files:";

        my $results = 0;

        do {

                my $where = '';
                my @data;
                if (defined($host_id) && defined($share_id) && defined($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;

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

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

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

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

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

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

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

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

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

                $offset += EST_CHUNK;

        } while ($results == EST_CHUNK);

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

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

#---- /subs ----


## update index ##
if (($opt{i} || $opt{j} || ($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 "by -j flag" if ($opt{j});
        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/\W+/_/g;
                print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
                $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)
                );            

                create table shares (
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,
                        share   VARCHAR(200)    NOT NULL
                );            

                create table dvds (
                        ID      SERIAL          PRIMARY KEY, 
                        num     INTEGER         NOT NULL,
                        name    VARCHAR(255)    NOT NULL,
                        mjesto  VARCHAR(255)
                );

                create table backups (
                        id      serial,
                        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    bigint          not null,
                        inc_size bigint         not null default -1,
                        inc_deleted boolean     default false,
                        parts   integer         not null default 1,
                        PRIMARY KEY(id)
                );            

                create table files (
                        ID              SERIAL,
                        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            bigint  NOT NULL,
                        primary key(id)
                );

                create table archive (
                        id              serial,
                        dvd_nr          int not null,
                        total_size      bigint default -1,
                        note            text,
                        username        varchar(20) not null,
                        date            timestamp default now(),
                        primary key(id)
                );      

                create table archive_backup (
                        archive_id      int not null references archive(id) on delete cascade,
                        backup_id       int not null references backups(id),
                        primary key(archive_id, backup_id)
                );

                create table archive_burned (
                        archive_id int references archive(id),
                        date date default now(),
                        iso_size bigint default -1
                );

        });

        print "creating indexes: ";

        foreach my $index (qw(
                hosts:name
                backups:hostID
                backups:num
                backups:shareID
                shares:hostID
                shares:name
                files:shareID
                files:path
                files:name
                files:date
                files:size
                archive:dvd_nr
                archive_burned:archive_id
        )) {
                do_index($index);
        }

        print " creating sequence: ";
        foreach my $seq (qw/dvd_nr/) {
                print "$seq ";
                $dbh->do( qq{ CREATE SEQUENCE $seq } );
        }


        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'},
                                substr($backup->{'type'},0,4),
                                $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) 
                VALUES (?,?,?)
        });

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

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