4 use lib "/usr/local/BackupPC/lib";
10 use Getopt::Long::Descriptive;
11 use Time::HiRes qw/time/;
13 use POSIX qw/strftime/;
16 use Data::Dump qw(dump);
18 use constant BPC_FTYPE_DIR => 5;
19 use constant EST_CHUNK => 4096;
21 # daylight saving time change offset for 1h
22 my $dst_offset = 60 * 60;
29 my $pid_path = abs_path($0);
30 $pid_path =~ s/\W+/_/g;
32 my $pidfile = new File::Pid({
33 file => "/tmp/search_update.pid",
36 if (my $pid = $pidfile->running ) {
37 die "$0 already running: $pid\n";
38 } elsif ($pidfile->pid ne $$) {
40 $pidfile = new File::Pid;
42 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
45 my $t_fmt = '%Y-%m-%d %H:%M:%S';
48 my $bpc = BackupPC::Lib->new || die;
49 my %Conf = $bpc->Conf();
50 my $TopDir = $bpc->TopDir();
53 my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
54 my $user = $Conf{SearchUser} || '';
56 my $index_node_url = $Conf{HyperEstraierIndex};
58 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
60 my ($opt,$usage) = describe_options(
62 [ 'create|c', "create database on first use" ],
63 [ 'delete|d', "delete database before import" ],
64 [ 'max|m=i', "import just max increments for one host" ],
65 [ 'host|h=s@', "import just host(s)" ],
67 [ 'verbose|v:i', 'set verbosity (debug) level' ],
68 [ 'index|i', 'update full text index' ],
69 [ 'junk|j', "update full text, don't check existing files" ],
70 [ 'fast|f', "don't do anything with full text index" ],
71 [ 'quiet|q', "be quiet for hosts without changes" ],
72 [ 'help', "show help" ],
75 print($usage->text), exit if $opt->help;
77 warn "hosts: ",dump( $opt->host );
82 my $t = shift || return;
84 my ($ss,$mm,$hh) = gmtime($t);
85 $out .= "${hh}h" if ($hh);
86 $out .= sprintf("%02d:%02d", $mm,$ss);
91 return strftime($t_fmt,localtime());
96 my ($host_id, $share_id, $num) = @_;
98 my $skip_check = $opt->junk && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
100 print curr_time," updating fulltext:";
107 my $search = BackupPC::Search->search_module;
115 if (defined($host_id) && defined($share_id) && defined($num)) {
122 @data = ( $host_id, $share_id, $num );
125 my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);
127 my $sth = $dbh->prepare(qq{
131 shares.name AS sname,
132 -- shares.share AS sharename,
133 files.backupnum AS backupnum,
134 -- files.name AS filename,
135 files.path AS filepath,
139 files.shareid AS shareid,
140 backups.date AS backup_date
142 INNER JOIN shares ON files.shareID=shares.ID
143 INNER JOIN hosts ON hosts.ID = shares.hostID
144 INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
149 $sth->execute(@data);
150 $results = $sth->rows;
153 print " - no new files\n";
160 my $t = shift || return;
161 my $iso = BackupPC::Lib::timeStamp($t);
166 while (my $row = $sth->fetchrow_hashref()) {
167 next if $search->exists( $row );
168 $search->add_doc( $row );
174 $offset += EST_CHUNK;
176 } while ($results == EST_CHUNK);
180 my $dur = (time() - $t) || 1;
181 printf(" [%.2f/s dur: %s]\n",
191 if ( ( $opt->index || $opt->junk ) && !$opt->create ) {
193 print "force update of Hyper Estraier index ";
194 print "by -i flag" if ($opt->index);
195 print "by -j flag" if ($opt->junk);
203 my $index = shift || return;
204 my ($table,$col,$unique) = split(/:/, $index);
207 print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
208 $dbh->do(qq{ create $unique index $index on $table($col) });
211 print "creating tables...\n";
215 ID SERIAL PRIMARY KEY,
216 name VARCHAR(30) NOT NULL,
220 create table shares (
221 ID SERIAL PRIMARY KEY,
222 hostID INTEGER NOT NULL references hosts(id),
223 name VARCHAR(30) NOT NULL,
224 share VARCHAR(200) NOT NULL
228 ID SERIAL PRIMARY KEY,
229 num INTEGER NOT NULL,
230 name VARCHAR(255) NOT NULL,
234 create table backups (
236 hostID INTEGER NOT NULL references hosts(id),
237 num INTEGER NOT NULL,
238 date integer NOT NULL,
239 type CHAR(4) not null,
240 shareID integer not null references shares(id),
241 size bigint not null,
242 inc_size bigint not null default -1,
243 inc_deleted boolean default false,
244 parts integer not null default 0,
250 shareID INTEGER NOT NULL references shares(id),
251 backupNum INTEGER NOT NULL,
252 name VARCHAR(255) NOT NULL,
253 path VARCHAR(255) NOT NULL,
254 date integer NOT NULL,
255 type INTEGER NOT NULL,
256 size bigint NOT NULL,
260 create table archive (
263 total_size bigint default -1,
265 username varchar(20) not null,
266 date timestamp default now(),
270 create table archive_backup (
271 archive_id int not null references archive(id) on delete cascade,
272 backup_id int not null references backups(id),
273 primary key(archive_id, backup_id)
276 create table archive_burned (
277 archive_id int references archive(id),
278 date timestamp default now(),
279 part int not null default 1,
280 copy int not null default 1,
281 iso_size bigint default -1
284 create table backup_parts (
286 backup_id int references backups(id),
287 part_nr int not null check (part_nr > 0),
288 tar_size bigint not null check (tar_size > 0),
289 size bigint not null check (size > 0),
291 items int not null check (items > 0),
292 date timestamp default now(),
296 -- report backups and corresponding dvd
298 create view backups_on_dvds as
301 hosts.name || ':' || shares.name as share,
303 backups.type as type,
304 abstime(backups.date) as backup_date,
305 backups.size as size,
306 backups.inc_size as gzip_size,
307 archive.id as archive_id,
310 join shares on backups.shareid=shares.id
311 join hosts on shares.hostid = hosts.id
312 left outer join archive_backup on backups.id = archive_backup.backup_id
313 left outer join archive on archive_backup.archive_id = archive.id
314 where backups.parts > 0 and size > 0
315 order by backups.date
319 print "creating indexes: ";
321 foreach my $index (qw(
334 archive_burned:archive_id
335 backup_parts:backup_id,part_nr:unique
340 print " creating sequence: ";
341 foreach my $seq (qw/dvd_nr/) {
343 $dbh->do( qq{ CREATE SEQUENCE $seq } );
348 print " creating triggers ";
349 $dbh->do( <<__END_OF_TRIGGER__ );
351 create or replace function backup_parts_check() returns trigger as '
357 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
358 if (TG_OP=''UPDATE'') then
360 b_parts := new.parts;
361 elsif (TG_OP = ''INSERT'') then
363 b_parts := new.parts;
365 b_counted := (select count(*) from backup_parts where backup_id = b_id);
366 -- raise notice ''backup % parts %'', b_id, b_parts;
367 if ( b_parts != b_counted ) then
368 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
374 create trigger do_backup_parts_check
375 after insert or update or delete on backups
376 for each row execute procedure backup_parts_check();
378 create or replace function backup_backup_parts_check() returns trigger as '
384 if (TG_OP = ''INSERT'') then
385 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
386 b_id = new.backup_id;
387 my_part_nr = new.part_nr;
388 execute ''update backups set parts = parts + 1 where id = '' || b_id;
389 elsif (TG_OP = ''DELETE'') then
390 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
391 b_id = old.backup_id;
392 my_part_nr = old.part_nr;
393 execute ''update backups set parts = parts - 1 where id = '' || b_id;
395 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
396 if ( my_part_nr != calc_part ) then
397 raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part;
403 create trigger do_backup_backup_parts_check
404 after insert or update or delete on backup_parts
405 for each row execute procedure backup_backup_parts_check();
417 ## delete data before inseting ##
420 foreach my $table (qw(files dvds backups shares hosts)) {
422 $dbh->do(qq{ DELETE FROM $table });
429 ## insert new values ##
432 $hosts = $bpc->HostInfoRead();
438 $sth->{insert_hosts} = $dbh->prepare(qq{
439 INSERT INTO hosts (name, IP) VALUES (?,?)
442 $sth->{hosts_by_name} = $dbh->prepare(qq{
443 SELECT ID FROM hosts WHERE name=?
446 $sth->{backups_count} = $dbh->prepare(qq{
449 WHERE hostID=? AND num=? AND shareid=?
452 $sth->{insert_backups} = $dbh->prepare(qq{
453 INSERT INTO backups (hostID, num, date, type, shareid, size)
454 VALUES (?,?,?,?,?,-1)
457 $sth->{update_backups_size} = $dbh->prepare(qq{
458 UPDATE backups SET size = ?
459 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
462 $sth->{insert_files} = $dbh->prepare(qq{
464 (shareID, backupNum, name, path, date, type, size)
465 VALUES (?,?,?,?,?,?,?)
468 my @hosts = keys %{$hosts};
471 foreach my $host_key (@hosts) {
473 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
475 next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
477 $sth->{hosts_by_name}->execute($hostname);
479 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
480 $sth->{insert_hosts}->execute(
481 $hosts->{$host_key}->{'host'},
482 $hosts->{$host_key}->{'ip'}
485 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
489 # get backups for a host
490 my @backups = $bpc->BackupInfoRead($hostname);
491 my $incs = scalar @backups;
493 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
494 $hosts->{$host_key}->{'host'},
499 print $host_header unless $opt->quiet;
504 foreach my $backup (@backups) {
507 last if defined $opt->max && $inc_nr > $opt->max;
509 my $backupNum = $backup->{'num'};
510 my @backupShares = ();
512 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
513 $hosts->{$host_key}->{'host'},
514 $inc_nr, $incs, $backupNum,
515 $backup->{type} || '?',
516 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
517 strftime($t_fmt,localtime($backup->{startTime})),
518 fmt_time($backup->{endTime} - $backup->{startTime})
520 print $share_header unless $opt->quiet;
522 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
524 foreach my $share ($files->shareList($backupNum)) {
528 $shareID = getShareID($share, $hostID, $hostname);
530 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
531 my ($count) = $sth->{backups_count}->fetchrow_array();
532 # skip if allready in database!
533 next if ($count > 0);
535 # dump host and share header for -q
539 $host_header = undef;
545 print curr_time," ", $share;
547 $sth->{insert_backups}->execute(
550 $backup->{'endTime'},
551 substr($backup->{'type'},0,4),
555 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
558 $sth->{update_backups_size}->execute(
562 $backup->{'endTime'},
563 substr($backup->{'type'},0,4),
574 my $dur = (time() - $t) || 1;
575 printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
577 ($size / 1024 / 1024),
583 eval { hest_update($hostID, $shareID, $backupNum) };
584 warn "ERROR: $@" if $@;
594 print "total duration: ",fmt_time(time() - $start_t),"\n";
600 my ($share, $hostID, $hostname) = @_;
602 $sth->{share_id} ||= $dbh->prepare(qq{
603 SELECT ID FROM shares WHERE hostID=? AND name=?
606 $sth->{share_id}->execute($hostID,$share);
608 my ($id) = $sth->{share_id}->fetchrow_array();
610 return $id if (defined($id));
612 $sth->{insert_share} ||= $dbh->prepare(qq{
618 my $drop_down = $hostname . '/' . $share;
619 $drop_down =~ s#//+#/#g;
621 $sth->{insert_share}->execute($hostID,$share, $drop_down);
622 return $dbh->last_insert_id(undef,undef,'shares',undef);
630 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
632 return $beenThere->{$key} if (defined($beenThere->{$key}));
634 $sth->{file_in_db} ||= $dbh->prepare(qq{
636 WHERE shareID = ? and
639 ( date = ? or date = ? or date = ? )
643 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
644 $sth->{file_in_db}->execute(@param);
645 my $rows = $sth->{file_in_db}->rows;
646 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
648 $beenThere->{$key}++;
650 $sth->{'insert_files'}->execute(@data) unless ($rows);
654 ####################################################
655 # recursing through filesystem structure and #
656 # and returning flattened files list #
657 ####################################################
658 sub recurseDir($$$$$$$$) {
660 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
662 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
664 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
669 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
670 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
672 # first, add all the entries in current directory
673 foreach my $path_key (keys %{$filesInBackup}) {
674 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
679 $filesInBackup->{$path_key}->{'relPath'},
680 $filesInBackup->{$path_key}->{'mtime'},
681 $filesInBackup->{$path_key}->{'type'},
682 $filesInBackup->{$path_key}->{'size'}
685 my $key = join(" ", (
689 $filesInBackup->{$path_key}->{'mtime'},
690 $filesInBackup->{$path_key}->{'size'}
693 my $key_dst_prev = join(" ", (
697 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
698 $filesInBackup->{$path_key}->{'size'}
701 my $key_dst_next = join(" ", (
705 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
706 $filesInBackup->{$path_key}->{'size'}
711 ! defined($beenThere->{$key}) &&
712 ! defined($beenThere->{$key_dst_prev}) &&
713 ! defined($beenThere->{$key_dst_next}) &&
714 ! ($found = found_in_db($key, @data))
716 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
718 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
719 $new_dirs++ unless ($found);
720 print STDERR " dir\n" if ($debug >= 2);
722 $new_files++ unless ($found);
723 print STDERR " file\n" if ($debug >= 2);
725 $size += $filesInBackup->{$path_key}->{'size'} || 0;
728 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
731 my $full_path = $dir . '/' . $path_key;
732 push @stack, $full_path;
733 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
735 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
747 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
749 while ( my $dir = shift @stack ) {
750 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
751 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
760 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);