4 use lib "/usr/local/BackupPC/lib";
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 });
62 if ( !getopts("cdm:v:ijfq", \%opt ) ) {
64 usage: $0 [-c|-d] [-m num] [-v|-v level] [-i|-j|-f]
67 -c create database on first use
68 -d delete database before import
69 -m num import just num increments for one host
70 -v num set verbosity (debug) level (default $debug)
71 -i update Hyper Estraier full text index
72 -j update full text, don't check existing files
73 -f don't do anything with full text index
74 -q be quiet for hosts without changes
76 Option -j is variation on -i. It will allow faster initial creation
77 of full-text index from existing database.
79 Option -f will create database which is out of sync with full text index. You
80 will have to re-run $0 with -i to fix it.
87 print "Debug level at $opt{v}\n";
90 print "WARNING: disabling full-text index update. You need to re-run $0 -j !\n";
91 $index_node_url = undef;
97 my $t = shift || return;
99 my ($ss,$mm,$hh) = gmtime($t);
100 $out .= "${hh}h" if ($hh);
101 $out .= sprintf("%02d:%02d", $mm,$ss);
106 return strftime($t_fmt,localtime());
111 my ($host_id, $share_id, $num) = @_;
113 my $skip_check = $opt{j} && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
115 print curr_time," updating fulltext:";
122 my $search = BackupPC::Search->search_module;
130 if (defined($host_id) && defined($share_id) && defined($num)) {
137 @data = ( $host_id, $share_id, $num );
140 my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);
142 my $sth = $dbh->prepare(qq{
146 shares.name AS sname,
147 -- shares.share AS sharename,
148 files.backupnum AS backupnum,
149 -- files.name AS filename,
150 files.path AS filepath,
154 files.shareid AS shareid,
155 backups.date AS backup_date
157 INNER JOIN shares ON files.shareID=shares.ID
158 INNER JOIN hosts ON hosts.ID = shares.hostID
159 INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
164 $sth->execute(@data);
165 $results = $sth->rows;
168 print " - no new files\n";
175 my $t = shift || return;
176 my $iso = BackupPC::Lib::timeStamp($t);
181 while (my $row = $sth->fetchrow_hashref()) {
182 next if $search->exists( $row );
183 $search->add_doc( $row );
189 $offset += EST_CHUNK;
191 } while ($results == EST_CHUNK);
195 my $dur = (time() - $t) || 1;
196 printf(" [%.2f/s dur: %s]\n",
206 if ( ( $opt{i} || $opt{j} ) && !$opt{c} ) {
208 print "force update of Hyper Estraier index ";
209 print "by -i flag" if ($opt{i});
210 print "by -j flag" if ($opt{j});
218 my $index = shift || return;
219 my ($table,$col,$unique) = split(/:/, $index);
222 print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
223 $dbh->do(qq{ create $unique index $index on $table($col) });
226 print "creating tables...\n";
230 ID SERIAL PRIMARY KEY,
231 name VARCHAR(30) NOT NULL,
235 create table shares (
236 ID SERIAL PRIMARY KEY,
237 hostID INTEGER NOT NULL references hosts(id),
238 name VARCHAR(30) NOT NULL,
239 share VARCHAR(200) NOT NULL
243 ID SERIAL PRIMARY KEY,
244 num INTEGER NOT NULL,
245 name VARCHAR(255) NOT NULL,
249 create table backups (
251 hostID INTEGER NOT NULL references hosts(id),
252 num INTEGER NOT NULL,
253 date integer NOT NULL,
254 type CHAR(4) not null,
255 shareID integer not null references shares(id),
256 size bigint not null,
257 inc_size bigint not null default -1,
258 inc_deleted boolean default false,
259 parts integer not null default 0,
265 shareID INTEGER NOT NULL references shares(id),
266 backupNum INTEGER NOT NULL,
267 name VARCHAR(255) NOT NULL,
268 path VARCHAR(255) NOT NULL,
269 date integer NOT NULL,
270 type INTEGER NOT NULL,
271 size bigint NOT NULL,
275 create table archive (
278 total_size bigint default -1,
280 username varchar(20) not null,
281 date timestamp default now(),
285 create table archive_backup (
286 archive_id int not null references archive(id) on delete cascade,
287 backup_id int not null references backups(id),
288 primary key(archive_id, backup_id)
291 create table archive_burned (
292 archive_id int references archive(id),
293 date timestamp default now(),
294 part int not null default 1,
295 copy int not null default 1,
296 iso_size bigint default -1
299 create table backup_parts (
301 backup_id int references backups(id),
302 part_nr int not null check (part_nr > 0),
303 tar_size bigint not null check (tar_size > 0),
304 size bigint not null check (size > 0),
306 items int not null check (items > 0),
307 date timestamp default now(),
311 -- report backups and corresponding dvd
313 create view backups_on_dvds as
316 hosts.name || ':' || shares.name as share,
318 backups.type as type,
319 abstime(backups.date) as backup_date,
320 backups.size as size,
321 backups.inc_size as gzip_size,
322 archive.id as archive_id,
325 join shares on backups.shareid=shares.id
326 join hosts on shares.hostid = hosts.id
327 left outer join archive_backup on backups.id = archive_backup.backup_id
328 left outer join archive on archive_backup.archive_id = archive.id
329 where backups.parts > 0 and size > 0
330 order by backups.date
334 print "creating indexes: ";
336 foreach my $index (qw(
349 archive_burned:archive_id
350 backup_parts:backup_id,part_nr:unique
355 print " creating sequence: ";
356 foreach my $seq (qw/dvd_nr/) {
358 $dbh->do( qq{ CREATE SEQUENCE $seq } );
361 print " creating triggers ";
362 $dbh->do( <<__END_OF_TRIGGER__ );
364 create or replace function backup_parts_check() returns trigger as '
370 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
371 if (TG_OP=''UPDATE'') then
373 b_parts := new.parts;
374 elsif (TG_OP = ''INSERT'') then
376 b_parts := new.parts;
378 b_counted := (select count(*) from backup_parts where backup_id = b_id);
379 -- raise notice ''backup % parts %'', b_id, b_parts;
380 if ( b_parts != b_counted ) then
381 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
387 create trigger do_backup_parts_check
388 after insert or update or delete on backups
389 for each row execute procedure backup_parts_check();
391 create or replace function backup_backup_parts_check() returns trigger as '
397 if (TG_OP = ''INSERT'') then
398 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
399 b_id = new.backup_id;
400 my_part_nr = new.part_nr;
401 execute ''update backups set parts = parts + 1 where id = '' || b_id;
402 elsif (TG_OP = ''DELETE'') then
403 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
404 b_id = old.backup_id;
405 my_part_nr = old.part_nr;
406 execute ''update backups set parts = parts - 1 where id = '' || b_id;
408 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
409 if ( my_part_nr != calc_part ) then
410 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;
416 create trigger do_backup_backup_parts_check
417 after insert or update or delete on backup_parts
418 for each row execute procedure backup_backup_parts_check();
428 ## delete data before inseting ##
431 foreach my $table (qw(files dvds backups shares hosts)) {
433 $dbh->do(qq{ DELETE FROM $table });
440 ## insert new values ##
443 $hosts = $bpc->HostInfoRead();
449 $sth->{insert_hosts} = $dbh->prepare(qq{
450 INSERT INTO hosts (name, IP) VALUES (?,?)
453 $sth->{hosts_by_name} = $dbh->prepare(qq{
454 SELECT ID FROM hosts WHERE name=?
457 $sth->{backups_count} = $dbh->prepare(qq{
460 WHERE hostID=? AND num=? AND shareid=?
463 $sth->{insert_backups} = $dbh->prepare(qq{
464 INSERT INTO backups (hostID, num, date, type, shareid, size)
465 VALUES (?,?,?,?,?,-1)
468 $sth->{update_backups_size} = $dbh->prepare(qq{
469 UPDATE backups SET size = ?
470 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
473 $sth->{insert_files} = $dbh->prepare(qq{
475 (shareID, backupNum, name, path, date, type, size)
476 VALUES (?,?,?,?,?,?,?)
479 my @hosts = keys %{$hosts};
482 foreach my $host_key (@hosts) {
484 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
486 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
488 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
489 $sth->{insert_hosts}->execute(
490 $hosts->{$host_key}->{'host'},
491 $hosts->{$host_key}->{'ip'}
494 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
498 # get backups for a host
499 my @backups = $bpc->BackupInfoRead($hostname);
500 my $incs = scalar @backups;
502 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
503 $hosts->{$host_key}->{'host'},
508 print $host_header unless ($opt{q});
513 foreach my $backup (@backups) {
516 last if (defined $opt{m} && $inc_nr > $opt{m});
518 my $backupNum = $backup->{'num'};
519 my @backupShares = ();
521 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
522 $hosts->{$host_key}->{'host'},
523 $inc_nr, $incs, $backupNum,
524 $backup->{type} || '?',
525 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
526 strftime($t_fmt,localtime($backup->{startTime})),
527 fmt_time($backup->{endTime} - $backup->{startTime})
529 print $share_header unless ($opt{q});
531 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
533 foreach my $share ($files->shareList($backupNum)) {
537 $shareID = getShareID($share, $hostID, $hostname);
539 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
540 my ($count) = $sth->{backups_count}->fetchrow_array();
541 # skip if allready in database!
542 next if ($count > 0);
544 # dump host and share header for -q
548 $host_header = undef;
554 print curr_time," ", $share;
556 $sth->{insert_backups}->execute(
559 $backup->{'endTime'},
560 substr($backup->{'type'},0,4),
564 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
567 $sth->{update_backups_size}->execute(
571 $backup->{'endTime'},
572 substr($backup->{'type'},0,4),
583 my $dur = (time() - $t) || 1;
584 printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
586 ($size / 1024 / 1024),
592 eval { hest_update($hostID, $shareID, $backupNum) };
593 warn "ERROR: $@" if $@;
603 print "total duration: ",fmt_time(time() - $start_t),"\n";
609 my ($share, $hostID, $hostname) = @_;
611 $sth->{share_id} ||= $dbh->prepare(qq{
612 SELECT ID FROM shares WHERE hostID=? AND name=?
615 $sth->{share_id}->execute($hostID,$share);
617 my ($id) = $sth->{share_id}->fetchrow_array();
619 return $id if (defined($id));
621 $sth->{insert_share} ||= $dbh->prepare(qq{
627 my $drop_down = $hostname . '/' . $share;
628 $drop_down =~ s#//+#/#g;
630 $sth->{insert_share}->execute($hostID,$share, $drop_down);
631 return $dbh->last_insert_id(undef,undef,'shares',undef);
639 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
641 return $beenThere->{$key} if (defined($beenThere->{$key}));
643 $sth->{file_in_db} ||= $dbh->prepare(qq{
645 WHERE shareID = ? and
648 ( date = ? or date = ? or date = ? )
652 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
653 $sth->{file_in_db}->execute(@param);
654 my $rows = $sth->{file_in_db}->rows;
655 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
657 $beenThere->{$key}++;
659 $sth->{'insert_files'}->execute(@data) unless ($rows);
663 ####################################################
664 # recursing through filesystem structure and #
665 # and returning flattened files list #
666 ####################################################
667 sub recurseDir($$$$$$$$) {
669 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
671 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
673 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
678 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
679 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
681 # first, add all the entries in current directory
682 foreach my $path_key (keys %{$filesInBackup}) {
683 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
688 $filesInBackup->{$path_key}->{'relPath'},
689 $filesInBackup->{$path_key}->{'mtime'},
690 $filesInBackup->{$path_key}->{'type'},
691 $filesInBackup->{$path_key}->{'size'}
694 my $key = join(" ", (
698 $filesInBackup->{$path_key}->{'mtime'},
699 $filesInBackup->{$path_key}->{'size'}
702 my $key_dst_prev = join(" ", (
706 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
707 $filesInBackup->{$path_key}->{'size'}
710 my $key_dst_next = join(" ", (
714 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
715 $filesInBackup->{$path_key}->{'size'}
720 ! defined($beenThere->{$key}) &&
721 ! defined($beenThere->{$key_dst_prev}) &&
722 ! defined($beenThere->{$key_dst_next}) &&
723 ! ($found = found_in_db($key, @data))
725 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
727 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
728 $new_dirs++ unless ($found);
729 print STDERR " dir\n" if ($debug >= 2);
731 $new_files++ unless ($found);
732 print STDERR " file\n" if ($debug >= 2);
734 $size += $filesInBackup->{$path_key}->{'size'} || 0;
737 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
740 my $full_path = $dir . '/' . $path_key;
741 push @stack, $full_path;
742 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
744 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
756 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
758 while ( my $dir = shift @stack ) {
759 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
760 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
769 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);