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 );
85 $new =~ s{^[\w\/]+/(\w+) }{$1 }; # strip path from process name
86 if ( $text =~ m/^\|/ ) {
87 $new =~ s/\|.*/$text/ or $new .= " $text";
89 $new =~ s/\s+.*/ $text/ or $new .= " $text";
95 my $t = shift || return;
97 my ($ss,$mm,$hh) = gmtime($t);
98 $out .= "${hh}h" if ($hh);
99 $out .= sprintf("%02d:%02d", $mm,$ss);
104 return strftime($t_fmt,localtime());
109 my ($host_id, $share_id, $num) = @_;
111 my $skip_check = $opt->junk && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
113 print curr_time," updating fulltext:";
120 my $search = BackupPC::Search->search_module;
128 if (defined($host_id) && defined($share_id) && defined($num)) {
135 @data = ( $host_id, $share_id, $num );
138 my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);
140 my $sth = $dbh->prepare(qq{
144 shares.name AS sname,
145 -- shares.share AS sharename,
146 files.backupnum AS backupnum,
147 -- files.name AS filename,
148 files.path AS filepath,
152 files.shareid AS shareid,
153 backups.date AS backup_date
155 INNER JOIN shares ON files.shareID=shares.ID
156 INNER JOIN hosts ON hosts.ID = shares.hostID
157 INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
162 $sth->execute(@data);
163 $results = $sth->rows;
166 print " - no new files\n";
173 my $t = shift || return;
174 my $iso = BackupPC::Lib::timeStamp($t);
179 while (my $row = $sth->fetchrow_hashref()) {
180 next if $search->exists( $row );
181 $search->add_doc( $row );
188 $offset += EST_CHUNK;
190 } while ($results == EST_CHUNK);
194 my $dur = (time() - $t) || 1;
195 printf(" [%.2f/s dur: %s]\n",
205 if ( ( $opt->index || $opt->junk ) && !$opt->create ) {
207 print "force update of Hyper Estraier index ";
208 print "by -i flag" if ($opt->index);
209 print "by -j flag" if ($opt->junk);
217 my $index = shift || return;
218 my ($table,$col,$unique) = split(/:/, $index);
221 print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
222 $dbh->do(qq{ create $unique index $index on $table($col) });
225 print "creating tables...\n";
228 { local $/ = undef; $sql = <DATA> };
231 print "creating indexes: ";
233 foreach my $index (qw(
246 archive_burned:archive_id
247 backup_parts:backup_id,part_nr:unique
258 ## delete data before inseting ##
261 foreach my $table (qw(files dvds backups shares hosts)) {
263 $dbh->do(qq{ DELETE FROM $table });
270 ## insert new values ##
273 $hosts = $bpc->HostInfoRead();
279 $sth->{insert_hosts} = $dbh->prepare(qq{
280 INSERT INTO hosts (name, IP) VALUES (?,?)
283 $sth->{hosts_by_name} = $dbh->prepare(qq{
284 SELECT id FROM hosts WHERE name=?
287 $sth->{backups_count} = $dbh->prepare(qq{
290 WHERE hostID=? AND num=? AND shareid=?
293 $sth->{insert_backups} = $dbh->prepare(qq{
294 INSERT INTO backups (hostID, num, date, type, shareid, size)
295 VALUES (?,?,?,?,?,-1)
298 $sth->{update_backups_size} = $dbh->prepare(qq{
299 UPDATE backups SET size = ?
300 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
303 $sth->{insert_files} = $dbh->prepare(qq{
305 (shareID, backupNum, name, path, date, type, size)
306 VALUES (?,?,?,?,?,?,?)
309 my @hosts = keys %{$hosts};
312 foreach my $host_key (@hosts) {
314 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
315 $hostname = lc $hostname;
317 next if $opt->host && ! grep { m/^$hostname$/i } @{ $opt->host };
319 $sth->{hosts_by_name}->execute($hostname);
321 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
322 $sth->{insert_hosts}->execute(
323 $hosts->{$host_key}->{'host'},
324 $hosts->{$host_key}->{'ip'}
327 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
331 # get backups for a host
332 my @backups = $bpc->BackupInfoRead($hostname);
333 my $incs = scalar @backups;
335 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
336 $hosts->{$host_key}->{'host'},
341 print $host_header unless $opt->quiet;
346 foreach my $backup (@backups) {
349 last if defined $opt->max && $inc_nr > $opt->max;
351 my $backupNum = $backup->{'num'};
352 my @backupShares = ();
354 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
355 $hosts->{$host_key}->{'host'},
356 $inc_nr, $incs, $backupNum,
357 $backup->{type} || '?',
358 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
359 strftime($t_fmt,localtime($backup->{startTime})),
360 fmt_time($backup->{endTime} - $backup->{startTime})
362 print $share_header unless $opt->quiet;
363 status "$hostname $backupNum $share_header";
365 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_increment => 1 });
367 foreach my $share ($files->shareList($backupNum)) {
371 $shareID = getShareID($share, $hostID, $hostname);
373 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
374 my ($count) = $sth->{backups_count}->fetchrow_array();
375 # skip if allready in database!
376 next if ($count > 0);
378 # dump host and share header for -q
382 $host_header = undef;
388 print curr_time," ", $share;
390 $sth->{insert_backups}->execute(
393 $backup->{'endTime'},
394 substr($backup->{'type'},0,4),
398 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
401 $sth->{update_backups_size}->execute(
405 $backup->{'endTime'},
406 substr($backup->{'type'},0,4),
417 my $dur = (time() - $t) || 1;
418 my $status = sprintf("%d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]",
420 ($size / 1024 / 1024),
425 status "$hostname $backupNum $status";
428 status "$hostname $backupNum full-text | indexing";
429 #eval { hest_update($hostID, $shareID, $backupNum) };
430 #warn "ERROR: $@" if $@;
431 hest_update($hostID, $shareID, $backupNum);
432 # eval breaks our re-try logic
442 print "total duration: ",fmt_time(time() - $start_t),"\n";
448 my ($share, $hostID, $hostname) = @_;
450 $sth->{share_id} ||= $dbh->prepare(qq{
451 SELECT ID FROM shares WHERE hostID=? AND name=?
454 $sth->{share_id}->execute($hostID,$share);
456 my ($id) = $sth->{share_id}->fetchrow_array();
458 return $id if (defined($id));
460 $sth->{insert_share} ||= $dbh->prepare(qq{
466 my $drop_down = $hostname . '/' . $share;
467 $drop_down =~ s#//+#/#g;
469 $sth->{insert_share}->execute($hostID,$share, $drop_down);
470 return $dbh->last_insert_id(undef,undef,'shares',undef);
478 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
480 return $beenThere->{$key} if (defined($beenThere->{$key}));
482 $sth->{file_in_db} ||= $dbh->prepare(qq{
484 WHERE shareID = ? and
487 ( date = ? or date = ? or date = ? )
491 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
492 $sth->{file_in_db}->execute(@param);
493 my $rows = $sth->{file_in_db}->rows;
494 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
496 $beenThere->{$key}++;
498 $sth->{'insert_files'}->execute(@data) unless ($rows);
502 ####################################################
503 # recursing through filesystem structure and #
504 # and returning flattened files list #
505 ####################################################
506 sub recurseDir($$$$$$$$) {
508 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
510 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
512 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
517 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
518 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
520 # first, add all the entries in current directory
521 foreach my $path_key (keys %{$filesInBackup}) {
522 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
527 $filesInBackup->{$path_key}->{'relPath'},
528 $filesInBackup->{$path_key}->{'mtime'},
529 $filesInBackup->{$path_key}->{'type'},
530 $filesInBackup->{$path_key}->{'size'}
533 my $key = join(" ", (
537 $filesInBackup->{$path_key}->{'mtime'},
538 $filesInBackup->{$path_key}->{'size'}
541 my $key_dst_prev = join(" ", (
545 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
546 $filesInBackup->{$path_key}->{'size'}
549 my $key_dst_next = join(" ", (
553 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
554 $filesInBackup->{$path_key}->{'size'}
559 ! defined($beenThere->{$key}) &&
560 ! defined($beenThere->{$key_dst_prev}) &&
561 ! defined($beenThere->{$key_dst_next}) &&
562 ! ($found = found_in_db($key, @data))
564 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
566 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
567 $new_dirs++ unless ($found);
568 print STDERR " dir\n" if ($debug >= 2);
570 $new_files++ unless ($found);
571 print STDERR " file\n" if ($debug >= 2);
573 $size += $filesInBackup->{$path_key}->{'size'} || 0;
576 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
579 my $full_path = $dir . '/' . $path_key;
580 push @stack, $full_path;
581 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
583 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
595 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
597 while ( my $dir = shift @stack ) {
598 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
599 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
608 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
614 ID SERIAL PRIMARY KEY,
615 name VARCHAR(30) NOT NULL,
619 create table shares (
620 ID SERIAL PRIMARY KEY,
621 hostID INTEGER NOT NULL references hosts(id),
622 name VARCHAR(30) NOT NULL,
623 share VARCHAR(200) NOT NULL
627 ID SERIAL PRIMARY KEY,
628 num INTEGER NOT NULL,
629 name VARCHAR(255) NOT NULL,
633 create table backups (
635 hostID INTEGER NOT NULL references hosts(id),
636 num INTEGER NOT NULL,
637 date integer NOT NULL,
638 type CHAR(4) not null,
639 shareID integer not null references shares(id),
640 size bigint not null,
641 inc_size bigint not null default -1,
642 inc_deleted boolean default false,
643 parts integer not null default 0,
647 create table backup_parts (
649 backup_id int references backups(id),
650 part_nr int not null check (part_nr > 0),
651 tar_size bigint not null check (tar_size > 0),
652 size bigint not null check (size > 0),
654 items int not null check (items > 0),
655 date timestamp default now(),
656 filename text not null,
662 shareID INTEGER NOT NULL references shares(id),
663 backupNum INTEGER NOT NULL,
664 name VARCHAR(255) NOT NULL,
665 path VARCHAR(255) NOT NULL,
666 date integer NOT NULL,
667 type INTEGER NOT NULL,
668 size bigint NOT NULL,
672 create sequence dvd_nr;
674 create table archive (
677 total_size bigint default -1,
679 username varchar(20) not null,
680 date timestamp default now(),
684 create table archive_parts (
685 archive_id int not null references archive(id) on delete cascade,
686 backup_part_id int not null references backup_parts(id),
687 primary key(archive_id, backup_part_id)
690 create table archive_burned (
691 archive_id int references archive(id),
692 date timestamp default now(),
693 part int not null default 1,
694 copy int not null default 1,
695 iso_size bigint default -1
698 -- report backups and corresponding dvd
700 --create view backups_on_dvds as
703 -- hosts.name || ':' || shares.name as share,
704 -- backups.num as num,
705 -- backups.type as type,
706 -- abstime(backups.date) as backup_date,
707 -- backups.size as size,
708 -- backups.inc_size as gzip_size,
709 -- archive.id as archive_id,
712 --join shares on backups.shareid=shares.id
713 --join hosts on shares.hostid = hosts.id
714 --left outer join archive_backup_parts on backups.id = archive_backup_parts.backup_id
715 --left outer join archive on archive_backup_parts.archive_id = archive.id
716 --where backups.parts > 0 and size > 0
717 --order by backups.date
721 -- used by BackupPC_ASA_BurnArchiveMedia
722 CREATE VIEW archive_backup_parts AS
724 backup_parts.backup_id,
729 shares.name as share,
731 backups.date as date,
732 backup_parts.part_nr as part_nr,
733 backups.parts as parts,
734 backup_parts.size as size,
735 backup_parts.md5 as md5,
737 backup_parts.filename
739 JOIN archive_parts ON backup_parts.id = backup_part_id
740 JOIN archive ON archive_id = archive.id
741 JOIN backups ON backup_id = backups.id
742 JOIN hosts ON hostid = hosts.id
743 JOIN shares ON shareid = shares.id
747 CREATE VIEW backups_burned AS
748 SELECT backup_parts.backup_id,
749 count(backup_parts.backup_id) as backup_parts,
750 count(archive_burned.archive_id) AS burned_parts,
751 count(backup_parts.backup_id) = count(archive_burned.archive_id) as burned
753 left outer JOIN archive_parts ON backup_part_id = backup_parts.id
754 left join archive on archive.id = archive_id
755 left outer join archive_burned on archive_burned.archive_id = archive.id
756 GROUP BY backup_parts.backup_id ;
759 -- triggers for backup_parts consistency
760 create or replace function backup_parts_check() returns trigger as '
766 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
767 if (TG_OP=''UPDATE'') then
769 b_parts := new.parts;
770 elsif (TG_OP = ''INSERT'') then
772 b_parts := new.parts;
774 b_counted := (select count(*) from backup_parts where backup_id = b_id);
775 -- raise notice ''backup % parts %'', b_id, b_parts;
776 if ( b_parts != b_counted ) then
777 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
783 create trigger do_backup_parts_check
784 after insert or update or delete on backups
785 for each row execute procedure backup_parts_check();
787 create or replace function backup_backup_parts_check() returns trigger as '
793 if (TG_OP = ''INSERT'') then
794 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
795 b_id = new.backup_id;
796 my_part_nr = new.part_nr;
797 execute ''update backups set parts = parts + 1 where id = '' || b_id;
798 elsif (TG_OP = ''DELETE'') then
799 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
800 b_id = old.backup_id;
801 my_part_nr = old.part_nr;
802 execute ''update backups set parts = parts - 1 where id = '' || b_id;
804 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
805 if ( my_part_nr != calc_part ) then
806 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;
812 create trigger do_backup_backup_parts_check
813 after insert or update or delete on backup_parts
814 for each row execute procedure backup_backup_parts_check();