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
252 print " creating sequence: ";
253 foreach my $seq (qw/dvd_nr/) {
255 $dbh->do( qq{ CREATE SEQUENCE $seq } );
264 ## delete data before inseting ##
267 foreach my $table (qw(files dvds backups shares hosts)) {
269 $dbh->do(qq{ DELETE FROM $table });
276 ## insert new values ##
279 $hosts = $bpc->HostInfoRead();
285 $sth->{insert_hosts} = $dbh->prepare(qq{
286 INSERT INTO hosts (name, IP) VALUES (?,?)
289 $sth->{hosts_by_name} = $dbh->prepare(qq{
290 SELECT id FROM hosts WHERE name=?
293 $sth->{backups_count} = $dbh->prepare(qq{
296 WHERE hostID=? AND num=? AND shareid=?
299 $sth->{insert_backups} = $dbh->prepare(qq{
300 INSERT INTO backups (hostID, num, date, type, shareid, size)
301 VALUES (?,?,?,?,?,-1)
304 $sth->{update_backups_size} = $dbh->prepare(qq{
305 UPDATE backups SET size = ?
306 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
309 $sth->{insert_files} = $dbh->prepare(qq{
311 (shareID, backupNum, name, path, date, type, size)
312 VALUES (?,?,?,?,?,?,?)
315 my @hosts = keys %{$hosts};
318 foreach my $host_key (@hosts) {
320 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
322 next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
324 $sth->{hosts_by_name}->execute($hostname);
326 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
327 $sth->{insert_hosts}->execute(
328 $hosts->{$host_key}->{'host'},
329 $hosts->{$host_key}->{'ip'}
332 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
336 # get backups for a host
337 my @backups = $bpc->BackupInfoRead($hostname);
338 my $incs = scalar @backups;
340 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
341 $hosts->{$host_key}->{'host'},
346 print $host_header unless $opt->quiet;
351 foreach my $backup (@backups) {
354 last if defined $opt->max && $inc_nr > $opt->max;
356 my $backupNum = $backup->{'num'};
357 my @backupShares = ();
359 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
360 $hosts->{$host_key}->{'host'},
361 $inc_nr, $incs, $backupNum,
362 $backup->{type} || '?',
363 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
364 strftime($t_fmt,localtime($backup->{startTime})),
365 fmt_time($backup->{endTime} - $backup->{startTime})
367 print $share_header unless $opt->quiet;
368 status "$hostname $backupNum $share_header";
370 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_increment => 1 });
372 foreach my $share ($files->shareList($backupNum)) {
376 $shareID = getShareID($share, $hostID, $hostname);
378 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
379 my ($count) = $sth->{backups_count}->fetchrow_array();
380 # skip if allready in database!
381 next if ($count > 0);
383 # dump host and share header for -q
387 $host_header = undef;
393 print curr_time," ", $share;
395 $sth->{insert_backups}->execute(
398 $backup->{'endTime'},
399 substr($backup->{'type'},0,4),
403 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
406 $sth->{update_backups_size}->execute(
410 $backup->{'endTime'},
411 substr($backup->{'type'},0,4),
422 my $dur = (time() - $t) || 1;
423 my $status = sprintf("%d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]",
425 ($size / 1024 / 1024),
430 status "$hostname $backupNum $status";
433 status "$hostname $backupNum full-text | indexing";
434 #eval { hest_update($hostID, $shareID, $backupNum) };
435 #warn "ERROR: $@" if $@;
436 hest_update($hostID, $shareID, $backupNum);
437 # eval breaks our re-try logic
447 print "total duration: ",fmt_time(time() - $start_t),"\n";
453 my ($share, $hostID, $hostname) = @_;
455 $sth->{share_id} ||= $dbh->prepare(qq{
456 SELECT ID FROM shares WHERE hostID=? AND name=?
459 $sth->{share_id}->execute($hostID,$share);
461 my ($id) = $sth->{share_id}->fetchrow_array();
463 return $id if (defined($id));
465 $sth->{insert_share} ||= $dbh->prepare(qq{
471 my $drop_down = $hostname . '/' . $share;
472 $drop_down =~ s#//+#/#g;
474 $sth->{insert_share}->execute($hostID,$share, $drop_down);
475 return $dbh->last_insert_id(undef,undef,'shares',undef);
483 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
485 return $beenThere->{$key} if (defined($beenThere->{$key}));
487 $sth->{file_in_db} ||= $dbh->prepare(qq{
489 WHERE shareID = ? and
492 ( date = ? or date = ? or date = ? )
496 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
497 $sth->{file_in_db}->execute(@param);
498 my $rows = $sth->{file_in_db}->rows;
499 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
501 $beenThere->{$key}++;
503 $sth->{'insert_files'}->execute(@data) unless ($rows);
507 ####################################################
508 # recursing through filesystem structure and #
509 # and returning flattened files list #
510 ####################################################
511 sub recurseDir($$$$$$$$) {
513 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
515 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
517 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
522 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
523 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
525 # first, add all the entries in current directory
526 foreach my $path_key (keys %{$filesInBackup}) {
527 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
532 $filesInBackup->{$path_key}->{'relPath'},
533 $filesInBackup->{$path_key}->{'mtime'},
534 $filesInBackup->{$path_key}->{'type'},
535 $filesInBackup->{$path_key}->{'size'}
538 my $key = join(" ", (
542 $filesInBackup->{$path_key}->{'mtime'},
543 $filesInBackup->{$path_key}->{'size'}
546 my $key_dst_prev = join(" ", (
550 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
551 $filesInBackup->{$path_key}->{'size'}
554 my $key_dst_next = join(" ", (
558 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
559 $filesInBackup->{$path_key}->{'size'}
564 ! defined($beenThere->{$key}) &&
565 ! defined($beenThere->{$key_dst_prev}) &&
566 ! defined($beenThere->{$key_dst_next}) &&
567 ! ($found = found_in_db($key, @data))
569 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
571 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
572 $new_dirs++ unless ($found);
573 print STDERR " dir\n" if ($debug >= 2);
575 $new_files++ unless ($found);
576 print STDERR " file\n" if ($debug >= 2);
578 $size += $filesInBackup->{$path_key}->{'size'} || 0;
581 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
584 my $full_path = $dir . '/' . $path_key;
585 push @stack, $full_path;
586 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
588 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
600 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
602 while ( my $dir = shift @stack ) {
603 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
604 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
613 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
619 ID SERIAL PRIMARY KEY,
620 name VARCHAR(30) NOT NULL,
624 create table shares (
625 ID SERIAL PRIMARY KEY,
626 hostID INTEGER NOT NULL references hosts(id),
627 name VARCHAR(30) NOT NULL,
628 share VARCHAR(200) NOT NULL
632 ID SERIAL PRIMARY KEY,
633 num INTEGER NOT NULL,
634 name VARCHAR(255) NOT NULL,
638 create table backups (
640 hostID INTEGER NOT NULL references hosts(id),
641 num INTEGER NOT NULL,
642 date integer NOT NULL,
643 type CHAR(4) not null,
644 shareID integer not null references shares(id),
645 size bigint not null,
646 inc_size bigint not null default -1,
647 inc_deleted boolean default false,
648 parts integer not null default 0,
652 create table backup_parts (
654 backup_id int references backups(id),
655 part_nr int not null check (part_nr > 0),
656 tar_size bigint not null check (tar_size > 0),
657 size bigint not null check (size > 0),
659 items int not null check (items > 0),
660 date timestamp default now(),
661 filename text not null,
667 shareID INTEGER NOT NULL references shares(id),
668 backupNum INTEGER NOT NULL,
669 name VARCHAR(255) NOT NULL,
670 path VARCHAR(255) NOT NULL,
671 date integer NOT NULL,
672 type INTEGER NOT NULL,
673 size bigint NOT NULL,
677 create table archive (
680 total_size bigint default -1,
682 username varchar(20) not null,
683 date timestamp default now(),
687 create table archive_parts (
688 archive_id int not null references archive(id) on delete cascade,
689 backup_part_id int not null references backup_parts(id),
690 primary key(archive_id, backup_part_id)
693 create table archive_burned (
694 archive_id int references archive(id),
695 date timestamp default now(),
696 part int not null default 1,
697 copy int not null default 1,
698 iso_size bigint default -1
701 -- report backups and corresponding dvd
703 --create view backups_on_dvds as
706 -- hosts.name || ':' || shares.name as share,
707 -- backups.num as num,
708 -- backups.type as type,
709 -- abstime(backups.date) as backup_date,
710 -- backups.size as size,
711 -- backups.inc_size as gzip_size,
712 -- archive.id as archive_id,
715 --join shares on backups.shareid=shares.id
716 --join hosts on shares.hostid = hosts.id
717 --left outer join archive_backup on backups.id = archive_backup.backup_id
718 --left outer join archive on archive_backup.archive_id = archive.id
719 --where backups.parts > 0 and size > 0
720 --order by backups.date
724 create or replace function backup_parts_check() returns trigger as '
730 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
731 if (TG_OP=''UPDATE'') then
733 b_parts := new.parts;
734 elsif (TG_OP = ''INSERT'') then
736 b_parts := new.parts;
738 b_counted := (select count(*) from backup_parts where backup_id = b_id);
739 -- raise notice ''backup % parts %'', b_id, b_parts;
740 if ( b_parts != b_counted ) then
741 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
747 create trigger do_backup_parts_check
748 after insert or update or delete on backups
749 for each row execute procedure backup_parts_check();
751 create or replace function backup_backup_parts_check() returns trigger as '
757 if (TG_OP = ''INSERT'') then
758 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
759 b_id = new.backup_id;
760 my_part_nr = new.part_nr;
761 execute ''update backups set parts = parts + 1 where id = '' || b_id;
762 elsif (TG_OP = ''DELETE'') then
763 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
764 b_id = old.backup_id;
765 my_part_nr = old.part_nr;
766 execute ''update backups set parts = parts - 1 where id = '' || b_id;
768 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
769 if ( my_part_nr != calc_part ) then
770 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;
776 create trigger do_backup_backup_parts_check
777 after insert or update or delete on backup_parts
778 for each row execute procedure backup_backup_parts_check();