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/$pid_path",
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);
193 my $dur = (time() - $t) || 1;
194 printf(" [%.2f/s dur: %s]\n",
204 if ( ( $opt{i} || $opt{j} ) && !$opt{c} ) {
206 print "force update of Hyper Estraier index ";
207 print "by -i flag" if ($opt{i});
208 print "by -j flag" if ($opt{j});
216 my $index = shift || return;
217 my ($table,$col,$unique) = split(/:/, $index);
220 print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";
221 $dbh->do(qq{ create $unique index $index on $table($col) });
224 print "creating tables...\n";
228 ID SERIAL PRIMARY KEY,
229 name VARCHAR(30) NOT NULL,
233 create table shares (
234 ID SERIAL PRIMARY KEY,
235 hostID INTEGER NOT NULL references hosts(id),
236 name VARCHAR(30) NOT NULL,
237 share VARCHAR(200) NOT NULL
241 ID SERIAL PRIMARY KEY,
242 num INTEGER NOT NULL,
243 name VARCHAR(255) NOT NULL,
247 create table backups (
249 hostID INTEGER NOT NULL references hosts(id),
250 num INTEGER NOT NULL,
251 date integer NOT NULL,
252 type CHAR(4) not null,
253 shareID integer not null references shares(id),
254 size bigint not null,
255 inc_size bigint not null default -1,
256 inc_deleted boolean default false,
257 parts integer not null default 0,
263 shareID INTEGER NOT NULL references shares(id),
264 backupNum INTEGER NOT NULL,
265 name VARCHAR(255) NOT NULL,
266 path VARCHAR(255) NOT NULL,
267 date integer NOT NULL,
268 type INTEGER NOT NULL,
269 size bigint NOT NULL,
273 create table archive (
276 total_size bigint default -1,
278 username varchar(20) not null,
279 date timestamp default now(),
283 create table archive_backup (
284 archive_id int not null references archive(id) on delete cascade,
285 backup_id int not null references backups(id),
286 primary key(archive_id, backup_id)
289 create table archive_burned (
290 archive_id int references archive(id),
291 date timestamp default now(),
292 part int not null default 1,
293 copy int not null default 1,
294 iso_size bigint default -1
297 create table backup_parts (
299 backup_id int references backups(id),
300 part_nr int not null check (part_nr > 0),
301 tar_size bigint not null check (tar_size > 0),
302 size bigint not null check (size > 0),
304 items int not null check (items > 0),
305 date timestamp default now(),
309 -- report backups and corresponding dvd
311 create view backups_on_dvds as
314 hosts.name || ':' || shares.name as share,
316 backups.type as type,
317 abstime(backups.date) as backup_date,
318 backups.size as size,
319 backups.inc_size as gzip_size,
320 archive.id as archive_id,
323 join shares on backups.shareid=shares.id
324 join hosts on shares.hostid = hosts.id
325 left outer join archive_backup on backups.id = archive_backup.backup_id
326 left outer join archive on archive_backup.archive_id = archive.id
327 where backups.parts > 0 and size > 0
328 order by backups.date
332 print "creating indexes: ";
334 foreach my $index (qw(
347 archive_burned:archive_id
348 backup_parts:backup_id,part_nr:unique
353 print " creating sequence: ";
354 foreach my $seq (qw/dvd_nr/) {
356 $dbh->do( qq{ CREATE SEQUENCE $seq } );
359 print " creating triggers ";
360 $dbh->do( <<__END_OF_TRIGGER__ );
362 create or replace function backup_parts_check() returns trigger as '
368 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
369 if (TG_OP=''UPDATE'') then
371 b_parts := new.parts;
372 elsif (TG_OP = ''INSERT'') then
374 b_parts := new.parts;
376 b_counted := (select count(*) from backup_parts where backup_id = b_id);
377 -- raise notice ''backup % parts %'', b_id, b_parts;
378 if ( b_parts != b_counted ) then
379 raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
385 create trigger do_backup_parts_check
386 after insert or update or delete on backups
387 for each row execute procedure backup_parts_check();
389 create or replace function backup_backup_parts_check() returns trigger as '
395 if (TG_OP = ''INSERT'') then
396 -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
397 b_id = new.backup_id;
398 my_part_nr = new.part_nr;
399 execute ''update backups set parts = parts + 1 where id = '' || b_id;
400 elsif (TG_OP = ''DELETE'') then
401 -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
402 b_id = old.backup_id;
403 my_part_nr = old.part_nr;
404 execute ''update backups set parts = parts - 1 where id = '' || b_id;
406 calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
407 if ( my_part_nr != calc_part ) then
408 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;
414 create trigger do_backup_backup_parts_check
415 after insert or update or delete on backup_parts
416 for each row execute procedure backup_backup_parts_check();
426 ## delete data before inseting ##
429 foreach my $table (qw(files dvds backups shares hosts)) {
431 $dbh->do(qq{ DELETE FROM $table });
438 ## insert new values ##
441 $hosts = $bpc->HostInfoRead();
447 $sth->{insert_hosts} = $dbh->prepare(qq{
448 INSERT INTO hosts (name, IP) VALUES (?,?)
451 $sth->{hosts_by_name} = $dbh->prepare(qq{
452 SELECT ID FROM hosts WHERE name=?
455 $sth->{backups_count} = $dbh->prepare(qq{
458 WHERE hostID=? AND num=? AND shareid=?
461 $sth->{insert_backups} = $dbh->prepare(qq{
462 INSERT INTO backups (hostID, num, date, type, shareid, size)
463 VALUES (?,?,?,?,?,-1)
466 $sth->{update_backups_size} = $dbh->prepare(qq{
467 UPDATE backups SET size = ?
468 WHERE hostID = ? and num = ? and date = ? and type =? and shareid = ?
471 $sth->{insert_files} = $dbh->prepare(qq{
473 (shareID, backupNum, name, path, date, type, size)
474 VALUES (?,?,?,?,?,?,?)
477 my @hosts = keys %{$hosts};
480 foreach my $host_key (@hosts) {
482 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
484 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
486 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
487 $sth->{insert_hosts}->execute(
488 $hosts->{$host_key}->{'host'},
489 $hosts->{$host_key}->{'ip'}
492 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
496 # get backups for a host
497 my @backups = $bpc->BackupInfoRead($hostname);
498 my $incs = scalar @backups;
500 my $host_header = sprintf("host %s [%d/%d]: %d increments\n",
501 $hosts->{$host_key}->{'host'},
506 print $host_header unless ($opt{q});
511 foreach my $backup (@backups) {
514 last if ($opt{m} && $inc_nr > $opt{m});
516 my $backupNum = $backup->{'num'};
517 my @backupShares = ();
519 my $share_header = sprintf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
520 $hosts->{$host_key}->{'host'},
521 $inc_nr, $incs, $backupNum,
522 $backup->{type} || '?',
523 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
524 strftime($t_fmt,localtime($backup->{startTime})),
525 fmt_time($backup->{endTime} - $backup->{startTime})
527 print $share_header unless ($opt{q});
529 my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
531 foreach my $share ($files->shareList($backupNum)) {
535 $shareID = getShareID($share, $hostID, $hostname);
537 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
538 my ($count) = $sth->{backups_count}->fetchrow_array();
539 # skip if allready in database!
540 next if ($count > 0);
542 # dump host and share header for -q
546 $host_header = undef;
552 print curr_time," ", $share;
554 $sth->{insert_backups}->execute(
557 $backup->{'endTime'},
558 substr($backup->{'type'},0,4),
562 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
565 $sth->{update_backups_size}->execute(
569 $backup->{'endTime'},
570 substr($backup->{'type'},0,4),
581 my $dur = (time() - $t) || 1;
582 printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
584 ($size / 1024 / 1024),
589 hest_update($hostID, $shareID, $backupNum) if ($nf + $nd > 0);
598 print "total duration: ",fmt_time(time() - $start_t),"\n";
604 my ($share, $hostID, $hostname) = @_;
606 $sth->{share_id} ||= $dbh->prepare(qq{
607 SELECT ID FROM shares WHERE hostID=? AND name=?
610 $sth->{share_id}->execute($hostID,$share);
612 my ($id) = $sth->{share_id}->fetchrow_array();
614 return $id if (defined($id));
616 $sth->{insert_share} ||= $dbh->prepare(qq{
622 my $drop_down = $hostname . '/' . $share;
623 $drop_down =~ s#//+#/#g;
625 $sth->{insert_share}->execute($hostID,$share, $drop_down);
626 return $dbh->last_insert_id(undef,undef,'shares',undef);
634 my ($key, $shareID,undef,$name,$path,$date,undef,$size) = @_;
636 return $beenThere->{$key} if (defined($beenThere->{$key}));
638 $sth->{file_in_db} ||= $dbh->prepare(qq{
640 WHERE shareID = ? and
643 ( date = ? or date = ? or date = ? )
647 my @param = ($shareID,$path,$size,$date, $date-$dst_offset, $date+$dst_offset);
648 $sth->{file_in_db}->execute(@param);
649 my $rows = $sth->{file_in_db}->rows;
650 print STDERR "## found_in_db($shareID,$path,$date,$size) ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
652 $beenThere->{$key}++;
654 $sth->{'insert_files'}->execute(@data) unless ($rows);
658 ####################################################
659 # recursing through filesystem structure and #
660 # and returning flattened files list #
661 ####################################################
662 sub recurseDir($$$$$$$$) {
664 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
666 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
668 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
673 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
674 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
676 # first, add all the entries in current directory
677 foreach my $path_key (keys %{$filesInBackup}) {
678 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
683 $filesInBackup->{$path_key}->{'relPath'},
684 $filesInBackup->{$path_key}->{'mtime'},
685 $filesInBackup->{$path_key}->{'type'},
686 $filesInBackup->{$path_key}->{'size'}
689 my $key = join(" ", (
693 $filesInBackup->{$path_key}->{'mtime'},
694 $filesInBackup->{$path_key}->{'size'}
697 my $key_dst_prev = join(" ", (
701 $filesInBackup->{$path_key}->{'mtime'} - $dst_offset,
702 $filesInBackup->{$path_key}->{'size'}
705 my $key_dst_next = join(" ", (
709 $filesInBackup->{$path_key}->{'mtime'} + $dst_offset,
710 $filesInBackup->{$path_key}->{'size'}
715 ! defined($beenThere->{$key}) &&
716 ! defined($beenThere->{$key_dst_prev}) &&
717 ! defined($beenThere->{$key_dst_next}) &&
718 ! ($found = found_in_db($key, @data))
720 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
722 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
723 $new_dirs++ unless ($found);
724 print STDERR " dir\n" if ($debug >= 2);
726 $new_files++ unless ($found);
727 print STDERR " file\n" if ($debug >= 2);
729 $size += $filesInBackup->{$path_key}->{'size'} || 0;
732 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
735 my $full_path = $dir . '/' . $path_key;
736 push @stack, $full_path;
737 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
739 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
751 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
753 while ( my $dir = shift @stack ) {
754 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
755 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
764 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);