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";
316 next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
318 $sth->{hosts_by_name}->execute($hostname);
320 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
321 $sth->{insert_hosts}->execute(
322 $hosts->{$host_key}->{'host'},
323 $hosts->{$host_key}->{'ip'}
326 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
330 # get backups for a host
331 my @backups = $bpc->BackupInfoRead($hostname);
332 my $incs = scalar @backups;
334 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
335 $hosts->{$host_key}->{'host'},
340 print $host_header unless $opt->quiet;
345 foreach my $backup (@backups) {
348 last if defined $opt->max && $inc_nr > $opt->max;
350 my $backupNum = $backup->{'num'};
351 my @backupShares = ();
353 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
354 $hosts->{$host_key}->{'host'},
355 $inc_nr, $incs, $backupNum,
356 $backup->{type} || '?',
357 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
358 strftime($t_fmt,localtime($backup->{startTime})),
359 fmt_time($backup->{endTime} - $backup->{startTime})
361 print $share_header unless $opt->quiet;
362 status "$hostname $backupNum $share_header";
364 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_increment => 1 });
366 foreach my $share ($files->shareList($backupNum)) {
370 $shareID = getShareID($share, $hostID, $hostname);
372 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
373 my ($count) = $sth->{backups_count}->fetchrow_array();
374 # skip if allready in database!
375 next if ($count > 0);
377 # dump host and share header for -q
381 $host_header = undef;
387 print curr_time," ", $share;
389 $sth->{insert_backups}->execute(
392 $backup->{'endTime'},
393 substr($backup->{'type'},0,4),
397 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
400 $sth->{update_backups_size}->execute(
404 $backup->{'endTime'},
405 substr($backup->{'type'},0,4),
416 my $dur = (time() - $t) || 1;
417 my $status = sprintf("%d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]",
419 ($size / 1024 / 1024),
424 status "$hostname $backupNum $status";
427 status "$hostname $backupNum full-text | indexing";
428 #eval { hest_update($hostID, $shareID, $backupNum) };
429 #warn "ERROR: $@" if $@;
430 hest_update($hostID, $shareID, $backupNum);
431 # eval breaks our re-try logic
441 print "total duration: ",fmt_time(time() - $start_t),"\n";
447 my ($share, $hostID, $hostname) = @_;
449 $sth->{share_id} ||= $dbh->prepare(qq{
450 SELECT ID FROM shares WHERE hostID=? AND name=?
453 $sth->{share_id}->execute($hostID,$share);
455 my ($id) = $sth->{share_id}->fetchrow_array();
457 return $id if (defined($id));
459 $sth->{insert_share} ||= $dbh->prepare(qq{
465 my $drop_down = $hostname . '/' . $share;
466 $drop_down =~ s#//+#/#g;
468 $sth->{insert_share}->execute($hostID,$share, $drop_down);
469 return $dbh->last_insert_id(undef,undef,'shares',undef);
477 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
479 return $beenThere->{$key} if (defined($beenThere->{$key}));
481 $sth->{file_in_db} ||= $dbh->prepare(qq{
483 WHERE shareID = ? and
486 ( date = ? or date = ? or date = ? )
490 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
491 $sth->{file_in_db}->execute(@param);
492 my $rows = $sth->{file_in_db}->rows;
493 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
495 $beenThere->{$key}++;
497 $sth->{'insert_files'}->execute(@data) unless ($rows);
501 ####################################################
502 # recursing through filesystem structure and #
503 # and returning flattened files list #
504 ####################################################
505 sub recurseDir($$$$$$$$) {
507 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
509 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
511 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
516 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
517 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
519 # first, add all the entries in current directory
520 foreach my $path_key (keys %{$filesInBackup}) {
521 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
526 $filesInBackup->{$path_key}->{'relPath'},
527 $filesInBackup->{$path_key}->{'mtime'},
528 $filesInBackup->{$path_key}->{'type'},
529 $filesInBackup->{$path_key}->{'size'}
532 my $key = join(" ", (
536 $filesInBackup->{$path_key}->{'mtime'},
537 $filesInBackup->{$path_key}->{'size'}
540 my $key_dst_prev = join(" ", (
544 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
545 $filesInBackup->{$path_key}->{'size'}
548 my $key_dst_next = join(" ", (
552 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
553 $filesInBackup->{$path_key}->{'size'}
558 ! defined($beenThere->{$key}) &&
559 ! defined($beenThere->{$key_dst_prev}) &&
560 ! defined($beenThere->{$key_dst_next}) &&
561 ! ($found = found_in_db($key, @data))
563 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
565 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
566 $new_dirs++ unless ($found);
567 print STDERR " dir\n" if ($debug >= 2);
569 $new_files++ unless ($found);
570 print STDERR " file\n" if ($debug >= 2);
572 $size += $filesInBackup->{$path_key}->{'size'} || 0;
575 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
578 my $full_path = $dir . '/' . $path_key;
579 push @stack, $full_path;
580 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
582 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
594 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
596 while ( my $dir = shift @stack ) {
597 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
598 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
607 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
613 ID SERIAL PRIMARY KEY,
614 name VARCHAR(30) NOT NULL,
618 create table shares (
619 ID SERIAL PRIMARY KEY,
620 hostID INTEGER NOT NULL references hosts(id),
621 name VARCHAR(30) NOT NULL,
622 share VARCHAR(200) NOT NULL
626 ID SERIAL PRIMARY KEY,
627 num INTEGER NOT NULL,
628 name VARCHAR(255) NOT NULL,
632 create table backups (
634 hostID INTEGER NOT NULL references hosts(id),
635 num INTEGER NOT NULL,
636 date integer NOT NULL,
637 type CHAR(4) not null,
638 shareID integer not null references shares(id),
639 size bigint not null,
640 inc_size bigint not null default -1,
641 inc_deleted boolean default false,
642 parts integer not null default 0,
646 create table backup_parts (
648 backup_id int references backups(id),
649 part_nr int not null check (part_nr > 0),
650 tar_size bigint not null check (tar_size > 0),
651 size bigint not null check (size > 0),
653 items int not null check (items > 0),
654 date timestamp default now(),
655 filename text not null,
661 shareID INTEGER NOT NULL references shares(id),
662 backupNum INTEGER NOT NULL,
663 name VARCHAR(255) NOT NULL,
664 path VARCHAR(255) NOT NULL,
665 date integer NOT NULL,
666 type INTEGER NOT NULL,
667 size bigint NOT NULL,
671 create sequence dvd_nr;
673 create table archive (
676 total_size bigint default -1,
678 username varchar(20) not null,
679 date timestamp default now(),
683 create table archive_parts (
684 archive_id int not null references archive(id) on delete cascade,
685 backup_part_id int not null references backup_parts(id),
686 primary key(archive_id, backup_part_id)
689 create table archive_burned (
690 archive_id int references archive(id),
691 date timestamp default now(),
692 part int not null default 1,
693 copy int not null default 1,
694 iso_size bigint default -1
697 -- report backups and corresponding dvd
699 --create view backups_on_dvds as
702 -- hosts.name || ':' || shares.name as share,
703 -- backups.num as num,
704 -- backups.type as type,
705 -- abstime(backups.date) as backup_date,
706 -- backups.size as size,
707 -- backups.inc_size as gzip_size,
708 -- archive.id as archive_id,
711 --join shares on backups.shareid=shares.id
712 --join hosts on shares.hostid = hosts.id
713 --left outer join archive_backup_parts on backups.id = archive_backup_parts.backup_id
714 --left outer join archive on archive_backup_parts.archive_id = archive.id
715 --where backups.parts > 0 and size > 0
716 --order by backups.date
720 -- used by BackupPC_ASA_BurnArchiveMedia
721 CREATE VIEW archive_backup_parts AS
723 backup_parts.backup_id,
728 shares.name as share,
730 backups.date as date,
731 backup_parts.part_nr as part_nr,
732 backups.parts as parts,
733 backup_parts.size as size,
734 backup_parts.md5 as md5,
736 backup_parts.filename
738 JOIN archive_parts ON backup_parts.id = backup_part_id
739 JOIN archive ON archive_id = archive.id
740 JOIN backups ON backup_id = backups.id
741 JOIN hosts ON hostid = hosts.id
742 JOIN shares ON shareid = shares.id
746 CREATE VIEW backups_burned AS
747 SELECT backup_parts.backup_id,
748 count(backup_parts.backup_id) as backup_parts,
749 count(archive_burned.archive_id) AS burned_parts,
750 count(backup_parts.backup_id) = count(archive_burned.archive_id) as burned
752 left outer JOIN archive_parts ON backup_part_id = backup_parts.id
753 left join archive on archive.id = archive_id
754 left outer join archive_burned on archive_burned.archive_id = archive.id
755 GROUP BY backup_parts.backup_id ;
758 -- triggers for backup_parts consistency
759 create or replace function backup_parts_check() returns trigger as '
765 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
766 if (TG_OP=''UPDATE'') then
768 b_parts := new.parts;
769 elsif (TG_OP = ''INSERT'') then
771 b_parts := new.parts;
773 b_counted := (select count(*) from backup_parts where backup_id = b_id);
774 -- raise notice ''backup % parts %'', b_id, b_parts;
775 if ( b_parts != b_counted ) then
776 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
782 create trigger do_backup_parts_check
783 after insert or update or delete on backups
784 for each row execute procedure backup_parts_check();
786 create or replace function backup_backup_parts_check() returns trigger as '
792 if (TG_OP = ''INSERT'') then
793 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
794 b_id = new.backup_id;
795 my_part_nr = new.part_nr;
796 execute ''update backups set parts = parts + 1 where id = '' || b_id;
797 elsif (TG_OP = ''DELETE'') then
798 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
799 b_id = old.backup_id;
800 my_part_nr = old.part_nr;
801 execute ''update backups set parts = parts - 1 where id = '' || b_id;
803 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
804 if ( my_part_nr != calc_part ) then
805 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;
811 create trigger do_backup_backup_parts_check
812 after insert or update or delete on backup_parts
813 for each row execute procedure backup_backup_parts_check();