4 use lib "/usr/local/BackupPC/lib";
11 use Time::HiRes qw/time/;
13 use POSIX qw/strftime/;
16 use Data::Dump qw(dump);
20 $search_module = "BackupPC::Search::Estraier";
21 $search_module = "BackupPC::Search::KinoSearch" if $ENV{KINO};
22 eval "use $search_module";
24 warn "ERROR: $search_module: $!";
26 warn "# using $search_module for full-text search";
30 use constant BPC_FTYPE_DIR => 5;
31 use constant EST_CHUNK => 4096;
33 # daylight saving time change offset for 1h
34 my $dst_offset = 60 * 60;
41 my $pid_path = abs_path($0);
42 $pid_path =~ s/\W+/_/g;
44 my $pidfile = new File::Pid({
45 file => "/tmp/$pid_path",
48 if (my $pid = $pidfile->running ) {
49 die "$0 already running: $pid\n";
50 } elsif ($pidfile->pid ne $$) {
52 $pidfile = new File::Pid;
54 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
57 my $t_fmt = '%Y-%m-%d %H:%M:%S';
60 my $bpc = BackupPC::Lib->new || die;
61 my %Conf = $bpc->Conf();
62 my $TopDir = $bpc->TopDir();
65 my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
66 my $user = $Conf{SearchUser} || '';
68 my $index_node_url = $Conf{HyperEstraierIndex};
70 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
74 if ( !getopts("cdm:v:ijfq", \%opt ) ) {
76 usage: $0 [-c|-d] [-m num] [-v|-v level] [-i|-j|-f]
79 -c create database on first use
80 -d delete database before import
81 -m num import just num increments for one host
82 -v num set verbosity (debug) level (default $debug)
83 -i update Hyper Estraier full text index
84 -j update full text, don't check existing files
85 -f don't do anything with full text index
86 -q be quiet for hosts without changes
88 Option -j is variation on -i. It will allow faster initial creation
89 of full-text index from existing database.
91 Option -f will create database which is out of sync with full text index. You
92 will have to re-run $0 with -i to fix it.
99 print "Debug level at $opt{v}\n";
102 print "WARNING: disabling full-text index update. You need to re-run $0 -j !\n";
103 $index_node_url = undef;
109 my $t = shift || return;
111 my ($ss,$mm,$hh) = gmtime($t);
112 $out .= "${hh}h" if ($hh);
113 $out .= sprintf("%02d:%02d", $mm,$ss);
118 return strftime($t_fmt,localtime());
123 my ($host_id, $share_id, $num) = @_;
125 my $skip_check = $opt{j} && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
127 print curr_time," updating fulltext:";
134 my $search = $search_module->new( $index_node_url );
142 if (defined($host_id) && defined($share_id) && defined($num)) {
149 @data = ( $host_id, $share_id, $num );
152 my $limit = sprintf('LIMIT '.EST_CHUNK.' OFFSET %d', $offset);
154 my $sth = $dbh->prepare(qq{
158 shares.name AS sname,
159 -- shares.share AS sharename,
160 files.backupnum AS backupnum,
161 -- files.name AS filename,
162 files.path AS filepath,
166 files.shareid AS shareid,
167 backups.date AS backup_date
169 INNER JOIN shares ON files.shareID=shares.ID
170 INNER JOIN hosts ON hosts.ID = shares.hostID
171 INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
176 $sth->execute(@data);
177 $results = $sth->rows;
180 print " - no new files\n";
187 my $t = shift || return;
188 my $iso = BackupPC::Lib::timeStamp($t);
193 while (my $row = $sth->fetchrow_hashref()) {
194 next if $search->exists( $row );
195 $search->add_doc( $row );
201 $offset += EST_CHUNK;
203 } while ($results == EST_CHUNK);
205 my $dur = (time() - $t) || 1;
206 printf(" [%.2f/s dur: %s]\n",
216 if ( ( $opt{i} || $opt{j} ) && !$opt{c} ) {
218 print "force update of Hyper Estraier index ";
219 print "by -i flag" if ($opt{i});
220 print "by -j flag" if ($opt{j});
228 my $index = shift || return;
229 my ($table,$col,$unique) = split(/:/, $index);
232 print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
233 $dbh->do(qq{ create $unique index $index on $table($col) });
236 print "creating tables...\n";
240 ID SERIAL PRIMARY KEY,
241 name VARCHAR(30) NOT NULL,
245 create table shares (
246 ID SERIAL PRIMARY KEY,
247 hostID INTEGER NOT NULL references hosts(id),
248 name VARCHAR(30) NOT NULL,
249 share VARCHAR(200) NOT NULL
253 ID SERIAL PRIMARY KEY,
254 num INTEGER NOT NULL,
255 name VARCHAR(255) NOT NULL,
259 create table backups (
261 hostID INTEGER NOT NULL references hosts(id),
262 num INTEGER NOT NULL,
263 date integer NOT NULL,
264 type CHAR(4) not null,
265 shareID integer not null references shares(id),
266 size bigint not null,
267 inc_size bigint not null default -1,
268 inc_deleted boolean default false,
269 parts integer not null default 0,
275 shareID INTEGER NOT NULL references shares(id),
276 backupNum INTEGER NOT NULL,
277 name VARCHAR(255) NOT NULL,
278 path VARCHAR(255) NOT NULL,
279 date integer NOT NULL,
280 type INTEGER NOT NULL,
281 size bigint NOT NULL,
285 create table archive (
288 total_size bigint default -1,
290 username varchar(20) not null,
291 date timestamp default now(),
295 create table archive_backup (
296 archive_id int not null references archive(id) on delete cascade,
297 backup_id int not null references backups(id),
298 primary key(archive_id, backup_id)
301 create table archive_burned (
302 archive_id int references archive(id),
303 date timestamp default now(),
304 part int not null default 1,
305 copy int not null default 1,
306 iso_size bigint default -1
309 create table backup_parts (
311 backup_id int references backups(id),
312 part_nr int not null check (part_nr > 0),
313 tar_size bigint not null check (tar_size > 0),
314 size bigint not null check (size > 0),
316 items int not null check (items > 0),
317 date timestamp default now(),
321 -- report backups and corresponding dvd
323 create view backups_on_dvds as
326 hosts.name || ':' || shares.name as share,
328 backups.type as type,
329 abstime(backups.date) as backup_date,
330 backups.size as size,
331 backups.inc_size as gzip_size,
332 archive.id as archive_id,
335 join shares on backups.shareid=shares.id
336 join hosts on shares.hostid = hosts.id
337 left outer join archive_backup on backups.id = archive_backup.backup_id
338 left outer join archive on archive_backup.archive_id = archive.id
339 where backups.parts > 0 and size > 0
340 order by backups.date
344 print "creating indexes: ";
346 foreach my $index (qw(
359 archive_burned:archive_id
360 backup_parts:backup_id,part_nr:unique
365 print " creating sequence: ";
366 foreach my $seq (qw/dvd_nr/) {
368 $dbh->do( qq{ CREATE SEQUENCE $seq } );
371 print " creating triggers ";
372 $dbh->do( <<__END_OF_TRIGGER__ );
374 create or replace function backup_parts_check() returns trigger as '
380 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
381 if (TG_OP=''UPDATE'') then
383 b_parts := new.parts;
384 elsif (TG_OP = ''INSERT'') then
386 b_parts := new.parts;
388 b_counted := (select count(*) from backup_parts where backup_id = b_id);
389 -- raise notice ''backup % parts %'', b_id, b_parts;
390 if ( b_parts != b_counted ) then
391 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
397 create trigger do_backup_parts_check
398 after insert or update or delete on backups
399 for each row execute procedure backup_parts_check();
401 create or replace function backup_backup_parts_check() returns trigger as '
407 if (TG_OP = ''INSERT'') then
408 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
409 b_id = new.backup_id;
410 my_part_nr = new.part_nr;
411 execute ''update backups set parts = parts + 1 where id = '' || b_id;
412 elsif (TG_OP = ''DELETE'') then
413 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
414 b_id = old.backup_id;
415 my_part_nr = old.part_nr;
416 execute ''update backups set parts = parts - 1 where id = '' || b_id;
418 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
419 if ( my_part_nr != calc_part ) then
420 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;
426 create trigger do_backup_backup_parts_check
427 after insert or update or delete on backup_parts
428 for each row execute procedure backup_backup_parts_check();
438 ## delete data before inseting ##
441 foreach my $table (qw(files dvds backups shares hosts)) {
443 $dbh->do(qq{ DELETE FROM $table });
450 ## insert new values ##
453 $hosts = $bpc->HostInfoRead();
459 $sth->{insert_hosts} = $dbh->prepare(qq{
460 INSERT INTO hosts (name, IP) VALUES (?,?)
463 $sth->{hosts_by_name} = $dbh->prepare(qq{
464 SELECT ID FROM hosts WHERE name=?
467 $sth->{backups_count} = $dbh->prepare(qq{
470 WHERE hostID=? AND num=? AND shareid=?
473 $sth->{insert_backups} = $dbh->prepare(qq{
474 INSERT INTO backups (hostID, num, date, type, shareid, size)
475 VALUES (?,?,?,?,?,-1)
478 $sth->{update_backups_size} = $dbh->prepare(qq{
479 UPDATE backups SET size = ?
480 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
483 $sth->{insert_files} = $dbh->prepare(qq{
485 (shareID, backupNum, name, path, date, type, size)
486 VALUES (?,?,?,?,?,?,?)
489 my @hosts = keys %{$hosts};
492 foreach my $host_key (@hosts) {
494 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
496 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
498 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
499 $sth->{insert_hosts}->execute(
500 $hosts->{$host_key}->{'host'},
501 $hosts->{$host_key}->{'ip'}
504 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
508 # get backups for a host
509 my @backups = $bpc->BackupInfoRead($hostname);
510 my $incs = scalar @backups;
512 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
513 $hosts->{$host_key}->{'host'},
518 print $host_header unless ($opt{q});
523 foreach my $backup (@backups) {
526 last if ($opt{m} && $inc_nr > $opt{m});
528 my $backupNum = $backup->{'num'};
529 my @backupShares = ();
531 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
532 $hosts->{$host_key}->{'host'},
533 $inc_nr, $incs, $backupNum,
534 $backup->{type} || '?',
535 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
536 strftime($t_fmt,localtime($backup->{startTime})),
537 fmt_time($backup->{endTime} - $backup->{startTime})
539 print $share_header unless ($opt{q});
541 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
543 foreach my $share ($files->shareList($backupNum)) {
547 $shareID = getShareID($share, $hostID, $hostname);
549 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
550 my ($count) = $sth->{backups_count}->fetchrow_array();
551 # skip if allready in database!
552 next if ($count > 0);
554 # dump host and share header for -q
558 $host_header = undef;
564 print curr_time," ", $share;
566 $sth->{insert_backups}->execute(
569 $backup->{'endTime'},
570 substr($backup->{'type'},0,4),
574 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
577 $sth->{update_backups_size}->execute(
581 $backup->{'endTime'},
582 substr($backup->{'type'},0,4),
593 my $dur = (time() - $t) || 1;
594 printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
596 ($size / 1024 / 1024),
601 hest_update($hostID, $shareID, $backupNum) if ($nf + $nd > 0);
610 print "total duration: ",fmt_time(time() - $start_t),"\n";
616 my ($share, $hostID, $hostname) = @_;
618 $sth->{share_id} ||= $dbh->prepare(qq{
619 SELECT ID FROM shares WHERE hostID=? AND name=?
622 $sth->{share_id}->execute($hostID,$share);
624 my ($id) = $sth->{share_id}->fetchrow_array();
626 return $id if (defined($id));
628 $sth->{insert_share} ||= $dbh->prepare(qq{
634 my $drop_down = $hostname . '/' . $share;
635 $drop_down =~ s#//+#/#g;
637 $sth->{insert_share}->execute($hostID,$share, $drop_down);
638 return $dbh->last_insert_id(undef,undef,'shares',undef);
646 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
648 return $beenThere->{$key} if (defined($beenThere->{$key}));
650 $sth->{file_in_db} ||= $dbh->prepare(qq{
652 WHERE shareID = ? and
655 ( date = ? or date = ? or date = ? )
659 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
660 $sth->{file_in_db}->execute(@param);
661 my $rows = $sth->{file_in_db}->rows;
662 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
664 $beenThere->{$key}++;
666 $sth->{'insert_files'}->execute(@data) unless ($rows);
670 ####################################################
671 # recursing through filesystem structure and #
672 # and returning flattened files list #
673 ####################################################
674 sub recurseDir($$$$$$$$) {
676 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
678 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
680 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
685 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
686 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
688 # first, add all the entries in current directory
689 foreach my $path_key (keys %{$filesInBackup}) {
690 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
695 $filesInBackup->{$path_key}->{'relPath'},
696 $filesInBackup->{$path_key}->{'mtime'},
697 $filesInBackup->{$path_key}->{'type'},
698 $filesInBackup->{$path_key}->{'size'}
701 my $key = join(" ", (
705 $filesInBackup->{$path_key}->{'mtime'},
706 $filesInBackup->{$path_key}->{'size'}
709 my $key_dst_prev = join(" ", (
713 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
714 $filesInBackup->{$path_key}->{'size'}
717 my $key_dst_next = join(" ", (
721 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
722 $filesInBackup->{$path_key}->{'size'}
727 ! defined($beenThere->{$key}) &&
728 ! defined($beenThere->{$key_dst_prev}) &&
729 ! defined($beenThere->{$key_dst_next}) &&
730 ! ($found = found_in_db($key, @data))
732 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
734 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
735 $new_dirs++ unless ($found);
736 print STDERR " dir\n" if ($debug >= 2);
738 $new_files++ unless ($found);
739 print STDERR " file\n" if ($debug >= 2);
741 $size += $filesInBackup->{$path_key}->{'size'} || 0;
744 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
747 my $full_path = $dir . '/' . $path_key;
748 push @stack, $full_path;
749 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
751 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
763 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
765 while ( my $dir = shift @stack ) {
766 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
767 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
776 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);